REORG THE TABLESPACE

Tablespace Name :  APPS_TS_SUMMARY

Step 1 :

Collect the information about the Tablespace. i.e., find the objects in that Tablespace. Spool the below information to a log file .

Find the Invalids in the Database :
SQL > spool pre_infor.log
SQL > col owner for a12
SQL > col object_name for a30
SQL > save invalid_obj.sql
SQL > Select owner,object_type,count(*) from dba_objects where
 status=’INVALID’ group by owner,object_type;
No invalids are there belongs to that Tablespace.

Find any unused  Indexes :
      SQL > col tablespace_name for a15
      SQL > save unused_index.sql
      SQL > Select owner,index_name,tablespace_name from dba_indexes
  where status=’UNUSABLE’ order by owner;
No unusable indexes are there.

Find the Objects in the Tablespace :
SQL > save object.sql
SQL > Select tablespace_name,owner,segment_type,count(*) from
 dba_segments where tablespace_name='APPS_TS_SUMMARY' group by
 tablespace_name,owner,segment_type;

Find the count of segments in the Tablespace :
   SQL > select segment_type,count(*) from dba_segments where  
  tablespace_name=’APPS_TS_SUMMARY’ group by segment_type;

Find the Free Space in the Tablespace :
   SQL > save free.sql
   SQL > select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
     round((1-(b.fsz/a.tsz))*100)
    "Pct Used",round((b.fsz/a.tsz)*100)
     "Pct Free" from (select tablespace_name
tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
          where tablespace_name = 'APPS_TS_SUMMARY' group by
tablespace_name) a,(select tablespace_name
tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
          where tablespace_name = 'APPS_TS_SUMMARY' group by
          tablespace_name) b where a.tbl=b.tblsp;



Find the Total size of the Tablespace :
SQL > save total.sql
SQL > Select file_name,bytes/1024/1024 from dba_data_files where
 tablespace_name=’APPS_TS_SUMMARY’

Now stop capturing the information.
SQL > spool off

STEP 2

Create a new tablespace for Reorg.

SQL > create tablespace apps_reorg datafile
 '/oraDB/applmgr/globaldata/apps_reorg.dbf' size 750m
 autoextend on maxsize 1000m extent management local uniform size
 128k segment space management auto;

STEP 3

Export the tables which have LONG datatypes. These tables can be moved by using EXPORT/IMPORT only.

SQL > col owner for a8
SQL > col table_name for a30
SQL > select b.owner,a.table_name from dba_tables a,dba_tab_columns b
 where a.owner=b.owner and a.table_name=b.table_name and
 a.tablespace_name='APPS_TS_SUMMARY' and b.data_type='LONG';

There are no long column datatype in this tablespace. I there are any long columns, move these tables using export and import.

STEP 4

Find out the quotas for the users in the oldTablespace(APPS_TS_SUMMARY)
SQL > select username,bytes/1024 from dba_ts_quotas where
 tablespace_name=’APPS_TS_SUMMARY’;

Assign unlimited tablespace to the users for the new tablespace by using below sql.
SQL > set lines 120 head off pages 0
SQL> select 'alter user '||username||' quota unlimited  on
apps_ts_summary_reorg0;' from dba_ts_quotas where
tablespace_name='APPS_TS_SUMMARY';

Out Put :
SQL > alter user ZPB quota unlimited  on apps_ts_summary_reorg0;
Check for whether the quota is assigned or not by using the below query.

SQL > select username,bytes/1024,max_bytes/1024 from dba_ts_quotas where
tablespace_name=’APPS_TS_SUMMARY_REORG0’;
In the column of max_bytes it shows negative values.

STEP 5 ( time : 3 Min )

Move the tables to new tablespace. Generate the Dynamic SQL.
Spool mv_tables.sql
Set lines 120 head off pages 0 feed off
SQL > select 'alter table '||owner||'.'||table_name||' move tablespace
 APPS_TS_SUMMARY_REORG0;' from dba_tables where
 tablespace_name='APPS_TS_SUMMARY';
spool off

Edit the spool file.
$ sqlplus ‘/as sysdba’

SQL> set time on  timing on
SQL> spool mv_tables.log
SQL> select name from v$dtabase;
SQL> @ mv_tables.sql
SQL> spool off

Split the spool file into multiple files and run the files in multiple sessions, because it completes very fastly.

STEP 6

Move the Indexes to the new tablespace. Generate the below dynamic SQL
Spool mv_indexes1.sql
Set lines 120 head off pages 0 feed off
SQL > select 'alter index '||owner||'.'||index_name||' rebuild
 tablespace APPS_TS_SUMMARY_REORG0;' from dba_indexes where
 tablespace_name='APPS_TS_SUMMARY';
spool off
SQL> set time on timing on
SQL> spool mv_indexes.log
SQL> select name from v$database;
SQL> @ mv_indexes.sql
SQL> spool off

STEP 7

List out the LOB tables and move them to new tablespace.

SQL > col table_name for a25
SQL > col segment_name for a30
In 9i use the below query to find the LOB objects.
SQL > select a.owner,a.table_name,b.segment_name from dba_lobs a,
      dba_segments b where a.table_name=b.segment_name and
 b.tablespace_name='APPS_TS_SUMMARY';
There is only one segment.
In 10g use the below query.
SQL > select owner,table_name,segment_name from dba_lobs  where
      tablespace_name='APPS_TS_SUMMARY';
LOB table name :
Move this LOB segment to new Tablespace.
Dynamic Query :
In 9i.
SQL > select 'ALTER TABLE '||a.owner||'.'||a.table_name||' MOVE
 TABLESPACE APPS_TS_SUMMARY_REORG0 LOB('||a.COLUMN_NAME||') STORE
 AS lobsegment1(TABLESPACE APPS_TS_SUMMARY_REORG0 );'
 from dba_lobs a,dba_segments b where  
 b.tablespace_name='APPS_TS_SUMMARY' and
 a.segment_name=b.segment_name;
Out put :
ALTER TABLE INV.MTL_COPY_ORG_REPORT
    MOVE TABLESPACE  APPS_REORG LOB(ERROR_MSG)
    STORE AS lobsegment (TABLESPACE APPS_REORG );
In 10g :
SQL > select 'ALTER TABLE '||owner||'.'||table_name||' MOVE
 LOB('||COLUMN_NAME||') STORE AS lobsegment (TABLESPACE
 APPS_TS_SUMMARY_REORG0 );'
 from dba_lobs where tablespace_name='APPS_TS_SUMMARY';

STEP 8
Move Partioned indexes and Tables.
Partitioned Tables:
SQL >select 'alter table '||TABLE_OWNER||'.'||table_name||' move
partition '||partition_name||' tablespace
APPS_TS_SUMMARY_REORG0;' from dba_tab_partitions where tablespace_name='APPS_TS_SUMMARY';

SQL > select count(*) from dba_tab_partitions where
 tablespace_name='APPS_TS_SUMMARY';

SQL > alter table AP.AP_LIABILITY_BALANCE move partition SYS_P214
 tablespace APPS_TS_SUMMARY_REORG0;
Partitioned Indexes :
SQL > select 'alter index '||index_owner||'.'||index_name||' rebuild
 partition '||partition_name||' tablespace APPS_TS_SUMMARY_REORG0;'
 from dba_ind_partitions where tablespace_name='APPS_TS_SUMMARY';

SQL > alter index MSC.MSC_ITEM_HIERARCHY_MV_N1 rebuild partition
 ITEM_HIERARCHY_2 tablespace APPS_TS_SUMMARY_REORG0;

STEP 9

Check if any objects left in the old tablespace. Compare it with the log file.
SQL > Select segment_type,count(*) from dba_segments where
 tablespace_name='APPS_TS_SUMMARY_REORG0' group by  segment_type;
Check for the Invalids in the database. If any invalids exists compile them.
SQL >exec utl_recomp.recomp_parallel(32);
Check for any Unusable Indexes and Invalid indexes are there,if exists rebuild them.
SQL > select ' alter index '||owner||'.'||index_name||' rebuild;' from
 dba_indexes where status='UNUSABLE';

STEP 10

Drop the old Tablespace.
SQL > drop tablespace APPS_TS_SUMMARY including contents and datafiles;

STEP 11

Rename the new tablespace if the database version is 10g and datafiles also.
If it is 9i create a new tablespace with the old name and move all the objects from the previous tablespace to new tablespace as we done earlier.
STEP 12
 Now perform the step 1. Compare the 2 spool files.