Query/Find Oracle Queries that populate an Applications Data

Question

I work with an application that uses an Oracle Database to administer its data. Within the application we have a form that searches data from the database and surfaces up the results. Often times, I use the results from the search dialogue to confirm the the SQL queries I build to ensure I am getting correct results.

I got to thinking that there must be SQL's being run the in the background that queries the database when someone uses the search form. If I am correct, how would I go about finding those queries? Is there a logical place I can find them? (I use SQL Developer mostly to access the database). Are they even stored in the database? Stored in the application? Stored somewhere else? Would this be dependent upon how the application was designed?

This is a case of 'I don't know what I don't know'.

I have reached out to the manager of the application and this person kinda shrugged their shoulders and didn't know either. We do have a contractor that manages the database but because we bill them every time we ask them for something, I have been told not to ask for this. Any thoughts are appreciated.

d r · Answer

You should, probably, try to find the particular SQLs info somewhere within (quite a few) Oracle's v$ views. https://docs.oracle.com/database/121/REFRN/GUID-2B9DD73A-4C3A-467A-A934-01B705AB77A8.htm#REFRN-GUID-2B9DD73A-4C3A-467A-A934-01B705AB77A8
Maybe one of these two SQLs could help you to find what you are looking for:

v$SQLAREA

Select * 
From v$SQLAREA
Where SubStr(nvl(ACTION, 'SYS'), 1, 3) != 'SYS'

v$SESSION, v$ACTIVE_SESSION_HISTORY, v$SQLAREA, DBA_OBJECTS

Select
    ses.SID "SID",
    ses.SERIAL# "SERIAL_NO",
    sql.SQL_FULLTEXT "SQL_FULLTEXT",
    obj.OBJECT_NAME "OBJ_NAME",
    obj.owner "OBJ_OWNER",
    ash.event "EVENT",
    ash.session_state "SESSION_STATE",
    ash.top_level_sql_id "TOP_LEVEL_SQL_ID",
    ash.sql_exec_id "SQL_EXEC_ID",
    to_char(ash.sql_exec_start, 'yyyy-mm-dd hh24:mi:ss') "SQL_EXEC_START",
    ash.*
From
    v$SESSION ses
Inner Join
    v$ACTIVE_SESSION_HISTORY ash on ses.SID = ash.SESSION_ID and ses.SERIAL# = ash.SESSION_SERIAL#  
Left Join
    v$SQLAREA sql on ash.TOP_LEVEL_SQL_ID = sql.SQL_ID                              
Left Join
    DBA_OBJECTS obj on ash.CURRENT_OBJ# = obj.OBJECT_ID
Where
    ash.SQL_EXEC_ID Is Not Null
Order By
    ash.SAMPLE_ID desc

Regards...

Query/Find Oracle Queries that populate an Applications Data

1 Answers