database - 如何使用 DBMS_OUTPUT.PUT_LINE 打印出当前时间？

Question

我使用以下代码执行导出数据泵。

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
  >>>>>>>>>>>>>>v_systimestamp TIMESTAMP := SYSTIMESTAMP;<<<<<<<<<<<<<<

BEGIN

  h1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'EXAMPLE3','LATEST');

  DBMS_DATAPUMP.ADD_FILE(h1, 'dumpfile.dmp', 'EXPORT_DIRECTORY', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE, 1);

  DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR','IN (''SchemaName'')');

  DBMS_DATAPUMP.START_JOB(h1);



  percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
             DBMS_OUTPUT.PUT_LINE(v_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.

    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(v_systimestamp);<<<<<<<<<<<<<<
        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(h1);
END;

问题是导出时间太长。使用此 SQL 代码需要 25 分钟。架构的大小为 1.8 GB。

我想知道每个步骤需要多少时间。这就是为什么我想在每个流程步骤之后插入一个时间戳。然后我可以看到各个步骤需要多长时间。

我在代码中用(>>>> <<<<)标记了时间戳的代码。

时间戳没有更新时间。每个过程后我都需要当前时间。你能帮帮我吗？

Answer 1

在您的代码中，您在脚本的开头设置了 v_systimestamp 的值 - 这在整个脚本运行过程中都不会改变。您可以在登录之前重置该值:

v_systimestamp TIMESTAMP := SYSTIMESTAMP
DBMS_OUTPUT.PUT_LINE(v_systimestamp)

或者在你想要监控的每个部分之前和之后做类似下面的事情(不需要变量):

DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

DBMS_OUTPUT.PUT_LINE('Time Ended: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));