To know all the sql DDL,DML statements executed by a user

I have some procedures which will invoke some DML,DDL statements,how will I find all those executed by that procedure.
nandini22Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
n4nazimConnect With a Mentor Commented:
Hi Nandini,

I checked the flw query for a procedure containing INSERT/DELETE stmts:

SELECT * FROM v$SQLAREA S, dba_users U
WHERE UPPER(S.SQL_TEXT) LIKE '%SPECIFY_TABLE_NAME_HERE%'
and U.user_id = S.PARSING_USER_ID
and UPPER(U.USERNAME) = 'SPECIFY_USER_ID_HERE'
ORDER BY s.FIRST_LOAD_TIME

The above query worked perfectly for me.... Might be there is a case problem at yr end.

pl use the qry i have pasted now.
PLEASE NOTE, THE VALUES U SPECIFY OVER HERE SHOULD BE IN UPPERCASE.

Hope this helps,
Rgds
Nazim M
0
 
n4nazimCommented:
Hi,

Can u pl tell me whether u are using Oracle on Windows or Linux ?

Rgds,
NHM
0
 
nandini22Author Commented:
hi,
I am working on oracle9i on solaris.I would like to know all the DML,DDL statements executed by that procedure
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
baonguyen1Commented:
I think you can find which user execute the statemnet by query dba_users and v$sqlarea view but not sure how to find which procedure involve. May be dba_tab_privs view can help ?

0
 
baleeCommented:
For DDLs, you can create database triggers (BEFORE / AFTER DDL) and you can use them to capture the event of any DDL in your database.

For DML, you have to use triggers on tables are affected. I don't think you can capture DML events in general.
0
 
kannankumaraCommented:
Hi

Its not possible to find out the DML and DDL statements "executed by a procedure". As baonguyen1 said you can see the statements executed in a session or by a user, but not by a procedure.

Could you be more eloborate in your requirement as to why do you want to know the statements being executed by a procedure?! Is this for some log purpose? Normally in a scenario like this, we would send the log information to a pipe and monitor the pipe in another session or write log files using utl_file. By doing so you can analyse the flow of execution of your procedure under various conditions/business logics.

We might be able to help something if you could explain "the reason" for which you want to know the statements being executed in a procedure.
0
 
n4nazimCommented:
Hi,

There is a way where in u can do this . Here it is...

1. Execute your procedure.

2. Connect to your database using SYSTEM login ..

3. SYSTEM login provides a dynamic view v$SQLAREA which consists of all queries executed.

Now when u say

SELECT * FROM v$SQLAREA

it show u many records ( each record per query executed ).

The question arises how to search for the queries of your stored procedure in this view's data

The answer is that, v$SQLAREA consists of a column SQL_TEXT which contains the SQL text of the query execute. You need to search in this view based on this column's data.

For example, I have a procedure which had a query like

OPEN CURSOR FOR
SELECT col1 FROM table1 WHERE rownum < 100;

Now if ui need to search for this query in v$SQLAREA, u would search using part of the query to specify in the WHERE condition. If u cant use part of the query, use the table name which is used in the query. At least this would filter the records from the record-set of v$SQLAREA. Here is the query which I used for above example

SELECT * FROM v$SQLAREA
WHERE SQL_TEXT LIKE '%rownum < 100%'.

If i can't use the above WHERE condition, I would search for something like

SELECT * FROM v$SQLAREA
WHERE SQL_TEXT LIKE '%table1%'.

Atleast it would be filter for me only those records for which table1 was used. In this recordset, i will have to search manually.

If u have any queries abt how to do this, revert back ..
Hope this helps.
Rgds,
NHM

NOTE, I HAD USED SOMETHING FROM MY ACTUAL QUERY TO SPECIFY IN THE WHERE CONDITION OF THE v$SQLAREA query.

For me, this has worked for all the queries inside the procedure. I need to check with





0
 
n4nazimCommented:
Hi,

Pl ignore text displayed after my Name in my previous answers..

Rgds,
NHM
0
 
n4nazimCommented:
Hi

Pl post any replies for this question !!!!

Rgds,
Nazim M
0
 
nandini22Author Commented:
Hi,
Thanks,
Your solution to query v$sqlarea is fine ,it shows all the sql's executed.Would you help me out for finding for a particular user and to find it ordered by timestamp or anything as such.
0
 
baonguyen1Commented:
The query looks like this:

SELECT
    b.username username, a.FIRST_LOAD_TIME
    a.command_type,  a.sql_text
    FROM v$sqlarea a, dba_users b
    WHERE
     a.parsing_user_id=b.user_id
ORDER by a.FIRST_LOAD_TIME;
   
0
 
nandini22Author Commented:
Hi Kannan,
My requirement is as follows

I have a set of stored packages consisting of numerous stored procedures and functions within them in Oracle7.3.The package specification is known,and the package body is in binary wrapped.What would be the ideal way to reengineer these in Oracle9.0.What would be the way to read what the package procedure or function does when executed,like what tables are affected,what kind of operation was performed on the related tables DML,DDL specifically all the SQL's
Is it possible to reengineer the packages?
Are there any built-in packages to trace all the SQL's invoked by the packages ?
0
 
n4nazimCommented:
Hi,

Here is what u want ..

SELECT * FROM v$SQLAREA S, dba_users U
WHERE S.SQL_TEXT LIKE '%table1%'
and U.user_id = S.PARSING_USER_ID
and U.USERNAME = 'user_name'
ORDER BY s.FIRST_LOAD_TIME


This query will filter the result-set for a particular user "user_name'. Please note this is THE ORACLE DATABASE USER and not the APPLICATION USER.

RGD ordering by timestamp, this can be handled by the ORDER BY clause.

For any kind of help, pl revert back.
Rgds
Nazim M
0
 
n4nazimCommented:
Hi,

If u want to ORDER the result set based on some other parameters let know abt the same

Rgds
Nazim M
0
 
nandini22Author Commented:
Hi Nazim,
It shows me only the select,and the Pl/sql code of my procedure but not the insert,deletion.. done by my pl/sql code.Id sql_trace the only option or any other view which can help me with the DML's

Regards

Nandini22
0
 
nandini22Author Commented:
In the same process I have come across where a sequence value is returned and assigned to a variable in a function the code is something like the below

create or replace FUNCTION test33
      (newid_in IN table1.newid%TYPE)
       RETURN table2.seqid%TYPE is
       seq_id table2.seqid%type;
begin
       seq_id:=test_SEQ.NEXTVAL;
        return seq_id;
        end;
I get the following error message
Table,View Or Sequence reference
'test_SEQ.NEXTVAL' not allowed in this context

How can I use a sequnce value to be inserted into a table ,return the sequence value through a stored procedure or function.

0
 
n4nazimCommented:
Hi Nandini,

Regarding the function, here's the code

create or replace FUNCTION test33
      (newid_in IN table1.newid%TYPE)
       RETURN table2.seqid%TYPE is
       seq_id table2.seqid%type;
begin
   SELECT  test_SEQ.NEXTVAL INTO seq_id FROM dual
    return seq_id;
end;

Hope this helps,
Nazim M
0
 
n4nazimCommented:
Hi Nandini,

Regarding the function, here's the code

create or replace FUNCTION test33
      (newid_in IN table1.newid%TYPE)
       RETURN table2.seqid%TYPE is
       seq_id table2.seqid%type;
begin
   SELECT  test_SEQ.NEXTVAL INTO seq_id FROM dual;
    return seq_id;
end;

Hope this helps,
Nazim M
0
 
nandini22Author Commented:
Thanks Nazim ,it works.Will it work for DDL statements and Dynamic SQL Statements

Regards
Nandini
0
 
n4nazimCommented:
Hi Nandini,

Yes, it should work for them as well,

Rgds,
Nazim M
0
 
anand_2000vCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.