SET COLSEP ','
SET PAGESIZE 50000
SET LINESIZE 2000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET HEADING ON
SPOOL lob_storage_summary.csv
SELECT
l.OWNER,
l.TABLE_NAME,
l.COLUMN_NAME,
l.SEGMENT_NAME,
l.TABLESPACE_NAME,
DECODE(l.SECUREFILE, 'NO', 'BASICFILE', 'YES', 'SECUREFILE', 'UNKNOWN') AS LOB_TYPE,
l.IN_ROW,
l.CHUNK,
l.PCTVERSION,
l.RETENTION,
DECODE(l.COMPRESSION, 'ENABLED', 'COMPRESSED', 'DISABLED', 'UNCOMPRESSED', 'UNKNOWN') AS COMPRESSION_STATUS,
NVL(s.BYTES/1024/1024, 0) AS SEGMENT_SIZE_MB
FROM
DBA_LOBS l
LEFT JOIN
DBA_SEGMENTS s
ON l.SEGMENT_NAME = s.SEGMENT_NAME
AND l.OWNER = s.OWNER
AND s.SEGMENT_TYPE = 'LOBSEGMENT'
ORDER BY
l.OWNER, l.TABLE_NAME, l.COLUMN_NAME;
SPOOL OFF
0 comments:
Post a Comment