今天开发需求从生产库down个库到测试库,我拿了个离线备份文件(expdp)导入,需求是只用一个ICME SCHEMA即可,在impdp时忘了expdp是个full,有其它schemes存在备份文件中
[oracle@dev-db datapump]$ ll -h
total 12G
-rw-rw---- 1 oracle oracle 12G Sep 10 05:07 192.168.212.56_icme.2012-09-10.dmpdp
[oracle@dev-db datapump]$ impdp system/oracle directory=icme dumpfile=192.168.212.56_icme.2012-09-10.dmpdp remap_schema=icme:icme4 remap_tablespace=icme:icmetbs remap_tablespace=users:icmetbs
开始后才发现,错了,导入的默认全部,如果时间可以接受也就算了,导入后再删,检查一下
SQL> l
1* select opname,start_time,elapsed_seconds,(totalwork-sofar)/sofar*elapsed_seconds from v$session_longops
SQL> /
OPNAME START_TIME ELAPSED_SECONDS (TOTALWORK-SOFAR)/SOFAR*ELAPSED_SECONDS
------------------------------ ------------------- --------------- ---------------------------------------
SYS_IMPORT_FULL_03 2012-09-17 10:29:03 4530 992.331906
Sort Output 2012-09-17 10:35:09 15 0
Table Scan 2012-09-17 10:38:02 11 0
Table Scan 2012-09-17 10:41:39 106 0
Sort Output 2012-09-17 10:43:25 245 0
Index Fast Full Scan 2012-09-17 10:47:30 35 0
Sort Output 2012-09-17 10:48:05 87 0
Index Fast Full Scan 2012-09-17 10:49:32 24 0
Sort Output 2012-09-17 10:49:56 42 0
Table Scan 2012-09-17 10:50:40 61 0
Sort Output 2012-09-17 10:51:43 112 0
Index Fast Full Scan 2012-09-17 10:53:35 27 0
Sort Output 2012-09-17 10:54:02 69 0
Index Fast Full Scan 2012-09-17 10:55:11 31 0
Sort Output 2012-09-17 10:55:42 90 0
Index Fast Full Scan 2012-09-17 10:57:12 33 0
Sort Output 2012-09-17 10:57:48 55 0
Table Scan 2012-09-17 10:58:43 38 0
Sort Output 2012-09-17 10:59:21 19 0
Sort Output 2012-09-17 10:59:45 13 0
Table Scan 2012-09-17 10:59:58 62 0
Sort Output 2012-09-17 11:01:00 103 0
Index Fast Full Scan 2012-09-17 11:04:01 11 0
Index Fast Full Scan 2012-09-17 11:04:14 7 0
SYS_IMPORT_FULL_04 2012-09-17 10:29:31 4484 20778.1593
25 rows selected.
SQL> SELECT * FROM DBA_DATAPUMP_SESSIONS;
OWNER_NAME JOB_NAME SADDR SESSION_TYPE
------------------------------ ------------------------------ ---------------- --------------
SYSTEM SYS_IMPORT_FULL_04 000000021B3475F0 DBMS_DATAPUMP
SYSTEM SYS_IMPORT_FULL_03 000000021A330A00 MASTER
SYSTEM SYS_IMPORT_FULL_03 000000021B339788 WORKER
SYSTEM SYS_IMPORT_FULL_04 000000021A308300 MASTER
SYSTEM SYS_IMPORT_FULL_04 000000021B335AE0 WORKER
tip :
session_type reference http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3085.htm
确认当前的运行job,注意不是dba_jobs_running,也不是v$scheduler_running_jobs
SQL> select * from dba_datapump_jobs where state='EXECUTING';
OWNER_NAME JOB_NAME OPERATION JOB_MODE
------------------------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ---------- ----------------- -----------------
SYSTEM SYS_IMPORT_FULL_03 IMPORT FULL
EXECUTING 1 0 2
SYSTEM SYS_IMPORT_FULL_04 IMPORT FULL
EXECUTING 1 1 3
TIP:
其中有一个是我刚开始,按了CTRL +C ,并未回收所以SYS_IMPORT_FULL_03应该是那个事务
估算的SYS_IMPORT_FULL_04 剩余20778.1593秒,无法接受,下面停掉该JOB,此处不用OS 的kill 暴力结束
SQL> DECLARE
2 hdl number;
3 begin
4 hdl := dbms_datapump.attach('SYS_IMPORT_FULL_03','SYSTEM');
5 DBMS_DATAPUMP.STOP_JOB(hdl,1,0);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> l4
4* hdl := dbms_datapump.attach('SYS_IMPORT_FULL_03','SYSTEM');
SQL> c/_03/_04/
4* hdl := dbms_datapump.attach('SYS_IMPORT_FULL_04','SYSTEM');
SQL> l
1 DECLARE
2 hdl number;
3 begin
4 hdl := dbms_datapump.attach('SYS_IMPORT_FULL_04','SYSTEM');
5 DBMS_DATAPUMP.STOP_JOB(hdl,1,0);
6* end;
SQL> /
PL/SQL procedure successfully completed.
–观察dba_datapump_jobs 的状态值有EXECUTING—>STOPPING—>NOT RUNNING,结束后IMPDP加schemas 参数
--转自