Oracle : Rename schema
In oracle there is no straight way to rename a schema, but it can be done by using transport tablespaces.The following is the brief overview of the steps using transport tablespaces. Normally we will use transport tablespaces to migrate the database. In our scenario, the source and target database will be same to achieve schema rename.
Source schema : The schema name which is going to be renamed
Target schema : The new schema name
TT= transport tablespaces
- Check whether tablespace can be used for TT
- Take a note on source schema objects.
- Create target schema in db with the previlages same as source schema
- Export the source schema metadata excluding tables and indexes
- Export all the tablespace metadata which are going to be TT
- Put the tablespace in read only which is involved in TT activity.
- Shutdown the database
- Rename all the transport tablespace datafiles
- Start the database in mount.
- Since we renamed, database will complain that it cant identify the datafiles. So put all the transport tablespace datafiles to offline.
- Drop the source schema tables - > This is required if you have partition tables spanned to different tablespaces or tables have referential tables in other tablespaces.
- Drop the tablespaces with keep datafile option which are marked for TT
- Rename the datafiles back to original name.
- Import the metadata of the TT with remap_schema option (source_schema:target_schema). This will migrate all the tables and index but not the procedures,packages etc.
- Import the metadata of the source schema to target schema.
- Verify the objects counts, recompile the invalid objects.
- Drop the source schema.
- You may have to recompile any public synonyms created for this source schema.
so renaming schema is done :)
---------------------------------------------------------------------------------------------------------
Example
Pre-check-
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 13 11:24:34 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
SQL> execute dbms_tts.transport_set_check('user_data,pay_data',TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM transport_set_violations;
no rows selected
-- export meta data
> cat transport_expdp.par
transport_tablespaces=user_data,pay_data
directory=test_exp
dumpfile=trans_expdp.dmp
logfile=trans_expdp.log
userid=system
SQL> @create_target_user.sql --create target user with same previlages as source schema (sys_privs and roles )
SQL> alter tablespace user_data read only;
Tablespace altered.
SQL> alter tablespace pay_data read only;
Tablespace altered.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
> expdp parfile=transport_expdp.par
Export: Release 12.1.0.2.0 - Production on Mon Jul 13 11:08:30 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=transport_expdp.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/usr/work/trans_expdp.dmp
******************************************************************************
Datafiles required for transportable tablespace USER_DATA:
/usr/data/Testdb/user_data_01.dbf
Datafiles required for transportable tablespace PAY_DATA:
/usr/data/Testdb/pay_data_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 13 1:09:10 2015 elapsed 0 00:00:35
Rename the files
SQL>shutdown immediate
mv /usr/data/Testdb/user_data_01.dbf /usr/data/Testdb/temp_user_data_01.dbf
mv
/usr/data/Testdb/pay_data_01.dbf /usr/data/Testdb/temp_pay_data_01.dbf
Drop the tablespaces
SQL> startup mount;
SQL> alter database datafile '/usr/data/Testdb/user_data_01.dbf' offline;
SQL>alter database datafile '/usr/data/Testdb/pay_data_01.dbf' offline;
SQL> alter database open;
SQL> @drop_source_schema_tables.sql
-- the above step is required if the table has partitions span over different tablespaces. That's why we are putting the datafiles offline and dropping the tables. otherwise you may face the following errors.
SQL> drop tablespace user_data including contents keep datafiles;
drop tablespace user_data including contents keep datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
SQL> drop tablespace pay_data including contents keep datafiles;
drop tablespace pay_data including contents keep datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
or
constraint errors which has referential in different partition.
SQL> drop tablespace user_data including contents keep datafiles;
Tablespace dropped.
SQL> drop tablespace pay_data including contents keep datafiles;
Tablespace dropped.
SQL> shutdown immediate;
Rename the files back to original name
mv /usr/data/Testdb/temp_user_data_01.dbf /usr/data/Testdb/user_data_01.dbf
mv/usr/data/Testdb/temp_pay_data_01.dbf /usr/data/Testdb/pay_data_01.dbf
Start the db and import the metadata
SQL> startup
> cat trans_impdp.par
remap_schema=TESTUSER:NEW_TESTU
directory=test_exp
dumpfile=trans_expdp.dmp
logfile=trans_test_imp.log
transport_datafiles=/usr/data/Testdb/user_data_01.dbf,
/usr/data/Testdb/pay_data_01.dbf
> impdp parfile=trans_impdp.par userid=system
Import: Release 12.1.0.2.0 - Production on Mon Jul 13 11:16:51 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": parfile=trans_impdp.par userid=system/********
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 13 11:17:21 2015 elapsed 0 00:00:13
SQL> select count(object_type),object_type from dba_objects where owner='TESTUSER' group by object_type;
COUNT(OBJECT_TYPE) OBJECT_TYPE
------------------ -----------------------
1 SEQUENCE
2 PROCEDURE
25 VIEW
5 FUNCTION
SQL> select count(object_type),object_type from dba_objects where owner='NEW_TESTU' group by object_type;
COUNT(OBJECT_TYPE) OBJECT_TYPE
------------------ -----------------------
1 LOB
54 INDEX
74 TABLE
Import the metadata of source schema excluding table and index
Import the metadata and then check the object count again.
impdp remap_schema=TESTUSER:NEW_TESTU directory=test_exp dumpfile=TEST_metadata.dmp logfile=import_test.log userid=system
SQL> select count(object_type),object_type from dba_objects where owner='TESTUSER' group by object_type;
COUNT(OBJECT_TYPE) OBJECT_TYPE
------------------ -----------------------
1 SEQUENCE
2 PROCEDURE
25 VIEW
5 FUNCTION
SQL> select count(object_type),object_type from dba_objects where owner='NEW_TESTU' group by object_type;
COUNT(OBJECT_TYPE) OBJECT_TYPE
------------------ -----------------------
1 SEQUENCE
2 PROCEDURE
1 LOB
54 INDEX
74 TABLE
25 VIEW
5 FUNCTION
7 rows selected.
