| retrieve deleted data of user_jobs [message #430050] |
Sat, 07 November 2009 10:29  |
spur230
Messages: 25
Registered: April 2009
Location: US
|
Junior Member |
|
|
Hello All,
I had scheduled a job in oracle 10g to run only once using dbms_jobs.
I wanted to find out how long the job took ( total_time) and what SQL it was running ( user_job.what) but the information about the job seem to get deleted from user_jobs once the job is completed.
Is there any other table/ view I can use to find these information?
Also, will it be possible to query the user_job as of previous time when the data was there? if yes, please also let me know what privilidges and setting is required. Currently, I just have basic developer priviledge.
Many thanks in advance for any help. 
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: retrieve deleted data of user_jobs [message #433731 is a reply to message #433634] |
Thu, 03 December 2009 20:00  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
JRowbottom wrote on Thu, 03 December 2009 19:43I'm intrigued - what would you add a trigger on to capture the creation of a Job?
No, not a creation of a job from oracle or how does job implement something. It's simply as following:
1. Job insert into table A (id, content, date, status) from a subset query (proc, func).
2. Trigger captures the newest status of table A into table B. From our system, the other configured whatever happened yesterday have been emailed to the other or send one message to. So, I've got the newest information about jobs.
[Updated on: Thu, 03 December 2009 20:01] Report message to a moderator |
|
|
|
|
|
| Re: retrieve deleted data of user_jobs [message #433775 is a reply to message #433731] |
Fri, 04 December 2009 04:12 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I put it to you that this:Quote:1. Job insert into table A (id, content, date, status) from a subset query (proc, func).
2. Trigger captures the newest status of table A into table B. From our system, the other configured whatever happened yesterday have been emailed to the other or send one message to. So, I've got the newest information about jobs.
does not provide an answer to the requirement:
Quote:I wanted to find out how long the job took ( total_time) and what SQL it was running ( user_job.what) but the information about the job seem to get deleted from user_jobs once the job is completed.
|
|
|
|
| Re: retrieve deleted data of user_jobs [message #433867 is a reply to message #433735] |
Fri, 04 December 2009 11:01 |
spur230
Messages: 25
Registered: April 2009
Location: US
|
Junior Member |
|
|
kuailingtong wrote on Thu, 03 December 2009 21:44Using flash query.
select *
from dba_jobs as of TIMESTAMP to_timestamp('2009-12-4 11:30:33', 'YYYY-MM-DD HH:MI:SS');
I am getting ORA-01031: insufficient privileges for above query. I am able to access dba_jobs and I and able to use flashback query for other regular tables in the schema. What privileges do I need to have to flashback user_jobs view?
|
|
|
|
|
|
|
|
|
|
| Re: retrieve deleted data of user_jobs [message #434029 is a reply to message #430050] |
Sun, 06 December 2009 19:56 |
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
|
Member |
|
|
Whatever query you are running just put it into a sql script and spool it. you have to do SET TIME ON and SET TIMING ON so that in that spool log you can see elapsed time and start time end time from the prompt.
Please revert if this can help you.
|
|
|
|
|
|
| Re: retrieve deleted data of user_jobs [message #434043 is a reply to message #430050] |
Mon, 07 December 2009 00:09 |
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
|
Member |
|
|
Either you have problem with me or you never ever try to understand or reply if some one is asking help. I am not intereseted to increase my blog count to reply back to you or comment on any peole like you.
Let me explain you step by step....
Objective : How much time taken by a sql/ plsql block.
step 1) put the sql or plsql block inside this .sql file
dbms_job.submit(what=>'exaple.sql;', next_date=>sysdate+1/24);
step 2)let it be the script contains
spool /tmp/exaple.sql.log
set time on
set timing on
select * from dual;
spool off
step 3)cat /tmp/exaple.sql.log
11:37:41 SQL> select sum(num_rows) from dba_tables;
SUM(NUM_ROWS)
-------------
12428458
Elapsed: 00:00:00.09
11:37:41 SQL>
step 4) The user/devloper/support can now easily check how muck time taken.
If you have still some doubt please revert.
[Updated on: Mon, 07 December 2009 00:10] Report message to a moderator |
|
|
|
|
|
|
|
| Re: retrieve deleted data of user_jobs [message #434111 is a reply to message #434105] |
Mon, 07 December 2009 03:55 |
 |
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Did you try it?
SQL> var jobno number
SQL> begin dbms_job.submit(:jobno,'C:\auto.sql',sysdate,'sysdate+1'); end;
2 /
begin dbms_job.submit(:jobno,'C:\auto.sql',sysdate,'sysdate+1'); end;
*
ERROR at line 1:
ORA-06550: line 1, column 94:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= . ( @ % ;
ORA-06512: at "SYS.DBMS_JOB", line 82
ORA-06512: at "SYS.DBMS_JOB", line 139
ORA-06512: at line 1
Show me how it works.
Regards
Michel
[Updated on: Mon, 07 December 2009 03:56] Report message to a moderator |
|
|
|
| Re: retrieve deleted data of user_jobs [message #434154 is a reply to message #434105] |
Mon, 07 December 2009 08:32 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Amusingly, the second link posted ended with everyone agreeing that you can't run SQL scripts from DBMS_JOB.
@DBA_SangramKeshari - the problem with your previous post is that the OP asked:Quote:I wanted to find out how long the job took ( total_time) and what SQL it was running
Your reply tangentially addresses part of this, in that it will tell you how long that code takes to run now, and totally fails to address the other half.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
