Forum Stats

  • 3,840,090 Users
  • 2,262,565 Discussions
  • 7,901,149 Comments

Discussions

event based mechanism to check if a view was refreshed

703344
703344 Member Posts: 1
edited Aug 7, 2009 4:31AM in General Database Discussions
Hi All,

I need to track an application that is not writing any logs. For this I have created a view on v$sql, v$session and user_object table to see which query is being executed currently by my application. Now i want to know when my view was updated so that i can insert those rows in a temp table and process accordingly.

here is the view i created

create view MY_View as select vs.SQL_ID, v.SESSION_ID,v.OS_USER_NAME,vi.MODULE,vi.COMMAND,vs.SQL_FULLTEXT,vs.SQL_TEXT,vs.ADDRESS from
v$locked_object v ,user_objects u,v$session vi,v$sql vs where vs.SQL_ID = vi.SQL_ID and v.OBJECT_ID = u.object_id and vi.SID = v.SESSION_ID and vi.MODULE like 'MY_MODULE'

I have tried instead of triggers but after studying them a bit more carefully i found they are not what i need.

Is there anyway i can know when my view was refreshed.

Best Regards,
Faran

Answers

  • 436423
    436423 Member Posts: 460
    You can't do this and this is the wrong approach anyway. The quick answer is triggers are not supported on sys objects, this is to prevent you from corrupting the data dictionary. The longer answer is that v$ views aren't in fact views of tables they are views of memory space of the Oracle executable, made to look like views. They aren't 'inserted' or 'deleted' in the way that regular tables are, the views are 'updated' by the Oracle code updating it's own memory during the course of execution.

    To track an application that has no instrumentation, you would need to trace the database session(s) that the application uses. You need to read about sql_trace (AKA event 10046) and the dbms_monitor package (on 10g) or dbms_system.set_ev packaged procedure (9i and earlier). You very unhelpfully did not provide your Oracle version. This will produce a trace file that can be anaylsed using tkprof (comes with the database), trace analyzer (requires Metalink) or any of a whole number of 3rd party trace file tools, some free, some commercial.

    If your application uses PL/SQL, the DBMS_PROFILER package is also very useful.

    Google, search these forums.

    HTH

    Chris
This discussion has been closed.