0

I'm trying to identify top tables by size in my Oracle DB, I tried with

select  segment_type,
        segment_name,
        sum(bytes/1024/1024) MB

from    user_segments s

where   segment_type like 'TABLE%'

group by segment_type,segment_name
order by 3 desc
;

and my top biggest table is ABC_TABLE. Querying USER_EXTENTS for the table

select  *
from    user_extents e
where   segment_name = 'ABC_TABLE'
;

I get 218 records, and querying USER_SEGMENTS again for this specific table this is what I get:

 SEGMENT_NAME   | PARTITION_NAME | SEGMENT_TYPE | SEGMENT_SUBTYPE | TABLESPACE_NAME | BYTES      | BLOCKS | EXTENTS | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | MAX_SIZE   | RETENTION | MINRETENTION | PCT_INCREASE | FREELISTS | FREELIST_GROUPS | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE 
 ABC_TABLE      |                | TABLE        | ASSM            | ABC             | 2341470208 | 285824 | 218     | 65536          | 1048576     | 1           | 2147483645  | 2147483645 |           |              |              |           |                 | DEFAULT     | DEFAULT     | DEFAULT          

What I don't understand is that by querying ABC_TABLE no records are shown, it seems to be empty. What am I getting wrong?

2

You might have empty blocks below the high water mark (HWM). Delete statement, by default, doesn't lower the HWM; however, TRUNCATE, CTAS to name few do (down to MINEXTENTS on the table). Thus the empty blocks are considered as used blocks.

Simple Demo:

SQL> create table t1(col1 varchar2(100));

Table created.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
     65536

SQL> insert into t1 select 'sample text of line'||level from dual connect by level<=2000;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
    131072

SQL> delete from t1;

2000 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
    131072

You see, the size of the table is still same though the table is empty.

SQL> truncate table t1;

Table truncated.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
     65536
  • Enlightening, thank you! But now... how can I resize the segment back to the space actually being occupied by the table? I mean, what if truncating is not an option because the table has some rows?? – Luigi Cortese Dec 2 '17 at 20:51
  • Export /import, CTAS, Table Redefinition, etc are some of the methods to lower the high water mark. – JSapkota Dec 3 '17 at 0:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.