1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
| create or replace
PROCEDURE pr_expdp_full AS
/**************************************************
Procedure permettant d'effectuer un export FULL
de l'instance avec la technologie DATAPUMP
Déclarer l'objet répertoire dans l'instance
CREATE DIRECTORY "DATAPUMP_DIR" AS '<Mon chemin réseau>'
**************************************************/
JobHandle NUMBER; -- Data Pump job handle
JobStamp VARCHAR2(13); -- Date time stamp
InstanceName varchar2(30); -- Name of instance
ind NUMBER; -- Loop index
n_Exist NUMBER; -- count Job
JobHandle_Exist NUMBER; --JobHandle exist
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
le ku$_LogEntry; -- For WIP and error messages
js ku$_JobStatus; -- The job status from get_status
jd ku$_JobDesc; -- The job description from get_status
sts ku$_Status; -- The status object returned by get_status
BEGIN
-- TimeStamp File with system date
select to_char(SYSDATE,'DDMMRRRR-HH24MI') into JobStamp from dual ;
-- Instance Name to export
select rtrim(global_name) into InstanceName from global_name;
--Delete Job if exist
select count(*) into n_Exist from user_datapump_jobs where job_name = 'DAILY_EXPDP_'||InstanceName;
IF n_Exist > 0
THEN
JobHandle_Exist := DBMS_DATAPUMP.ATTACH('DAILY_EXPDP_'||InstanceName,'SYSTEM');
dbms_datapump.stop_job(JobHandle_Exist);
DBMS_DATAPUMP.DETACH (JobHandle_Exist);
execute immediate('DROP TABLE DAILY_EXPDP_'||InstanceName||'');
END IF;
-- Create a (user-named) Data Pump job to do a schema export.
JobHandle :=
DBMS_DATAPUMP.OPEN(
operation => 'EXPORT'
,job_mode => 'FULL'
,job_name => 'DAILY_EXPDP_'||InstanceName
,version => 'COMPATIBLE'
);
dbms_output.put_line('after OPEN');
-- Specify a single dump file for the job (using the handle just returned)
-- and a directory object, which must already be defined and accessible
-- to the user running this procedure.
DBMS_DATAPUMP.ADD_FILE(
handle => JobHandle
,filename => 'FULL_EXPDP_'||InstanceName||'_'||JobStamp||'.dpf'
,directory => 'DATAPUMP_DIR'
,filetype => 1 );
dbms_datapump.set_parameter(handle => JobHandle, name => 'KEEP_MASTER', value => 0);
-- Specify a single log file for the job
DBMS_DATAPUMP.ADD_FILE(
handle => JobHandle
,filename => 'FULL_EXPDP_'||InstanceName||'_'||JobStamp||'.log'
,directory => 'DATAPUMP_DIR'
,filetype => 3 );
dbms_datapump.set_parameter(handle => JobHandle, name => 'INCLUDE_METADATA', value => 1);
dbms_datapump.set_parameter(handle => JobHandle, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
-- Start the job. An exception will be generated if something is not set up
-- properly.
DBMS_DATAPUMP.START_JOB(JobHandle);
-- The export job should now be running. In the following loop, the job
-- is monitored until it completes. In the meantime, progress information is
-- displayed.
percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(JobHandle,dbms_datapump.ku$_status_job_error +dbms_datapump.ku$_status_job_status +dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
-- If the percentage done changed, display the new value.
if js.percent_done != percent_done
then
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;
-- If any work-in-progress (WIP) or error messages were received for the job,
-- display them.
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;
-- Indicate that the job finished and detach from it.
dbms_output.put_line('Job has completed');
dbms_output.put_line('Final job state = ' || job_state);
dbms_datapump.detach(JobHandle);
END pr_expdp_full;" |
