WITH
table_segs AS (
SELECT owner, segment_name AS table_name, SUM(bytes) AS bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
GROUP BY owner, segment_name
) ,index_segs AS (
SELECT i.owner, i.table_name, SUM(s.bytes) AS bytes
FROM dba_indexes i
JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name
WHERE s.segment_type = 'INDEX'
GROUP BY i.owner, i.table_name
) ,table_part_segs AS (
SELECT p.table_owner AS owner, p.table_name, SUM(s.bytes) AS bytes
FROM dba_segments s
JOIN dba_tab_partitions p ON s.owner = p.table_owner AND s.segment_name = p.partition_name
WHERE s.segment_type = 'TABLE PARTITION'
GROUP BY p.table_owner, p.table_name
) ,index_part_segs AS (
SELECT i.owner, i.table_name, SUM(s.bytes) AS bytes
FROM dba_segments s
JOIN dba_ind_partitions ip ON s.owner = ip.index_owner AND s.segment_name = ip.partition_name
JOIN dba_indexes i ON ip.index_owner = i.owner AND ip.index_name = i.index_name
WHERE s.segment_type = 'INDEX PARTITION'
GROUP BY i.owner, i.table_name
),lob_segs AS (
SELECT l.owner, l.table_name, SUM(s.bytes) AS bytes
FROM dba_lobs l
JOIN dba_segments s ON l.owner = s.owner AND l.segment_name = s.segment_name
WHERE s.segment_type = 'LOBSEGMENT'
GROUP BY l.owner, l.table_name
),lob_ind AS (
SELECT l.owner, l.table_name, SUM(s.bytes) AS bytes
FROM dba_lobs l
JOIN dba_segments s ON l.owner = s.owner AND l.segment_name = s.segment_name
WHERE s.segment_type = 'LOBINDEX'
GROUP BY l.owner, l.table_name
), tot as (
select * from (
SELECT
sys_context ('USERENV', 'DB_NAME')db_name,
t.owner,
t.table_name,
t.num_rows record_count,
ROUND(NVL(ts.bytes, 2) / 1073741824, 2) AS table_size_GB,
ROUND(NVL(ix.bytes, 0) / 1073741824, 2) AS index_size_GB,
ROUND(NVL(ls.bytes, 2) / 1073741824, 2) AS lob_segment_size_GB,
ROUND(NVL(li.bytes, 2) / 1073741824, 2) AS lob_index_size_GB,
ROUND(NVL(tp.bytes, 0) / 1073741824, 2) AS table_part_size_GB,
ROUND(NVL(ip.bytes, 0) / 1073741824, 2) AS index_part_size_GB,
ROUND((NVL(ts.bytes, 2) + NVL(tp.bytes, 0) + NVL(ip.bytes, 0) + NVL(ix.bytes, 0) + NVL(ls.bytes, 2) + nvl(li.bytes,2)) / 1073741824, 2) AS total_size_GB
FROM dba_tables t
LEFT JOIN table_segs ts ON t.owner = ts.owner AND t.table_name = ts.table_name
LEFT JOIN index_segs ix ON t.owner = ix.owner AND t.table_name = ix.table_name
LEFT JOIN table_part_segs tp ON t.owner = tp.owner AND t.table_name = tp.table_name
LEFT JOIN index_part_segs ip ON t.owner = ip.owner AND t.table_name = ip.table_name
LEFT JOIN lob_segs ls ON t.owner = ls.owner AND t.table_name = ls.table_name
left join lob_ind li ON t.owner = li.owner AND t.table_name = li.table_name
)
where
owner in (select username from dba_users where oracle_maintained='N') and
( total_size_gb >= 1 /*OR record_count >= 10000 */)
ORDER BY owner , total_size_GB DESC
)
select DB_NAME, OWNER, TABLE_NAME, RECORD_COUNT,TOTAL_SIZE_GB, TABLE_SIZE_GB, INDEX_SIZE_GB, LOB_SEGMENT_SIZE_GB, LOB_INDEX_SIZE_GB, TABLE_PART_SIZE_GB, INDEX_PART_SIZE_GB
From tot
;
No comments:
Post a Comment