OraFAQ Forum: Performance Tuning » Need to know Sql ID and text

Home » RDBMS Server » Performance Tuning » Need to know Sql ID and text (HP-UX,Oracle 10g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Need to know Sql ID and text [message #544589]

Wed, 22 February 2012 13:12

pokhraj_d
Messages: 116
Registered: December 2007

Senior Member

Hi All,

At my prod instance one sql query ran for about 9 hours.
Now the sql query completed with success.

Suddenly our ops team want to know the which sql query was running for 9 hours.

As the query got success no record is being found at session level.

I know the SID.
How Do I get the SQL query ?

Thanks-
P

Report message to a moderator

Re: Need to know Sql ID and text [message #544590 is a reply to message #544589]

Wed, 22 February 2012 13:17

BlackSwan
Messages: 20106
Registered: January 2009

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>At my prod instance one sql query ran for about 9 hours.
How do you know this to be true without knowing the SQL itself?

The actual SQL may not be still resident within the DB.

You could query V$SQL.ELAPSED_TIME looking for appropriate large value

Report message to a moderator

Re: Need to know Sql ID and text [message #544591 is a reply to message #544589]

Wed, 22 February 2012 13:21

pokhraj_d
Messages: 116
Registered: December 2007

Senior Member

Hi,

Coz ops team informed that one peoplesoft process is running long time and completed after 9 hours.

It means at the process background the sql query runing for 9 hours.

regards-
P

[Updated on: Wed, 22 February 2012 13:22]

Report message to a moderator