Tables Data Dictionary Views

The following views allow you to access information about tables.

View	Description
`DBA_TABLES` `ALL_TABLES` `USER_TABLES`	`DBA` view describes all relational tables in the database. `ALL` view describes all tables accessible to the user. `USER` view is restricted to tables owned by the user. Some columns in these views contain statistics that are generated by the `DBMS_STATS` package or `ANALYZE` statement.
`DBA_TAB_COLUMNS` `ALL_TAB_COLUMNS` `USER_TAB_COLUMNS`	These views describe the columns of tables, views, and clusters in the database. Some columns in these views contain statistics that are generated by the `DBMS_STATS` package or `ANALYZE` statement.
`DBA_ALL_TABLES` `ALL_ALL_TABLES` `USER_ALL_TABLES`	These views describe all relational and object tables in the database. Object tables are not specifically discussed in this book.
`DBA_TAB_COMMENTS` `ALL_TAB_COMMENTS` `USER_TAB_COMMENTS`	These views display comments for tables and views. Comments are entered using the `COMMENT` statement.
`DBA_COL_COMMENTS` `ALL_COL_COMMENTS` `USER_COL_COMMENTS`	These views display comments for table and view columns. Comments are entered using the `COMMENT` statement.
`DBA_EXTERNAL_TABLES` `ALL_EXTERNAL_TABLES` `USER_EXTERNAL_TABLES`	These views list the specific attributes of external tables in the database.
`DBA_EXTERNAL_LOCATIONS` `ALL_EXTERNAL_LOCATIONS` `USER_EXTERNAL_LOCATIONS`	These views list the data sources for external tables.
`DBA_TAB_HISTOGRAMS` `ALL_TAB_HISTOGRAMS` `USER_TAB_HISTOGRAMS`	These views describe histograms on tables and views.
`DBA_TAB_STATISTICS` `ALL_TAB_STATISTICS` `USER_TAB_STATISTICS`	These views contain optimizer statistics for tables.
`DBA_TAB_COL_STATISTICS` `ALL_TAB_COL_STATISTICS` `USER_TAB_COL_STATISTICS`	These views provide column statistics and histogram information extracted from the related `TAB_COLUMNS` views.
`DBA_TAB_MODIFICATIONS` `ALL_TAB_MODIFICATIONS` `USER_TAB_MODIFICATIONS`	These views describe tables that have been modified since the last time table statistics were gathered on them. They are not populated immediately, but after a time lapse (usually 3 hours).
`DBA_ENCRYPTED_COLUMNS` `USER_ENCRYPTED_COLUMNS` `ALL_ENCRYPTED_COLUMNS`	These views list table columns that are encrypted, and for each column, lists the encryption algorithm in use.
`DBA_UNUSED_COL_TABS` `ALL_UNUSED_COL_TABS` `USER_UNUSED_COL_TABS`	These views list tables with unused columns, as marked by the `ALTER TABLE ... SET UNUSED` statement.
`DBA_PARTIAL_DROP_TABS` `ALL_PARTIAL_DROP_TABS` `USER_PARTIAL_DROP_TABS`	These views list tables that have partially completed `DROP COLUMN` operations. These operations could be incomplete because the operation was interrupted by the user or a system failure.

Example: Displaying Column Information

Column information, such as name, datatype, length, precision, scale, and default data values can be listed using one of the views ending with the _COLUMNS suffix. For example, the following query lists all of the default column values for the emp and dept tables:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, LAST_ANALYZED
    FROM DBA_TAB_COLUMNS
    WHERE OWNER = 'HR'
    ORDER BY TABLE_NAME;

The following is the output from the query:

TABLE_NAME           COLUMN_NAME          DATA_TYPE   DATA_LENGTH LAST_ANALYZED
-------------------- -------------------- ---------- ------------ -------------
COUNTRIES            COUNTRY_ID           CHAR                  2 05-FEB-03
COUNTRIES            COUNTRY_NAME         VARCHAR2             40 05-FEB-03
COUNTRIES            REGION_ID            NUMBER               22 05-FEB-03
DEPARTMENTS          DEPARTMENT_ID        NUMBER               22 05-FEB-03
DEPARTMENTS          DEPARTMENT_NAME      VARCHAR2             30 05-FEB-03
DEPARTMENTS          MANAGER_ID           NUMBER               22 05-FEB-03
DEPARTMENTS          LOCATION_ID          NUMBER               22 05-FEB-03
EMPLOYEES            EMPLOYEE_ID          NUMBER               22 05-FEB-03
EMPLOYEES            FIRST_NAME           VARCHAR2             20 05-FEB-03
EMPLOYEES            LAST_NAME            VARCHAR2             25 05-FEB-03
EMPLOYEES            EMAIL                VARCHAR2             25 05-FEB-03
.
.
.
LOCATIONS            COUNTRY_ID           CHAR                  2 05-FEB-03
REGIONS              REGION_ID            NUMBER               22 05-FEB-03
REGIONS              REGION_NAME          VARCHAR2             25 05-FEB-03

51 rows selected.