使用資料泵impdp匯入資料
前一些測試裡面，透過expdp匯出了各種資料庫表、模式、表空間以及全庫。
在這裡，我們就透過之前匯出來的邏輯備份檔案，使用impdp工具把匯出的檔案
逆向匯入資料庫裡面。
----使用資料泵impdp匯入資料：
--策略1：檢視Scott使用者擁有的表：
SQL> select tname from tab;
TNAME
------------------------------
BONUS
DEPT
EMP
HISLOADER
MYLOADER
SALGRADE
YOURLOADER
7 rows selected.
#可以看到Scott使用者下只用7張表。
--使用impdp將從suxing使用者匯出的mytest表匯入Scott使用者：
--嘗試策略2：impdp suxing/oracle directory=dump_home dumpfile=mytest.dmp remap_schema=suxing:scott
--匯入策略3：impdp system/oracle directory=dump_home dumpfile=mytest.dmp remap_schema=suxing:scott
[oracle@enmo dirhome]$ impdp suxing/oracle directory=dump_home dumpfile=mytest.dmp remap_schema=suxing:scott
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 13:53:14 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.
[oracle@enmo dirhome]$
#直接使用suxing使用者匯入發現沒有許可權，那就需要更改匯入方案中的使用者。
[oracle@enmo dirhome]$ impdp system/oracle directory=dump_home dumpfile=mytest.dmp remap_schema=suxing:scott
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 13:54:30 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_home dumpfile=mytest.dmp remap_schema=suxing:scott
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."MYTEST" 5.859 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 2 13:54:34 2016 elapsed 0 00:00:03
[oracle@enmo dirhome]$
#更換使用system使用者後，成功將suxing使用者的表匯入到Scott使用者。
--到Scott使用者檢視新匯入的表，並檢視錶中的資料：
SQL> select tname from tab;
TNAME
------------------------------
BONUS
DEPT
EMP
HISLOADER
MYLOADER
MYTEST
SALGRADE
YOURLOADER
8 rows selected.
#可見，比上面的查詢多出了一個表。
--檢視錶MYTEST的資料：
SQL>
SQL> select * from MYTEST;
ID NAME CREATED
---------- ------ ---------
123 susky 01-NOV-16
#匯入成功。
--匯入Scott模式schema到資料庫：
--策略1：刪除資料庫中已有的Scott使用者的schema：
SQL> show user
USER is "SYS"
SQL> drop user scott cascade;
User dropped.
SQL> select username from dba_users
2 where username like 'sco%';
no rows selected
#已經刪除使用者。
--沒有建立使用者下直接匯入：
[oracle@enmo dirhome]$ impdp system/oracle directory=dump_home dumpfile=scott.dmp
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 14:11:01 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_home dumpfile=scott.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema SCOTT is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'PROD', inst_scn=>'1050430');COMMIT; END;
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"SCOTT"."DEPT" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."DEPT" ("DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14 BYTE), "LOC" VARCHAR2(13 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPAC
ORA-39083: Object type TABLE:"SCOTT"."EMP" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
ORA-39083: Object type TABLE:"SCOTT"."BONUS" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."BONUS" ("ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "SAL" NUMBER, "COMM" NUMBER) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS"
ORA-39083: Object type TABLE:"SCOTT"."SALGRADE" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"
ORA-39083: Object type TABLE:"SCOTT"."MYLOADER" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."MYLOADER" ("ID" NUMBER(3,0), "DNAME" VARCHAR2(12 BYTE), "LNAME" VARCHAR2(10 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TAB
ORA-39083: Object type TABLE:"SCOTT"."HISLOADER" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."HISLOADER" ("ID" NUMBER(3,0), "DNAME" VARCHAR2(12 BYTE), "LNAME" VARCHAR2(10 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) T
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"SCOTT"."PK_DEPT" skipped, base object type TABLE:"SCOTT"."DEPT" creation failed
ORA-39112: Dependent object type INDEX:"SCOTT"."PK_EMP" skipped, base object type TABLE:"SCOTT"."EMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"SCOTT"."PK_DEPT" skipped, base object type TABLE:"SCOTT"."DEPT" creation failed
ORA-39112: Dependent object type CONSTRAINT:"SCOTT"."PK_EMP" skipped, base object type TABLE:"SCOTT"."EMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SCOTT"."PK_DEPT" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SCOTT"."PK_EMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39112: Dependent object type REF_CONSTRAINT:"SCOTT"."FK_DEPTNO" skipped, base object type TABLE:"SCOTT"."EMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SCOTT"."DEPT" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SCOTT"."EMP" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SCOTT"."BONUS" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SCOTT"."SALGRADE" creation failed
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 18 error(s) at Wed Nov 2 14:11:06 2016 elapsed 0 00:00:03
[oracle@enmo dirhome]$
匯入所有表都失敗。
--先建立使用者並授權：
SQL> create user scott identified by tiger;
User created.
SQL> grant create session,resource to scott;
Grant succeeded.
SQL> select tname from tab;
no rows selected
#發現沒有任何物件。
--匯入方案2：impdp system/oracle directory=dump_home dumpfile=scott.dmp
[oracle@enmo dirhome]$ impdp system/oracle directory=dump_home dumpfile=scott.dmp
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 14:22:12 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_home dumpfile=scott.dmp
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 "SCOTT"."DEPT" 5.929 KB 4 rows
. . imported "SCOTT"."EMP" 8.562 KB 14 rows
. . imported "SCOTT"."HISLOADER" 5.921 KB 3 rows
. . imported "SCOTT"."MYLOADER" 6.023 KB 7 rows
. . imported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . imported "SCOTT"."BONUS" 0 KB 0 rows
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 2 14:22:17 2016 elapsed 0 00:00:04
[oracle@enmo dirhome]$
#匯入成功。
--檢視使用者下的表對物件：
SQL> show user
USER is "SCOTT"
SQL> select tname from tab;
TNAME
------------------------------
BONUS
DEPT
EMP
HISLOADER
MYLOADER
SALGRADE
6 rows selected.
--匯入MySpace表空間：
--匯入策略： impdp system/oracle directory=dump_home dumpfile=myspace.dmp tablespaces=myspace
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
MYSPACE
7 rows selected.
#一共7個表空間。
--在Scott使用者建立一個表存放到MySpace的表空間資料檔案裡面：
SQL> create table t1 tablespace myspace
2 as select * from all_objects
3 where rownum <=100;
Table created.
SQL> select table_name from dba_tables
2 where tablespace_name='MYSPACE';
ABLE_NAME
------------------------------
MYTEST
T1
--刪除MYSPACE表空間的表：
SQL> drop table scott.t1 purge;
Table dropped.
SQL> drop table suxing.mytest purge;
Table dropped.
--再次檢視錶空間的表：
SQL> select table_name from dba_tables
2 where tablespace_name='MYSPACE';
no rows selected
--匯入表空間：
[oracle@enmo dirhome]$ impdp system/oracle directory=dump_home dumpfile=myspace.dmp tablespaces=myspace
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 15:29:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01": system/******** directory=dump_home dumpfile=myspace.dmp tablespaces=myspace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SUXING"."MYTEST" 5.859 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully completed at Wed Nov 2 15:29:33 2016 elapsed 0 00:00:02
[oracle@enmo dirhome]$
#匯入成功。
--檢視：
SQL> select table_name from dba_tables
2 where tablespace_name='MYSPACE';
TABLE_NAME
------------------------------
MYTEST
--使用impdp匯入全庫的的策略：
impdp system/oracle directory=dump_home dumpfile=PROD.dmp full=y
在這裡，我們就透過之前匯出來的邏輯備份檔案，使用impdp工具把匯出的檔案
逆向匯入資料庫裡面。
----使用資料泵impdp匯入資料：
--策略1：檢視Scott使用者擁有的表：
SQL> select tname from tab;
TNAME
------------------------------
BONUS
DEPT
EMP
HISLOADER
MYLOADER
SALGRADE
YOURLOADER
7 rows selected.
#可以看到Scott使用者下只用7張表。
--使用impdp將從suxing使用者匯出的mytest表匯入Scott使用者：
--嘗試策略2：impdp suxing/oracle directory=dump_home dumpfile=mytest.dmp remap_schema=suxing:scott
--匯入策略3：impdp system/oracle directory=dump_home dumpfile=mytest.dmp remap_schema=suxing:scott
[oracle@enmo dirhome]$ impdp suxing/oracle directory=dump_home dumpfile=mytest.dmp remap_schema=suxing:scott
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 13:53:14 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.
[oracle@enmo dirhome]$
#直接使用suxing使用者匯入發現沒有許可權，那就需要更改匯入方案中的使用者。
[oracle@enmo dirhome]$ impdp system/oracle directory=dump_home dumpfile=mytest.dmp remap_schema=suxing:scott
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 13:54:30 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_home dumpfile=mytest.dmp remap_schema=suxing:scott
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."MYTEST" 5.859 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 2 13:54:34 2016 elapsed 0 00:00:03
[oracle@enmo dirhome]$
#更換使用system使用者後，成功將suxing使用者的表匯入到Scott使用者。
--到Scott使用者檢視新匯入的表，並檢視錶中的資料：
SQL> select tname from tab;
TNAME
------------------------------
BONUS
DEPT
EMP
HISLOADER
MYLOADER
MYTEST
SALGRADE
YOURLOADER
8 rows selected.
#可見，比上面的查詢多出了一個表。
--檢視錶MYTEST的資料：
SQL>
SQL> select * from MYTEST;
ID NAME CREATED
---------- ------ ---------
123 susky 01-NOV-16
#匯入成功。
--匯入Scott模式schema到資料庫：
--策略1：刪除資料庫中已有的Scott使用者的schema：
SQL> show user
USER is "SYS"
SQL> drop user scott cascade;
User dropped.
SQL> select username from dba_users
2 where username like 'sco%';
no rows selected
#已經刪除使用者。
--沒有建立使用者下直接匯入：
[oracle@enmo dirhome]$ impdp system/oracle directory=dump_home dumpfile=scott.dmp
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 14:11:01 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_home dumpfile=scott.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema SCOTT is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'PROD', inst_scn=>'1050430');COMMIT; END;
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"SCOTT"."DEPT" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."DEPT" ("DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14 BYTE), "LOC" VARCHAR2(13 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPAC
ORA-39083: Object type TABLE:"SCOTT"."EMP" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
ORA-39083: Object type TABLE:"SCOTT"."BONUS" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."BONUS" ("ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "SAL" NUMBER, "COMM" NUMBER) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS"
ORA-39083: Object type TABLE:"SCOTT"."SALGRADE" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"
ORA-39083: Object type TABLE:"SCOTT"."MYLOADER" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."MYLOADER" ("ID" NUMBER(3,0), "DNAME" VARCHAR2(12 BYTE), "LNAME" VARCHAR2(10 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TAB
ORA-39083: Object type TABLE:"SCOTT"."HISLOADER" failed to create with error:
ORA-01918: user 'SCOTT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."HISLOADER" ("ID" NUMBER(3,0), "DNAME" VARCHAR2(12 BYTE), "LNAME" VARCHAR2(10 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) T
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"SCOTT"."PK_DEPT" skipped, base object type TABLE:"SCOTT"."DEPT" creation failed
ORA-39112: Dependent object type INDEX:"SCOTT"."PK_EMP" skipped, base object type TABLE:"SCOTT"."EMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"SCOTT"."PK_DEPT" skipped, base object type TABLE:"SCOTT"."DEPT" creation failed
ORA-39112: Dependent object type CONSTRAINT:"SCOTT"."PK_EMP" skipped, base object type TABLE:"SCOTT"."EMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SCOTT"."PK_DEPT" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SCOTT"."PK_EMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39112: Dependent object type REF_CONSTRAINT:"SCOTT"."FK_DEPTNO" skipped, base object type TABLE:"SCOTT"."EMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SCOTT"."DEPT" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SCOTT"."EMP" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SCOTT"."BONUS" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SCOTT"."SALGRADE" creation failed
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 18 error(s) at Wed Nov 2 14:11:06 2016 elapsed 0 00:00:03
[oracle@enmo dirhome]$
匯入所有表都失敗。
--先建立使用者並授權：
SQL> create user scott identified by tiger;
User created.
SQL> grant create session,resource to scott;
Grant succeeded.
SQL> select tname from tab;
no rows selected
#發現沒有任何物件。
--匯入方案2：impdp system/oracle directory=dump_home dumpfile=scott.dmp
[oracle@enmo dirhome]$ impdp system/oracle directory=dump_home dumpfile=scott.dmp
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 14:22:12 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump_home dumpfile=scott.dmp
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 "SCOTT"."DEPT" 5.929 KB 4 rows
. . imported "SCOTT"."EMP" 8.562 KB 14 rows
. . imported "SCOTT"."HISLOADER" 5.921 KB 3 rows
. . imported "SCOTT"."MYLOADER" 6.023 KB 7 rows
. . imported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . imported "SCOTT"."BONUS" 0 KB 0 rows
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 2 14:22:17 2016 elapsed 0 00:00:04
[oracle@enmo dirhome]$
#匯入成功。
--檢視使用者下的表對物件：
SQL> show user
USER is "SCOTT"
SQL> select tname from tab;
TNAME
------------------------------
BONUS
DEPT
EMP
HISLOADER
MYLOADER
SALGRADE
6 rows selected.
--匯入MySpace表空間：
--匯入策略： impdp system/oracle directory=dump_home dumpfile=myspace.dmp tablespaces=myspace
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
MYSPACE
7 rows selected.
#一共7個表空間。
--在Scott使用者建立一個表存放到MySpace的表空間資料檔案裡面：
SQL> create table t1 tablespace myspace
2 as select * from all_objects
3 where rownum <=100;
Table created.
SQL> select table_name from dba_tables
2 where tablespace_name='MYSPACE';
ABLE_NAME
------------------------------
MYTEST
T1
--刪除MYSPACE表空間的表：
SQL> drop table scott.t1 purge;
Table dropped.
SQL> drop table suxing.mytest purge;
Table dropped.
--再次檢視錶空間的表：
SQL> select table_name from dba_tables
2 where tablespace_name='MYSPACE';
no rows selected
--匯入表空間：
[oracle@enmo dirhome]$ impdp system/oracle directory=dump_home dumpfile=myspace.dmp tablespaces=myspace
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 15:29:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01": system/******** directory=dump_home dumpfile=myspace.dmp tablespaces=myspace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SUXING"."MYTEST" 5.859 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully completed at Wed Nov 2 15:29:33 2016 elapsed 0 00:00:02
[oracle@enmo dirhome]$
#匯入成功。
--檢視：
SQL> select table_name from dba_tables
2 where tablespace_name='MYSPACE';
TABLE_NAME
------------------------------
MYTEST
--使用impdp匯入全庫的的策略：
impdp system/oracle directory=dump_home dumpfile=PROD.dmp full=y
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31392094/viewspace-2127541/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Impdp資料泵匯入
- 資料泵IMPDP 匯入工具的使用
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- 資料泵匯出匯入
- Oracle使用資料泵匯出匯入表Oracle
- 資料泵 impdp 操作
- 資料泵的匯入匯出
- 資料泵匯出匯入表
- 資料泵匯出匯入資料標準文件
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- Oracle10g 資料泵匯出命令impdp 使用總結Oracle
- 資料泵(expdp,impdp)高版本匯入低版本操作例項
- expdp impdp Data Pump（資料泵）使用解析
- EXPDP 和 IMPDP 資料泵的使用_1
- EXPDP 和 IMPDP 資料泵的使用_2
- Oracle資料泵－schema匯入匯出Oracle
- 使用impdp,expdp資料泵進入海量資料遷移
- IMPDP匯入遠端資料庫資料庫
- 資料泵無法匯入JOB
- 資料泵基礎(impdp/expdp)
- Oracle資料泵的匯入和匯出Oracle
- 資料泵取匯出和匯入（一）
- 使用資料泵工具expdp工具匯出資料
- 【IMPDP】使用IMPDP自動建立使用者並完成資料的匯入
- 使用EXPDP/IMPDP匯入匯出表中資料/後設資料測試
- 【匯入匯出】資料泵 job_name引數的使用
- rac環境下使用impdp匯入資料出錯
- oracle10g expdp資料泵的bug,按schema匯出，匯入impdp時無jobOracle
- Oracle使用資料泵 (expdp/impdp)實施遷移Oracle
- oracle 10g資料泵之impdp-同時匯入多個檔案Oracle 10g
- Oracle使用資料泵在異機之間匯出匯入表Oracle
- 針對資料泵匯出 (expdp) 和匯入 (impdp)工具效能降低問題的檢查表
- 使用資料泵匯出DDL語句
- 資料泵 EXPDP 匯出工具的使用
- 用資料泵併發匯入資料的一個案例
- expdp impdp 資料庫匯入匯出命令詳解資料庫