Get Recent SQL

[Michael42]

Hello,

In Oracle9i on Sun Solaris 8 how can I retrieve SQL that has been executed recently without using LogMiner?

I would like to search through SQL executed anywhere from the last hour to all the way through all available Redo. I have heard there are some v$ views that can be queried.

Thanks,

Michael42

 

[SantaMufasa]

You can look at recently executed SQL in v$sqlarea (SQL_TEXT column). If you find that the contents of SQL_TEXT has been truncated due to its length, you can reconstruct the full code text by accessing all of the code pieces in the SQL_TEXT column from v$sqltext, grouped by HASH_VALUE.

Unfortunately, neither source discloses when the code executed, so I don't know how you are going to designate that you want to see code that has executed "for the last hour."

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Michael, could you explain the reason? I mean that if your original task is not to just retreive ALL recent statements but rather to find in some way bad statements among them, you may instruct your statspack to do this.

Regards, Dima

http://seminihin.narod.ru

 

[Michael42]

Sem,

>> could you explain the reason?

We need to determine how and by whom records are being deleted. Turning on Auditing is not an option. We have placed triggers on the tables in question.

Being able to check recent SQL gives us another option.

Thanks,

Michael42



Thanks,

Michael42

 

[SantaMufasa]

We have placed triggers on the tables in question.

I presume, Michael, that your triggers are auditing:


1. WHO (both Oracle and o/s logins) are making changes,
2. From WHERE (client computer name) changes are occurring,
3. WHEN (exact date and time) changes are taking place,
4. WHICH records are affected by the changes (deletes),
5. WHAT data is changing: old values, new values, PK of changed records, et cetera.

Some of the above information may not be important to you, but all of the above is available during trigger execution if you want it, and you can store all if it in a single audit table for all tables you are auditing.

Let us know.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.