1.1 介绍

查看expdp/impdp的进度,当你导出或导入的时候,如果数据量比较大,中途有些人会着急,不免想看看进度如何?

1.1.1 两个视图

dba_datapump_jobs;

dba_datapump_sessions;

1.1.2 expdp/impdp控制台查看进程状态

$ impdp <user>/<password> attach=<JOB_NAME>

1.2 查看进度


1.2.1 查看视图

1.2.1.1 dba_datapump_jobs

SQL> select * from dba_datapump_jobs;

OWNER_NAME           JOB_NAME             OPERATION JOB_MODE   STATE           DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
-------------------- -------------------- ---------- ---------- ------------ ---------- ----------------- -----------------
SYSTEM               SYS_IMPORT_FULL_02   IMPORT     FULL       EXECUTING             1                 1                 3
SYSTEM               SYS_IMPORT_FULL_01   IMPORT     FULL       NOT RUNNING           0                 0                 0

SQL>

1.2.1.2 dba_datapump_sessions

SQL> select * from dba_datapump_sessions;

OWNER_NAME           JOB_NAME               INST_ID SADDR           SESSION_TYPE
-------------------- -------------------- ---------- ---------------- --------------
SYSTEM               SYS_IMPORT_FULL_02           1 00000000A61C6D20 DBMS_DATAPUMP
SYSTEM               SYS_IMPORT_FULL_02           1 00000000A61C21B0 MASTER
SYSTEM               SYS_IMPORT_FULL_02           1 00000000A764B710 WORKER

SQL>

1.2.2 查看impdp控制台job status

$ impdp system/123456 attach=SYS_IMPORT_FULL_02

Import: Release 12.2.0.1.0 - Production on Tue Aug 10 00:27:54 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Job: SYS_IMPORT_FULL_02
Owner: SYSTEM                        
Operation: IMPORT                        
Creator Privs: TRUE                          
GUID: C92320CBA7A31527E0531451A8C059EF
Start Time: Monday, 09 August, 2021 23:35:03
Mode: FULL                          
Instance: testdb1
Max Parallelism: 1
Timezone: +08:00
Timezone version: 26
Endianness: LITTLE
NLS character set: ZHS16GBK
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name     Parameter Value:
    CLIENT_COMMAND       system/******** schemas=jylh dumpfile=jylh_20210809.dmp directory=dp_dir logfile=jylh_20210809.log
    TRACE                 0
IMPORT Job Parameters:
Parameter Name     Parameter Value:
    CLIENT_COMMAND       system/******** remap_schema=jylh:jylh dumpfile=jylh_20210809.dmp directory=dmp_bak logfile=jylh_impdp_20210809.log
    TRACE                 0
State: EXECUTING                      
Bytes Processed: 96,364,940,840
Percent Done: 99
Current Parallelism: 1
Job Error Count: 0
Job heartbeat: 17
Dump File: home/oracle/dmp_bak/jylh_20210809.dmp
 
Worker 1 Status:
Instance ID: 1
Instance name: testdb1
Host name: testdb1
Object start time: Tuesday, 10 August, 2021 0:13:16
Object status at: Tuesday, 10 August, 2021 0:13:16
Process Name: DW00
State: EXECUTING                      
Object Schema: JYLH
Object Name: INDEX_USERINFO_LOG
Object Type: SCHEMA_EXPORT/TABLE/INDEX/INDEX
Completed Objects: 5
Worker Parallelism: 1

Import>

1.2.3 查看操作系统进程

$ ps -ef |grep impdp
oracle   5411 23990 0 Aug09 pts/0   00:00:00 impdp                         remap_schema=jylh:jylh dumpfile=jylh_20210809.dmp directory=dmp_bak logfile=jylh_impdp_20210809.log
oracle   26329 4946 0 00:53 pts/1   00:00:00 grep --color=auto impdp
$

1.2.4 查看impdp log

. . imported "JYLH"."DB_AI_CONFIG_QHT"                   13.59 MB 547976 rows
. . imported "JYLH"."DB_VIP_PRESENT"                         0 KB       0 rows
. . imported "JYLH"."DB_BULLET"                             0 KB       0 rows
. . imported "JYLH"."DB_SCENEMODE"                       1.715 MB   38001 rows
. . imported "JYLH"."DB_ZJ1"                             67.46 MB 2540396 rows
. . imported "JYLH"."DB_CAP_OPER"                           0 KB       0 rows
. . imported "JYLH"."DB_LJQ_TEST1"                       9.859 KB     201 rows
. . imported "JYLH"."T_SYS_OPERATE_LOG"                     0 KB       0 rows
. . imported "JYLH"."DB_REPORT_TOHUAWEI"                 77.71 MB 2393036 rows
. . imported "JYLH"."DB_USERINFO_BASE":"DB_USERINFO471" 314.4 MB 3052955 rows
. . imported "JYLH"."DB_IVR_WORD":"DB_IVR_WORD451"       3.146 MB   21769 rows
. . imported "JYLH"."DB_USERINFO":"DB_USERINFO000"       1.175 MB   13408 rows
. . imported "JYLH"."DB_DEVICE_MEM"                     11.16 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
最后修改:2021 年 10 月 02 日
如果觉得我的文章对你有用,请随意赞赏