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_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	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	Subscribe via RSS Get Daily Updates via Get Latest Free Training Updates delivered directly to your Inbox... Enter your email address:       Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap   Copyright © 2000 - 2009 exforsys.com. All Rights Reserved