Wednesday, July 8, 2009

Oracle Concepts: The Data Dictionary

I have come to rely pretty heavily on the Data Dictionary as I don't typically use a GUI. This stems from the fact that in my first professional IT job (reporting off of Oracle), I was given a tnsnames.ora file and something called SQL*Plus. I came from Microsoft Access...I wanted pretty pictures of my database objects. My first year was spent asking how to find the tables in a schema (schema? what's that?), how to view the SQL that made up a view, and how to view the source code.

Find Tables
SELECT * FROM user_tables;
View Text
SET LONG 100000
SELECT text FROM user_views WHERE view_name = :your_view;
View Source
SELECT line, text 
FROM user_source
WHERE name = :package_name
AND type = :package_spec_or_package_body;
Needless to say it wasn't fun.
Without pain there is no progress.
What is the Data Dictionary? dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains:

* The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
* How much space has been allocated for, and is currently used by, the schema objects
* Default values for columns
* Integrity constraint information
* The names of Oracle Database users
* Privileges and roles each user has been granted
* Auditing information, such as who has accessed or updated various schema objects
* Other general database information
Not completely read-only as I found out once. What's the point? None really. I just like being able to copy and paste the documentation. OK, that's only partially true.

All the information that you retrieve via your nice GUI interface comes from these tables. SQL Developer, JDeveloper, Toad, etc. I believe some even have an option to view the SQL being submitted to the database. If you haven't used (knowingly) the Data Dictionary before, check it out. You can easily view all 651 views (10gR2) by issuing:
SELECT * FROM dictionary;
Happy hunting.


Coskan Gundogar said...


Dictionary was my primary lookup location too till I hit this :)

oraclenerd said...

That's kind of funny. How cool is it though that you found a bug? Kind of like me trying (and succeeding) to delete records from DBA_JOBS. That's definitely a WIN for you.