How to see query executed for a specific user from V$SQL

monitoringoracle

I am trying to have the queries used for a specific user at the end of the day. But it seems the queries in this view V$SQL are deleted every short time. I am not sure how much time. Also i dont know why they are deleted if i still see some records with older LAST_ACTIVE_TIME is there a way to save them before they are deleted. For that when are they deleted?or other suggestion. Hope you can help me.

Thank you!

Best Answer

You can query v$sqlarea, which holds sql history for a little longer than v$sql (holds info only about current running sql). Both have similar columns, but depending on the report you want to extract, you might wanna join v$sqlarea with other views. But V$sqlarea is also limited to a period of time, and if you need to go more in depth, the view is DBA_HIST_SQLTEXT. Keep in mind that for every dynamic (V$) view, there is a corresponding dba_hist table... But since dba_hist views are often maintained by AWR, you might need to be licensed for diagnostic/ tuning pack for complete reports.

Related Solutions

Oracle SQL for left outer join to rownum = 1 of another query

Basically, I think you should just get the max date, if that is all you are looking for here, using your same filter, etc. You already have STATUS (=COMP) and WONUM (JOIN). If you needed the whole record from this table, and it was more complicated than this, I would recommend the oracle inline analytic functions with over/partition by logic to filter by the max date.

SELECT *
FROM   WORKORDER
       LEFT OUTER JOIN (SELECT WONUM AS STATUSWONUM 
                               , STATUS AS STATUS
                               , MAX(CHANGEDATE) AS STATUSCHANGEDATE
                        FROM   WOSTATUSHISTORY
                        WHERE  STATUS = 'COMP'
                        GROUP  BY WONUM, STATUS)LASTCOMPLETE
         ON ( WORKORDER.WONUM = LASTCOMPLETE.STATUSWONUM ) 

;

Sql-server – How to monitor INSERT failures

Just to extend on Max Vermon's comment...

INSERT failures should be monitored at the data access layer.

An exception is raised by SQL with information regarding the error for failed INSERTs. If you are accessing data through a Stored Procedure (SP) you can handle the error with the SP using the BEGIN TRY...END TRY...BEGIN CATCH...END CATCH exception handling blocks.

You can easily write code to store the exception information into a generic table.

You can use exception handling in other programming environments such as C#, VB.NET etc...

Here is a good example of handling exceptions in T-SQL: http://www.codeproject.com/Articles/38211/Exception-Handling-in-SQL-Server

Best Answer

Related Solutions

Oracle SQL for left outer join to rownum = 1 of another query

Sql-server – How to monitor INSERT failures

Related Question