Searching The Oracle Data Dictionary

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, all_tab_cols (or user_tab_cols or dba_tab_cols) is often your go-to object in Oracle databases. For instance, you want to list all tables that have the column AMOUNT_ORDERED:

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. The 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.

Looking for Tables with Certain Columns

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. Fine. 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 AMOUNT_DELIVERED, the SUPPLIER_NAME, and the CUSTOMER_LOCATION. 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 &) and correctly takes care of double quotes.

You can check out the procedure find_tab and the utils package on GitHub. 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). The types string_t and identifier_t are subtypes of VARCHAR2 and they are defined in the package specification.

Please note that the package queries dba_tab_comments and 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 INDEX_NAME, TABLE_NAME, LAST_ANALYZED, and DEGREE. Furthermore, you know they belong to SYS. 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

Great! 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 'V' or '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:

EXEC utils.find_tab('index_name,table_name,last_analyzed,degree');

It does not get much easier than that!

Notes

1. The output from utils is 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.

2. Because we do not know the number of columns provided by the user in advance, DBMS_SQL is used. The following table is adapted from Oracle PL/SQL Programming, p. 549 (Sixth Edition) by Steven Feuerstein and Bill Pribyl; IUD stands for INSERT, UPDATE, and DELETE. Since we are clearly dealing with the fourth situation, DBMS_SQL is 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_SQL instead of native dynamic SQL, which is easier to write and typically faster in the execution, is negligible because find_tab is 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_SQL rather 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.

3. The package utils depends 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_env that can be set to 'PROD' or '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.