Keep track of dropped objects

This procedure gives information about the objects being dropped from an Oracle database.

This procedure gives information about the objects being dropped from a database, such as the name and type of

the object, the user who is dropping the object, terminal, etc. Here's what to do:

  1. Create a table with the following structure:
  2. USER_NAME 
    SESSION_ID     
    MACHINE        
    STATUS         
    DATETIME       
    CHANGED_OBJECT 
    OBJECT_OWNER   
    OBJECT_TYPE    
    
  3. Create a public synonym for this table.
  4. Grant insert privileges to all the users of this table.
  5. Create or replace trigger logon_trg after drop on database.
begin   
insert into dbobject_statistics values 
(lower(ltrim(rtrim(user))),userenv('sessionid'),
 lower(ltrim(rtrim(userenv('terminal')))),
'drop',sysdate,ora_dict_obj_name,ora_dict_obj_owner,ora_dict_obj_type);
end; 

By querying this table, we now know the users that are dropping objects in the database.

Reader Feedback

Alberto T. writes: I appreciate this tip but it works only in 8.1.7. In fact, database functions like ora_dict_obj_name, ora_dict_obj_owner, and ora_dict_obj_type are not available in versions like 8.0.6 or 7.3.4. So how do you solve the same matter in those versions? Any suggestions?

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, relational model, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in July 2002

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close