AI 助理
备案控制台
开发者社区 数据库 文章 正文

oracle-expdp_imdp常用命令

2022-07-08 297
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

1 查看及停止expdp&impdp作业

查看导出jobs:

system@db10g> select * from dba_datapump_jobs;
手工control+C停止expdp/impdp后，其实不是真正的停止，它还在后台运行，

从这两个查询来确定正在运行的dump job name：

    select * from DBA_DATAPUMP_JOBS;
    select * from DBA_DATAPUMP_SESSIONS;

    select * from v$session where status='ACTIVE'  and module='Data Pump Master';

    SQL> select * from dba_datapump_jobs;

    OWNER_NAME JOB_NAME                       OPERATION       JOB_MODE   STATE               DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
    ---------- ------------------------------ --------------- ---------- --------------- ---------- ----------------- -----------------
    SYSTEM     SYS_EXPORT_SCHEMA_01           EXPORT          SCHEMA     NOT RUNNING              0                 0                 0
    SYSTEM     SYS_EXPORT_TABLE_01            EXPORT          TABLE      NOT RUNNING              0                 0                 0

查会话的详细信息

select sid,s.saddr,s.paddr,username,session_type,job_name,action ,module,program ,machine,type,server,status  from v$session s,DBA_DATAPUMP_SESSIONS j where  s.saddr=j.saddr;

生成kill会话语句

select 'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| ' immediate;' kill_session,sid,s.saddr,s.paddr,username,session_type,job_name,action ,module,program ,machine,type,server,status  from v$session s,DBA_DATAPUMP_SESSIONS j where  s.saddr=j.saddr;

查看会话正在执行的sql

select q.sql_text,s.* from v$session s ,v$sql q  where s.sql_id=q.sql_id and s.status='ACTIVE'  and s.action='SYS_IMPORT_TABLE_01';

查出作业名

SQL> select * from DBA_DATAPUMP_SESSIONS;

OWNER_NAME      JOB_NAME                          INST_ID SADDR            SESSION_TYPE
--------------- ------------------------------ ---------- ---------------- --------------
SYSTEM          SYS_EXPORT_SCHEMA_01                    1 000000097472AE68 MASTER
SYSTEM          SYS_EXPORT_SCHEMA_01                    1 000000095079A4E8 WORKER

停止expdp&impdp作业,进入expdp交互界面：expdp system/cenorcl attach=[作业名(job_name)]

expdp导出时：
set line 300
select * from DBA_DATAPUMP_SESSIONS;

OWNER_NAME      JOB_NAME                          INST_ID SADDR            SESSION_TYPE
--------------- ------------------------------ ---------- ---------------- --------------
SYSTEM          SYS_EXPORT_SCHEMA_01                    1 000000097472AE68 MASTER
SYSTEM          SYS_EXPORT_SCHEMA_01                    1 000000095079A4E8 WORKER

    expdp system/cenorcl attach=SYS_EXPORT_SCHEMA_01
    Export> help
    Export> kill_job
    Are you sure you wish to stop this job ([yes]/no): yes

    Export>STOP_JOB=IMMEDIATE 将立即关闭数据泵作业。
impdp导入时：
set line 300
select * from DBA_DATAPUMP_SESSIONS;


OWNER_NAME                     JOB_NAME                          INST_ID SADDR            SESSION_TYPE  
------------------------------ ------------------------------ ---------- ---------------- --------------
SYS                            IMP01                                   1 00000006307DD750 DBMS_DATAPUMP 
SYS                            IMP01                                   1 000000063485A148 MASTER        
SYS                            IMP01                                   1 0000000630876310 WORKER        
SYS                            IMP01                                   1 000000065C7DD4C0 WORKER        
SYS                            IMP01                                   1 00000006347874E8 WORKER


impdp \"/ as sysdba \" ATTACH=IMP01 
Import> help
------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.


Import>

2 导出命令：

默认导出目录：

     -- select directory_path,directory_name from dba_directories;
    SQL> select directory_path from dba_directories where directory_name='DATA_PUMP_DIR';

    DIRECTORY_PATH
    --------------------------------------------------------------------------------
    /opt/oracle/admin/ninvoice/dpdump/

创建导出目录：

    mkdir reportbak
    create or replace directory dumpdir as '/home/oracle/reportbak';

    --建directory 后要授read，write权限给directory 
    grant read,write on directory dumpdir to &user_name;

查看数据库字符集

    select * from v$nls_parameters where parameter='NLS_CHARACTERSET';  
    select userenv('language') from dual;

按schema方式导出：

    expdp system/ninvoice parallel=4 logfile=expdp22.log dumpfile=expdp%U.dmp schemas=ninvoice,mobile,ninvoice_jsgs,ninvoice_nxds
    
    nohup  expdp \"/ as sysdba\" directory=dumpdir dumpfile=VMANAGE_20220713_%U.dmp logfile=VMANAGE_20220713_expdp.log schemas=VMANAGE parallel=4 exclude=STATISTICS filesize=50G CONTENT=ALL COMPRESSION=ALL job_name=expdp20220713  &

按tablespaces方式导出：

    expdp system/ninvoice parallel=2 logfile=expdp22.log dumpfile=expdp%U.dmp tablespaces=users,example cluster=n

按tables导出：

    expdp system/ninvoice parallel=2 logfile=expdp22.log dumpfile=expdp%U.dmp tables=hr.employees,hr.departments  cluster=n

按tables导出符合条件的行：

    expdp system/mytest parallel=2 logfile=expdp22.log dumpfile=expdptable%U.dmp tables=hr.employees query=hr.employees:\"where job_id\=\'IT_PROG\' and salary \>800\"  cluster=n reuse_dumpfiles=y

只导表数据（不导表索引，授权，统计信息，约束，trigger，同义词，表ddl语句等）

expdp system/oracle  logfile=expdp20161123-2.log  DIRECTORY=dumpdir dumpfile=expdp2%U.dmp tables=hr.t1,hr.t2  INCLUDE=TABLE_DATA

只导出ddl语句数据

expdp system/oracle  logfile=expdp20161123-2.log  DIRECTORY=dumpdir dumpfile=expdp2%U.dmp tables=hr.t1,hr.t2  CONTENT=METADATA_ONLY

按flashback_time或flashback_scn参数导出：

    ----如导出10分钟前employees表的数据：
    expdp system/mytest cluster=n flashback_time=\"systimestamp\-interval\'10\'minute\"  tables=hr.employees logfile=expdp22.log dumpfile=expdptable%U.dmp reuse_dumpfiles=y

通过par参数文件方式

cat expd.par <<

userid="/ as sysdba"
DIRECTORY=dumpdir
dumpfile=expdp_20220708_%U.dmp
logfile=expdp_20220708.log
schemas=usreA
CONTENT=all   
COMPRESSION=ALL  
exclude=STATISTICS
PARALLEL=6
filesize=50G
CLUSTER=N

---nohup导出

nohup expdp parfile=expdp.par 2>&1 >> expdp_nohup.out &

3 导入命令：

table_exists_action参数：

1)  skip：默认操作
2)  replace：先drop表，然后创建表，最后插入数据
3)  append：在原来数据的基础上增加数据
4)  truncate：先truncate，然后再插入数据

预估导出文件大小

    expdp apjdbo/Pd8df#dOt  schemas=INVENTORY ESTIMATE_ONLY=y

生成ddl语句到文件

    impdp system/oracle   logfile=impdp20161123-1.log  DIRECTORY=dumpdir dumpfile=expdp2%U.dmp SQLFILE=ddl.sql

生成create index sql

impdp \"/ as sysdba \" directory=DATA_PUMP_DIR logfile=nss_20210317_impdp.log network_link=DBLINK_FOR_IMPDP schemas=apps,callcenter1,whx,appread,biuser  SQLFILE=ddl20210317.sql include=INDEX

按表空间导入

    impdp system/oracle  parallel=2 logfile=impdpninvoice.log  dumpfile=expdp%U.dmp tablespaces=users,example cluster=n

按schema导入

    impdp system/oracle  parallel=2 logfile=impdpninvoice_nxds.log  dumpfile=expdp%U.dmp schemas=ninvoice cluster=n

按表导入（与及其相关对象,索引,权限,触发器等）

    impdp system/oracle  parallel=2 logfile=impdpninvoice_jsgs.log  dumpfile=expdp%U.dmp tables=hr.employees cluster=n

-导入表，并重命名表，不导入表的约束

    impdp system/mytest cluster=n tables=hr.employees dumpfile=exp%U.dmp remap_table=employees:employees_other exclude=constraint,ref_constraint

不导表约束和触发器

    impdp system/oracle   logfile=impdp20161123-1.log  DIRECTORY=dumpdir dumpfile=expdp2%U.dmp table_exists_action=truncate tables=hr.t1,hr.t2 exclude=constraint,index

只导入表约束和触发器

    impdp system/oracle   logfile=impdp20161123-1.log  DIRECTORY=dumpdir dumpfile=expdp2%U.dmp table_exists_action=append tables=hr.t1,hr.t2 include=constraint,trigger

导入某个表符合条件的行

    impdp system/mytest parallel=2 logfile=expdp22.log dumpfile=expdptable%U.dmp tables=hr.employees 
       query=hr.employees:\"where job_id\=\'IT_PROG\' and salary \>800\"  cluster=n

转换schema导入

    impdp system/mytest logfile=impdp22.log cluster=n schemas=hr  dumpfile=exp%U.dmp remap_schema=hr:hr2

转换schema,并转换表空间导入

    impdp system/oracle logfile=impdp1107.log schemas=HYBRISUAT  dumpfile=expdp_HYBRISUAT_20171106.dmp remap_schema=HYBRISUAT:uat20171106 REMAP_TABLESPACE=USERS:uat20171106

导出数据，不导统计信息,索引

    expdp apjdbo/Pd8df#dOt  logfile=expdp20161009-2.log  DIRECTORY=DUMPDIR dumpfile=expdp%U.dmp schemas=INVENTORY exclude=STATISTICS,INDEX

只导入ddl语句数据

    impdp system/oracle  logfile=expdp20161123-2.log  DIRECTORY=dumpdir dumpfile=expdp2%U.dmp tables=hr.t1,hr.t2  CONTENT=METADATA_ONLY

Remap_tablespace如果需要转换多个表空间，如A1转换成B1，A2转换成B1，有如下两种方式

    remap_tablespace=A1:B1 remap_tablespace=A2:B1

    remap_tablespace= A1:B1, A2:B1

同一个schema的情况下，转换表名如A1转换成B1，A2转换成B1，有如下两种方式


    remap_table=A1:B1 remap_table=A2:B1

    remap_table= A1:B1, A2:B1

通过par参数文件方式

--cat impdp.par

userid="/ as sysdba"
DIRECTORY=DATAPUMP
dumpfile=in_VMANAGE_20220715_%U.dmp
logfile=impdp_in_VMANAGE_20220715.log
schemas=userA,userB,userC
PARALLEL=8
job_name=impdp20220715

---nohup导入

nohup impdp parfile=impdp.par &

4. 目标端通过dblink方式impdp导入metadata

    ----1.目标端创建dblink
        sqlplus / as sysdba
        create database link DBLINK_FOR_IMPDP
          connect to system identified by password
          using '(DESCRIPTION =
            (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.10)(PORT = 1521))
            )
            (CONNECT_DATA =
              (SERVICE_NAME = orcl)
            )
          )';
    ----2.通过dblink方式impdp导入metadata,排除job,dblink,统计信息，不导入数据,并行度设为1(如果非1，可能会表创建先后不一样，导致外键等结束创建失败。)
        impdp \"/ as sysdba \" directory=DATA_PUMP_DIR logfile=mes_20220215_impdp.log network_link=DBLINK_FOR_IMPDP schemas=mes,APPDEV,erp,APPOPS CONTENT=METADATA_ONLY  exclude=statistics,DB_LINK,JOB PARALLEL=1

5.导dblink

只导某个用户下的dblink

impdp \"/ as sysdba \" directory=DATA_PUMP_DIR logfile=table_20220314_dblink_impdp.log network_link=DBLINK_FOR_IMPDP schemas=userA CONTENT=METADATA_ONLY  include=DB_LINK PARALLEL=1 

Import: Release 11.2.0.4.0 - Production on Mon Mar 14 16:44:20 2022

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
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR logfile=userA_20220314_dblink_impdp.log network_link=DBLINK_FOR_IMPDP schemas=userA CONTENT=METADATA_ONLY include=DB_LINK PARALLEL=1 
Processing object type SCHEMA_EXPORT/DB_LINK
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Mar 14 16:44:26 2022 elapsed 0 00:00:06

导所有Dblink，包括public dblink

impdp \"/ as sysdba \" directory=DATA_PUMP_DIR logfile=table_20220314_dblink_impdp.log network_link=DBLINK_FOR_IMPDP full=y CONTENT=METADATA_ONLY  include=DB_LINK PARALLEL=1


impdp \"/ as sysdba \" directory=DATA_PUMP_DIR logfile=wmstest20220526_dblink_impdp.log network_link=link_wmstest_for_dp full=y  remap_schema=wm:pm CONTENT=METADATA_ONLY  include=DB_LINK PARALLEL=1

按dblink名称导

impdp \"/ as sysdba \" directory=DATA_PUMP_DIR logfile=table_20220314_dblink_impdp.log network_link=DBLINK_FOR_IMPDP full=y CONTENT=METADATA_ONLY  include=DB_LINK：\"IN \'TEST\'\"   PARALLEL=1
文章标签：
SQL
索引
数据库
关键词：
Oracle数据库一键上云命令
lemotree
目录
相关文章
三分钟热度的鱼
|
7月前
|
Oracle 关系型数据库 分布式数据库
PolarDB常见问题之PolarDB(Oracle兼容版) 执行命令报错如何解决
PolarDB是阿里云推出的下一代关系型数据库，具有高性能、高可用性和弹性伸缩能力，适用于大规模数据处理场景。本汇总囊括了PolarDB使用中用户可能遭遇的一系列常见问题及解答，旨在为数据库管理员和开发者提供全面的问题指导，确保数据库平稳运行和优化使用体验。
三分钟热度的鱼
554 1
pbeskoyq7ffm4
|
4月前
|
运维 Oracle 前端开发
Oracle 11g RAC集群日常运维命令总结
Oracle 11g RAC集群日常运维命令总结
pbeskoyq7ffm4
99 2
pbeskoyq7ffm4
|
4月前
|
SQL 运维 Oracle
入门级Oracle 11g日常运维命令总结
入门级Oracle 11g日常运维命令总结
pbeskoyq7ffm4
132 1
听风的鱼鱼儿
|
6月前
|
SQL Oracle 关系型数据库
探索 Linux 命令 `db_archive`：Oracle 数据库归档日志的工具
探索 Linux 中的 `db_archive`，实际与 Oracle 数据库归档日志管理相关。在 Oracle 中，归档日志用于恢复，当在线重做日志满时自动归档。管理员可使用 SQL*Plus 查看归档模式，通过 `RMAN` 进行备份和恢复操作。管理归档日志需谨慎，避免数据丢失。了解归档管理对 Oracle 管理员至关重要，确保故障时能快速恢复数据库。
听风的鱼鱼儿
249 6
1941623231718325
|
7月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的SET命令：你的数据库会话“调色板”
【4月更文挑战第19天】Oracle SQL*Plus的SET命令是数据库会话的“调色板”，用于设置输出格式、反馈信息和各种偏好。它能调整PAGESIZE和LINESIZE以优化显示，控制ECHO和FEEDBACK开关以定制反馈，以及统计命令执行时间(TIMING)和调试SQL(VERIFY)。更高级的选项如HEADING和COLSEP可改善输出的可读性。通过灵活运用SET命令，能提升工作效率和体验，是数据库管理员和开发者的必备工具。
1941623231718325
72 2
1941623231718325
|
7月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的HELP命令：你的数据库“百事通”
【4月更文挑战第19天】`Oracle SQL*Plus` 的 `HELP` 命令是数据库查询的强大工具，犹如“百事通”。在遇到困惑时，`HELP` 可提供详细命令解释和用法示例，解答基础到高级的 SQL 和 PL/SQL 疑问。它还是“活字典”，揭示命令关联与区别，如 `SET` 和 `ALTER SESSION`。此外，`HELP` 解释数据库概念，如“事务”，并支持模糊查询。无论新手还是专家，`HELP` 都是数据库探索的得力助手。
1941623231718325
51 1
Zh.晨光
|
SQL Oracle 关系型数据库
Oracle杂谈二 SQL*PLUS命令的使用大全
Oracle杂谈二 SQL*PLUS命令的使用大全
Zh.晨光
73 0
蓝易云
|
7月前
|
SQL Oracle 关系型数据库
Oracle spool格式化数据命令
在这个示例中，通过设置不同的 `SET`命令参数，你可以控制输出的格式，包括每页行数、每行字符数、列分隔符等。你也可以使用其他的 `SET`命令参数来进一步定制输出格式。
蓝易云
74 0
1941623231718325
|
7月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的TTITLE和BTITLE命令：为你的数据报告加上精美的“画框”
【4月更文挑战第19天】`SQL*Plus`的`TTITLE`和`BTITLE`命令用于为数据报告添加吸引人的标题和边框。
1941623231718325
66 0
1941623231718325
|
7月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的COLUMN命令：数据展示的“化妆师”
【4月更文挑战第19天】Oracle SQL*Plus的COLUMN命令是数据展示的利器，能美化和格式化输出。它可定制列标题、调整显示格式（如数字的小数位数和日期格式），添加前缀和后缀（如货币符号），以及控制列宽和是否折行，使得数据呈现更直观、专业。利用COLUMN命令，能将原始数据转化为易于理解和视觉吸引力强的展示形式。
1941623231718325
66 0