Skip to Main Content
  • Questions

  • Changing Schema and Schema Object names

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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)

We're not taking comments currently, so please try again later if you want to add 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.
Connor McDonald
July 06, 2017 - 1:15 am UTC

Sorry ... cut/paste fail.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30020192108317


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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

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