Transaction Logs

[mooks]

Hi,

I am wondering where to search for all the transactions being made by an application in the database. I have searched through v$sql and v$sqltext, but I dun get to see the sql that I have executed from the application. Any help please? Thanks!

[julian]

Originally posted by mooks
Hi,

I am wondering where to search for all the transactions being made by an application in the database

All transactions since WHEN?
Probably your SQL statement has been already flushed out.

Check FIRST_LOAD_TIME in v$sqlarea. Note: it is not in DATE format but VARCHAR2!

[mooks]

I am looking for a particular transcation being made a couple of weeks ago. When I checked for all the transcations for that day, it was not available. The v$sqlarea actually stores the transaction from april till now..... so may I know what happened to those transaction logs that I need to look for? ?? Thanks!

[julian]

The v$sqlarea actually stores the transaction from april till now.....

Wow, what kind of a LRU algorithm are you using :-) How big is your shared pool?

[mooks]

Hi,

Actually the table size and shared pool size is not very big... so I am also wondering what happened to those transaction records.... could Oracle remove or overwritten it? The table has about 2k records only..... so what happened to the rest? Any ideas? 8)

[pando]

I am totally lost... what do you mean transaction records in v$sqlarea???

Do you mean the sql statement? Oracle will flush it out if it´s not used as often as other sql statements

What do you mean what happened to the rest... rest of SQL?

[denevge]

You want to know which statements have been executed ?
If so, that info is in the archive logs.

You can use logminer to examine those files.

Regards
Gert

[mooks]

Hi,

I am sorry for causing all the confusion! :P Yes, I am looking for all the sql statement being executed for the past 1 week. If I do not have archiving, can I still view those records or have they been flushed out by Oracle? Many thanks!

[julian]

Originally posted by mooks
Hi,

I am sorry for causing all the confusion! :P Yes, I am looking for all the sql statement being executed for the past 1 week. If I do not have archiving, can I still view those records or have they been flushed out by Oracle? Many thanks!

Since you are not in archivelog mode (why not?), then if Oracle has flushed out the SQL statements, then you will not be able to find them. Logminer is a very good tool, put your system in archivelog mode, use it!