125 lines
3.5 KiB
SQL
125 lines
3.5 KiB
SQL
WITH
|
|
relevant_segments AS (
|
|
SELECT /*+ OPT_PARAM('_px_cdb_view_enabled' 'FALSE') */
|
|
s.con_id,
|
|
s.owner,
|
|
s.segment_name,
|
|
s.partition_name,
|
|
s.segment_type,
|
|
s.tablespace_name,
|
|
s.bytes,
|
|
s.blocks,
|
|
CASE
|
|
WHEN s.segment_type LIKE 'TABLE%' THEN t.num_rows
|
|
END AS num_rows,
|
|
CASE
|
|
WHEN s.segment_type LIKE 'TABLE%' THEN s.segment_name
|
|
WHEN s.segment_type LIKE 'LOB%' AND s.segment_type <> 'LOBINDEX' THEN l.table_name
|
|
WHEN s.segment_type LIKE 'INDEX%' OR s.segment_type = 'LOBINDEX' THEN i.table_name
|
|
END AS table_name,
|
|
CASE
|
|
WHEN s.segment_type LIKE 'LOB%' AND s.segment_type <> 'LOBINDEX' THEN l.index_name
|
|
WHEN s.segment_type LIKE 'INDEX%' OR s.segment_type = 'LOBINDEX' THEN i.index_name
|
|
END AS index_name,
|
|
CASE
|
|
WHEN s.segment_type LIKE 'LOB%' AND s.segment_type <> 'LOBINDEX' THEN l.column_name
|
|
END AS column_name
|
|
FROM cdb_users u,
|
|
cdb_segments s,
|
|
cdb_tables t,
|
|
cdb_lobs l,
|
|
cdb_indexes i
|
|
WHERE u.oracle_maintained = 'N'
|
|
AND u.common = 'NO'
|
|
AND s.con_id = u.con_id
|
|
AND s.owner = u.username
|
|
AND s.bytes > 0
|
|
AND s.blocks > 0
|
|
AND (s.segment_type LIKE 'TABLE%' OR s.segment_type LIKE 'LOB%' OR s.segment_type LIKE 'INDEX%')
|
|
AND t.con_id(+) = s.con_id
|
|
AND t.owner(+) = s.owner
|
|
AND t.table_name(+) = s.segment_name
|
|
AND l.con_id(+) = s.con_id
|
|
AND l.owner(+) = s.owner
|
|
AND l.segment_name(+) = s.segment_name
|
|
AND i.con_id(+) = s.con_id
|
|
AND i.owner(+) = s.owner
|
|
AND i.index_name(+) = s.segment_name
|
|
),
|
|
aggregated_segments AS (
|
|
SELECT s.con_id,
|
|
s.owner,
|
|
s.segment_name,
|
|
1 AS segments,
|
|
s.partition_name,
|
|
s.segment_type,
|
|
s.tablespace_name,
|
|
s.bytes,
|
|
s.blocks,
|
|
s.num_rows,
|
|
s.table_name,
|
|
s.index_name,
|
|
s.column_name
|
|
FROM relevant_segments s
|
|
WHERE s.bytes >= POWER(10, 7) -- include segments >= 10MB
|
|
AND s.segment_name NOT LIKE 'BIN$%==$0' -- exclude recycle bin segments
|
|
UNION ALL
|
|
SELECT s.con_id,
|
|
s.owner,
|
|
'segments_under_10mb' AS segment_name,
|
|
COUNT(*) AS segments,
|
|
NULL AS partition_name,
|
|
s.segment_type,
|
|
s.tablespace_name,
|
|
SUM(s.bytes) AS bytes,
|
|
SUM(s.blocks) AS blocks,
|
|
TO_NUMBER(NULL) AS num_rows,
|
|
NULL AS table_name,
|
|
NULL AS index_name,
|
|
NULL AS column_name
|
|
FROM relevant_segments s
|
|
WHERE s.bytes < POWER(10, 7) -- include segments < 10MB
|
|
AND s.segment_name NOT LIKE 'BIN$%==$0' -- exclude recycle bin segments
|
|
GROUP BY
|
|
s.con_id,
|
|
s.owner,
|
|
s.segment_type,
|
|
s.tablespace_name
|
|
UNION ALL
|
|
SELECT s.con_id,
|
|
s.owner,
|
|
'recycle_bin_segments' AS segment_name,
|
|
COUNT(*) AS segments,
|
|
NULL AS partition_name,
|
|
s.segment_type,
|
|
s.tablespace_name,
|
|
SUM(s.bytes) AS bytes,
|
|
SUM(s.blocks) AS blocks,
|
|
TO_NUMBER(NULL) AS num_rows,
|
|
NULL AS table_name,
|
|
NULL AS index_name,
|
|
NULL AS column_name
|
|
FROM relevant_segments s
|
|
WHERE s.segment_name LIKE 'BIN$%==$0' -- include recycle bin segments
|
|
GROUP BY
|
|
s.con_id,
|
|
s.owner,
|
|
s.segment_type,
|
|
s.tablespace_name
|
|
)
|
|
SELECT s.con_id,
|
|
s.owner,
|
|
s.segment_name,
|
|
s.segments,
|
|
s.partition_name,
|
|
s.segment_type,
|
|
s.tablespace_name,
|
|
s.bytes,
|
|
s.blocks,
|
|
s.num_rows,
|
|
s.table_name,
|
|
s.index_name,
|
|
s.column_name
|
|
FROM aggregated_segments s
|
|
/
|