Impdp using remap_schema and remap_tablespace parameter
- Keywords:
remap_schema: source_schema:destination_schema and remap_tablespace =source_tablespace:target_tablespace
- Scenario :
We have done impdp into different schema level to different user without specifying remap_tablespace parameter and face error below
- Error:
ORA-39083: Object type TABLE:"TCMANAGER"."ASSIGNME_1773957227_113294481" failed to create with error:
ORA-00959: tablespace 'TCPRODMANAGER' does not exist
- Scenario without tablespace
oracle@PLM12:~/expdp> impdp \"sys/sys@PLM12 as sysdba\" directory=expdp dumpfile=expdp-manager2021-09-28_14-36-23.dmp remap_schema=TCPRODMANAGER:TCMANAGER logfile=impdp_tcmanager.log parallel=2
Import: Release 12.1.0.2.0 - Production on Tue Sep 28 14:45:43 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "sys/********@PLM12 AS SYSDBA" directory=expdp dumpfile=expdp-manager2021-09-28_14-36-23.dmp remap_schema=TCPRODMANAGER:TCMANAGER logfile=impdp_tcmanager.log parallel=2
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TCMANAGER" 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/TABLESPACE_QUOTA
ORA-39083: Object type TABLESPACE_QUOTA:"TCMANAGER" failed to create with error:
ORA-00959: tablespace 'TCPRODMANAGER' does not exist
Failing sql is:
DECLARE TEMP_COUNT NUMBER; SQLSTR VARCHAR2(200); BEGIN SQLSTR := 'ALTER USER "TCMANAGER" QUOTA 209715200 ON "TCPRODMANAGER"'; EXECUTE IMMEDIATE SQLSTR;EXCEPTION WHEN OTHERS THEN IF SQLCODE = -30041 THEN SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES WHERE TABLESPACE_NAME = ''TCPRODMANAGER'' AND CONTENTS = ''TEMPORARY''';
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"TCMANAGER"."ASSIGNME_1773957227_113294481" failed to create with error:
ORA-00959: tablespace 'TCPRODMANAGER' does not exist
Failing sql is:
CREATE TABLE "TCMANAGER"."ASSIGNME_1773957227_113294481" ("ESESSION_ID" NVARCHAR2(128) NOT NULL ENABLE, "POOL_ID" NVARCHAR2(64) NOT NULL ENABLE, "SERVER" NVARCHAR2(128) NOT NULL ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 214748
ORA-39083: Object type TABLE:"TCMANAGER"."POOLS_1773957227_1093459165" failed to create with error:
ORA-00959: tablespace 'TCPRODMANAGER' does not exist
Failing sql is:
CREATE TABLE "TCMANAGER"."POOLS_1773957227_1093459165" ("POOL_ID" NVARCHAR2(64) NOT NULL ENABLE, "MANAGER_URI" NVARCHAR2(255) NOT NULL ENABLE, "MUX_URI" NVARCHAR2(255) NOT NULL ENABLE, "NUM_ASSIGNED" NUMBER(10,0) NOT NULL ENABLE, "USE_PERC" FLOAT(126) NOT NULL ENABLE, "LAST_LOGIN" NUMBER(19,0), "CPU_LOAD" NUMBER(10,0)) SEGMENT CREATION IMMED
ORA-39083: Object type TABLE:"TCMANAGER"."CLUSTERC_1773957227_385776844" failed to create with error:
ORA-00959: tablespace 'TCPRODMANAGER' does not exist
Failing sql is:
CREATE TABLE "TCMANAGER"."CLUSTERC_1773957227_385776844" ("ID" NUMBER(10,0) NOT NULL ENABLE, "PROCESS_MAX_PER_USER" NUMBER(10,0) NOT NULL ENABLE, "HARD_TIMEOUT_EDIT" NUMBER(10,0) NOT NULL ENABLE, "QUERY_TIMEOUT" NUMBER(10,0) NOT NULL ENABLE, "HARD_TIMEOUT_READ" NUMBER(10,0) NOT NULL ENABLE, "SOFT_TIMEOUT_EDIT" NUMBER(10,0) NOT NULL ENABLE,
ORA-39083: Object type TABLE:"TCMANAGER"."POOLCON_1773957227_2046161837" failed to create with error:
ORA-00959: tablespace 'TCPRODMANAGER' does not exist
Failing sql is:
CREATE TABLE "TCMANAGER"."POOLCON_1773957227_2046161837" ("POOL_ID" NVARCHAR2(64) NOT NULL ENABLE, "HOSTNAME" NVARCHAR2(64) NOT NULL ENABLE, "MANAGER_CREATION_TIME" NUMBER(19,0), "ENABLE_CPU_LIMIT_CHECK" NUMBER(10,0) NOT NULL ENABLE, "LOGINS_PER_MINUTE" NUMBER(10,0) NOT NULL ENABLE, "MAX_CPU_LOAD" NUMBER(10,0) NOT NULL ENABLE, "JMX_HTTP_AD
ORA-39083: Object type TABLE:"TCMANAGER"."SERVERS_1773957227_1743233923" failed to create with error:
ORA-00959: tablespace 'TCPRODMANAGER' does not exist
Failing sql is:
CREATE TABLE "TCMANAGER"."SERVERS_1773957227_1743233923" ("SERVER" NVARCHAR2(128) NOT NULL ENABLE, "POOL_ID" NVARCHAR2(64) NOT NULL ENABLE, "CREATION_TIME" NUMBER(19,0) NOT NULL ENABLE, "PIPE" NVARCHAR2(128) NOT NULL ENABLE, "SYS_LOG" NVARCHAR2(512) NOT NULL ENABLE, "PID" NUMBER(10,0) NOT NULL ENABLE, "ASGN" NVARCHAR2(512)) SEGMENT CREATIO
ORA-39083: Object type TABLE:"TCMANAGER"."USERSASS_1773957227_428210791" failed to create with error:
ORA-00959: tablespace 'TCPRODMANAGER' does not exist
Failing sql is:
CREATE TABLE "TCMANAGER"."USERSASS_1773957227_428210791" ("ESESSION_ID" NVARCHAR2(128) NOT NULL ENABLE, "USER_ID" NVARCHAR2(64) NOT NULL ENABLE) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "TCPRODMANAGER"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"TCMANAGER"."SYS_C0035955" skipped, base object type TABLE:"TCMANAGER"."ASSIGNME_1773957227_113294481" creation failed
ORA-39112: Dependent object type CONSTRAINT:"TCMANAGER"."SYS_C0035956" skipped, base object type TABLE:"TCMANAGER"."POOLS_1773957227_1093459165" creation failed
ORA-39112: Dependent object type CONSTRAINT:"TCMANAGER"."SYS_C0035952" skipped, base object type TABLE:"TCMANAGER"."CLUSTERC_1773957227_385776844" creation failed
ORA-39112: Dependent object type CONSTRAINT:"TCMANAGER"."SYS_C0035954" skipped, base object type TABLE:"TCMANAGER"."POOLCON_1773957227_2046161837" creation failed
ORA-39112: Dependent object type CONSTRAINT:"TCMANAGER"."SYS_C0035953" skipped, base object type TABLE:"TCMANAGER"."SERVERS_1773957227_1743233923" creation failed
ORA-39112: Dependent object type CONSTRAINT:"TCMANAGER"."SYS_C0035951" skipped, base object type TABLE:"TCMANAGER"."USERSASS_1773957227_428210791" creation failed
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" completed with 14 error(s) at Tue Sep 28 14:45:55 2021 elapsed 0 00:00:12
- Impdp with specifying remap_schema: source_schema:destination_schema and remap_tablespace =source_tablespace:target_tablespace
oracle@PLM12:~/expdp> impdp \"sys/sys@PLM12 as sysdba\" directory=expdp dumpfile=expdp-manager2021-09-28_14-36-23.dmp remap_schema=TCPRODMANAGER:TCMANAGER remap_tablespace=TCPRODMANAGER:TCMANAGER logfile=impdp_tcmanager.log parallel=2
Import: Release 12.1.0.2.0 - Production on Tue Sep 28 14:59:49 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "sys/********@PLM12 AS SYSDBA" directory=expdp dumpfile=expdp-manager2021-09-28_14-36-23.dmp remap_schema=TCPRODMANAGER:TCMANAGER remap_tablespace=TCPRODMANAGER:TCMANAGER logfile=impdp_tcmanager.log parallel=2
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TCMANAGER" 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/TABLESPACE_QUOTA
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 "TCMANAGER"."SERVERS_1773957227_1743233923" 91.78 KB 238 rows
. . imported "TCMANAGER"."ASSIGNME_1773957227_113294481" 36.10 KB 172 rows
. . imported "TCMANAGER"."CLUSTERC_1773957227_385776844" 10.41 KB 1 rows
. . imported "TCMANAGER"."POOLCON_1773957227_2046161837" 8.562 KB 2 rows
. . imported "TCMANAGER"."POOLS_1773957227_1093459165" 8.195 KB 2 rows
. . imported "TCMANAGER"."USERSASS_1773957227_428210791" 0 KB 0 rows
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/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Sep 28 14:59:54 2021 elapsed 0 00:00:04
oracle@PLM12:~/expdp>
No comments:
Post a Comment