| How to move a schema to another schema in a same database? [message #379114] |
Mon, 05 January 2009 00:02  |
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 #379138 is a reply to message #379122] |
Mon, 05 January 2009 01:12  |
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 #379207 is a reply to message #379142] |
Mon, 05 January 2009 06:27 |
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
|
|
|
|
|
|
|
|
| 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 |
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 #518654 is a reply to message #518651] |
Fri, 05 August 2011 05:06 |
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 #518661 is a reply to message #518658] |
Fri, 05 August 2011 06:32 |
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 #518670 is a reply to message #518667] |
Fri, 05 August 2011 07:01 |
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>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
