Poking around inside Oracle

What separates SAs from everyone else? We want to understand everything. Being highly analytical people by nature we need to understand not only how to do something but the environment in which we operate. But, sadly, when it comes to Oracle thats a tall order especially without the assistance of the Enterprise Manager or tools like Tora.

I find "smaller" databases like PostGreSQL or MySQL very refreshing because they are so easy to dig around in. In PostGreSQL if you want to see all the tables just issue a $\backslash$dt and look at the list. Want to see the indexes? $\backslash$di and there they are. This makes it easy to poke around when you are either new or lost in your environment. With Oracle, it's a little harder. Want to see all the systems tables in PostGreSQL? $\backslash$dS and you see all the systems tables where the configuration info is. When I really started playing with Oracle I was frustrated by the seemingly blindness of the whole experience. I could only see the tip of my nose, no further. I soon realized just why you can't see everything with such clarity in Oracle... there is just too much!

Oracle keeps so many internal tables that it's hard to be able to just browse along them. If you pull up Enterprise Manager and have a look at the list of tables you'll get a total somewhere near (sit down for this): 1468 tables! I think thats worthy of a big "holy $#!". If your thinking "no problem, there are probably a few views that make all the info more usable", you'd be right... except when you list the views you find 3470 of 'em! This immediate answers the "why doesn't SQL*Plus have a $\backslash$dt feature like PostGreSQL?" question. Thankfully, you can find lists around that have lists of the commonly used tables, such as:

http://www.techonthenet.com/oracle/sys_tables/

Some interesting tables include the ALL_* tables. For instance, using the SQL COUNT() function we can count the number of tables listed in the ALL_TABLES system table:

SQL> select COUNT(*) from all_tables;

  COUNT(*)
----------
      1488

SQL>

Sadly, when managing Oracle your going to need to query the system tables a lot, so keeping your SQL skills sharp and a reference or cheat sheet nearby is a good idea. Here's a common example:

SQL> select * from dba_users    
  2  where username = upper('benr');

USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA
-------------------------------- --------- ---------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ ---------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
BENR                                   58 BEFEAB8A2CDD5A85
OPEN
USERS                          TEMP                           08-OCT-04

USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA
-------------------------------- --------- ---------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ ---------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
DEFAULT                        DEFAULT_CONSUMER_GROUP



SQL>

Something you'll immediately find, if you haven't already, is that SQL*Plus table output sucks! Look at all that output, it's hard to see whats happening. For this reason, whenever possible limit your queries down to just what you really need. You'll also notice that when you are searching (the where clause) the search parameter is case sensitive, which can be confusing because so much of SQL is very case insensitive. Most everything is stored in uppercase, so you can either search for 'BENR' or you can use the SQL function upper() to convert the casing to uppercase.

Lets try outputting that table again with a little less cruft this time:

SQL> select username, user_id, profile from dba_users
  2  where username = 'BENR';

USERNAME                          USER_ID PROFILE
------------------------------ ---------- ------------------------------
BENR                                   58 DEFAULT

SQL>

Must better that time. Whenever possible, just trim down queries as a best practice.