oracle-expdp_imdp常用命令-阿里云开发者社区

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

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

oracle-expdp_imdp常用命令

1 查看及停止expdp&impdp作业

查看导出jobs:

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

查会话的详细信息

生成kill会话语句

查看会话正在执行的sql

查出作业名

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

expdp导出时：

impdp导入时：

2 导出命令：

默认导出目录：

创建导出目录：

查看数据库字符集

按schema方式导出：

按tablespaces方式导出：

按tables导出：

按tables导出符合条件的行：

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

只导出ddl语句数据

按flashback_time或flashback_scn参数导出：

通过par参数文件方式

3 导入命令：

table_exists_action参数：

预估导出文件大小

生成ddl语句到文件

生成create index sql

按表空间导入

按schema导入

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

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

不导表约束和触发器

只导入表约束和触发器

导入某个表符合条件的行

转换schema导入

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

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

只导入ddl语句数据

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

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

通过par参数文件方式

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

5.导dblink

只导某个用户下的dblink

导所有Dblink，包括public dblink

按dblink名称导

热门文章

最新文章

相关电子书

推荐镜像