| Currently executing statement for Oracle Job- Oracle 10g [message #260383] |
Sun, 19 August 2007 07:22  |
orasaket
Messages: 70
Registered: November 2006
|
Member |
|
|
Hi,
I am using Oracle 10g R2 on Linux
While I am monitoring sessions for currently executing statements, i am using following query
select sql_text from v$sql where hash_value=(select sql_hash_value from v$session where sid=1940);
However, while i am trying to retrieve currently executing statement for a oracle job session, i am getting 'no rows returned' for above query all the time.
Moreover, hash_value for the session is always retrned as 0.
In oracle 9i R2, i was able to get currently executing sql statement for Oracle job using above query.
Is there any way getting it in Oracle 10g R2?(other than tracing session)
Thanks and Regards,
OraSaket
|
|
|
|
|
|
|
|
| Re: Currently executing statement for Oracle Job- Oracle 10g [message #260412 is a reply to message #260383] |
Sun, 19 August 2007 10:59  |
orasaket
Messages: 70
Registered: November 2006
|
Member |
|
|
Michel,
Unfortunately sql_id didn't work.
While the Oracle Job is executing, sql_id throughout the session was NULL
Where as there were N number of dmls executed by the Oracle job
Any workaround?
Regards,
OraSaket
Note :
I submitted Oracle Job to call following Procedure
Begin
tb1prc;
end;
table tb1 was created from all_objects
and procedure tb1prc with sufficient DMLs to check session details meanwhile
But Alas !!
create or replace procedure tb1prc as
begin
update tb1 set object_id=object_id+1;
update tb1 set object_id=object_id-1;
update tb1 set object_id=object_id-2;
update tb1 set object_id=object_id+2;
commit;
update tb1 set object_id=object_id-3;
update tb1 set object_id=object_id+3;
update tb1 set object_id=object_id-4;
update tb1 set object_id=object_id+4;
commit;
update tb1 set object_id=object_id-5;
update tb1 set object_id=object_id+5;
update tb1 set object_id=object_id-6;
update tb1 set object_id=object_id+6;
commit;
update tb1 set object_id=object_id+1;
update tb1 set object_id=object_id-1;
update tb1 set object_id=object_id-2;
update tb1 set object_id=object_id+2;
commit;
update tb1 set object_id=object_id-3;
update tb1 set object_id=object_id+3;
update tb1 set object_id=object_id-4;
update tb1 set object_id=object_id+4;
commit;
update tb1 set object_id=object_id-5;
update tb1 set object_id=object_id+5;
update tb1 set object_id=object_id-6;
update tb1 set object_id=object_id+6;
commit;
update tb1 set object_id=object_id+1;
update tb1 set object_id=object_id-1;
update tb1 set object_id=object_id-2;
update tb1 set object_id=object_id+2;
commit;
update tb1 set object_id=object_id-3;
update tb1 set object_id=object_id+3;
update tb1 set object_id=object_id-4;
update tb1 set object_id=object_id+4;
commit;
update tb1 set object_id=object_id-5;
update tb1 set object_id=object_id+5;
update tb1 set object_id=object_id-6;
update tb1 set object_id=object_id+6;
commit;
update tb1 set object_id=object_id+1;
update tb1 set object_id=object_id-1;
update tb1 set object_id=object_id-2;
update tb1 set object_id=object_id+2;
commit;
update tb1 set object_id=object_id-3;
update tb1 set object_id=object_id+3;
update tb1 set object_id=object_id-4;
update tb1 set object_id=object_id+4;
commit;
update tb1 set object_id=object_id-5;
update tb1 set object_id=object_id+5;
update tb1 set object_id=object_id-6;
update tb1 set object_id=object_id+6;
commit;
update tb1 set object_id=object_id+1;
update tb1 set object_id=object_id-1;
update tb1 set object_id=object_id-2;
update tb1 set object_id=object_id+2;
commit;
update tb1 set object_id=object_id-3;
update tb1 set object_id=object_id+3;
update tb1 set object_id=object_id-4;
update tb1 set object_id=object_id+4;
commit;
update tb1 set object_id=object_id-5;
update tb1 set object_id=object_id+5;
update tb1 set object_id=object_id-6;
update tb1 set object_id=object_id+6;
commit;
end tb1prc ;
|
|
|
|
| Re: Currently executing statement for Oracle Job- Oracle 10g [message #260419 is a reply to message #260412] |
Sun, 19 August 2007 12:31 |
 |
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
sql_id in v$session gives the id of the top most level SQL the session is executing.
You're not executing SQL (at top most level) but PL/SQL so you don't have any SQL_ID.
If you want to follow what the procedure does you have to use dbms_application_info package to set client_info field.
Then you can query v$session for this.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
