Oracle Learning (13): Data Dictionary

Oracle Learning (13): Data Dictionary
The data dictionary structure includes:-basic table: Description of database information, which can only be modified by the database server-User table: User-Defined table

Data Dictionary naming rules

Prefix	Description
USER	User's
ALL	Users can access
DBA	Administrator View
V $	Performance-related data

How to use data dictionary View

Starting from DICTIONARY, this data object contains the table name and description of the data DICTIONARY.

DESCRIBE DICTIONARY

SELECT *

FROM dictionary

WHERE table_name = 'user _ objects ';

USER_OBJECTS and ALL_OBJECTS

USER_OBJECTS: You can query USER_OBJECTS to determine all the objects created by the current user.
You can obtain the following information:
Date created
Date of last modification
Status (valid or invalid)

USER_OBJECTS View:

 

SELECTobject_name, object_type, created, status

FROM user_objects

ORDERBY object_type;

ALL_OBJECTS:

? You can query ALL_OBJECTS to determine the data objects that the current user can access.


Table Information

USER_TABLES user's table:

Statement:

DESCRIBE user_tables

SELECTtable_name

FROM user_tables;

Column information lUSER_TAB_COLUMNS:
Statement:

DESCRIBEuser_tab_columns

SELECT column_name, data_type, data_length,

Data_precision, data_scale, nullable

FROM user_tab_columns

WHERE table_name = 'ployees ';

Constraint USER_CONSTRAINTS: constraints on the current user table
USER_CONS_COLUMNS column constraints created by the current user
Statement:

DESCRIBE user_constraints

SELECT constraint_name, constraint_type,

Search_condition, r_constraint_name,

Delete_rule, status

FROM user_constraints

WHERE table_name = 'ployees ';

----------------------------------------------- ---------

DESCRIBE user_cons_columns

 

 

 

SELECT constraint_name, column_name

FROM user_cons_columns

WHERE table_name = 'ployees ';

View

DESCRIBE user_views

 

 

 

 

 

SELECTDISTINCT view_nameFROM user_views;

SELECT text FROM user_views

WHERE view_name = 'emp_details_view ';

Sequence

DESCRIBE user_sequences

Use USER_SEQUENCES to query the sequence information:

SELECT sequence_name, min_value, max_value,

Increment_by, last_number

FROM user_sequences;

 

 

LAST_NUMBER indicates the next available value when NOCAHCE is not used

 

 

Synonym

DESCRIBE user_synonyms

SELECT *

FROM user_synonyms;

Add comments to a table
Use the COMMENT statement to add a COMMENT to a table or column: Statement:

Commenton table employees

IS 'employee information ';

Commentcreated.

Comments: ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS
Query the table comment select * from user_tab_commentswhere table_name = '??? ';
Conclusion lDICTIONARY lUSER_OBJECTS lUSER_TABLES lUSER_TAB_COLUMNS lUSER_CONSTRAINTS lUSER_CONS_COLUMNS lUSER_VIEWS lUSER_SEQUENCES lUSER_TAB_SYNONYMS l table comment