oracle impdp导入full,使用数据泵impdp导入数据

最新推荐文章于 2024-02-18 14:12:57 发布

苏星河牛通

最新推荐文章于 2024-02-18 14:12:57 发布

阅读量2.8k

点赞数

文章标签： oracle impdp导入full

前一些测试里面，通过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

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

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

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: