Solved

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

Posted on 2004-03-23
21
1,895 Views
Last Modified: 2008-01-09
I have some procedures which will invoke some DML,DDL statements,how will I find all those executed by that procedure.
0
Comment
Question by:nandini22
  • 10
  • 6
  • 2
  • +3
21 Comments
 
LVL 2

Expert Comment

by:n4nazim
ID: 10664675
Hi,

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

Rgds,
NHM
0
 

Author Comment

by:nandini22
ID: 10664932
hi,
I am working on oracle9i on solaris.I would like to know all the DML,DDL statements executed by that procedure
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10665195
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
 
LVL 1

Expert Comment

by:balee
ID: 10665446
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
 
LVL 3

Expert Comment

by:kannankumara
ID: 10673588
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
 
LVL 2

Expert Comment

by:n4nazim
ID: 10675219
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
 
LVL 2

Expert Comment

by:n4nazim
ID: 10675235
Hi,

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

Rgds,
NHM
0
 
LVL 2

Expert Comment

by:n4nazim
ID: 10710974
Hi

Pl post any replies for this question !!!!

Rgds,
Nazim M
0
 

Author Comment

by:nandini22
ID: 10711007
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
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10711061
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:nandini22
ID: 10711098
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
 
LVL 2

Expert Comment

by:n4nazim
ID: 10711207
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
 
LVL 2

Expert Comment

by:n4nazim
ID: 10711218
Hi,

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

Rgds
Nazim M
0
 

Author Comment

by:nandini22
ID: 10711237
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
 

Author Comment

by:nandini22
ID: 10711394
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
 
LVL 2

Expert Comment

by:n4nazim
ID: 10711450
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
 
LVL 2

Expert Comment

by:n4nazim
ID: 10711452
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
 
LVL 2

Accepted Solution

by:
n4nazim earned 125 total points
ID: 10711970
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
 

Author Comment

by:nandini22
ID: 10712022
Thanks Nazim ,it works.Will it work for DDL statements and Dynamic SQL Statements

Regards
Nandini
0
 
LVL 2

Expert Comment

by:n4nazim
ID: 10712383
Hi Nandini,

Yes, it should work for them as well,

Rgds,
Nazim M
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 12970808
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now