Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.