Introspection: making the best of broken database operations

In the middle of a recent nice tutorial on use of large objects (LOBs) in Oracle databases, Senior Oracle ADF Developer Mahmoud ElSayed makes a remark that some readers will overlook: “… I come back after long period of time, I don’t remember the locations of the OS files …” What is that about?

In a textbook, data architects design databases, administrators implement and configure them, Operations loads them up with the applications provided by Development, and so on. When a question arises about interpretation of a value or dependences between different tables, one simply refers to the project documentation shelved in the datacenter library.

Anyone who has worked at all in commercial practice knows how far it deviates from this idealization. It can easily happen that DevOps needs to patch together a little experiment to see what kind of performance it achieves, then … well, months later, someone notices that customers have come to depend on what was originally only a trial. The implementation wasn’t documented at all, let alone to the level of detail that makes for healthy maintenance. How do we deal with situations like this?

Power of introspection

Use the computer. Get the database to tell you what it knows. Some call this “debugging”; for me, the point is not that there’s not so much a “bug” to remove, as knowledge to obtain. My label for this is “introspection“.

The canonical example of database introspection is someone who sits down with sqlplus or SQL Developer and enters “s elect * from $MY_TABLE” to gain a little insight into what columns are kept in $MY_TABLE, if the usernames there are maintained as all-uppercase, whether PARTNO is stored as a numeric integer or a string, and so on. There’s much more that introspection can do for us, though.

ElSayed shows that with LOBs (among other sorts of data), s‌elect * ... only leads to “... Column or attribute type can not be displayed ...” Don’t give up, though! Use the database itself to help. ElSayed himself takes advantage of the built-in DBMS_LOB.FILEGETNAME to write a custom function which renders the LOB table in a human-readable form.

Still more is possible. What if it’s been a few months, and you don’t even remember the name of the table you hope to search, or you take responsibility for an old application, and you’re unsure what database instance backs it? You’ll want to know about such possibilities as

s elect * from cat; Show all tables
desc $TABLE_NAME Report on the design of $TABLE_NAME
s elect table_name, column_name from all_tab_columns where column_name = 'ORDER_ID' and table_name like 'WIP%'; Which tables prefixed with the work-in-progress tag have an ORDER_ID field?
s elect * from v$version; Show the version numbers of core library components
s elect * from all_views; Show all views

Does someone on your team think a particular code is a primary key in only three tables? Do you suspect that physical-file assignments have been made so inefficiently that read-write access is slower than it should be? Don’t wonder about such things–have the database itself provide an authoritative answer.

Remember security

A final tip for making the best use of introspection: you might need to elevate your privileges. Good DBAs (database administrators) often restrict access to schema information. A particular log-in could have full privileges to view and even update certain application tables–but not the schema which defines those tables. If queries like the ones in the table above return fewer rows than you expect, take this as a sign that the account with which you’re connecting has been blocked from at least some kinds of introspection.

Leave a Reply

Your email address will not be published. Required fields are marked *