Hi,
I want to reclaim deleted space in database 11.2.0.4. How can I reclaim deleted space in database level
I have done below steps
Table Size:
select bytes/1024/1024/1024 from dba_segments where segment_name like '%IBE_CT_RELATED_ITEMS%';
29.8336
Database Size:
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual
1658.619
Truncate the table:
SQL> Truncate table IBE.IBE_CT_RELATED_ITEMS;
Shrink the table:
SQL> alter table IBE.IBE_CT_RELATED_ITEMS enable row movement;
Table altered.
SQL> alter table IBE.IBE_CT_RELATED_ITEMS shrink space cascade;
Table altered.
SQL> SQL>
SQL> alter table IBE.IBE_CT_RELATED_ITEMS disable row movement;
Table altered.
Rebuild indexes:
Re-Build the Indexes if any
select owner, index_name, status from all_indexes where table_name = 'IBE_CT_RELATED_ITEMS';
OWNER INDEX_NAME STATUS
IBE IBE_CT_RELATED_ITEMS_N1 VALID
IBE IBE_CT_RELATED_ITEMS_N2 VALID
IBE IBE_CT_RELATED_ITEMS_N3 VALID
IBE IBE_CT_RELATED_ITEMS_U1 VALID
SQL> alter index IBE.IBE_CT_RELATED_ITEMS_N1 rebuild;
Index altered.
SQL> alter index IBE.IBE_CT_RELATED_ITEMS_N2 rebuild;
alter index IBE.IBE_CT_RELATED_ITEMS_N3 rebuild;
alter index IBE.IBE_CT_RELATED_ITEMS_U1 rebuild;
Index altered.
SQL>
Index altered.
SQL>
Index altered.
Deallocate unused Space
SQL> ALTER TABLE IBE.IBE_CT_RELATED_ITEMS DEALLOCATE UNUSED KEEP 0;
Table altered.
Table Size:
select bytes/1024/1024/1024 from dba_segments where segment_name like '%IBE_CT_RELATED_ITEMS%';
1.2943
Database Size:
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual
1658.619
Thanks,
Ramaraju