Script to move all objects from one tablespace to another tablespace in Oracle.

Step 1: Check count of all the objects.

set line 200 pages 500
select owner,object_type, count(*) from dba_objects group by owner,object_type order by 1,2;

Step 2: Check invalid objects.

select owner,object_name,object_type from dba_objects where status = 'INVALID' order by 1,2,3;

Step 3: Took full backup of database.

run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
backup AS COMPRESSED BACKUPSET format 'H:\rman\full_db_%t_%sp%p' filesperset 10 database plus archivelog;
release channel ch00;
release channel ch01;
allocate channel ch00 type disk;
backup format 'H:\rman\cntrl_%s_%p_%t' CURRENT CONTROLFILE;
backup format 'H:\rman\spfile_%s_%p_%t' spfile;
release channel ch00;
}

Step 4: Run the following script for generating the Spool of movement commands for all tables from one tablespace to another.

set echo off
prompt Enter a value for the OLD tablespace:
define old_tablespace=&1
prompt Enter a value for the NEW tablespace:
define new_tablespace=&2

column order_col1 noprint
column order_col2 noprint
set heading off verify off feedback off echo off
set pages 0 lines 9999 trimspool on
purge dba_recyclebin;

spool tmp.sql

select decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
' tablespace &&new_tablespace ' || ';'
from dba_segments,
(select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
and segment_name = index_name (+)
and tablespace_name = '&&old_tablespace'
order by owner, order_col1, order_col2
/

select 'alter table ' || owner || '.' || table_name ||
' move lob(' || column_name || ') store as (tablespace &&new_tablespace);'
from dba_lobs where TABLESPACE_NAME='&&old_tablespace'
/

spool off

Step 5: Use it with care, make sure you have a database backup and start up the database in restricted mode if possible.

startup restrict

-- Execute the spool file for start the tablespace movement of tables.

-- Assign permission quota to user which tables belong to that tablespace.

Step 6: Check for unusable indexes.

SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';

Step 7: Check for invalid objects.

select owner,object_name,object_type 
from dba_objects where status = 'INVALID' order by 1,2,3;