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

view plainprint?

1. SELECT object_name, object_type  
2.   FROM user_objects;  

SELECT object_name, object_type
  FROM user_objects;

The public synonym for this is accessed with:

view plainprint?

1. SELECT * FROM obj;  

SELECT * FROM obj;

List Own Tables

view plainprint?

1. SELECT table_name  
2.   FROM user_tables;  

SELECT table_name
  FROM user_tables;

view plainprint?

1. SELECT object_name  
2.   FROM user_objects  
3.  WHERE object_type = 'TABLE';  

SELECT object_name
  FROM user_objects
 WHERE object_type = 'TABLE';

Related public synonyms are:

view plainprint?

1. SELECT * FROM tab;  
2. SELECT * FROM tabs;  

SELECT * FROM tab;
SELECT * FROM tabs;

List Own Views

view plainprint?

1. SELECT view_name, text  
2.   FROM user_views;  

SELECT view_name, text
  FROM user_views;

view plainprint?

1. SELECT object_name  
2.   FROM user_objects  
3.  WHERE object_type = 'VIEW';  

SELECT object_name
  FROM user_objects
 WHERE object_type = 'VIEW';

List Own Synonyms

view plainprint?

1. SELECT synonym_name  
2.   FROM user_synonyms;  

SELECT synonym_name
  FROM user_synonyms;

view plainprint?

1. SELECT object_name  
2.   FROM user_objects  
3.  WHERE object_type = 'SYNONYM';  

SELECT object_name
  FROM user_objects
 WHERE object_type = 'SYNONYM';

A related public synonym is:

view plainprint?

1. SELECT * FROM syn;  

SELECT * FROM syn;

List Own Sequences

view plainprint?

1. SELECT sequence_name, last_number  
2.   FROM user_sequences;  

SELECT sequence_name, last_number
  FROM user_sequences;

view plainprint?

1. SELECT object_name  
2.   FROM user_objects  
3.  WHERE object_type = 'SEQUENCE';  

SELECT object_name
  FROM user_objects
 WHERE object_type = 'SEQUENCE';

A related public synonym is:

view plainprint?

1. SELECT * FROM seq;  

SELECT * FROM seq;

List Own Constraints

view plainprint?

1. SELECT constraint_name, constraint_type, r_constraint_name  
2.   FROM user_constraints;  

SELECT constraint_name, constraint_type, r_constraint_name
  FROM user_constraints;

List Own Table Comments

view plainprint?

1. SELECT table_name, comments  
2.   FROM user_tab_comments;  

SELECT table_name, comments
  FROM user_tab_comments;

List Own Column Comments

view plainprint?

1. SELECT table_name, column_name, comments  
2.   FROM user_col_comments;  

SELECT table_name, column_name, comments
  FROM user_col_comments;

List Own Indexes

view plainprint?

1. SELECT index_name, index_type  
2.   FROM user_indexes;  

SELECT index_name, index_type
  FROM user_indexes;

view plainprint?

1. SELECT object_name  
2.   FROM user_objects  
3.  WHERE object_type = 'INDEX';  

SELECT object_name
  FROM user_objects
 WHERE object_type = 'INDEX';

A related public synonym is:

view plainprint?

1. SELECT * FROM ind;  

SELECT * FROM ind;

List One's Entire Catalog

view plainprint?

1. SELECT *  
2.   FROM user_catalog;  

SELECT *
  FROM user_catalog;

A related public synonym is:

view plainprint?

1. SELECT * FROM cat;  

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.