V$sql gives you the statistics for the SQL executed , I can join V$seesion and v$sql putting V$session.sql_address = v$sql.address, which will give me sqls which are being currently executing. When the above select statement is executed again, func1 will be called additional 6 times.Once they are done(means their status comes to inactive), sql_address in v$session will be '00' , but in v$sql I still can see that sql , but I will be missing the session info(who executed that sql). Can you please help me trying to find out sqls executed by killed or inactive sessions. , Going in little more detail , if I want to maintain sql list(per session) for a short intervel of time. Then is there any any by which I can force to maintain SQL address in V$session for a short interval of time, or is there any internal event can be fired before resetting sql address(in v$session) and I can pick that event. I want to add some SQL auditing code into to func1 so that: When the above select statement is executed once, the additional codes will be executed 1 time.

oracle sql trigger if updating-53oracle sql trigger if updating-4

This prints out the general SQL but it replaces the values with bind variables... Kyte OBJECT_SCHEMA : DEMO OBJECT_NAME : EMP POLICY_NAME : ENAME_SAL SCN : 919457 SQL_TEXT : select empno, ename from emp where sal set echo off Enter to continue ================================================================= The FGA works on the base tables, you cannot get around it via a higher level view [email protected] If I get what ctas statement a user is executing, I can estimate how long is it going to run from the table size.

I don't think Tom's code is replacing the values with bind variables as suggested - the actual code would be using bind variables (which I would consider a good thing). - Mark No, you are correct -- i'm not rewriting the SQL. COM select empno, ename from emp; EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 14 rows selected. It is too hard to judge from "db file scattered read scan" wait event vs table name, becuase we do have lots of full table scan's going on aside from the ctas commands.

The bind variables (good things) were in there, he was looking for the values -- which you cannot get from v$ tables (only for YOUR session could you see that) set echo off Enter to continue ================================================================= if you do not use CBO, you will get "false positives", it cannot check your predicate using RBO [email protected] Tom, I am trying to find out the Sql executed by the session which are currently not active, they have been killed or ended. LAST_LOAD_TIME from v$session a,v$sql b where status = 'ACTIVE' and rawtohex(prev_sql_addr) '00' and b.address = prev_sql_addr and username is not null order by last_call_et) loop for y in ( select max(decode(piece,0,sql_text,null)) || max(decode(piece,1,sql_text,null)) || max(decode(piece,2,sql_text,null)) || max(decode(piece,3,sql_text,null)) sql_text from v$sqltext_with_newlines where piece create table tab1 (col1 number, col2 number); insert into tab1 values (10, 15); insert into tab1 values (20, 25); insert into tab1 values (30, 35); commit; create package pkg1 as function func1(i in number) return number; end; / create package body pkg1 as function func1(i in number) return number is begin -- additional code that audit the calling SQL statement return i+1; end; end; / select pkg1.func1(col1), pkg1.func1(col2) from tab1; When the above select statement is executed once, func1 will be called 6 times.

For the last several months, I have been working primarily with Oracle 8i and 10g databases.

Over the last 10 years, 95% of my work has been with MS SQL server.

The other 5% was with Oracle, but it was limited to simple SELECT statements or calling stored procedures that other people wrote.

You don’t realize how much basic knowledge you take for granted until you get outside of your comfort zone and have to stop and figure out how to do every little thing.

Here’s a list of the top 10 things I wish I had known about Oracle – PL/SQL coming from an MS SQL Server – T-SQL background.

It shows you who's logged in and active -- and if active, the statement they are executing (and the last et text shows you how long that statement has been executing).

Currently, it shows only SQL that is executing right now, just change the predicate from "where status = 'ACTIVE'" to "where status = status" if you want to see the currently executing as well as LAST executed (in which case the last et column text shows you how long they've been idle -- not how long that statement took to execute): column status format a10 set feedback off set serveroutput on select username, sid, serial#, process, status from v$session where username is not null / column username format a20 column sql_text format a55 word_wrapped set serveroutput on size 1000000 declare x number; begin for x in ( select username