Oracle Data Pump导出中DBMS_OUTPUT输出实时时间的问题

Hey there, let's break down why your timestamps aren't updating and get this sorted out quickly.

The Root Cause #

Your v_systimestamp variable gets initialized once in the DECLARE section, right when the PL/SQL block starts. That means it only holds the timestamp from the very beginning of the job—no matter how many times you print it later, it'll never change to reflect the current time as the code runs.

Simple Fixes #

You have two easy ways to get real-time timestamps for each step:

Option 1: Call SYSTIMESTAMP Directly When Outputting

Skip the variable entirely and use SYSTIMESTAMP directly in your DBMS_OUTPUT.PUT_LINE calls. This pulls the exact current time every time the line executes.

Option 2: Reassign the Variable Before Each Output

If you want to keep using the variable, update its value right before you print it. This ensures it always holds the latest timestamp when you need it.

Modified Code Example #

I've updated your code with Option 1 (direct SYSTIMESTAMP calls) and added extra timestamp logs for key operations to help you pinpoint exactly where the delay is happening:

set serveroutput on;
DECLARE
 ind NUMBER; -- Loop index
 h1 NUMBER; -- Data Pump job handle
 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
 DBMS_OUTPUT.PUT_LINE('Job started at: ' || SYSTIMESTAMP);
 
 h1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'EXAMPLE3','LATEST');
 DBMS_OUTPUT.PUT_LINE('OPEN operation finished at: ' || SYSTIMESTAMP);
 
 DBMS_DATAPUMP.ADD_FILE(h1, 'dumpfile.dmp', 'EXPORT_DIRECTORY', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE, 1);
 DBMS_OUTPUT.PUT_LINE('ADD_FILE operation finished at: ' || SYSTIMESTAMP);
 
 DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR','IN (''SchemaName'')');
 DBMS_OUTPUT.PUT_LINE('METADATA_FILTER set at: ' || SYSTIMESTAMP);
 
 DBMS_DATAPUMP.START_JOB(h1);
 DBMS_OUTPUT.PUT_LINE('START_JOB triggered at: ' || SYSTIMESTAMP);
 
 percent_done := 0;
 job_state := 'UNDEFINED';
 while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
 DBMS_OUTPUT.PUT_LINE('Checking job status at: ' || SYSTIMESTAMP);
 dbms_datapump.get_status(h1, 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 with timestamp
 if js.percent_done != percent_done then
 dbms_output.put_line('*** Job percent done = ' || to_char(js.percent_done) || ' at ' || SYSTIMESTAMP);
 percent_done := js.percent_done;
 end if;
 
 -- Handle WIP/error messages with timestamps
 if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0) then
 le := sts.wip;
 elsif (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0) then
 le := sts.error;
 else
 le := null;
 end if;
 
 if le is not null then
 ind := le.FIRST;
 while ind is not null loop
 DBMS_OUTPUT.PUT_LINE('Log entry created at: ' || SYSTIMESTAMP);
 dbms_output.put_line(le(ind).LogText);
 ind := le.NEXT(ind);
 end loop;
 end if;
 end loop;
 
 -- Final job completion timestamps
 dbms_output.put_line('Job completed at: ' || SYSTIMESTAMP);
 dbms_output.put_line('Final job state = ' || job_state);
 dbms_datapump.detach(h1);
END;
/

Bonus Tips for Diagnosing Slow Runtime #

• The new timestamp logs for each major step will help you see exactly which operation (like START_JOB or the status loop) is eating up time.
• For even deeper insight, calculate elapsed time between steps using SYSTIMESTAMP - previous_timestamp (store the previous timestamp in a variable after each step).
• Double-check that your EXPORT_DIRECTORY is on fast storage—slow I/O is one of the most common reasons for long Data Pump jobs.

内容的提问来源于stack exchange，提问作者Daniel