Oracle Database Object List

{ Abhilash Kumar Bhattaram : Follow on LinkedIn }

One of the traditional problems of DBA’s are to keep a track of the Oracle Database objects before and after any major application deployment. Most Database deployments would have a change on all kinds of objects which a development team may not be able to clarify in numbers. In addition to the tables and indexes which are seen as probably the most visible changes many DBA’s lose track of the status of objects during deployments. The different kind of objects are table , index , synonym , package , procedure , function , etc.

The below script would show you the distinct objects in your database in case you are looking at this the first time.

SQL> select distinct(object_type) from all_objects;

It’s very difficult to get a snapshot of the current objects , so I came up with my utility objlst.sql . This will quickly give me a snapshot of all database objects accounts segregated per database user and their sum.

Below is the my objlst utility
https://github.com/abhilash-8/ora-tools/blob/master/objlst.sql

Below is a pictorial representation of what the SQL would do

Summary of Objects per user

Summary of Invalid Objects per user

Now as you can see having such a script would not only help me get a snapshot of my objects but also helps me compare the objects after a database deployment.

Many applications are sensitive to invalid objects , this scripts clearly points out which objetcs are invalid without much of an effort.