Home » RDBMS Server » Server Utilities » How to move a schema to another schema in a same database? (Oracle 10g)
How to move a schema to another schema in a same database? [message #379114] Mon, 05 January 2009 00:02 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,
I need to move the tables with data present in the user scott(full) to another schema named test.Waiting for your kind suggestions.In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.



Thanks & Regards,
Hammer.
Re: How to move a schema to another schema in a same database? [message #379122 is a reply to message #379114] Mon, 05 January 2009 00:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
export & then import
Re: How to move a schema to another schema in a same database? [message #379138 is a reply to message #379122] Mon, 05 January 2009 01:12 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear BlackSwan,
Thanks for your quick reply but i have one more question.I am using Oracle 10g in which for export and import will be using impdp and expdp.So first step i have created a directory named test and given read,write permission to the user Test.Below is the result which was shown when I done a export using expdp command.



C:\Documents and Settings\Administrator>EXPDP SCOTT/TIGER DIRECTORY=TESTDIR DUMP
FILE=TEST.DMP

Export: Release 10.1.0.2.0 - Production on Monday, 05 January, 2009 12:29

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  SCOTT/******** DIRECTORY=TESTDIR DUMPF
ILE=TEST.DMP
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\TEST\TEST.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:29


Which was successfull.But incase of import i get the below error.
I know that I am missing somewhere may i know where i am missing.

C:\Documents and Settings\Administrator>IMPDP TEST/TEST DIRECTORY=TESTDIR DUMPFI
LE=TEST.DMP

Import: Release 10.1.0.2.0 - Production on Monday, 05 January, 2009 12:30

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  TEST/******** DIRECTORY=TESTDIR DUMPFILE=
TEST.DMP
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-31684: Object type TABLE:"SCOTT"."DEPT" already exists
ORA-31684: Object type TABLE:"SCOTT"."EMP" already exists
ORA-31684: Object type TABLE:"SCOTT"."BONUS" already exists
ORA-31684: Object type TABLE:"SCOTT"."SALGRADE" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39111: Dependent object type INDEX:"SCOTT"."PK_DEPT" skipped, base object ty
pe TABLE:"SCOTT"."DEPT" already exists
ORA-39111: Dependent object type INDEX:"SCOTT"."PK_EMP" skipped, base object typ
e TABLE:"SCOTT"."EMP" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39111: Dependent object type CONSTRAINT:"SCOTT"."PK_DEPT" skipped, base obje
ct type TABLE:"SCOTT"."DEPT" already exists
ORA-39111: Dependent object type CONSTRAINT:"SCOTT"."PK_EMP" skipped, base objec
t type TABLE:"SCOTT"."EMP" already exists
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDE
X:"SCOTT"."PK_DEPT" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDE
X:"SCOTT"."PK_EMP" already exists
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39111: Dependent object type TABLE_STATISTICS skipped, base object type TABL
E:"SCOTT"."DEPT" already exists
ORA-39111: Dependent object type TABLE_STATISTICS skipped, base object type TABL
E:"SCOTT"."EMP" already exists
ORA-39111: Dependent object type TABLE_STATISTICS skipped, base object type TABL
E:"SCOTT"."BONUS" already exists
ORA-39111: Dependent object type TABLE_STATISTICS skipped, base object type TABL
E:"SCOTT"."SALGRADE" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39111: Dependent object type REF_CONSTRAINT:"SCOTT"."FK_DEPTNO" skipped, bas
e object type TABLE:"SCOTT"."EMP" already exists
Job "TEST"."SYS_IMPORT_FULL_01" completed with 15 error(s) at 12:30



Thanks & Regards,
Hammer.





Re: How to move a schema to another schema in a same database? [message #379142 is a reply to message #379138] Mon, 05 January 2009 01:42 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#i1007653

Re: How to move a schema to another schema in a same database? [message #379207 is a reply to message #379142] Mon, 05 January 2009 06:27 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear GentleBabu,
I have gone through the documention which you provided and i came with an new error which is stated below.

C:\Documents and Settings\Administrator>IMPDP TEST/TEST@ORCL DIRECTORY=TSTDIR DU
MPFILE=BACKUPSCOTT.DMP SCHEMAS=SCOTT REMAP_SCHEMA=SCOTT:TEST

Import: Release 10.1.0.2.0 - Production on Monday, 05 January, 2009 17:44

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.



1.I have given import/export full database to the user test.But then also it is not getting imported .
2.Privileges which i have in the user test as follows:


C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>SQLPLUS /NOLOG

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Jan 5 17:52:33 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> CONN TEST/TEST@ORCL
Connected.
SQL> SELECT * FROM SESSION_PRIVS;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE ANY TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE ANY PROCEDURE

PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
EXPORT FULL DATABASE
IMPORT FULL DATABASE

17 rows selected.

SQL>


Which privilege i need to grant to the user test(schema). Have i missed something in the code.

But if i grant DBA privilege to the user test it's getting imported.


Thanks & Regards,
Hammer
Re: How to move a schema to another schema in a same database? [message #379211 is a reply to message #379207] Mon, 05 January 2009 06:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You need IMP_FULL_DATABASE role to be granted.
The link provided by gentlebabu talks exactly about this.
Re: How to move a schema to another schema in a same database? [message #379284 is a reply to message #379211] Mon, 05 January 2009 23:01 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear Mahesh,
Thank You Very Much It worked...


Thanks & Regards,
Hammer.
ORA-31684: Object type REF_CONSTRAINT:" " already exists during impdp process [message #518650 is a reply to message #379284] Fri, 05 August 2011 04:38 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member

I am getting the below error during the import process of the schema using impdp utility. I could export the dump successfully without any error. But the table got imported

I have used the below command for import

E:\>impdp system/******@orcl DIRECTORY=DATA_PUMP_DIR  DUMPFILE=EXPDP_PHILIP_PROD_DIPLOYED_05AUG.dmp 
logfile=impEXPDP_PHILIP_PROD_DIPLOYED_05AUG.DMP.log SCHEMAS=phil
ip_prod

;;; 
Import: Release 11.2.0.1.0 - Production on Fri Aug 5 14:51:18 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@orcl 
DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_PHILIP_PROD_DIPLOYED_05AUG.dmp 
logfile=impEXPDP_PHILIP_PROD_DIPLOYED_05AUG.log SCHEMAS=philip_prod 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"PHILIP_PROD" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
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 "PHILIP_PROD"."PH_ORDER_FOOTPRINT"              0 KB       0 rows
. . imported "PHILIP_PROD"."PH_PROCESS_EVENT"                0 KB       0 rows
. . imported "PHILIP_PROD"."PH_RETURN_APPR_EVENT"            0 KB       0 rows
. . imported "PHILIP_PROD"."PH_RETURN_INTEGRATION_EVENT"      0 KB       0 rows
. . imported "PHILIP_PROD"."ROUT_HOST_RSRVD_LPS"             0 KB       0 rows
................
......................

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-31684: Object type REF_CONSTRAINT:"PHILIP_PROD"."PH_RETURN_APPR_EVENT_FK2" already exists
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at 14:51:55

[Updated on: Fri, 05 August 2011 04:46] by Moderator

Report message to a moderator

Re: ORA-31684: Object type REF_CONSTRAINT:" " already exists during impdp process [message #518651 is a reply to message #518650] Fri, 05 August 2011 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CODE tags are for code ONLY, keep your text out of the code tags.

The error (warning?) is quite clear, what don't you understand?

Regards
Michel
Re: ORA-31684: Object type REF_CONSTRAINT:" " already exists during impdp process [message #518654 is a reply to message #518651] Fri, 05 August 2011 05:06 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
The mentioned constriant 'PH_RETURN_APPR_EVENT_FK2' is not created int the schema. PLease see the below result
SQL> select  table_name, constraint_name from all_constraints where constraint_name like 'PH_RETURN_AP
PR_EVENT_FK%';

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
PH_RETURN_APPR_EVENT           PH_RETURN_APPR_EVENT_FK1

Re: ORA-31684: Object type REF_CONSTRAINT:" " already exists during impdp process [message #518658 is a reply to message #518654] Fri, 05 August 2011 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"owner" is missing in your query as well as "show user" and "def _CONNECT_IDENTIFIER".

Regards
Michel
Re: ORA-31684: Object type REF_CONSTRAINT:" " already exists during impdp process [message #518661 is a reply to message #518658] Fri, 05 August 2011 06:32 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Given below
SQL> show user
USER is "SYSTEM"
SQL> DEFINE _CONNECT_IDENTIFIER
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
SQL> select  owner,table_name, constraint_name from all_constraints where constraint_name like 'PH_RET
URN_APPR_EVENT_FK%';

OWNER                          TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
PHILIP_PROD                    PH_RETURN_APPR_EVENT           PH_RETURN_APPR_EVENT_FK1

SQL>

Re: ORA-31684: Object type REF_CONSTRAINT:" " already exists during impdp process [message #518667 is a reply to message #518661] Fri, 05 August 2011 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dba_constraints instead of all_ one.

Regards
Michel
Re: ORA-31684: Object type REF_CONSTRAINT:" " already exists during impdp process [message #518670 is a reply to message #518667] Fri, 05 August 2011 07:01 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
SQL> conn system@orcl
Enter password:
Connected.

SQL> select  owner,table_name, constraint_name from dba_constraints where constraint_name like 'PH_RET
URN_APPR_EVENT_FK%';

OWNER                          TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
PHILIP_PROD                    PH_RETURN_APPR_EVENT           PH_RETURN_APPR_EVENT_FK1

SQL>
Re: ORA-31684: Object type REF_CONSTRAINT:" " already exists during impdp process [message #518673 is a reply to message #518670] Fri, 05 August 2011 07:21 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If all what you posted is correct then you have found a bug and should raise a SR to Oracle.
Of course, you must first build a test case that Oracle can reproduce otherwise you will stay with your problem.
So, post this test case for us.

Regards
Michel
Previous Topic: Backup
Next Topic: Questions on export
Goto Forum:
  


Current Time: Fri Mar 29 06:29:30 CDT 2024