View of Management objects and data Dictionary based on SQL (XIX)
Data dictionary
Data dictionary structure
Data dictionary structure
View naming convention:
View prefix means USER user view (own, under your own scheme) ALL extended user view (on the basis of user_, users have access) DBA database administrator view (under all scenarios) V$ performance-related data
How to use the Dictionary View
The DICTIONARY view includes the names and descriptions of all dictionary tables and views.
Desc dictionary
Select * from dictionary where table_name = 'user_objects'
USER_OBJECTS and ALL_OBJECTS
USER_OBJECTS:
Query USER_OBJECTS to see all the objects you have.
Using USER_OBJECTS you can get the names of all the objects you own and
The type also has the following information:
-date of creation
-Last modified date
-status (valid or invalid)
ALL_OBJECTS:
Query ALL_OBJECTS and you can see all the objects you have access to.
USER_OBJECTS view
Select object_name, object_type, created, status
From user_objects
Order by object_type
The data dictionary view can query the contents:
-Table information
-Field information
-constraint information
USER_TABLES:
Desc user_tables
Select table_name from user_tables
Column information
USER_TAB_COLUMNS:
Desc user_tab_columns
Name Null? Type
-
TABLE_NAME NOT NULL VARCHAR2 (30)
COLUMN_NAME NOT NULL VARCHAR2 (30)
DATA_TYPE VARCHAR2 (106)
DATA_TYPE_MOD VARCHAR2 (3)
DATA_TYPE_OWNER VARCHAR2 (30)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2 (1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW (32)
HIGH_VALUE RAW (32)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2 (44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2 (3)
USER_STATS VARCHAR2 (3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2 (1)
Select column_name, data_type, data_length
Data_precision, data_scale, nullable
From user_tab_columns
Where table_name = 'employees'
Constraint information
USER_CONSTRAINTS describes the constraints defined in your table
USER_CONS_COLUMNS describes the fields specified by the constraint
Select constraint_name, constraint_type
Search_condition, r_constraint_name
Delete_rule, status
From user_constraints
Where table_name = 'employees'
Query USER_CONS_COLUMNS
Desc user_cons_columns
Select constraint_name, column_name
From user_cons_columns
Where table_name = 'employees'
View information
Desc user_views
Select view_name from user_views
Select text from user_views where view_name = 'EMP_DETAILS_VIEW'
Sequence information
Desc user_sequences
Confirm Sequences
Confirm your sequence values in the USER_SEQUENCES data dictionary table
Select sequence_name, min_value, max_value,increment_by, last_number from user_sequences
If the NOCACHE option is specified, the content of the LAST_NUMBER is the next available sequence number
Index information
USER_INDEXES provides information about the index.
USER_IND_COLUMNS describes index columns, including indexes and fields
Desc user_indexes
Name Null? Type
-
INDEX_NAME NOT NULL VARCHAR2 (30)
INDEX_TYPE VARCHAR2 (27)
TABLE_OWNER NOT NULL VARCHAR2 (30)
TABLE_NAME NOT NULL VARCHAR2 (30)
TABLE_TYPE VARCHAR2 (11)
UNIQUENESS VARCHAR2 (9)
COMPRESSION VARCHAR2 (8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2 (30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
Select index_name, table_name,uniqueness
From user_indexes
Where table_name = 'employees'
INDEX_NAME TABLE_NAME UNIQUENES
-
EMP_JOB_IX EMPLOYEES NONUNIQUE
EMP_EMAIL_UK EMPLOYEES UNIQUE
EMP_EMP_ID_PK EMPLOYEES UNIQUE
EMP_MANAGER_IX EMPLOYEES NONUNIQUE
EMP_NAME_IX EMPLOYEES NONUNIQUE
EMP_DEPARTMENT_IX EMPLOYEES NONUNIQUE
6 rows selected.
Query USER_IND_COLUMNS
Desc user_ind_columns
Name Null? Type
-
INDEX_NAME VARCHAR2 (30)
TABLE_NAME VARCHAR2 (30)
COLUMN_NAME VARCHAR2 (4000)
COLUMN_POSITION NUMBER
COLUMN_LENGTH NUMBER
CHAR_LENGTH NUMBER
DESCEND VARCHAR2 (4)
Synonym information
Desc user_synonyms
Select * from user_synonyms
Add comments to the table
Use the COMMENT command to annotate tables and fields:
Comment on table employees is' employee information'
Comment on column employees.first_name is' first name of the employee'
You can view comment information through the following data dictionary views:
-ALL_COL_COMMENTS
-USER_COL_COMMENTS
-ALL_TAB_COMMENTS
-USER_TAB_COMMENTS
Commonly used user data dictionaries are generally as follows. You can view the relevant column information as needed:
DICTIONARY
USER_OBJECTS
USER_TABLES
USER_TAB_COLUMNS
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_VIEWS
USER_SEQUENCES
USER_INDEXES
USER_SYNONYMS
