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;" |
Partager