Which View or Table contains user SELECT text? 1

[hallux]

Using Oracle 8.1.7

Greetings,
Does anybody know which view or table contains user SELECT text? An ms windows application is failing with an error regarding a missing view or table, and I would like to see the actual SELECT statement(s) to determine which view or table it expects to find.

Thanks,

-Brent

 

[sem]

You may turn sql tracing on.

Regards, Dima

 

[ManjulaMadhu]

I think you can query v$sqltext and get the last DML issued from sql_text

 

[ManjulaMadhu]

Just to add some points to my earlier reply,

The view, v$sqltext, does contain the full text of recent SQL statements. There are some problems though with this information. It does not contain bind variable values, so the actual rows affected are not recorded here. The view, v$sqltext, does contain the full text of the statement (split across multiple records if necessary) but it does not contain a time indicator. To get the timestamp though, you may have to join v$sqlarea . The timestamp on the data here is the first time that the statement was issued, not the last time. The view, v$sqlarea, has a timestamp (first_load_time) but only the first 1000 characters of the statement. Also, this data is not permanent. Data is removed from this view periodically as new statements are issued. The actual time that statements stay in this view is usually minutes or hours, but it may be days depending on the size and complexity of your database and the size of your shared_pool in the SGA.

Auditing is an option you can turn on from this point forward to track who made changes to which table and when, but it does not capture the actual SQL statement or the records affected.

The bottom line is there is no built-in way in Oracle to get the last DML statement for a table.

 

[hallux]

Thanks for your help. The v$sqltext had enough information for me. Also I found that in the tool PL/SQL Developer, under Tools, Sessions, it presents the information nicely.

Question for sem: Do you have a link for information on how to "To turn sql tracing on". I checked google and my limited Oracle manual collection and couldn't find instructions.

thanks,
-Brent