Finding objects in Oracle via user_objects

When working with Oracle databases in within a large company with multiple customers (departments or external customers) you will also be faced with the situation that not all databases are alike. Some databases will have a strict datamodel and a strict documentation policy. From those databases you will know exactly what is in the datamodel and you will be able to find all documentation of it. This is normally the case with all production databases and the associated D(evelopment), T(est) and A(cceptance) environments. However when it comes to the "play" environments and the environments used for research and development you are not always that lucky. Specially when you are looking into a database which is used by multiple developers to work on small coding projects and using it to learn new tricks of the trade.

In those cases it is not uncommon that you have to reverse engineer some parts of the code and from time to time find lost objects. Someone stating something like "yes I have stored that in a table a year ago and called in something like HELP" is not uncommon. In those cases you will have to start looking for the object and to do so your best friend is the USER_OBJECT table in the Oracle database.

The USER_OBJECT table holds information on all the objects available tot the users. This will help you finding the table you are looking for. Some people like to directly use CAT and do something like;

SELECT
      *
FROM
    CAT
WHERE
     TABLE_NAME LIKE 'HELP'

This however will only give you the table name (HELP) and the table_type (TABLE). Secondly you will have limited options to search. You can imagine that the person stating that the table name was HELP might have some mistaken as it is more than a year ago he created it. It might very well be that the table name is USERHELP and it might also very well be that a lot of objects have the "HELP" in their name. I do personally think that using USER_OBJECTS gives you just that extra power above CAT to find the correct object quickly.

Below you see a describe of the USER_OBJECTS table:

Name           Null Type          
-------------- ---- ------------- 
OBJECT_NAME         VARCHAR2(128) 
SUBOBJECT_NAME      VARCHAR2(30)  
OBJECT_ID           NUMBER        
DATA_OBJECT_ID      NUMBER        
OBJECT_TYPE         VARCHAR2(19)  
CREATED             DATE          
LAST_DDL_TIME       DATE          
TIMESTAMP           VARCHAR2(19)  
STATUS              VARCHAR2(7)   
TEMPORARY           VARCHAR2(1)   
GENERATED           VARCHAR2(1)   
SECONDARY           VARCHAR2(1)   
NAMESPACE           NUMBER        
EDITION_NAME        VARCHAR2(30)  

it might be wise to give the USER_OBJECTS table a good look and play arround with it some more to understand it correctly. For example it will be able to show you all the objects and it is not limited to tables only for example.
you can find out what kind of objects are used within the database by executing the below query. This will give you a list of used user_objects.

SELECT 
      DISTINCT(object_type)
FROM 
    user_objects 
ORDER BY 
        object_type

back to question of the "HELP" table. You know for example that you are looking for a table so you can filter on object_type to only show you the table objects. Secondly you know that it most likely has "HELP" in the name so you can filter for all objects having "HELP" as part of the object_name and secondly you know it is created 11 or more months ago so you can use this as a filer on the created date field. As you can see, it gives you just a little more options then using the CAT option in your Oracle database.