The most efficient/effective way to defrag/shrink a big tablespace in oracle is to migrate the tables/indexes to new tablespaces.
Here below are the steps:
Step 1. find tablespace “BMW” whether fragmented or not
Step 2. Log in with the dba account and create new tablespaces for the database user.
Sample SQL:
create tablespace BMW2 datafile ‘/opt/oracle/storage/BMW2.dbf’ size 256m autoextend on next 128m maxsize unlimited;
Step 3. Log in with the DB owner username/password
Step 4.Migrate the tables
Generate the table migration script
spool /tmp/username/moveTables.sql
select ‘alter table ‘ || SEGMENT_NAME || ‘ move tablespace BMW2;’
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name=‘BMW’ and segment_type=‘TABLE’
order by FILE_NAME,segment_name;
spool off;
Step 5.Migrate the Indexes
Generate the index migration script
spool /tmp/username/MoveIndex.sql
select ‘alter index ‘ || SEGMENT_NAME || ‘ rebuild tablespace BMW2;’
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name=‘BMW’ and segment_type=‘INDEX’
order by FILE_NAME,segment_name;
spool off;
Step 6. Migrate the LOB/LOBSegments if possible
spool /tmp/username/MoveLob.sql
select ‘ALTER TABLE ‘ || table_name || ‘ move lob(‘ || COLUMN_NAME || ‘) STORE AS (TABLESPACE BMW2);’
from dba_tab_columns
where owner=‘BMW’ and data_type=‘CLOB’;
spool off;
Step 7. check if anything missing in the original tablespace
set lines 300
col owner format A26
col segment_name format A26
col segment_type format A26
col tablespace_name format A26
col relative_fno format 99999
col file_name format A50
SELECT owner, segment_name, segment_type,a.tablespace_name, a.relative_fno, b.file_name
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name=‘BMW’
order by FILE_NAME,segment_name;
Step 8. Never forget to change the default tablespace of the user to the new one
ALTER USER default tablespace BMW2;
Step 9. change tablespace offline
alter tablespace BMW offline;
