why after truncate the table space not returned when view object size in tablespace?-CodePudding

I am always use TRUNCATE TABLE to free up space and always the space returned to free space

when I check the free space in oracle tablespace I found the free space increased always

I am using TOAD program to

TRUNCATE TABLE

But this time different I truncated 2 tables but its not free the table space and when I checked

the table size after truncated its still same size used before around 400 MB

why its not free the space , and how to free the space table already truncated and no data in it.

also I moved the table to another tablespace after truncate but its moved with same size

ALTER TABLE CAS_NOSHOW MOVE TABLESPACE   TRNG;

how to free up space please your help .

I am using oracle 10g database

CodePudding user response：

If you're looking to recover space above and below the high water mark you can shrink the table.

To do this you need to have row movement enabled.


 create table t as
     select rownum x, lpad('x', 500, 'x') xx from dual connect by level <= 10000;
/

-- ALTER TABLE t SHRINK SPACE CHECK;

 select bytes from user_segments
    where  segment_name = 'T';
/

 delete t where x <= 9900;
/

select bytes from user_segments
 where  segment_name = 'T';
/

 alter table t enable row movement;
/

 alter table t shrink space CASCADE;
/

 select bytes from user_segments
    where  segment_name = 'T';