Script To Quickly Find Object Info


Here is a script I’ve used to quickly hunt down objects and information about them.

Very useful to find things like: exact spellings, other objects with a similar spelling, or owned by a different user, synonyms, etc.

 

/*

foinfo.sql:  find object info

Script to find objects and information about them.
By Rodger Lepinsky

*/

accept ls_object_name prompt "Enter the object name to find info about:  " ;

select     object_type  ||  '   '  ||  owner || '.' || object_name   ||
               '    ' || object_id 
          as object_and_object_id
/*  ,  CREATED
  ,   TIMESTAMP
  ,   LAST_DDL_TIME    */
from all_objects    /* By Rodger Lepinsky */
/* where upper(object_name) = trim(upper('&ls_object_name'))  */
where upper(object_name) like trim(upper('%&ls_object_name%'))      
/* and object_type IN ('TABLE', 'VIEW')  */ 
order by object_type, owner, object_name
/

In some environments, the different timestamps have been useful. Ie. Has the object just been recompiled?

You can vary the script as you see fit. Change it to find only with an exact spelling. Restrict it to objects types such as a TABLE or VIEW. Check the timestamps. Etc.

Enjoy!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: