Kamus Data Table (Tables Data Dictionary Views) di Oracle

Berikut ini beberapa informasi yang perlu diketahui :

View	Description
DBA_TABLES ALL_TABLES USER_TABLE	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.

Contoh : Menampilkan kolom informasi

Kolom informasi, seperti nama, datatype, panjang, presisi, skala, dan data nilai default dapat dicantumkan dengan menggunakan salah satu Views Ending dengan akhiran _COLUMNS. Sebagai contoh, query berikut daftar semua nilai default kolom untuk table Employee dan tabel Departement:

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

Dari query tersebut akan didapatkan hasil:
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.

Posted by adewe