Wednesday, August 13, 2008

Size of the table including LOB segment

If you have a table having a column of LOB datatype, then we cant get the actual size of table from the following query.
SELECT SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS
WHERE SEGMENT_NAME='OLD_DATA';

Because the lob are stored in different segments other than the table segment. so we can get the size from the following  query,

SELECT SUM(BYTES)/1024/1024 MB
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN(
SELECT SEGMENT_NAME FROM DBA_LOBS DL
WHERE DL.TABLE_NAME='OLD_DATA'
AND DL.OWNER='PROD')
OR SEGMENT_NAME LIKE 'OLD_DATA' ;

No comments:

search engine

Custom Search