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?

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