Searching The Oracle Data Dictionary
Ian Hellström | 9 November 2014 | 5 min read
Databases and especially data warehouses typically consist of many dozens of tables and views. Good documentation is essential but even the best documentation cannot answer your questions as quickly as you want the information.
Suppose you need a table that has three particular columns. Going through a PDF with Ctrl+F is possible but not the most efficient solution. Oracle’s built-in data dictionary is ideal for such queries: you don’t have to leave the database and the information is always up to date. I’ll show you a package that allows you to search for tables (and views) armed only with a particular combination of columns.
When you’re looking for a table with a particular column,
dba_tab_cols) is often your go-to object in Oracle databases.
For instance, you want to list all tables that have the column
SELECT * FROM all_tab_cols WHERE column_name = 'AMOUNT_ORDERED';
This query is a bit, shall we say, simplistic. It does return what was requested but it’s not exactly a complex query that deserves a post of its own.
You may be interested to see the table and column comments too:
SELECT col.owner , col.table_name , col.column_name , col.data_type , col.column_id , col.last_analyzed , ccom.comments AS column_comment , tcom.comments AS table_comment FROM all_tab_cols col INNER JOIN all_col_comments ccom ON col.owner = ccom.owner AND col.table_name = ccom.table_name AND col.column_name = ccom.column_name INNER JOIN all_tab_comments tcom ON col.owner = tcom.owner AND col.table_name = tcom.table_name WHERE col.owner <> 'SYS' AND LOWER(col.column_name) LIKE '%...%' ORDER BY col.owner , col.table_name , col.column_name;
In for example SQL Developer you can create your own code template (Tools > Preferences > Database > SQL Editor Code Templates) and insert the snippet whenever you press a certain combination of keys.
Alternatively, you can use an external application like AutoKey on Linux or PhraseExpress on Windows, so you can control where the cursor lands when the snippet is expanded.
I have mine set to place the cursor at the
... in the
WHERE clause whenever I type
scol: search column.
LOWER() function is included so I don’t have to hold down the SHIFT key or press CAPS LOCK twice; I detest work that I can avoid.
Again, the SQL statement is useful but not exactly worthy of mention on Databaseline.
Whenever you are lost in a database the data dictionary is your best friend.
In case you are not familiar with Oracle’s data dictionary, please execute
SELECT * FROM dict and you’ll see the entire list of dictionary tables.
Sometimes you only need to locate a table with a particular column.
There’s a query for that.
There are, however, instances when you need a table with a particular set of columns.
Suppose you’re interested in a table that contains not only the
AMOUNT_ORDERED but also the
SUPPLIER_NAME, and the
Giving insightful, standardized names to your database objects is always a good idea, but that may not be enough.
You may have lots of tables with amounts and supplier information but only very few that also have the customer location.
Sure, you can create the join yourself but who wants to write a query when there is already an object available that has everything you need?
I have created a procedure that does exactly what is needed: supply a set of columns and receive a list of tables and views with that particular combination of columns.
Because column names can be both double-quoted and simple strings, there is an auxiliary function
split_objects in the
utils package that properly splits a string into its individual components based on delimiters (all punctuation marks except valid characters for object names, i.e.
&) and correctly takes care of double quotes.
You can check out the procedure
find_tab and the
utils package on the Databaseline code repository.
I shall add more code to the
utils package as I write more posts about Oracle-specific stuff.
For the time being, it’s the only procedure accessible from outside the package.
The procedure is defined as follows:
find_tab ( cols_in string_t, owner_in identifier_t DEFAULT NULL, prefix_in identifier_t DEFAULT NULL, tab_or_vw_in all_tab_comments.table_type%TYPE DEFAULT NULL );
All you have to do is supply a list of columns.
If you want to be more specific, it’s up to you to give the owner, table/view prefix, and whether you want to restrict the list to table, views, or both (default).
identifier_t are subtypes of
VARCHAR2 and they are defined in the package specification.
Please note that the package queries
dba_tab_cols even though
all_tab_comments is used in the definition of the parameters.
The reason is that the PL/SQL compiler does not like it when you attach the
%TYPE attribute to the
dba_* tables in the package specification.
Thankfully, the columns in the
all_* tables are the same, so we can easily circumvent the issue.
An example is probably in order at this point.
Let’s assume you know there are a bunch of tables or views that include the columns
Furthermore, you know they belong to
This is what you’d type in:
BEGIN utils.find_tab ( cols_in => 'index_name/table_name:LAST_ANALYZED dEGreE', owner_in => 'sys' ); END;
Typically you won’t fool around with cAPitaliZaTioN like that but it goes to show that the package does not care: the
split_objects function takes care of that for you.
The same function also does not care about the separators, as you can see in the snippet above.
Anyway, what Oracle spits out after a few moments is
Matches found: 4 1. SYS.ALL_INDEXES (VIEW): Descriptions of indexes on tables accessible to the user 2. SYS.CDB_INDEXES (VIEW): Description for all indexes in the database in all containers 3. SYS.DBA_INDEXES (VIEW): Description for all indexes in the database 4. SYS.USER_INDEXES (VIEW): Description of the user's own indexes
Now suppose you did not know that these tables/views belonged to
SYS but you did know that it started with
CD or something.
Stated differently, you want the second match on the list.
Well, you can do that too:
BEGIN utils.find_tab ( cols_in => 'index_name,table_name,last_analyzed,degree', prefix_in => 'cd' ); END;
Consequently, Oracle tells you that
Matches found: 1 1. SYS.CDB_INDEXES (VIEW): Description for all indexes in the database in all containers
The optional parameter
tab_or_vw_in only looks at the first letter to make it easier for you.
Whether you write
'view' is irrelevant.
If it starts with a ‘V’, then the procedure assumes you want a view.
If you write gibberish that does not start with a ‘V’ it defaults to tables only.
Of course, Oracle also caters to lazy developers:
It does not get much easier than that!
The output from
utilsis generated with
DBMS_OUTPUT, which is admittedly not as fancy as a pipelined table or
DBMS_SQL.RETURN_RESULT(as of 12c). Then again, I doubt you need the results as a table for further processing. The utility is intended to locate objects with certain columns, and that’s what it does. Nothing more, nothing less.
Because of a bug in SQL Developer 4 that causes it to complain about the Oracle Database client I refrained from using
DBMS_SQL.RETURN_RESULT. For more information I refer to the comments in the code.
Because we do not know the number of columns provided by the user in advance,
DBMS_SQLis used. The following table is adapted from Oracle PL/SQL Programming, p. 549 (Sixth Edition) by Steven Feuerstein and Bill Pribyl; IUD stands for
DELETE. Since we are clearly dealing with the fourth situation,
DBMS_SQLis the way to go. Yes, I could have gone with a concatenation of column values into the query itself but that is not recommended.
The performance hit by using
DBMS_SQLinstead of native dynamic SQL, which is easier to write and typically faster in the execution, is negligible because
find_tabis not a procedure that you are expected to run that often. The few extra (milli)seconds should not mar your experience.
Moreover, I wrote the package with
DBMS_SQLrather than native dynamic SQL to provide a non-trivial example of its use too. The documentation provided by Oracle is detailed but for many beginners quite dense. I hope I could show the built-in package’s use without resorting to a bare-bones example that leaves you wanting more.
utilsdepends on another package, called
errors, which is also on the code repository. It defines errors numbers and names in a central location and it has very basic error logging features. You are of course free to extend it as you see fit but it does provide a fairly clean interface to exception handling and it automatically includes context information to supplement the logged exception.
It also has a global parameter
g_envthat can be set to
'DEV'. The difference is that for
'PROD', exception details are stored in the table
error_log, whereas for
'DEV'the information is displayed on screen.
|1||DDL and IUD||
|2||IUD with fixed number of bind variables||
|3a||One-row queries with fixed number of columns and bind variables||
|3b||Multi-row queries with a fixed number of columns and bind variables||
|4||SQL statement number of columns selected or number of bind variables unknown until runtime||