Home » RDBMS Server » Server Administration » sql plus transaction ... (sql plus)
sql plus transaction ... [message #575328] Tue, 22 January 2013 05:10 Go to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
Hi,

When a user clicks on Save Button on a form, can we know from sql plus (Oracle) all the related queries (insert/Update etc)

for that transaction ??

Please let me know.

Thanks ...
Re: sql plus transaction ... [message #575334 is a reply to message #575328] Tue, 22 January 2013 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: sql plus transaction ... [message #575335 is a reply to message #575334] Tue, 22 January 2013 05:41 Go to previous messageGo to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
Hi, Can we know which tables got affected atleast ?
Thanks.
Re: sql plus transaction ... [message #575338 is a reply to message #575328] Tue, 22 January 2013 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: sql plus transaction ... [message #575340 is a reply to message #575338] Tue, 22 January 2013 05:50 Go to previous messageGo to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
ok.
But is there a way to find out during last n minutes which tables got affected ? From any logs ?

Thanks to reply.
Re: sql plus transaction ... [message #575342 is a reply to message #575340] Tue, 22 January 2013 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Redo logs; use Log Miner.
Or, depending on the Oracle version you didn't share with us (with 4 decimals), some Oracle views.

Regards
Michel
Re: sql plus transaction ... [message #575430 is a reply to message #575342] Wed, 23 January 2013 03:57 Go to previous messageGo to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
Hi,

Thanks for the reply. The Oracle version we are using is 10.2.0.1.0
Kindly let me know which view to look for.
If possible please tell me the query.

Thanks a lot.
Re: sql plus transaction ... [message #575439 is a reply to message #575430] Wed, 23 January 2013 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot know the queries, you can only know which rows have been modified/inserted/deleted.
Queries are stored nowhere.

Regards
Michel
Re: sql plus transaction ... [message #575440 is a reply to message #575439] Wed, 23 January 2013 04:45 Go to previous messageGo to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
Hi Michel,

From which view can i know which row has been modified/inserted?

Thanks.
Re: sql plus transaction ... [message #575441 is a reply to message #575440] Wed, 23 January 2013 05:00 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Quote:
But is there a way to find out during last n minutes which tables got affected


below may help

SELECT table_name,inserts,updates,deletes,timestamp FROM USER_TAB_MODIFICATIONS

[Updated on: Wed, 23 January 2013 06:02] by Moderator

Report message to a moderator

Re: sql plus transaction ... [message #575448 is a reply to message #575441] Wed, 23 January 2013 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> desc USER_TAB_MODIFICATIONS
 Name                             Null?    Type
 -------------------------------- -------- --------------------
 TABLE_NAME                                VARCHAR2(30)
 PARTITION_NAME                            VARCHAR2(30)
 SUBPARTITION_NAME                         VARCHAR2(30)
 INSERTS                                   NUMBER
 UPDATES                                   NUMBER
 DELETES                                   NUMBER
 TIMESTAMP                                 DATE
 TRUNCATED                                 VARCHAR2(3)
 DROP_SEGMENTS                             NUMBER

Where do you see anything about rows? There only are overall counts.
(And trim your lines, ending spaces are useless and just waste space.)

You can get this information from FLASHBACK_TRANSACTION_QUERY... while the information is still in the UNDO tablespace.

Regards
Michel
Re: sql plus transaction ... [message #575926 is a reply to message #575448] Tue, 29 January 2013 07:08 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Can't the session be traced?
Re: sql plus transaction ... [message #575928 is a reply to message #575926] Tue, 29 January 2013 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but I remind the question: "can we know from sql plus (Oracle) all the related queries (insert/Update etc)".

Regards
Michel
Re: sql plus transaction ... [message #575930 is a reply to message #575928] Tue, 29 January 2013 08:18 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Michel Cadot wrote on Tue, 29 January 2013 13:57
Yes but I remind the question: "can we know from sql plus (Oracle) all the related queries (insert/Update etc)".


Yes, perhaps he didn't know the correct question to ask Smile

To the OP, is the following any use to you?

DBMS_MONITOR.SESSION_TRACE_ENABLE
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm#i1003679

and

DBMS_MONITOR.SESSION_TRACE_DISABLE
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm#i1003678
Previous Topic: error message in TOAD
Next Topic: Change Database Character set
Goto Forum:
  


Current Time: Fri Mar 29 06:34:26 CDT 2024