Home » RDBMS Server » Performance Tuning » retrieve deleted data of user_jobs (Oracle 10g, )
retrieve deleted data of user_jobs [message #430050] Sat, 07 November 2009 10:29 Go to next message
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. Smile
Re: retrieve deleted data of user_jobs [message #430053 is a reply to message #430050] Sat, 07 November 2009 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the job is a one-shot it is removed from the table as soon as it is executed.
There is no way to know it existed.
Just like there is no way to know from what someone executed a SQL when it is over (assuming there is no trace and no audit activated).

Regards
Michel

Re: retrieve deleted data of user_jobs [message #433454 is a reply to message #430050] Wed, 02 December 2009 07:18 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member

Next time schedule the job through EM and it will record a log.
Razz
Re: retrieve deleted data of user_jobs [message #433459 is a reply to message #433454] Wed, 02 December 2009 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
quoting BlackSwan:
Those who live by the GUI, die by the GUI.


Wink

Regards
Michel
Re: retrieve deleted data of user_jobs [message #433544 is a reply to message #430050] Wed, 02 December 2009 21:17 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Create a trigger to insert anything happened into a user schema log's logtable, instead of using GUI. Or you can insert log into one table by adding some code inside your procedure.
Re: retrieve deleted data of user_jobs [message #433634 is a reply to message #433544] Thu, 03 December 2009 06:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm intrigued - what would you add a trigger on to capture the creation of a Job?
Re: retrieve deleted data of user_jobs [message #433731 is a reply to message #433634] Thu, 03 December 2009 20:00 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
JRowbottom wrote on Thu, 03 December 2009 19:43
I'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 #433735 is a reply to message #433731] Thu, 03 December 2009 21:44 Go to previous messageGo to next message
kuailingtong
Messages: 4
Registered: September 2009
Junior Member
Using flash query.

select *
from dba_jobs as of TIMESTAMP to_timestamp('2009-12-4 11:30:33', 'YYYY-MM-DD HH:MI:SS');
Re: retrieve deleted data of user_jobs [message #433775 is a reply to message #433731] Fri, 04 December 2009 04:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
spur230
Messages: 25
Registered: April 2009
Location: US
Junior Member
kuailingtong wrote on Thu, 03 December 2009 21:44
Using 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 #433869 is a reply to message #433867] Fri, 04 December 2009 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You need to be SYS.

Regards
Michel
Re: retrieve deleted data of user_jobs [message #433870 is a reply to message #433869] Fri, 04 December 2009 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or ask SYS to explicitly grant you FLASHBACK privilege on the view.

Regards
Michel
Re: retrieve deleted data of user_jobs [message #433880 is a reply to message #433869] Fri, 04 December 2009 13:52 Go to previous messageGo to next message
spur230
Messages: 25
Registered: April 2009
Location: US
Junior Member
Michel Cadot wrote on Fri, 04 December 2009 11:32
You need to be SYS.

Regards
Michel


Thank you Michel I am able to use flashback query through SYS user.
Re: retrieve deleted data of user_jobs [message #434029 is a reply to message #430050] Sun, 06 December 2009 19:56 Go to previous messageGo to next message
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 #434040 is a reply to message #434029] Sun, 06 December 2009 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no performances question about a SQL here.
How "set time on" or "set timing on" will help to get "how long the job took ( total_time) and what SQL it was running ( user_job.what)"?

Regards
Michel
Re: retrieve deleted data of user_jobs [message #434043 is a reply to message #430050] Mon, 07 December 2009 00:09 Go to previous messageGo to next message
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 #434054 is a reply to message #434043] Mon, 07 December 2009 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
dbms_job.submit(what=>'exaple.sql;', next_date=>sysdate+1/24);

Do you AT LEAST try what you suggest?
Do you ever run a sql file with dbms_job?

Yes, there is a problem with you, what you post is b...

Regards
Michel
Re: retrieve deleted data of user_jobs [message #434105 is a reply to message #430050] Mon, 07 December 2009 03:48 Go to previous messageGo to next message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
Please go through below links....

How to run a .sql file through dbms_job package

http://www.dba-oracle.com/tips_oracle_dbms_job.htm

http://www.dbforums.com/oracle/1008209-dbms_job.html
Re: retrieve deleted data of user_jobs [message #434111 is a reply to message #434105] Mon, 07 December 2009 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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 Go to previous message
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.

Previous Topic: can we write below query in a better way
Next Topic: Performance issue while pl/sql script execution (merged 3)
Goto Forum:
  


Current Time: Sun Dec 04 12:39:36 CST 2016

Total time taken to generate the page: 0.27466 seconds