Home » SQL & PL/SQL » SQL & PL/SQL » Query Required
Query Required [message #261749] Thu, 23 August 2007 08:49 Go to next message
sandydba
Messages: 2
Registered: August 2007
Location: Bangalore
Junior Member

Hi All,

One of the user has given the query in production which we want to identify which user has executed the query. Can anyone please let me know how to retrieve the user who has given the query.

Regards
Sandeep
Re: Query Required [message #261750 is a reply to message #261749] Thu, 23 August 2007 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For a query (select) and the past, there is no way. You can only who currently executes it (join v$sql and v$session).
For a DML (insert, update, delete) you can use Log Miner.

Regards
Michel
Re: Query Required [message #261759 is a reply to message #261750] Thu, 23 August 2007 09:12 Go to previous messageGo to next message
sandydba
Messages: 2
Registered: August 2007
Location: Bangalore
Junior Member

Hi Michel,

Actuall the query was select not any of DML ststaements.
It was run in last week. Is there any possible way to find it out who ran those queries.


Regards
Sandeep
Re: Query Required [message #261761 is a reply to message #261759] Thu, 23 August 2007 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, no one I am aware.

Regards
Michel
Re: Query Required [message #263336 is a reply to message #261749] Wed, 29 August 2007 14:10 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
For identification you need audit on, but this reduces database peformance
Re: Query Required [message #263398 is a reply to message #261749] Wed, 29 August 2007 23:40 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
For a query (select) and the past, there is no way.


It can be got which type of query is ran in past by flashback version query.

But it is depend on undo_retention parameter.

If you want to get before one week it is not possible. If you want to get before 5 minutes (or likely) ago it's possible.
Re: Query Required [message #263402 is a reply to message #261749] Wed, 29 August 2007 23:46 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
Actuall the query was select not any of DML ststaements.


The whole query you can get but 1 week may or may not possible. May be you can get that you ran 1 hour ago. The full query.But it needs an effort to do it.

You first need to determine the value of the versions_xid pseudo column and then

query from flashback_transaction_query. Undo_sql will give you actual query that you ran.





Regards,
Mohammad Abdul Momin Arju,
Islamic University of Technology.(IUT).
Re: Query Required [message #263404 is a reply to message #261749] Wed, 29 August 2007 23:51 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
One of the user has given the query in production which we want to identify which user has executed the query.


By the way you can also get back this information who ran the query.
Re: Query Required [message #263427 is a reply to message #263398] Thu, 30 August 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju wrote on Thu, 30 August 2007 06:40
Quote:
For a query (select) and the past, there is no way.


It can be got which type of query is ran in past by flashback version query.

But it is depend on undo_retention parameter.

If you want to get before one week it is not possible. If you want to get before 5 minutes (or likely) ago it's possible.

This was not the question.
The question was: who made the query (select statement)?
flashback version query and undo_retention are irrelevant.

Regards
Michel

Re: Query Required [message #263428 is a reply to message #263402] Thu, 30 August 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju wrote on Thu, 30 August 2007 06:46
Quote:
Actuall the query was select not any of DML ststaements.


The whole query you can get but 1 week may or may not possible. May be you can get that you ran 1 hour ago. The full query.But it needs an effort to do it.

You first need to determine the value of the versions_xid pseudo column and then

query from flashback_transaction_query. Undo_sql will give you actual query that you ran.

Regards,
Mohammad Abdul Momin Arju,
Islamic University of Technology.(IUT).

See my previous answer.

Regards
Michel

Re: Query Required [message #263429 is a reply to message #263404] Thu, 30 August 2007 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju wrote on Thu, 30 August 2007 06:51
Quote:
One of the user has given the query in production which we want to identify which user has executed the query.


By the way you can also get back this information who ran the query.

I don't think you can get any information for a query (i.e. a select).

Regards
Michel

Re: Query Required [message #263432 is a reply to message #261749] Thu, 30 August 2007 01:12 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Yes Michel you are right. It can be found other DML statement rather than select like delete,insert,update and etc.
Example,


SQL> SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '0A002B003B040000';

UNDO_SQL
--------------------------------------------------------------------------------
insert into "STRMADMIN"."TEST"("A","TEST1") values ('7',NULL);



Thank you.
Re: Query Required [message #263456 is a reply to message #261749] Thu, 30 August 2007 02:18 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

It was my fault from the previous of this thread. Wrongly I interpret myself "DML" instead of "select".

Next time I should think five times before I should say sometime after your thread. Cool
Cool

Thank you Michel Again.
Re: Query Required [message #263701 is a reply to message #263456] Thu, 30 August 2007 16:11 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Assuming the statement is still cached, just look in V$sqlarea and look at the parsing_user_id and parsing_schema_id.
Previous Topic: Help with Grant statements...
Next Topic: Need syntax for indexes
Goto Forum:
  


Current Time: Sun Dec 04 08:56:02 CST 2016

Total time taken to generate the page: 0.23692 seconds