export import
Hi guys,
I am trying to export my schema from database A to database B.
My oracle version is 10.2.0
and OS is solaris.
I am using datapump. I have succesfully exported the schema into the dumpfile.
bash-3.00$ expdp system/oracle directory=datap dumpfile=indx.dmp schemas=indx logfile=datap:indx.log
Export: Release 10.2.0.2.0 - Production on Tuesday, 20 July, 2010 0:02:48
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=datap dumpfile=indx.dmp schemas=indx logfile=datap:indx.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type SCHEMA_EXPORT/USER
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/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "INDX"."TABIDX" 4.730 MB 50680 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/oracle/indx.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 00:03:26
When I try to import into my other database, i am getting the following errors...
bash-3.00$ impdp system/oracle directory=datap dumpfile=indx.dmp schemas=indx logfile=datap:indx.log
Import: Release 10.2.0.2.0 - Production on Tuesday, 20 July, 2010 0:22:41
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=datap dumpfile=indx.dmp schemas=indx logfile=datap:indx.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"INDX" 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
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'INDX_TS' does not exist
Failing sql is:
CREATE TABLE "INDX"."TABIDX" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"INDX"."TESTIDX" skipped, base object type TABLE:"INDX"."TABIDX" creation failed
ORA-39112: Dependent object type INDEX:"INDX"."TESTIDX2" skipped, base object type TABLE:"INDX"."TABIDX" creation failed
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"INDX"."TESTIDX" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"INDX"."TESTIDX2" creation failed
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"INDX"."TABIDX" creation failed
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 7 error(s) at 00:22:46
================================================================
I have created a user with the same schema name (indx) in the target database to import. If I have not created an user in the target database, does the import will create it automatically?
Please help