Oracle SQL - Use data dictionary

Introduction

The CAT view lists the objects in the current schema.

The following code shows an example of using the CAT view.

select * from cat; 

To know more about a specific data dictionary view, use command DESCRIBE or query the data dictionary view DICT_COLUMNS.


describe ALL_USERS 
 Name                    Null?    Type 
 USERNAME       NOT NULL VARCHAR2(30) 
 USER_ID        NOT NULL NUMBER 
 CREATED        NOT NULL DATE 
  
select column_name, comments 
from   dict_columns 
where  table_name = 'ALL_USERS'; 
  

The following code shows a query against the NLS_SESSION_PARAMETERS view (NLS stands for National Language Support).

The result shows, for example, the NLS_DATE_FORMAT value used to display dates.

Demo

SQL>
SQL> select * from nls_session_parameters;

PARAMETER--  w  w w . j a  v  a2 s . com
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

The following table lists a selection of useful Oracle data dictionary tables.

View Description
DICTIONARYDescription of the data dictionary itself
DICT_COLUMNS Data dictionary column descriptions
ALL_USERS Information about all database users
ALL_INDEXES1 All indexes
ALL_SEQUENCES1All sequences
ALL_OBJECTS1 All objects
ALL_SYNONYMS1 All synonyms
ALL_TABLES1 All tables
ALL_VIEWS1All views
USER_INDEXES2 Indexes
USER_SEQUENCES2 Sequences
USER_OBJECTS2 Objects
USER_SYNONYMS2Synonyms
USER_TABLES2 Tables
USER_TAB_COLUMNS2 Columns
USER_VIEWS2 Views
USER_RECYCLEBIN Dropped objects
CAT Synonym for USER_CATALOG
COLS Synonym for USER_TAB_COLUMNS
DICT Synonym for DICTIONARY
DUAL Dummy table, with one row and one column
IND Synonym for USER_INDEXES
OBJ Synonym for USER_OBJECTS
SYN Synonym for USER_SYNONYMS
TABS Synonym for USER_TABLES