How to Move schema data from USERS tablespace to their own tablespace

#TASK – – Move schema (ABC) data from USERS tablespace to their own tablespace

It is very common for objects to be created in wrong tablespaces especially the USERS tablespace. Part of the DBA’s task is to move these objects to the right tablespace.

First. you want to note all objects belonging to the schema.

–Create new tablespaces to host schema data

CREATE SMALLFILE TABLESPACE XXXX_ts DATAFILE ‘XXXX_01.dbf’ SIZE 32767M AUTOEXTEND ON NEXT 512M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

— Add enough datafiles on the tablespace (This depends on the size of the data to be moved)

alter tablespace XXXX_ts add datafile’XXXX_02.dbf’ size 32767M autoextend on next 512M maxsize 32767M;

–Alter the user to point to the newly created tablespace

alter user ABC DEFAULT TABLESPACE XXXX_ts;
ALTER USER ABC quota unlimited on XXXX_ts;

–set the below before runing the output generated
alter session force parallel query;
alter session force parallel DDL;
alter session force parallel DML;

–Execute the below statement to generate a script to move lob objects belopging to the schema ABC
SELECT ‘ALTER TABLE ABC.’||LOWER(TABLE_NAME)||’ MOVE LOB(‘||LOWER(COLUMN_NAME)||’) STORE AS (TABLESPACE XXXX_ts);’ FROM DBA_TAB_COLS WHERE OWNER = ‘ABC’ AND DATA_TYPE like ‘%LOB%’;

–Execute the below statement to generate a script to move tables

SELECT ‘ALTER TABLE ABC.’ || OBJECT_NAME ||’ MOVE TABLESPACE ‘||’ XXXX_ts; ‘ FROM ALL_OBJECTS WHERE OWNER = ‘ABC’ AND OBJECT_TYPE = ‘TABLE’;

–Execute the below statement to generate a script to move partition table
select ‘ALTER TABLE ‘||TABLE_OWNER ||’.’||table_name||’ MOVE PARTITION ‘||partition_name||’ TABLESPACE XXXX_ts NOLOGGING;’ from dba_tab_partitions where TABLE_OWNER = ‘ABC’;

##create a new tablespace for the index

CREATE SMALLFILE TABLESPACE XXX_INDX_TS DATAFILE ‘XXXX_indx_ts_01.dbf’ SIZE 32767M AUTOEXTEND ON NEXT 512M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

–Add datafiles in the INDEX tablespace
alter tablespace XXXX_INDX_TS add datafile’XXXX_indx_ts_02.dbf’ size 32767M autoextend on next 512M maxsize 32767M;

–rebuilding the index

SELECT ‘ALTER INDEX ABC.’||INDEX_NAME||’ REBUILD TABLESPACE XXX_INDX_TS;’ FROM ALL_INDEXES WHERE OWNER = ‘ABC’;

Finally, verify if all the data has been moved.

by Anthony Besong

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s