JSSBAK> SET SERVEROUTPUT ON
JSSBAK> DECLARE
2 h1 NUMBER; -- Data Pump job handle
3 sscn NUMBER; -- Variable to hold current source SCN
4 job_state VARCHAR2(30); -- To keep track of job state
5 js ku$_JobStatus; -- The job status from GET_STATUS
6 sts ku$_Status; -- The status object returned by GET_STATUS
7 job_not_exist exception;
8 pragma exception_init(job_not_exist, -31626);
9 BEGIN
10 -- Create a (user-named) Data Pump job to do a table-level import.
11 h1 := DBMS_DATAPUMP.OPEN(
12 operation => ¨IMPORT¨,
13 job_mode => ¨TABLE¨,
14 remote_link => ¨jssweb.jss.cn¨,
15 job_name => ¨dp_sing3¨);
16 -- A metadata filter is used to specify the schema that owns the tables
17 -- that will be imported.
18 DBMS_DATAPUMP.METADATA_FILTER(
19 handle => h1,
20 name => ¨SCHEMA_EXPR¨,
21 value => ¨=¨¨MEMBER¨¨¨);
22 -- A metadata filter is used to specify the tables that will be imported.
23 DBMS_DATAPUMP.METADATA_FILTER(
24 handle => h1,
25 name => ¨NAME_EXPR¨,
26 value => ¨IN(¨¨USR_USER¨¨)¨);
27 -- Get the current SCN of the source database, and set the FLASHBACK_SCN
28 -- parameter to this value to ensure consistency between all of the
29 -- objects included in the import.
30 sscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@jssweb.jss.cn();
31 DBMS_DATAPUMP.SET_PARAMETER(
32 handle => h1,
33 name => ¨FLASHBACK_SCN¨,
34 value => sscn);
35 -- Start the job.
36 DBMS_DATAPUMP.START_JOB(h1);
37 -- The import job should be running. In the following loop, the job
38 -- is monitored until it completes.
39 job_state := ¨UNDEFINED¨;
40 BEGIN
41 WHILE (job_state != ¨COMPLETED¨) AND (job_state != ¨STOPPED¨) LOOP
42 sts:=DBMS_DATAPUMP.GET_STATUS(
43 handle => h1,
44 mask => DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR +
45 DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS +
46 DBMS_DATAPUMP.KU$_STATUS_WIP,
47 timeout => -1);
48 js := sts.job_status;
49 DBMS_LOCK.SLEEP(10);
50 job_state := js.state;
51 END LOOP;
52 -- Gets an exception when job no longer exists
53 EXCEPTION WHEN job_not_exist THEN
54 DBMS_OUTPUT.PUT_LINE(¨Data Pump job has completed¨);
55 DBMS_OUTPUT.PUT_LINE(¨Instantiation SCN: ¨ ||sscn);
56 END;
57 END;
58 /
Data Pump job has completed
Instantiation SCN: 679231
PL/SQL procedure successfully completed.
