statement - show view in oracle sql developer
How to see the actual Oracle SQL statement that is being executed (4)
I think as developer the interest of view is the "select" which make it valid and runnable, and the easiest way to edit a view is by using another application like pl/sql Developer or by TOAD.
editing any object in oracle database by text editor only like sqlplus.exe takes a long time for simple tasks, and it is a headache way to do so.
I'm using a custom-built inhouse application that generates a standard set of reports on a weekly basis. I have no access to the source code of the application, and everyone tells me there is no documentation available for the Oracle database schema. (Aargh!)
I've been asked to define the specs for a variant of an existing report (e.g., apply additional filters to constrain the data set, and modify the layout slightly). This sounds simple enough in principle, but is difficult without any existing documentation.
It's my understanding that the logs can't help me because the report only queries the database; it does not actually insert, delete, or update database values, so there is nothing to log (is this correct?).
So my question is this: is there a tool or utility (Oracle or otherwise) that I can use to see the actual SQL statement that is being executed while the report generation job is still running? I figure, if I can see what tables are actually being accessed to produce the existing report, I'll have a very good starting point for exploring the schema and determining the correct SQL to use for my own report.
I think the V$SQLAREA table contains what you're looking for (see columns SQL_TEXT and SQL_FULLTEXT).
Sorry for the short answer but it is late. Google "oracle event 10046 sql trace". It would be best to trace an individual session because figuring which SQL belongs to which session from v$sql is no easy if it is shared sql and being used by multiple users.
If you want to impress your Oracle DBA friends, learn how to set an oracle trace with event 10046, interpret the meaning of the wait events and find the top cpu consumers.
Quest had a free product that allowed you to capture the SQL as it went out from the client side but not sure if it works with your product/version of Oracle. Google "quest oracle sql monitor" for this.
You can use the following query:
SELECT VIEW_NAME, TEXT FROM USER_VIEWS;
or you can use ALL_VIEWS, as in
SELECT VIEW_NAME, TEXT FROM ALL_VIEWS;