SELECT ts.table_owner "owner",
ts.table_name "table name",
s.SEGMENT_NAME "segment name",
s.SEGMENT_TYPE "segment type",
s.SEGMENT_SUBTYPE "lob type",
s.PARTITION_NAME "lob part name",
lp.column_name "lob name",
lp.compression "lob compression",
lp.partition_name "table part name",
ts.num_rows "num rows",
lp.tablespace_name "tablespace",
ROUND(sum(s.bytes)/1024/1024/1024) "size GB"
FROM dba_segments s JOIN dba_lob_partitions lp
ON (s.partition_name = lp.lob_partition_name)
JOIN DBA_TAB_PARTITIONS ts
ON (TS.PARTITION_NAME = lp.PARTITION_NAME)
WHERE lp.table_name='MYTABLE'
AND ts.table_name='MYTABLE'
-- To limit the output to a specific tablespace, uncomment line below
AND s.tablespace_name='DATA1'
-- To limit output to specific table subpartitions only, uncomment the following row
--AND lp.subpartition_name like 'SYS_SUBP186786%'
AND s.segment_name IN ( SELECT lpt.lob_name
FROM dba_lob_partitions lpt
WHERE lpt.table_name IN ( 'MYTABLE' ) )
GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lp.COMPRESSION,/*lp.subpartition_name*/lp.partition_name, lp.column_name,ts.num_rows,lp.tablespace_name
/*
To limit output to lob subpartitions with a specific size,
uncomment the restriction above and change the operator to suit your needs ( <, >, =)
or use BETWEEN x AND y
*/
-- HAVING ROUND(sum(s.bytes)/1024/1024/1024) between 1 and 10
ORDER BY 12 DESC;
Output:
| owner | table name | segment name | segment type | lob type | lob part name | lob name | lob compression | table part name | num rows | tablespace | size GB |
|---|---|---|---|---|---|---|---|---|---|---|---|
| JIM | MYTABLE | SYS_LOB0000079505C00019$$ | LOB PARTITION | SECUREFILE | SYS_LOB_P3273 | MYLOB1 | NO | SYS_P3270 | 864051 |
DATA1 | 20 |
| JIM | MYTABLE | SYS_LOB0000079505C00019$$ | LOB PARTITION | SECUREFILE | SYS_LOB_P4684 | MYLOB1 | NO | SYS_P4681 | 593520 |
DATA1 | 17 |
| JIM | MYTABLE | SYS_LOB0000079505C00019$$ | LOB PARTITION | SECUREFILE | SYS_LOB_P5692 | MYLOB1 | NO | SYS_P5689 | 2021989 |
DATA1 | 16 |
For queries against a subpartitioned table, see this post.
No comments:
Post a Comment