expdp/impdp使用小结

expdp system/asdfg88@orcl schemas=rm dumpfile=expdp_rm.dmp directory=dpdata1 filesize=2G parallel=4;

 

 

impdp system/asdfg88@orcl directory=dpdata1 dumpfile=expdp_scott.dmp  remap_schema=scott:liucy;

 

==================================================

 

oracle pump expdp impdp

 

 

                一. 创建逻辑目录, 该命令不会在操作系统创建真正的目录. 最好以system等管理员创建.

                create directory dpdata1 as 'd:\test\dump';

                二. 查看管理理员目录(同时查看操作系统是否存在, 因为Oracle并不关心该目录是否存在, 如果不存在, 则出错.)

                select * from dba_directories;

                三. 给scott用户赋予在指定目录的操作权限, 最好以system等管理员赋予.

                grant read, write on directory dpdata1 to scott;

                四. 导出数据

                1)按用户导

                host expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp

            DIRECTORY=dpdata1;

                2)并行进程parallel

                expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp

            parallel=40 job_name=scott3

                3)按表名导

                host expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp

            DIRECTORY=dpdata1;

                4)按查询条件导

                host expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp

            Tables=emp query='WHERE deptno=20';

                5)按表空间导

                host expdp system/manager DIRECTORY=dpdata1

            DUMPFILE=tablespace.dmp  TABLESPACES=temp,example;

                6)导整个数据库

                host expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp

            FULL=y;

 

                五. 还原数据

                1)导到指定用户下

                host impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp

            SCHEMAS=scott;

                2)改变表的owner

                host impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp

            TABLES=scott.dept REMAP_SCHEMA=scott:system;

                3)导入表空间

                host impdp system/manager DIRECTORY=dpdata1

            DUMPFILE=tablespace.dmp TABLESPACES=example;

                4)导入数据库

                host impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp

            FULL=y;

 

                5)追加数据

                 host impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp

            SCHEMAS=system TABLE_EXISTS_ACTION=append;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28998293/viewspace-1814212/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28998293/viewspace-1814212/