Parbhani,
I disagree that unfragging a tablespace is "a massive operation". If I have a badly fragmented tablespace and fragmented tables (from swiss cheese holes amongst the blocks), we reorganize and defragment at a rate of about 360MB/minute (depending upon processor).
We use this process:
1) Starting with smaller tablespaces, create a second tablespace (named "xyz2" for old tablespace "xyz"
.
2) Run the following script that creates the SQL to rebuild tables and indexes for an entire tablespace:
set echo off
set feedback off
accept tsname prompt "Which tablespace do you want to reorganize?: "
set pagesize 0
set verify off
set trimspool on
spool TempSwissCheese&tsname..sql
prompt Set echo on
prompt set feedback off
select 'alter table '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
' */ move parallel nologging tablespace &tsname'||
'2;'
from dba_extents
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name
order by sum(bytes) desc,owner, segment_name
/
select 'alter index '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
' */ rebuild parallel tablespace &tsname'||'2;'
from dba_extents
where tablespace_name = upper('&tsname') and segment_type = 'INDEX'
group by owner,segment_name
order by sum(bytes) desc,owner, segment_name
/
prompt set feedback on
spool off
prompt
prompt Wrote 'TempSwissCheese&tsname..sql'
prompt
set pagesize 35
3) During a time when there are no users accessing the tablespace under reorganization, run the 'TempSwissCheese&tsname..sql' to MOVE table and indexes to the new tablespace.
4) Since the Oracle "MOVE" statement does not work for tables with a LONG column, run the following script that creates the EXP and IMP statements for tables with a LONG column, and script the DROP commands for those tables from the old tablespace:
set pagesize 0
set trimspool on
set linesize 500
set feedback off
set verify off
set echo off
accept tsname prompt "Enter the tablespace name that holds the tables with LONGs: "
accept syspw prompt "Enter the password for SYS: "
spool TempExp&tsname..bat
select 'exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y '
||'file='
||owner||'.'||segment_name||'.dump tables='||owner||'.'||segment_name
||' userid=sys/&syspw@'||instance_name
from dba_extents, v$instance
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name,instance_name
order by sum(bytes),owner,segment_name
/
spool off
prompt
prompt Wrote "TempExp&tsname..bat"
prompt
spool TempDropLongs&tsname..sql
prompt set echo on
prompt set feedback off
select 'drop table '||owner||'.'||table_name||' cascade constraints;'
from dba_tables
where tablespace_name = upper('&tsname')
order by owner,table_name
/
select 'alter user '||username||' default tablespace '||default_tablespace||'2;'
from dba_users
where default_tablespace = upper('&tsname')
order by username
/
spool off
prompt
prompt Wrote "TempDropLongs&tsname..sql"
prompt
spool TempImp&tsname..bat
select 'imp buffer=15000000 grants=y feedback=1000 full=Y file='
||owner||'.'||segment_name||'.dump '
||' userid=sys/&syspw@'||instance_name
from dba_extents, v$instance
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name,instance_name
order by sum(bytes),owner,segment_name
/
spool off
set feedback on
set linesize 180
prompt
prompt 'Wrote "TempImp&tsname..bat"'
prompt
5) At the o/s prompt run the "TempExp&tsname..bat" script for the tablespace.
6) Run the "TempDropLongs&tsname..sql" script to get rid of the tablespace's tables with a LONG column.
7) At the o/s prompt run the "TempImp&tsname..bat" to re- create the tables with a LONG column.
8) At this point, the old, fragmented tablespace should be empty. "DROP TABLESPACE old_tablespace_name;"
9) At the o/s prompt erase or rm the files that supported the old tablespace, reclaiming the space for the o/s.
10) Repeat steps 1-9 for other fragmented tablespaces.
Cheers,
Dave,
Sandy, Utah, USA @ 03:49 GMT, 20:49 Mountain Time
