oracle 如何用 DBMS_OUTPUT.PUT_LINE 打印出当前时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/50462546/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can print out the current time with DBMS_OUTPUT.PUT_LINE?
提问by Daniel
I use the following Code to execute an export data pump.
我使用以下代码来执行导出数据泵。
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;
The problem is that the export takes too long. It takes 25 minutes with this SQL Code. The Size of the schema is 1.8 GB.
问题是导出时间太长。使用此 SQL 代码需要 25 分钟。架构的大小为 1.8 GB。
And I would like to find out how much time individual steps take. That's why I want to insert a timestamp in after each process step. Then I can see how long individual steps need.
我想知道单个步骤需要多少时间。这就是为什么我想在每个流程步骤之后插入一个时间戳。然后我可以看到各个步骤需要多长时间。
I have marked the code for the timestamp with (>>>> <<<<) in the code.
我在代码中用 (>>>> <<<<) 标记了时间戳的代码。
The timestamp is not updating the time. I need the CURRENT time after each process. Can you help me?
时间戳不更新时间。我需要每个过程后的当前时间。你能帮助我吗？
回答by Amy Grange
In your code, you are setting the value of v_systimestamp at the beginning of your script - this will not change throughout the script run. You can reset the value before you log like:
在您的代码中，您在脚本的开头设置了 v_systimestamp 的值 - 这在整个脚本运行过程中都不会改变。您可以在登录之前重置该值，例如：
v_systimestamp TIMESTAMP := SYSTIMESTAMP
DBMS_OUTPUT.PUT_LINE(v_systimestamp)
or doing something like the following before and after each section you want to monitor (no variable required):
或者在要监视的每个部分之前和之后执行以下类似操作（不需要变量）：
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'));
回答by naveen
Here example 'CURRENT_TIMESTAMP'
这里的例子'CURRENT_TIMESTAMP'
curDateTime TIMESTAMP := CURRENT_TIMESTAMP ;
DBMS_OUTPUT.PUT_LINE('CUR TIME '|| curDateTime);
回答by Littlefoot
If a procedure you run takes a long time, DBMS_OUTPUT.PUT_LINEwon't help much. Well, it willdisplay something, but - as you use a loop, depending on number of iterations, DBMS_OUTPUTmight run out of buffer size or exceed number of visible lines in SQL*Plus (if you use it) and you'll lose part of the output.
如果您运行的程序需要很长时间，DBMS_OUTPUT.PUT_LINE则无济于事。好吧，它会显示一些东西，但是 - 当您使用循环时，根据迭代次数，DBMS_OUTPUT可能会用完缓冲区大小或超过 SQL*Plus 中的可见行数（如果您使用它）并且您将丢失部分输出。
Besides, you won't see a single letterof the DBMS_OUTPUT.PUT_LINEuntil the procedure finishes.
此外，你不会看到一个字母的DBMS_OUTPUT.PUT_LINE，直到程序完成。
Therefore, I'd suggest you to use another approach - a simple logging which requires a table, a sequence and an (autonomous transaction) procedure. Here's the script:
因此，我建议您使用另一种方法 - 一个简单的日志记录，它需要一个表、一个序列和一个（自主事务）过程。这是脚本：
CREATE TABLE a1_log
(
id NUMBER,
datum DATE,
descr VARCHAR2 (500)
);
CREATE SEQUENCE seqlognap START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE PROCEDURE a1_p_log (par_descr IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO a1_log
SELECT seqlognap.NEXTVAL, SYSDATE, par_descr FROM DUAL;
COMMIT;
END a1_p_log;
Then put A1_P_LOG calls into your own procedure; something like
然后把 A1_P_LOG 调用放到你自己的过程中；就像是
begin
a1_p_log('selecting from a large table');
select ... into ... from ...;
a1_p_log('entering the loop');
while ... loop
a1_p_log('doing something');
end loop;
a1_p_log('the end');
end;
Here's where the autonomous transaction matters - it'll commit inserts into the log table (without affecting the main transaction), so you can (in another session, of course) trace the execution, simply by repeatedly issuing
这就是自治事务重要的地方 - 它会将插入提交到日志表中（不影响主事务），因此您可以（在另一个会话中，当然）跟踪执行，只需重复发出
SELECT *
FROM a1_log
ORDER BY id;
It'll show (imaginary example):
它会显示（假想的例子）：
1 22.05.2018 18:13:00 selecting from a large table
2 22.05.2018 18:20:07 entering the loop
3 22.05.2018 18:20:07 doing something
4 22.05.2018 18:20:08 doing something
5 22.05.2018 18:20:09 doing something
6 22.05.2018 18:20:10 the end
and you'll see that step 1 takes 7 minutes to execute, so - that's what you need to investigate. It means that you don't have to wait the main procedure to finish - cancel it and start working on the bottleneck. Once you fix it, run everything again.
您会看到执行第 1 步需要 7 分钟，因此 - 这就是您需要调查的内容。这意味着您不必等待主程序完成 - 取消它并开始处理瓶颈。修复后，再次运行所有内容。
