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.
