The Oracle Data Dictionary – Keeping an eye on your application in uncertain times

I’ve got to say that it’s no surprise that were leaving Europe. It’s just that we expected it to be on penalties, probably to Germany.
Obviously, that “we” in the last gag is England. Wales and Northern Ireland have shown no sense of decorum and continued to antagonise our European Partners by beating them at football.
Currently, the national mood seems to be that of a naughty child who stuck their fingers in the light socket to see what would happen, and were awfully surprised when it did.

In the midst of all this uncertainty, I’ve decided to seek comfort in the reassuringly familiar.
Step forward the Oracle Data Dictionary – Oracle’s implementation of the Database Catalog.

However closely you follow the Thick Database Paradigm, the Data Dictionary will serve as the Swiss Army Knife in your toolkit for ensuring Maintainability.
I’ll start of with a quick (re)introduction of the Data Dictionary and how to search it using the DICTIONARY view.
Then I’ll cover just some of the ways in which the Data Dictionary can help you to get stones out of horses hooves keep your application healthy.

Right then….

What’s in the Data Dictionary ?

The answer is, essentially, metadata about any objects you have in your database down to and including source code for any stored program units.
Data Dictionary views tend to come in three flavours :

  • USER_ – anything owned by the currently connected user
  • ALL_ – anything in USER_ plus anything the current user has access to
  • DBA_ – anything in the current database

The Data Dictionary has quite a lot of stuff in it, as you can tell by running this query :

select count(*)
from dictionary

You can sift through this mountain of information by having a look at the comments available in DICTIONARY (DICT to it’s friends) for each of the Views listed.
For example…

select comments
from dict
where table_name = 'USER_TABLES'

Description of the user's own relational tables

You can see a graphical representation of these USER_ views in whatever Oracle IDE you happen to be using.
For example, in SQLDeveloper…


This graphical tree view corresponds roughly to the following Data Dictionary views :

View Name DICT Comments Additional Comments
USER_TABLES Description of the user’s own relational tables
USER_VIEWS Description of the user’s own views
USER_EDITIONING_VIEWS Descriptions of the user’s own Editioning Views
USER_INDEXES Description of the user’s own indexes
USER_OBJECTS Objects owned by the user This includes functions, packages, procedures etc
USER_QUEUES All queues owned by the user
ALL_QUEUE_TABLES All queue tables accessible to the user
USER_TRIGGERS Triggers having FOLLOWS or PRECEDES ordering owned by the user Includes Cross Edition Triggers
USER_TYPES Description of the user’s own types
USER_MVIEW_LOGS All materialized view logs owned by the user
USER_SEQUENCES Description of the user’s own SEQUENCEs
USER_SYNONYMS The user’s private synonyms
ALL_SYNONYMS All synonyms for base objects accessible to the user and session Includes PUBLIC synonyms
USER_DB_LINKS Database links owned by the user
ALL_DB_LINKS Database links accessible to the user
ALL_DIRECTORIES Description of all directories accessible to the user
ALL_EDITIONS Describes all editions in the database
USER_XML_SCHEMAS Description of XML Schemas registered by the user
USER_SCHEDULER_JOBS All scheduler jobs in the database
RESOURCE_VIEW Whilst not part of the DICTIONARY per se, you can see details of XML DB Schema in this view
USER_RECYCLEBIN User view of his recyclebin
ALL_USERS Information about all users of the database

As all of this metadata is available in views, it can be interrogated programatically via SQL, as we’ll discover shortly. Before that though, let’s introduce…

The Brexit Schema

To add an element of topicality, the following examples will be based on this schema.

The user creation script looks like this :

grant connect, create table, create procedure, create sequence
    to brexit identified by ceul8r

alter user brexit default tablespace users
alter user brexit quota unlimited on users

You’ll probably want to choose your own (weak) pun-based password.

The tables in this schema are ( initially at least)…

create table countries
    iso_code varchar2(3),
    coun_name varchar2(100) not null,
    curr_code varchar2(3) not null,
    is_eu_flag varchar2(1)

create table currencies
    iso_code varchar2(3) constraint curr_pk primary key,
    curr_name varchar2(100)

For reasons which will become apparent, we’ll also include this procedure, complete with “typo” to ensure it doesn’t compile…

create or replace procedure add_currency
	i_iso_code currencies.iso_code%type,
	i_curr_name currencies.curr_name%type

	-- Deliberate Mistake...
	brick it for brexit !
	insert into currencies( iso_code, curr_name)
	values( i_iso_code, i_curr_name);
end add_currency;

The examples that follow are based on the assumption that you are connected as the BREXIT user.

First up….

Spotting tables with No Primary Keys

Say that we want to establish whether a Primary Key has been defined for each table in the schema.
Specifically, we want to check permanent tables which comprise the core application tables. We’re less interested in checking on Global Temporary Tables or External Tables.
Rather than wading through the relevant DDL scripts, we can get the Data Dictionary to do the work for us :

select table_name
from user_tables
where temporary = 'N' -- exclude GTTs
and table_name not in
    -- exclude External Tables ...
    select table_name
    from user_external_tables
and table_name not in
    -- see if table has a Primary Key
    select table_name
    from user_constraints
    where constraint_type = 'P'


It looks like someone forgot to add constraints to the countries table. I blame the shock of Brexit. Anyway, we’d better fix that…

alter table countries add constraint
	coun_pk primary key (iso_code)

…and add an RI constraint whilst we’re at it…

alter table countries add constraint
	coun_curr_fk foreign key (curr_code) references currencies( iso_code)

…so that I’ve got some data with which to test…

Foreign Keys with No Indexes

In OLTP applications especially, it’s often a good idea to index any columns that are subject to a Foreign Key constraint in order to improve performance.
To see if there are any FK columns in our application that may benefit from an index…

with cons_cols as
    select cons.table_name,  cons.constraint_name,
        listagg(cols.column_name, ',') within group (order by cols.position) as columns
    from user_cons_columns cols
    inner join user_constraints cons
		on cols.constraint_name = cons.constraint_name
	where cons.constraint_type = 'R'
    group by cons.table_name, cons.constraint_name
ind_cols as
select ind.table_name, ind.index_name,
    listagg(ind.column_name, ',') within group( order by ind.column_position) as columns
from user_ind_columns  ind
group by ind.table_name, ind.index_name
select cons_cols.table_name, cons_cols.constraint_name, cons_cols.columns
from cons_cols
where cons_cols.table_name not in
    select ind_cols.table_name
    from ind_cols
    where ind_cols.table_name = cons_cols.table_name
    and ind_cols.columns like cons_cols.columns||'%'

Sure enough, when we run this as BREXIT we get…

------------------------------ -------------------- ------------------------------

Post Deployment Checks

It’s not just the Data Model that you can keep track of.
If you imagine a situation where we’ve just released the BREXIT code to an environment, we’ll want to check that everything has worked as expected. To do this, we may well recompile any PL/SQL objects in the schema to ensure that everything is valid….

exec dbms_utility.compile_schema(user)

…but once we’ve done this we want to make sure. So…

select object_name, object_type
from user_objects
where status = 'INVALID'
select constraint_name, 'CONSTRAINT'
from user_constraints
where status = 'DISABLED'

------------------------------ -------------------

Hmmm, I think we’d better fix that, but how do we find out what the error is without recompiling ? hmmm…

select line, position, text
from user_errors
where name = 'ADD_CURRENCY'
and type = 'PROCEDURE'
order by sequence

---- -------- --------------------------------------------------------------------------------
  10        8 PLS-00103: Encountered the symbol "IT" when expecting one of the following:     

                 := . ( @ % ;

Impact Analysis

Inevitably, at some point during the life of your application, you will need to make a change to it. This may well be a change to a table structure, or even to some reference data you previously thought was immutable.
In such circumstances, you really want to get a reasonable idea of what impact the change is going to have in terms of changes to your application code.
For example, if we need to make a change to the CURRENCIES table…

select name, type
from user_dependencies
where referenced_owner = user
and referenced_name = 'CURRENCIES'
and referenced_type = 'TABLE'
union all
select child.table_name, 'TABLE'
from user_constraints child
inner join user_constraints parent
	on child.r_constraint_name = parent.constraint_name
where child.constraint_type = 'R'
and parent.table_name = 'CURRENCIES'

NAME                           TYPE
------------------------------ ------------------
ADD_CURRENCY                   PROCEDURE
COUNTRIES                      TABLE             

Now we know the objects that are potentially affected by this proposed change, we have the scope of our Impact Analysis, at least in terms of objects in the database.


As always, there’s far more to the Data Dictionary than what we’ve covered here.
Steven Feuerstein has written a more PL/SQL focused article on this topic.
That about wraps it up for now, so time for Mexit.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.