Solved

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

Posted on 2004-03-23
21
1,906 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

920 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

14 Experts available now in Live!

Get 1:1 Help Now