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

Report message to a moderator

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: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
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

Report message to a moderator

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

Report message to a moderator

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: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No, no one I am aware.

Regards
Michel

Report message to a moderator

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

Report message to a moderator

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.

Report message to a moderator

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

Report message to a moderator

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.

Report message to a moderator

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: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
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

Report message to a moderator

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: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
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

Report message to a moderator

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: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
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

Report message to a moderator

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.

Report message to a moderator

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.

Report message to a moderator

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.

Report message to a moderator

Previous Topic: Help with Grant statements...
Next Topic: Need syntax for indexes
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Dec 13 06:00:26 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.