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)
Fetching the last DML statements exectuted. [message #422262] Tue, 15 September 2009 05:32 Go to next message
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
Re: Fetching the last DML statements exectuted. [message #422265 is a reply to message #422262] Tue, 15 September 2009 05:42 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
query v$sqlarea
Re: Fetching the last DML statements exectuted. [message #422266 is a reply to message #422262] Tue, 15 September 2009 05:43 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Activate trace
Create user with the application
Stop trace
Check SQL statements in trace file.

Regards
Michel
Re: Fetching the last DML statements exectuted. [message #422268 is a reply to message #422267] Tue, 15 September 2009 05:48 Go to previous messageGo to next message
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

Re: Fetching the last DML statements exectuted. [message #422269 is a reply to message #422268] Tue, 15 September 2009 05:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Fetching the last DML statements exectuted. [message #422275 is a reply to message #422271] Tue, 15 September 2009 06:03 Go to previous messageGo to next message
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
Re: Fetching the last DML statements exectuted. [message #422422 is a reply to message #422262] Wed, 16 September 2009 05:22 Go to previous message
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:
  


Current Time: Fri Dec 02 12:17:03 CST 2016

Total time taken to generate the page: 0.14385 seconds