Oracle Tablespace Fragmentation
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 “benz” whether fragmented or not
Step 1.find tablespace “benz” whether fragmented or not
select
tablespace_name,
count(*) free_chunks,
decode(
round((max(bytes) / 1024000),2),
null,0,
round((max(bytes) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),
0) fragmentation_index
from
sys.dba_free_space
group by
tablespace_name
order by
2 desc, 1;
Step 2.Login with dba account and create new tablespaces for the database user.
Sample SQL:create tablespace BENZ2 datafile ‘/opt/oracle/storage/BENZ2.dbf’ size 256m autoextend on next 128m maxsize unlimited;
Step 3.Login with the db owner username/password
Step 3.Login 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 BENZ2;’
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name=‘BENZ’ 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 BENZ2;’
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name=‘BENZ’ 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 BENZ2);’
from dba_tab_columns
where owner=‘BENZ’ and data_type=‘CLOB’;
spool off;
Step 7.check if anything missing in the original tablespace
set lin 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=‘BENZ’
order by FILE_NAME,segment_name;
Step 8. Never forget to change the default tablespace of the user to the new one
ALTER USER <userName> default tablespace BENZ2;
Step 9.change tablespace offlinealter tablespace BENZ offline;
Step 9.change tablespace offlinealter tablespace BENZ offline;
Comments
Post a Comment