Home » SQL & PL/SQL » SQL & PL/SQL » which OS user has dropped the objects (Oracle 9i)
icon10.gif  which OS user has dropped the objects [message #318264] Tue, 06 May 2008 05:12 Go to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Dear Sir

could you please tell me the script which will give me the OSUSER and the action performed by him on a particular day.I know v$session is the view which will give us the information about the user and the action performed by him.
Suppose I have dropped 10 tables(i.e objects) on a particular day.Can I find which user has dropped 10 tables on that particular day,.

Any query will help me to solve my problem.?
Thanks..


Re: which OS user has dropped the objects [message #318269 is a reply to message #318264] Tue, 06 May 2008 05:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you've got Auditing enabled, then you can get this information.
If you've written some triggers that fire on the appropriate DROP event, then you can get this information.

Otherwise, I think you're out of luck.
Re: which OS user has dropped the objects [message #318271 is a reply to message #318264] Tue, 06 May 2008 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can use Log Miner... if you are in archivelog mode and kept the archived logs since this date.
You will have Oracle user but not OS user.

Regards
Michel
Re: which OS user has dropped the objects [message #318272 is a reply to message #318269] Tue, 06 May 2008 05:39 Go to previous messageGo to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Dear Sir

Could you please tell how to get AUDITING enabled.or
If i should write triggers on the appropriate drop event...Could you please elaborate more in detail?
Thanks
Regards
Re: which OS user has dropped the objects [message #318275 is a reply to message #318272] Tue, 06 May 2008 05:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Read all about Auditing.

Te standard Documentation on triggers tells you how to create triggers on DDL events.

Neither of these is any use after the fact - if the objects are already dropped, and you didn't have one of these solutions in place, then Logminer is your only hope.
Re: which OS user has dropped the objects [message #318276 is a reply to message #318271] Tue, 06 May 2008 05:46 Go to previous messageGo to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Dear sir

Thank you....

As you said that you can use log miner which will give me the information as to when did i dropped the objects.
Suppose I as a user have dropped 10 objects on 01-May-08 time and today is 06-May-08.Will log miner tool be helpful for showing the audit of 01-may-08?Will it be showing the time also as to what is the exact time I have dropped the particular objects on 01-May-08?

Regards
Re: which OS user has dropped the objects [message #318277 is a reply to message #318276] Tue, 06 May 2008 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It gives you the exact date and time (to the second), the SCN and so on.

Regards
Michel
Re: which OS user has dropped the objects [message #318279 is a reply to message #318277] Tue, 06 May 2008 06:04 Go to previous messageGo to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Dear Sir

One last question I would like to ask in order to start logminer I need to create a dictionary file .After creating the dictionary file using the build procedure I need to add logs using the DBMS_LOGMNR package. Then I need to start the logminer which will give me the logs using the DBMS_LOGMNR.start_logmnr but what should be the value of startscn and the stopscn should be it should be 100 and 150 or any default value?..

Regards

[Updated on: Tue, 06 May 2008 06:05]

Report message to a moderator

Re: which OS user has dropped the objects [message #318280 is a reply to message #318279] Tue, 06 May 2008 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't give value for these parameters, it will scan all the logs you give it.

Regards
Michel
Re: which OS user has dropped the objects [message #318287 is a reply to message #318280] Tue, 06 May 2008 06:34 Go to previous message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Thank you very much sir

regards
Previous Topic: Guidelines
Next Topic: Encryption of data in a column
Goto Forum:
  


Current Time: Mon Feb 10 00:51:02 CST 2025