Oracle Learning (13): Data Dictionary, oracle dictionary

Oracle Learning (13): Data Dictionary, oracle 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: constraint on the current user table • column constraint created by the current user USER_CONS_COLUMNS
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

• Query the sequence information through USER_SEQUENCES:

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 COMMENT statements to add comments to tables or columns: • Statement:

Commenton table employees

IS 'employee information ';

Commentcreated.

• Comments-related views: • ALL_COL_COMMENTS • USER_COL_COMMENTS • ALL_TAB_COMMENTS • USER_TAB_COMMENTS • query Table Comments • select * from user_tab_commentswhere table_name = '??? ';





Summary comment