Skip to Main Content
  • Questions
  • Changing Schema and Schema Object names

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Vamshi.

Asked: June 15, 2017 - 1:56 pm UTC

Last updated: July 06, 2017 - 1:15 am UTC

Version: 11g

Viewed 1000+ times

You Asked

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

and Connor said...

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

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Link?

A reader, July 05, 2017 - 9:10 am UTC

Connor,

It seems that the link just refers back to this question.

Alex, July 06, 2017 - 4:33 pm UTC

Long overdue feature Oracle needs is the ability to alter an object and set a new user. A la Postgres:

https://www.postgresql.org/docs/9.1/static/sql-altertable.html

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.