StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle 11g Catalog / System Tables DBA_COL_COMMENTS and DBA_OBJECTS Entries
text
Body
copied!<p>I am working on a project where the objective is simple. We have a surrogate key whose data type needs to change from integer to decimal(18,0) to accomodate more transactions due to business growth. Apparently, this field is very important and is referenced in thousands of objects.</p> <p>I want figure out an automated way to identify all the objects where this field is present or referenced.Field name is say SRV_ACCS_XXX. That is tables/views/macros so on and so forth which has reference to this field.</p> <p>Thought will use the catalog/system system tables :) .This is the problem I am facing. There are objects in dba_col_comments which has reference to field SRV_ACCS however these objects do not have entry into dba_objects - so I am ending up with object type as null. The object names are like <code>"BIN$01gtIjJPB0vgRAALXeCowg==$0"</code> however they have a valid database schema name. </p> <p>Is my approach right? If so what are these types of objects - with names "BIN$01gtIjJPB0vgRAALXeCowg==$0"? How can I eliminate temporary objects/volatile objects from result to get only permanently created objects?. Because everytime I run the query with out any modifications its producing different results.</p> <p>Pls read the following query.</p> <pre><code>select current_date "Load_date", NULL "Tower_owner", 'Oracle Production' as "Environment_Name", Object_Details_Tbl.TABLE_NAME "Object_Name", object_typ_tbl.object_type "Object Type", 1 "Num Occurances", Object_Details_Tbl.Owner "Database Schema", 'p1etl1d8.edc.cingular.net' "Server", 'N/A' "Object_Repository", 'Oracle Table' "Object_Description", 'N/A' "Object_Folder_Name", 'N/A' "Object_Directory_Name", 'A' AS "Object_Status" from dba_col_comments Object_Details_Tbl left outer join dba_objects Object_Typ_Tbl on object_details_tbl.table_name = object_typ_tbl.object_name and object_details_tbl.owner = object_typ_tbl.owner where Object_Details_Tbl.column_name like('%SRV_ACCS_%') </code></pre> <p>Thanks Markiv.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload