We have a project where we are planning change the schema name and the objects in schema
Suppose Table Name is Schemaname_<TABLE_NAME> to have New Schema Suffixes same applies to packages
Example Current Schema Name - SCOTT
Objects in Schema - SCOTT_<OBJECT_NAME>
We are planning to decommisssion SCOTT schema and replace it with some other schema name - VALUEREPORTER because SCOTT does not make any meaning Business Wise and objects in SCHEMA as VR_<OBJECT_NAME>
I want to make sure that i do not miss anything while making this change,
This is to standardize all the schemas.
Could you please let me know all the steps as a developer I should take care..
Thanks for the help
To change the *schema* you have to unload/reload your objects. DataPump can assist with *some* of the work but not all, eg
SQL> create user demo identified by demo;
User created.
SQL>
SQL> grant resource, connect to demo;
Grant succeeded.
SQL> alter user demo quota 100m on users;
User altered.
SQL>
SQL> grant read, write on directory TEMP to demo;
Grant succeeded.
SQL>
SQL> connect demo/demo
Connected.
SQL>
SQL> create table demo.demo_t1 ( x int );
Table created.
SQL> create table demo.demo_t2 ( x int );
Table created.
SQL> create procedure demo.demo_proc1 is begin null; end;
2 /
Procedure created.
SQL>
SQL> create or replace trigger demo.demo_t1_trg
2 before insert or update on demo.demo_t1
3 begin
4 null;
5 end;
6 /
Trigger created.
SQL>
SQL>
SQL> create or replace trigger demo.demo_t2_trg
2 before insert or update on demo_t2
3 begin
4 null;
5 end;
6 /
Trigger created.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
C:\Users\hamcdc>
C:\Users\hamcdc>expdp userid=demo/demo directory=TEMP dumpfile=my_schema
Export: Release 12.2.0.1.0 - Production on Wed Jul 5 09:57:17 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DEMO"."SYS_EXPORT_SCHEMA_01": userid=demo/******** directory=TEMP dumpfile=my_schema
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "DEMO"."DEMO_T1" 0 KB 0 rows
. . exported "DEMO"."DEMO_T2" 0 KB 0 rows
Master table "DEMO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_SCHEMA_01 is:
C:\TEMP\MY_SCHEMA.DMP
Job "DEMO"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jul 5 09:57:45 2017 elapsed 0 00:00:27
SQL> drop user vr cascade;
User dropped.
SQL>
SQL> create user vr identified by vr;
User created.
SQL>
SQL> grant resource, connect to vr;
Grant succeeded.
SQL> alter user vr quota 100m on users;
User altered.
SQL>
SQL> grant read, write on directory TEMP to vr;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
C:\Users\hamcdc>
C:\Users\hamcdc>impdp userid=vr/vr directory=TEMP dumpfile=my_schema remap_table=demo_t1:vr_t1 remap_table=demo_t2:vr_t2 remap_schema=demo:vr
Import: Release 12.2.0.1.0 - Production on Wed Jul 5 10:06:06 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "VR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "VR"."SYS_IMPORT_FULL_01": userid=vr/******** directory=TEMP dumpfile=my_schema remap_table=demo_t1:vr_t1 remap_table=demo_t2:vr_t2 remap_schema=demo:vr
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "VR"."VR_T1" 0 KB 0 rows
. . imported "VR"."VR_T2" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39083: Object type TRIGGER:"VR"."DEMO_T1_TRG" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE EDITIONABLE TRIGGER "VR"."DEMO_T1_TRG"
before insert or update on demo.demo_t1
begin
null;
end;
ORA-39083: Object type TRIGGER:"VR"."DEMO_T2_TRG" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE EDITIONABLE TRIGGER "VR"."DEMO_T2_TRG"
before insert or update on demo_t2
begin
null;
end;
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "VR"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Wed Jul 5 10:06:08 2017 elapsed 0 00:00:01
SQL> conn vr/vr
Connected.
SQL> select object_name from obj;
OBJECT_NAME
--------------------------------------
DEMO_PROC1
VR_T1
VR_T2
but you can see that there are still things like triggers, procedures etc to be managed.
Take a look here for a way of doing this incrementally rather than "big bang"
https://asktom.oracle.com/pls/asktom/f?p=100:11::P11_QUESTION_ID:30020192108317