set linesize 120
set pagesize 160
col table_name format a30
select
owner,
Table_name,
sum(bytes) Bytes,
Round(sum(bytes)/1024,2) as KB,
Round(sum(bytes)/1024/1024,2) as MB,
Round(sum(bytes)/1024/1024/1024,2) as GB
from
(Select
owner,
segment_name as Table_name,
bytes
from
dba_segments
union all
SELECT
i.table_name,
i.owner,
s.bytes
FROM
dba_indexes i,
dba_segments s
WHERE
s.segment_name = i.index_name
AND
s.owner = i.owner
AND
s.segment_type = 'INDEX'
UNION ALL
SELECT
l.table_name,
l.owner,
s.bytes
FROM
dba_lobs l,
dba_segments s
WHERE
s.segment_name = l.segment_name
AND
s.owner = l.owner
AND
s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT
l.table_name,
l.owner,
s.bytes
FROM
dba_lobs l,
dba_segments s
WHERE
s.segment_name = l.index_name
AND
s.owner = l.owner
AND
s.segment_type = 'LOBINDEX')
WHERE
owner in UPPER('&owner')
and
table_name in upper('&table_name')
GROUP BY
table_name,
owner
order by
MB desc;
set pagesize 160
col table_name format a30
select
owner,
Table_name,
sum(bytes) Bytes,
Round(sum(bytes)/1024,2) as KB,
Round(sum(bytes)/1024/1024,2) as MB,
Round(sum(bytes)/1024/1024/1024,2) as GB
from
(Select
owner,
segment_name as Table_name,
bytes
from
dba_segments
union all
SELECT
i.table_name,
i.owner,
s.bytes
FROM
dba_indexes i,
dba_segments s
WHERE
s.segment_name = i.index_name
AND
s.owner = i.owner
AND
s.segment_type = 'INDEX'
UNION ALL
SELECT
l.table_name,
l.owner,
s.bytes
FROM
dba_lobs l,
dba_segments s
WHERE
s.segment_name = l.segment_name
AND
s.owner = l.owner
AND
s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT
l.table_name,
l.owner,
s.bytes
FROM
dba_lobs l,
dba_segments s
WHERE
s.segment_name = l.index_name
AND
s.owner = l.owner
AND
s.segment_type = 'LOBINDEX')
WHERE
owner in UPPER('&owner')
and
table_name in upper('&table_name')
GROUP BY
table_name,
owner
order by
MB desc;
No comments:
Post a Comment