Oracle 10g Tutorials -Creating Index-Organized 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_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_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 = 'SYSTEM’
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

05-FEB-03

COUNTRIES

COUNTRY_NAME

VARCHAR2

05-FEB-03

COUNTRIES

REGION_ID

NUMBER

05-FEB-03

DEPARTMENTS

DEPARTMENT_ID

NUMBER

05-FEB-03

DEPARTMENTS

DEPARTMENT_NAME

VARCHAR2

05-FEB-03

DEPARTMENTS

MANAGER_ID

NUMBER

05-FEB-03

DEPARTMENTS

LOCATION_ID

NUMBER

05-FEB-03

EMPLOYEES

EMPLOYEE_ID

NUMBER

05-FEB-03

EMPLOYEES

FIRST_NAME

VARCHAR2

05-FEB-03

EMPLOYEES

LAST_NAME

VARCHAR2

05-FEB-03

EMPLOYEES

VARCHAR2

05-FEB-03

LOCATIONS

COUNTRY_ID

CHAR

05-FEB-03

REGIONS

REGION_ID

NUMBER

05-FEB-03

REGIONS

REGION_NAME

VARCHAR2

05-FEB-03

51 rows selected.

Viewing Information About Tables

Tags