Move the objects from one tablespace to another tablespace Oracle

Script to move the 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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.