Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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
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 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;



Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools