Home » SQL & PL/SQL » SQL & PL/SQL » v$session_longops
v$session_longops [message #244310] Tue, 12 June 2007 05:27 Go to next message
sanjitpandey
Messages: 16
Registered: April 2007
Junior Member
I have rows in v$session_longops for one sql_id. The querry was run more than once so there are more than one row in this view for same sql_id. Now I want to select the most recent row. I cannot find the combination of fields that can uniquely define a row. Can any body suggest how can i select the latest row ?

Thanks
Sanjit
Re: v$session_longops [message #244317 is a reply to message #244310] Tue, 12 June 2007 05:40 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

SID, SERIAL# can uniquely idenfies a row from v$session_longops

try this query:
select * from
(select *
from v$session_longops
where SQL_ID = '&SQL_ID'
order by LAST_UPDATE_TIME desc)
where rownum=1


Regards
Sanka
Re: v$session_longops [message #244329 is a reply to message #244317] Tue, 12 June 2007 06:24 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
sanka_yanka wrote on Tue, 12 June 2007 13:40
SID, SERIAL# can uniquely idenfies a row from v$session_longops



????????
-- to populate some rows
SQL> exec dbms_stats.gather_schema_stats(user);

PL/SQL procedure successfully completed.

-- let's check
SQL> select count(*),  SID, SERIAL# from v$session_longops
  2  group by SID, SERIAL#
  3  having count(*) >1;

  COUNT(*)        SID    SERIAL#
---------- ---------- ----------
         6        138         98
         2        105        534

SQL> 


For the latest row you can look at (and order by) columns START_TIME
LAST_UPDATE_TIME

Gints Plivna
http://www.gplivna.eu
Re: v$session_longops [message #244335 is a reply to message #244310] Tue, 12 June 2007 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rows in v$session_longops are only valid if time_remaining > 0.
If you want the latest one maybe select on last START_TIME.

Regards
Michel
Re: v$session_longops [message #244456 is a reply to message #244310] Tue, 12 June 2007 23:55 Go to previous messageGo to next message
sanjitpandey
Messages: 16
Registered: April 2007
Junior Member
Thank you for the replies,
I am trying to keep log of currently executing long process,
For this i am writing the query as:


SELECT a.sql_id sql_id, sql_fulltext, sql_text, time_remaining, elapsed_seconds,c.username , first_load_time, last_load_time, To_Char(LAST_UPDATE_TIME,'DD-MON-YYYY HH:MI:SS') LAST_UPDATE_TIME
FROM v$session_longops a,v$sql b,v$session c
WHERE a.sql_id = b.sql_id AND (a.sid = c.sid)
AND c.username NOT LIKE 'san%'
AND elapsed_seconds > 5
AND To_Char(LAST_UPDATE_TIME,'DD-MON-YYYY HH:MI:SS') IN (SELECT Max(To_Char(LAST_UPDATE_TIME,'DD-MON-YYYY HH:MI:SS')) FROM v$session_longops GROUP BY sql_id)

This query is taking very long time and gives error:
ORA-00600: internal error code,arguments:[kghfrf:prv],[0x37FBF4],[],[],[],[],[],[]

Can any body suggest what is the problem with this script or any other ways I can achive the objective of this script ?
Re: v$session_longops [message #244461 is a reply to message #244456] Wed, 13 June 2007 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said:
Quote:
time_remaining > 0

This is the only condition you really need.

ORA-600 = bug
bug depends on version and patchset
you don't tell us what is your version.

Regards
Michel
Re: v$session_longops [message #244475 is a reply to message #244310] Wed, 13 June 2007 01:09 Go to previous messageGo to next message
sanjitpandey
Messages: 16
Registered: April 2007
Junior Member
Thanks Michel,

But this still returns rows for all incomplete executions. For example if sql with sql id 'dn5uk4a2u1dd8' is run more than once but stopped before that is complete then the record will remain with time_remaining>0. I am not concerned about the rows for previously executed sql. I am concerned obout the row corresponding to the currently running script. That is the row with latest LAST_UPDATE_TIME.

Actually i want to use this in merge as follows:

MERGE INTO my_table a USING
(SELECT a.sql_id sql_id,Dbms_Lob.SubStr(sql_fulltext) sql_fulltext,sql_text,time_remaining,elapsed_seconds,c.username ,first_load_time,last_load_time
FROM v$session_longops a,v$sql b,v$session c
WHERE a.sql_id = b.sql_id AND (a.sid = c.sid)
AND c.username NOT LIKE 'san%'
AND elapsed_seconds > 3
AND time_remaining > 0
) b
ON (a.sql_id = b.sql_id)
WHEN MATCHED THEN
UPDATE SET a.time_remaining = b.time_remaining,
a.elapsed_seconds = b.elapsed_seconds,
a.username = b.username,
a.first_load_time = b.first_load_time
WHEN NOT MATCHED THEN
INSERT VALUES (b.sql_id ,b.sql_fulltext, b.sql_text, b.time_remaining, b.elapsed_seconds, b.username, b.first_load_time);


but our sql:
SELECT a.sql_id sql_id,Dbms_Lob.SubStr(sql_fulltext) sql_fulltext,sql_text,time_remaining,elapsed_seconds,c.username ,first_load_time,last_load_time,To_Char(LAST_UPDATE_TIME,'DD-MON-YYYY HH:MI:SS') LAST_UPDATE_TIME
FROM v$session_longops a,v$sql b,v$session c
WHERE a.sql_id = b.sql_id AND (a.sid = c.sid)
AND c.username NOT LIKE 'san%'
AND elapsed_seconds > 3
AND time_remaining > 0

returns more than on row so the merge statements generate error:
ORA-30926: unable to get a stable set of rows in the source tables


I think this is because merge needs one to one or one to zero relationship.

so i am still having problem Sad


thank you,
Sanjit
Re: v$session_longops [message #244478 is a reply to message #244475] Wed, 13 June 2007 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you plan to delete rows from my_table?

Regards
Michel
Re: v$session_longops [message #244480 is a reply to message #244310] Wed, 13 June 2007 01:25 Go to previous messageGo to next message
sanjitpandey
Messages: 16
Registered: April 2007
Junior Member
No, I plan to create a procedure to update/insert rows in my_table using these scripts. Put that procedure in a schedular to run every 30 seconds or 1 mins and monitor the execution of the long running processes.

As you can see in the merge statement i am trying to insert new sql if they do not exist in my_table else regularly update the table for progress of executing script.

thanks
Sanjit

[Updated on: Wed, 13 June 2007 01:29]

Report message to a moderator

Re: v$session_longops [message #244495 is a reply to message #244480] Wed, 13 June 2007 02:18 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, why do you care about old rows that no more change?
Just update them or use "where" in update clause if you are in 10g.

Regards
Michel
Previous Topic: center allignment in the select query
Next Topic: How to add a blank line between two paragraph?
Goto Forum:
  


Current Time: Sun Dec 11 04:06:47 CST 2016

Total time taken to generate the page: 0.25382 seconds