SEO

#1 (**permalink**) 02-25-2008, 02:57 AM

I am using Oracle 9.2.0.5 on HP UNX 11i. An Oracle error occurred at
some point in the past. I want to find what sql statements might have
caused the problem. Is there a way to find sql statements run between
two points of time by looking at v$ views.

I know using log miner, one can get some information. However, log
miner only provides insert, delete and update statements but not select
statements.

This is a production system. It does not have any Oracle auditing
implemented.

Thanks

#2 (**permalink**) 02-25-2008, 02:57 AM

On 9 Mar 2006 12:05:22 -0800, dbaplusplus@hotmail.com wrote:

>I am using Oracle 9.2.0.5 on HP UNX 11i. An Oracle error occurred at
>some point in the past. I want to find what sql statements might have
>caused the problem. Is there a way to find sql statements run between
>two points of time by looking at v$ views.
>
>I know using log miner, one can get some information. However, log
>miner only provides insert, delete and update statements but not select
>statements.
>
>This is a production system. It does not have any Oracle auditing
>implemented.
>
>
>
>Thanks

Using statspack taking snapshots on level 5

--
Sybrand Bakker, Senior Oracle DBA

#3 (**permalink**) 02-25-2008, 02:57 AM

see below, a little clunky... relies on the fact that it had to be the
first time the SQL was ran.. and not aged out... but you never know,
you might get lucky. The best way is to take snapshots as Sybrand
recommended.

change date/times etc

SELECT
*
FROM
(
SELECT
SQL_TEXT,TO_DATE(FIRST_LOAD_TIME,'YYYY-MM-DD/HH24:MI:SS')
FIRST_LOAD_TIME
FROM
V$SQL
)
WHERE
FIRST_LOAD_TIME BETWEEN TO_DATE('2006-03-09 09:00:00','YYYY-MM-DD
HH24:MI:SS') AND TO_DATE('2006-03-09 11:00:00','YYYY-MM-DD HH24:MI:SS')

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Is sthere any way to find what sql stataements were run between two points in time