Data Pump 複製 Schema 到另一個 Database Instance

--Login by SYSTEM@wmdev (Target DB)
-- 1. 登入 target db，建立一個 db directory，指定一個檔案系統的目錄路徑
CREATE DIRECTORY [DIR_NAME] AS '/xxxxx/xxxxx';


-- 2. 將此 directory grant read and write 權限給此次 import 會用到的 user schemas
GRANT READ,WRITE ON DIRECTORY [DIR_NAME] TO [Schema_Name];





-- 3. 建立 db link 到 source db (最好使用 source db 的 system 帳號建立 db link)
CREATE PUBLIC DATABASE LINK [Connection_Name]
CONNECT TO system IDENTIFIED BY '********'
USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = [Host_Name])(Port = 1521))) (CONNECT_DATA = (SID = [SID])))';



--查看 DB Link 有沒有建成功
SELECT *
FROM ALL_DB_LINKS;



--試著從 DB Link 做 SELECT
SELECT 'A'
FROM DUAL@[Connection_Name];


-- 4. 登入 terminal 執行 impdb 的指令 (詳見附件檔案的範例，指令用法請參考 Oracle Data Pump)
--Login Terminal - 執行 Data Pump - Network Import
--Import Schema  impdp system/*******@[TNS_NAME] DIRECTORY=[DIR_NAME] SCHEMAS=[Schema_Name] TABLE_EXISTS_ACTION=TRUNCATE NETWORK_LINK=[Connection_Name] ESTIMATE=STATISTICS VERSION=10.2 EXCLUDE=JOB PARALLEL=8



-- 5. Import 完成，檢查 import.log 是否有 error


-- 6. Drop db link
DROP PUBLIC DATABASE LINK WMDB_ONLINE2_CONNECTION;

-- 7. Drop db DIRECTORY
DROP DIRECTORY DMPDIR;

清除 Oracle Data Pump Job

步驟 1：找出正在執行的 JOB


用 system 帳號登入 oracle 並利用下面 SQL 指令

SQL> select job_name, state from dba_datapump_jobs;

JOB_NAME         STATE
------------------------------ ------------------------------
SYS_IMPORT_SCHEMA_02        EXECUTING
SYS_EXPORT_SCHEMA_01        NOT RUNNING
SYS_IMPORT_SCHEMA_01        NOT RUNNING
SYS_EXPORT_SCHEMA_02        NOT RUNNING

看到 STATE = EXECUTING 則代表此 JOB 正在執行中，因此我們可以先將 JOB_NAME 記下來

此例子 JOB_NAME = SYS_IMPORT_SCHEMA_02 

步驟 2：執行 Impdp utility

   impdp system/******* attach=system.SYS_IMPORT_SCHEMA_02 

system/******* ==> 代表 system 之帳號及密碼

執行結果：

Import: Release 11.2.0.3.0 - Production on Sab May 19 21:55:38 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Job: SYS_IMPORT_SCHEMA_02
  Owner: system
  Operation: IMPORT
  Creator Privs: TRUE
  GUID: C06A6B4EAEB4122ER0434EDH74850F
  Start Time: Sabado, 19 Mayo, 2012 21:55:04
  Mode: SCHEMA
  Instance: BD1
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** SCHEMAS=SCHEMA DIRECTORY=BCK dumpfile=export_SCHEMA.dmp logfile=export.SCHEMA.log reuse_dumpfiles=true
  IMPORT Job Parameters:
     CLIENT_COMMAND        system/******** SCHEMAS=SCHEMA DIRECTORY=BCK dumpfile=export_SCHEMA.dmp logfile=import_SCHEMA.log
  State: EXECUTING
  Bytes Processed: 90.681.272
  Percent Done: 98
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /bck/export_SCHEMA.dmp

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: SCHEMA
  Object Name: TABLE_EXAMPLE
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 28
  Completed Bytes: 90.832
  Worker Parallelism: 1

Import> 

亦可透過 STATUS 指令觀察該 Job 之狀態：

Import> STATUS
Job: SYS_IMPORT_SCHEMA_02
  Operation: IMPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 91.676.344
  Percent Done: 99
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /bck/export_SCHEMA.dmp

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: SCHEMA
  Object Name: TABLE_OBJECTS_EXAMPLE
  Object Type: SCHEMA_EXPORT/TABLE/INDEX/INDEX
  Completed Objects: 38
  Worker Parallelism: 1

步驟 3：刪除 Job

Import> kill_job
Are you sure you wish to stop this job ([yes]/no):

 
步驟 4：刪除 Table (若 Table 還在)

sqlplus / as sysdba
SQL> drop table SYS_IMPORT_SCHEMA_02;
Table dropped. 

參考資料：http://albertolarripa.com/2012/05/19/clean-oracle-data-pump-jobs/

How to delete/remove non executing datapump jobs?
https://pavandba.com/2011/07/12/how-to-deleteremove-non-executing-datapump-jobs/

Kill, cancel and resume or restart datapump expdp and impdp jobs
http://blog.oracle48.nl/wordpress/killing-and-resuming-datapump-expdp-and-impdp-jobs/

Find DB Session and DB Process by Server Process ID  (SPID)
--Find Session by SPID (Server Process ID)
SELECT
     S.SID,
     S.SERIAL#,
     S.PROCESS,
     P.SERIAL# PROCESS_SERIAL#,
     P.PID,
     P.SPID,
     S.LOCKWAIT,
     S.STATUS,
     S.LOGON_TIME,
     S.MODULE,
     S.ACTION,
     S.MACHINE
FROM
     V$SESSION S,
     V$PROCESS P
WHERE 1 = 1
AND S.PADDR = P.ADDR
AND (
     P.SPID IN (
          '21581','21674','23559'
     )
)