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:
- Create a table with the following structure:
- Create a public synonym for this table.
- Grant insert privileges to all the users of this table.
- Create or replace trigger logon_trg after drop on database.
USER_NAME SESSION_ID MACHINE STATUS DATETIME CHANGED_OBJECT OBJECT_OWNER OBJECT_TYPE
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.
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