Favorite Oracle Data Dictionary Query Statements

I write a software development blog for a variety of reasons, not the least of which is as a "glorified bookmark" and easy reference to find things I need to look up quickly. This post is a good example of that because it provides me a convenient location to store some notes I have taken regarding my favorite queries on the Oracle Data Dictionary.

General Notes on Oracle Data Dictionary

The Oracle Data Dictionary contains metadata that is readily available via database views. These static data dictionary views are typically prefixed with USER_, DBA_, or ALL_ that respectively include metadata about that user's own schema, metadata for administration, and metadata available to the current user about his or her own schema and other authorized metadata.

Most of the syntax shown in the queries in this post is case insensitive. The exceptions are the strings contained within single quotes, which are case sensitive.

Many modern tools reduce the necessity of being familiar with these views, but I still find them to be a "quick and dirty" way to determine what is going on in my Oracle database. As I have blogged before, there are times when I can find out metadata about my database more quickly with SQL*Plus than with the fancier tools.


List All of One's Own Objects

SELECT object_name, object_type
  FROM user_objects;

The public synonym for this is accessed with:

SELECT * FROM obj;

List Own Tables

SELECT table_name
  FROM user_tables;

SELECT object_name
  FROM user_objects
 WHERE object_type = 'TABLE';

Related public synonyms are:

SELECT * FROM tab;
SELECT * FROM tabs;

List Own Views

SELECT view_name, text
  FROM user_views;

SELECT object_name
  FROM user_objects
 WHERE object_type = 'VIEW';

List Own Synonyms

SELECT synonym_name
  FROM user_synonyms;

SELECT object_name
  FROM user_objects
 WHERE object_type = 'SYNONYM';

A related public synonym is:

SELECT * FROM syn;

List Own Sequences

SELECT sequence_name, last_number
  FROM user_sequences;

SELECT object_name
  FROM user_objects
 WHERE object_type = 'SEQUENCE';

A related public synonym is:

SELECT * FROM seq;

List Own Constraints

SELECT constraint_name, constraint_type, r_constraint_name
  FROM user_constraints;

List Own Table Comments

SELECT table_name, comments
  FROM user_tab_comments;

List Own Column Comments

SELECT table_name, column_name, comments
  FROM user_col_comments;

List Own Indexes

SELECT index_name, index_type
  FROM user_indexes;

SELECT object_name
  FROM user_objects
 WHERE object_type = 'INDEX';

A related public synonym is:

SELECT * FROM ind;

List One's Entire Catalog

SELECT *
  FROM user_catalog;

A related public synonym is:

SELECT * FROM cat;

Listing Database Privileges

Useful data dictionary views for viewing database privileges include ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, USER_ROLE_PRIVS, USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD, USER_COL_PRIVS_MADE, and USER_COL_PRIVS_RECD.


Conclusion

Each of the above queries of Oracle Data Dictionary metadata views can be easily executed as shown. However, they can often be joined together for even more useful details. There are many, many more views available in the Oracle database, but these views provide perspective on some of the most commonly used database objects.