Move objects from one schema to another schema

All database objects of a home-grown business application were created in a wrong schema. After year and year, it became a very large database with more than 2,000GB data. The problem never got corrected until getting urgent calls for violating company auditing standard. I used transport tablespaces concept in Doc ID 371556.1 to move those objects, mainly tables, to a new dedicated schema within the same 11G database.

Pre-steps:

1. create new schema owner EDI in the database

create user edi identified by "ediPassword"
default tablespace TBS_MED_DT1
temporary tablespace temp
quota unlimited on TBS_MED_DT1
profile STANDARD;

alter user edi quota unlimited on TBS_LRG_DT1;    -- do the same with other tablespaces
grant ALTER ANY MATERIALIZED VIEW to edi;
grant CREATE SESSION to edi;
grant GLOBAL QUERY REWRITE to edi;
grant CREATE TABLE to edi;

2. Create DIRECTORY for datapump
SQL> create or replace directory dpump_dir as '/path/to/export';
SQL> grant read, write on directory dpump_dir to system;

3. Identify 5 tablespaces that hold all targeted objects for moving. ORADBA is the schema where objects will be moved out. Save the counts
.
SQL> select distinct tablespace_name, count(*) from dba_tables
where owner = 'ORADBA' group by tablespace_name;

SQL> select distinct tablespace_name, count(*) from dba_indexes where owner = 'ORADBA' group by tablespace_name;

4. Get and save the list of data files for IMPDP script (142 data files)

SQL> select ''''||file_name|| ''',' from dba_data_files where tablespace_name in
('TBS_LRG_DT1','TBS_MED_DT1','TBS_DT_LOB','ORADBA_IX1','ORADBA_IX2');

5. compile invalid objects and record down the invalid list
SQL> @?/rdbms/admin/utlrp.sql
SQL> select owner, object_type, object_name from dba_objects where status = 'INVALID';
... ... ...
29 rows selected.

6. statements for re-creating sequences
SQL> select 'create sequence edi.' || sequence_name || ' start with  ' || last_number || ' INCREMENT BY  1;' FROM dba_sequences where sequence_owner = 'ORADBA';

7. statement for re-creating synonyms
select 'create public synonym ' || synonym_name || ' for EDI.' || table_name || ';' from dba_synonyms where table_owner = 'ORADBA' and owner='PUBLIC'
union
select 'create synonym ' || owner||'.'||synonym_name || ' for EDI.' || table_name ||';' from dba_synonyms where table_owner = 'ORADBA' and owner!='PUBLIC'

8. query dba_tab_privs to get all necessary "grant" statements.
9. extract affected codes of packages, functions, triggers, views, materialized views, db links.

Moving steps:

1.  Prepare for exporting the tablespace (Doc 371556.1)

SQL> execute sys.dbms_tts.transport_set_check(
'TBS_LRG_DT1,TBS_MED_DT1,TBS_DT_LOB,ORADBA_IX1,ORADBA_IX2', true);

SQL> select * from sys.transport_set_violations;


2. Make fixes to 6 violations from Step 1. Such as:

SQL> ALTER TABLE ORADBA.AIS_DATA DROP PRIMARY KEY CASCADE drop index;
SQL> alter table ORADBA.AIS_DATA move tablespace TBS_DT_LOB;
SQL> ALTER TABLE ORADBA.AI_DATA_SET ADD
  CONSTRAINT AIS_DATA_PK
  PRIMARY KEY (KEY, FORMAT, STATUS_CD)
  USING INDEX tablespace ORADBA_IX1 ; 
... ... ...

Repeat Step 1 until no violation in  sys.transport_set_violations

3. Drop two materialized views from TBS_MED_DT1, and drop other objects

SQL> drop materialized view oradba.xxxxxx;
SQL> drop MATERIALIZED VIEW LOG ON ORADBA.STATUS_TAB;

Drop triggers and synonyms
SQL> @drop_trigger.sql
SQL> @drop_synonym.sql

4. Alter 5 tablespaces to READ only

$ more tbs_read_only.sql
alter tablespace TBS_LRG_DT1 read only;
alter tablespace TBS_MED_DT1 read only;
alter tablespace TBS_DT_LOB read only;
alter tablespace ORADBA_IX1 read only;
alter tablespace ORADBA_IX2 read only;

SQL> @tbs_read_only.sql

confirm by
SQL> select * from dba_tablespaces where status = 'READ ONLY';

4. Run EXPDP script: No "TRANSPORT_FULL_CHECK=Y"

$ more expdp_5tbs.par
## $ expdp system/PSWD parfile=expdp_8tbs.par
DUMPFILE=expdp_5tbs.dmp
LOGFILE=expdp_5tbs.log
DIRECTORY=dpump_dir
TRANSPORT_TABLESPACES=TBS_LRG_DT1,TBS_MED_DT1,TBS_DT_LOB,ORADBA_IX1,ORADBA_IX2
# TRANSPORT_FULL_CHECK=Y

$ expdp system/PSWD parfile=expdp_5tbs.par

5. Drop tablespaces. The KEY is all datafiles will not be deleted and untouched!

$ more tbs_drop.sql
 drop tablespace TBS_LRG_DT1 INCLUDING CONTENTS;
 drop tablespace TBS_MED_DT1 INCLUDING CONTENTS;
 drop tablespace TBS_DT_LOB INCLUDING CONTENTS;
 drop tablespace ORADBA_IX1 INCLUDING CONTENTS;
 drop tablespace ORADBA_IX2 INCLUDING CONTENTS;

SQL> @tbs_drop.sql

confirm by
SQL> select * from dba_data_files where tablespace_name in
('TBS_LRG_DT1','TBS_MED_DT1','TBS_DT_LOB','ORADBA_IX1','ORADBA_IX2');
 0 row returned

6. Run IMPDP script

$ more impdp_5tbs.par
# impdp system/PSWD parfile=impdp_8tbs.par
#
DUMPFILE=expdp_5tbs.dmp
LOGFILE=impdp_5tbs.log
DIRECTORY=dpump_dir
TRANSPORT_DATAFILES='/path/to/data/sml_data_001.dbf',
'/path/to/index/sml_index_001.dbf',
'/path/to/index/med_index_001.dbf',
'..... list all 142 files, See Pre-setp 4',
REMAP_SCHEMA=ORADBA:EDI

$ impdp system/PSWD parfile=impdp_8tbs.par

7. Bring them back "READ WRITE"

alter tablespace TBS_LRG_DT1 read write;
alter tablespace TBS_MED_DT1 read write;
alter tablespace TBS_DT_LOB read write;
alter tablespace ORADBA_IX1 read write;
alter tablespace ORADBA_IX2 read write;

SQL> @tbs_read_write.sql

Verify:
SQL> select * from dba_tablespaces where status = 'READ ONLY';

-- Make sure the object counts match what the database had before the move
SQL> select distinct tablespace_name, count(*) from dba_tables where owner = 'EDI' group by tablespace_name;
SQL> select distinct tablespace_name, count(*) from dba_indexes where owner = 'EDI' group by tablespace_name;
-- The old schema still keeps some objects as needed
SQL> select distinct tablespace_name, count(*) from dba_tables where owner = 'ORADBA' group by tablespace_name;

TABLESPACE_NAME    COUNT(*)
-------------------------------------  ----------
                                              2
TOOLS                                  34

SQL> select distinct tablespace_name, count(*) from dba_indexes where owner = 'ORADBA' group by tablespace_name;

TABLESPACE_NAME   COUNT(*)
------------------------------------ ----------
TOOLS                                   3

8. Compile objects
SQL> @?/rdbms/admin/utlrp.sql

9.Sync a MVIEW log
SQL> exec SYS.DBMS_SNAPSHOT_UTL.SYNC_UP_LOG('EDI','STATU_TAB');
PL/SQL procedure successfully completed.

10. Re-create views, sequences.
11. Re-create synonyms, triggers, etc
12. Run grant statements.