OraFAQ Forum: Server Administration » sql plus transaction ...

Home » RDBMS Server » Server Administration » sql plus transaction ... (sql plus)

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

sql plus transaction ... [message #575328]

Tue, 22 January 2013 05:10

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 ...

Report message to a moderator

Re: sql plus transaction ... [message #575334 is a reply to message #575328]

Tue, 22 January 2013 05:39

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

Senior Member
Account Moderator

No.

Regards
Michel

Report message to a moderator

Re: sql plus transaction ... [message #575335 is a reply to message #575334]

Tue, 22 January 2013 05:41

yasinirshad
Messages: 12
Registered: January 2013

Junior Member

Hi, Can we know which tables got affected atleast ?
Thanks.

Report message to a moderator

Re: sql plus transaction ... [message #575338 is a reply to message #575328]

Tue, 22 January 2013 05:48

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

Senior Member
Account Moderator

No.

Regards
Michel

Report message to a moderator

Re: sql plus transaction ... [message #575340 is a reply to message #575338]

Tue, 22 January 2013 05:50

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.

Report message to a moderator

Re: sql plus transaction ... [message #575342 is a reply to message #575340]

Tue, 22 January 2013 05:52

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

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

Report message to a moderator

Re: sql plus transaction ... [message #575430 is a reply to message #575342]

Wed, 23 January 2013 03:57

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.

Report message to a moderator

Re: sql plus transaction ... [message #575439 is a reply to message #575430]

Wed, 23 January 2013 04:43

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

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

Report message to a moderator

Re: sql plus transaction ... [message #575440 is a reply to message #575439]

Wed, 23 January 2013 04:45

yasinirshad
Messages: 12
Registered: January 2013

Junior Member

Hi Michel,

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

Thanks.

Report message to a moderator

Re: sql plus transaction ... [message #575441 is a reply to message #575440]

Wed, 23 January 2013 05:00

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

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

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

Report message to a moderator

Re: sql plus transaction ... [message #575926 is a reply to message #575448]

Tue, 29 January 2013 07:08

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Can't the session be traced?

Report message to a moderator

Re: sql plus transaction ... [message #575928 is a reply to message #575926]

Tue, 29 January 2013 07:57

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

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

Report message to a moderator

Re: sql plus transaction ... [message #575930 is a reply to message #575928]

Tue, 29 January 2013 08:18

gazzag
Messages: 1119
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

Report message to a moderator

Previous Topic:	error message in TOAD
Next Topic:	Change Database Character set

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 06 02:11:55 CDT 2025