OraFAQ Forum: SQL & PL/SQL » Fetching the last DML statements exectuted.

Home » SQL & PL/SQL » SQL & PL/SQL » Fetching the last DML statements exectuted. (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Fetching the last DML statements exectuted. [message #422262]

Tue, 15 September 2009 05:32

NewLife
Messages: 170
Registered: April 2008

Senior Member

Hi,

My Application (A 3rd party tool) access my database. I need to automate the creation of a new user, which is currently done through the application manually.

The problem is that I need to identify the different tables that are being hit during the creation of new user through the application. Since there are around 180 table and it is practically impossible to check each and every table post each step of creation of new user, I want to know if there is any way that I can find out the set of DML statements that are executed by the application during the process of creating a new user.]

The module being executed is "Mgr.exe".

Since it is a 3rd part tool and also due to some budget constarints it is not possible to ask the vendor for the code Sad

Any suggestions are welcome.

Thanks Smile

Report message to a moderator

Re: Fetching the last DML statements exectuted. [message #422265 is a reply to message #422262]

Tue, 15 September 2009 05:42

ayush_anand
Messages: 417
Registered: November 2008

Senior Member

query v$sqlarea

Report message to a moderator

Re: Fetching the last DML statements exectuted. [message #422266 is a reply to message #422262]

Tue, 15 September 2009 05:43

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

Check this link and search for dbms_monitor.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:330817260752

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_monitor.htm#ARPLS091

Regards

Raj

[Updated on: Tue, 15 September 2009 05:43]

Report message to a moderator

Re: Fetching the last DML statements exectuted. [message #422267 is a reply to message #422262]

Tue, 15 September 2009 05:45

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Activate trace
Create user with the application
Stop trace
Check SQL statements in trace file.

Regards
Michel

Report message to a moderator

Re: Fetching the last DML statements exectuted. [message #422268 is a reply to message #422267]

Tue, 15 September 2009 05:48

NewLife
Messages: 170
Registered: April 2008

Senior Member

I have queried v$sqlarea, It did not prove good. I am going in for the 2 suggestion by S.Rajaram, then I would look at your's Michel.

Thanks All.

I would revert once i have something Smile

Report message to a moderator

Re: Fetching the last DML statements exectuted. [message #422269 is a reply to message #422268]

Tue, 15 September 2009 05:49

ayush_anand
Messages: 417
Registered: November 2008

Senior Member

query v$sqlarea after creating user Wink

but Michel's suggestion is better

[Updated on: Tue, 15 September 2009 05:53]

Report message to a moderator

Re: Fetching the last DML statements exectuted. [message #422271 is a reply to message #422268]

Tue, 15 September 2009 05:56

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is no difference between Raj's solution and mine. He just gave you the way to activate this trace.

Regards
Michel

Report message to a moderator

Re: Fetching the last DML statements exectuted. [message #422275 is a reply to message #422271]

Tue, 15 September 2009 06:03

NewLife
Messages: 170
Registered: April 2008

Senior Member

lolz.... Yeah !!

Thanks Guys, It's been really late since I visited this forum, almost 3 - 4 months. Feels like home Smile

Report message to a moderator

Re: Fetching the last DML statements exectuted. [message #422422 is a reply to message #422262]

Wed, 16 September 2009 05:22

ayush_anand
Messages: 417
Registered: November 2008

Senior Member

One more solution I stumbled upon::
Frequently, people want to know what SQL their application actually executes. They cannot modify the application and setting SQL_TRACE ON is far too costly.
Using an ON LOGON trigger for some users of the application, you can automatically capture, into the OUTLINE tables, all of the SQL the application executes at run‐time. You can use this for tuning or analysis later.
[Source Expert one on one Oracle Tom Kytes]

[Updated on: Wed, 16 September 2009 05:24]

Report message to a moderator

Previous Topic:	Bulk Collect for Record type
Next Topic:	ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 15:59:50 CST 2024