Find size of a table and Associated segments ( index , lob )


COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
owner, table_name,segment_type, TRUNC(sum(bytes)/1024/1024/1024) Size_GB
FROM
(SELECT segment_name table_name, owner, bytes ,segment_type
FROM dba_segments  WHERE segment_type = ‘TABLE’
UNION ALL
SELECT i.table_name, i.owner, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name AND   s.owner = i.owner
AND   s.segment_type = ‘INDEX’
UNION ALL
SELECT l.table_name, l.owner, s.bytes ,segment_type
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = ‘LOBSEGMENT’
UNION ALL
SELECT l.table_name, l.owner, s.bytes ,segment_type
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = ‘LOBINDEX’)
WHERE owner =’&owner’
and table_name=’&table’
GROUP BY owner,table_name,segment_type
ORDER BY SUM(bytes) desc
/

Enter value for owner: SYS
old 22: WHERE owner =’&owner’
new 22: WHERE owner =’SYS’
Enter value for table: T1
old 23: and table_name=’&table’
new 23: and table_name=’T1′

Flash Recovery Area Usage

OWNER TABLE_NAME SEGMENT_TYPE SIZE_GB
———- ——————————– —————— ———-
SYS           T1                     TABLE                                1.2
SYS            T1                     INDEX                                1

Reference: http://ora10gadmin.blogspot.sg/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s