Oracle Database Discussions

Hello,

My table PRICE_T table gets deleted and inserted almost 50 million records everyday.
Now it takes too much time to perform this operation because of TABLE fragmented.

i can use "CREATE table as select” option to remove fragmentation from table.
but i dont want to use this option.

so i hv tried ALTER TABLE SHRINK SPACE as i m using ORACLE 10g

alter table price_t enable row movement;

alter table price_t shrink space

alter table price_t disable row movement;

and i have applied following query to check table gets remove free space but still having free space after applying ALTER SPACE COMMAND

select table_name,round((blocks*8),2)||'kb' "size1",round((num_rows*avg_row_len/1024),2)||'kb' "size"
from DBA_tables
where table_name = 'PRICE_T';

Tablespace in PRICE_T table fall into is SEGMENT SPACE MANAGEMENT AUTO

please help me to remove fragmentation from table using SHRINK command.


Thanks.