oracle database about impdp&empdp -----数据泵导入导出

本是古典何须时尚 2022-06-01 08:53 266阅读 0赞

创建存放备份的目录：*directory*

mkdir <*Folder\_path*>  
chmod 775 <*Folder\_path*>  
chown oracle:oinstall <*Folder\_path*>

--------------------

授予用户对目录的读写权限：

create or replace directory <*directory\_name*> as ’ <*Folder\_path*> ‘;  
select \* from dba\_directories;  
grant read,write on directory <*directory\_name*> to <*user\_name*>;

--------------------

查询oracle server端的字符集：

select \* from nls\_database\_parameters; select \* from nls\_database\_parameters;

SQL>select userenv('language') from dual;
    
          USERENV('LANGUAGE')
          ----------------------------------------
          AMERICAN_AMERICA.AL32UTF8

设置字符集：  
设置中文字符集：  
export NLS\_LANG=”SIMPLIFIED CHINESE\_CHINA”.AL32UTF8

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    
                          export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

expdp导出示例：  
（1）导出表  
expdp <*user\_name*>/<*password*> DIRECTORY=<*directory\_name*> DUMPFILE=<*table\_name*>.dmp TABLES=<*table\_name*> logfile=<*table\_name*>.log

（2）导出用户 （schema，与用户对应）  
expdp \\”sys/<*password*> as sysdba\\” DIRECTORY=<*directory\_name*> DUMPFILE=<*user\_name*>.dmp SCHEMAS=<*user\_name*> logfile=<*user\_name*>.log

（3）导出表空间  
expdp \\”sys/<*password*> as sysdba\\” DIRECTORY=<*directory\_name*> DUMPFILE=<*tablespace*>.dmp TABLESPACES=<*tablespace\_name*>,<*tablespace\_name*> logfile=<*tablespace*>.log

（4）导出数据库  
expdp \\”sys/<*password*> as sysdba\\” DIRECTORY=<*directory\_name*> DUMPFILE=<*instance*>.dmp FULL=Y logfile=<*instance*>.log;

--------------------

在目标库中新建用户或新建表空间

impdp导出示例：  
（1）导入表  
impdp <*user\_name*>/<*password*> DIRECTORY=<*directory\_name*> DUMPFILE=<*table\_name*> .dmp TABLES=<*table\_name*> logfile=<*table\_name*>.log

（2）导入用户  
impdp \\”sys/<*password*> as sysdba\\” DIRECTORY=<*directory\_name*> DUMPFILE=<*user\_name*>.dmp SCHEMAS=<*user\_name*> logfile=<*user\_name*>.log;

（3）导入表空间  
impdp \\”sys/<*password*> as sysdba\\” DIRECTORY=<*directory\_name*> DUMPFILE=<*tablespace*>.dmp TABLESPACES=<*tablespace\_name*> logfile=<*tablespace*>.log;

（4）导入数据库  
impdp \\”sys/<*password*> as sysdba\\” DIRECTORY=<*directory\_name*> DUMPFILE=<*instance*>.dmp FULL=Y logfile=<*instance*>.log;

关于参数  
PARALLEL 1/2/3 并行度  
CLUSTER Y/N N为只在当前节点执行不跨节点

nohup impdp \\”sys/oracle2017 as sysdba\\” directory= <*directory\_name*> dumpfile=\_bak.DMP schemas=<*user\_name*> &