Home » SQL & PL/SQL » SQL & PL/SQL » How to find out queries which ran in last 1hr
How to find out queries which ran in last 1hr [message #257995] Thu, 09 August 2007 14:47 Go to next message
ghotu
Messages: 11
Registered: August 2007
Location: Richmond
Junior Member
Hell All,

I have been trying this thing from morning but couldn't find anything.

I want to find out what are queries which ran in last one hour for a particular session. Please let me know if anybody has any clue...

Thanks
Re: How to find out queries which ran in last 1hr [message #258002 is a reply to message #257995] Thu, 09 August 2007 15:21 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you can only see SQL for a specific schema if it's still in the cache. SQL can get aged out of the cache or be flushed by several things including analyzing tables, changing session settings etc.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:497421739750

Re: How to find out queries which ran in last 1hr [message #258005 is a reply to message #257995] Thu, 09 August 2007 16:07 Go to previous messageGo to next message
ghotu
Messages: 11
Registered: August 2007
Location: Richmond
Junior Member
I found it an it was very simple....

I have used sql tracing for a particular session and found the sql statements issued in that session.

but still my problem is not resolved cuz one my front-end developer is trying to run one setup which issues sql statement behind the screen and that particular statement is giving error (invalid column). He can't see sql so he asked me but i think by using tracing we can only see statements which ran successfully...

anybody has any ides how to resolve this problem?

thanks
Re: How to find out queries which ran in last 1hr [message #258010 is a reply to message #258005] Thu, 09 August 2007 16:39 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
in tkprof, try specifying something like "record=my_log.sql". You'll be able to see the last statements successfully run - that might give you a clue where to look for the bad statement.

Enabling a sufficiently high trace level in the client's sqlnet.ora should show the statement and maybe the ORA error if it's not getting suppressed in the pl/sql.

Of course the real problem is that your app suppresses errors and apparently doesn't log them anywhere...
Re: How to find out queries which ran in last 1hr [message #258074 is a reply to message #257995] Fri, 10 August 2007 01:46 Go to previous messageGo to next message
karteek507
Messages: 9
Registered: April 2005
Junior Member
Use following view

slecct * from v$sql
Re: How to find out queries which ran in last 1hr [message #258076 is a reply to message #258074] Fri, 10 August 2007 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read Andrew's answer:
Quote:
SQL can get aged out of the cache or be flushed by several things

Regards
Michel
Re: How to find out queries which ran in last 1hr [message #258179 is a reply to message #257995] Fri, 10 August 2007 08:45 Go to previous messageGo to next message
ghotu
Messages: 11
Registered: August 2007
Location: Richmond
Junior Member
Hey Andrew/Michel,


Thanks a lot for your responses.

Andrew i understood what are you trying to say but can you elaborate how can we enable high level tracing in client's sqlnet.ora.

Thanks

Re: How to find out queries which ran in last 1hr [message #258212 is a reply to message #258179] Fri, 10 August 2007 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add the following lines to your sqlnet.ora file (change names as you want):
trace_level_client = ADMIN
trace_directory_client = /oracle/network/trace
trace_file_client =  cli.trc
trace_unique_client = ON

Beware this can generate HUGE trace file, have space on your disk.

Regards
Michel
Re: How to find out queries which ran in last 1hr [message #258218 is a reply to message #257995] Fri, 10 August 2007 10:28 Go to previous messageGo to next message
ghotu
Messages: 11
Registered: August 2007
Location: Richmond
Junior Member
Hey Michel,

Thanks for quick response as always...

Do you think this trace will capture those sql's too which did not run successfully.

Thanks
Re: How to find out queries which ran in last 1hr [message #258221 is a reply to message #258218] Fri, 10 August 2007 10:35 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a network trace, it traps only and all what transit on the network.
It means it does not trap SQL in PL/SQL stored procedure.

The only way to trap all SQL in to enable your session SQL trace but you already did it.
I don't understand why you didn't get the wrong SQL... unless you use a front end that analyzes the statement before sending it to the server.

Regards
Michel
Previous Topic: Temporary tables
Next Topic: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem
Goto Forum:
  


Current Time: Fri Dec 09 19:31:08 CST 2016

Total time taken to generate the page: 0.13070 seconds