An example of Oracle data dictionary application

oracle| Data | Application Example
Introduction to Oracle Database Dictionaries
Oracle data dictionaries consist of tables and views that store some database objects about database structure information. The database dictionary describes how the actual data is organized. They can be queried like other database tables or views, but they cannot be modified.
Oracle database dictionaries are often created when databases are created and installed, Oracle data dictionaries are the foundation of Oracle Database system work, and Oracle database systems cannot do any work without the support of data dictionaries.
In the Oracle database dictionary, many views have three different instances, their prefixes being "user_", "All_" and "Dba_" respectively. A database dictionary view of the prefix "User_" Typically records information about the objects owned by the account that executes the query. All_ the database dictionary view of the prefix typically records information about the objects owned by the account that executed the query and information about the objects owned by the account user authorized to public, "Dba_" A database dictionary view that is prefixed contains information about all database objects, regardless of their owner. The main thing in other dictionary views is the v$ view, which is called because they all start with v$ or gv$. The v$ view is based on the x$ virtual view. The v$ view is owned by the SYS user and, by default, only the SYS user and users with DBA system privileges can see all views, and users without DBA authority can see the user_ and All_ views, but cannot see the Dba_ view. In contrast to database-oriented information in Dba_,all and user_ views, these views give an instance-oriented message.
There are view families like Dba_tables,all_tables and User_tables in most of Oracle's data dictionary views. There are more than 100 view families in Oracle, the following table lists the most important and commonly used view families, and it should be noted that each view family has a dba_, a All_ a user_ view.


View Family

Describe

Col_privs

Contains column permissions for the table, including the grantor, the grantee, and the permissions

Extents

Data range information, such as data files, data segment names (segment_name), and size

INDEXES

Index information, such as type, uniqueness, and involved tables

Ind_columns

Indexed column information, such as how the columns on the index are sorted

OBJECTS

Object information, such as State and DDL time

Role_privs

Role permissions, such as Grant and admin options

Segments

Data segment information for tables and indexes, such as tablespace and storage

Sequecnces

Sequence information, such as cache, cycle, and ast_number of sequences

SOURCE

All built-in procedures, functions, and source code for packages except triggers

Synonyms

Alias information, such as referenced objects and database links db_link

Sys_privs

System permissions, such as grantee, privilege, admin options

Tab_columns

Column information for tables and views, including data types for columns

Tab_privs

Table permissions, such as the grantor, the grantee, and the permissions

TABLES

Table information, such as table space (tablespace), storage parameters (storage parms), and number of data rows

Triggers

Trigger information, such as type, event, trigger (trigger body)

USERS

User information, such as temporary and default table spaces

Views

View information, including view definition


There are also some infrequently used data dictionary tables in Oracle, but these tables are not real dictionary families, they are important single views. These views are shown in the following table:

View Name

Describe

User_col_privs_made

User Grant column permissions to others

User_col_privs_recd

User-Obtained column permissions

User_tab_privs_made

Table permissions granted to others by the user

User_tab_privs_recd

User-Obtained table permissions

Application of Oracle Database Dictionary
With Oracle data dictionaries, there are many things we can do with Oracle's DDL statements, and almost all Oracle development aids are designed with this in mind. The author describes how to obtain database table field information.
First we define a database table, the database table structure is as follows:

database table name [Table_test]

Field name

Data type

Length

Default value

Allow null

Primary key

Comments

NAME

VARCHAR2

40



N

Y

Name

SEX

VARCHAR2

1

Y

N



Gender

Birthday

DATE

0



Y



Birthday

HEIGHT

Number

3,1



Y



Height

WEIGHT

Number

3,1



Y



Weight

MEMO

Blob

0



Y



Note

The SQL statement that creates the table is as follows
--Creating data tables
CREATE TABLE Table_test
(
NAME varchar2 NOT NULL,
SEX varchar2 (1) Default ' Y ' NOT NULL,
Birthday date NOT NULL,
HEIGHT number (3,2),
WEIGHT number (3,2),
MEMO blob
);
--Add a note to a column
Comment on column table_test.name is ' name ';
Comment on column table_test. SEX is ' gender ';
Comment on column table_test. Birthday is ' birthday ';
Comment on column table_test. The height is ' tall ';
Comment on column table_test. WEIGHT is ' weight ';
Comment on column table_test. MEMO is ' remarks ';
--Create Constraint relationship primary key foreign key other
ALTER TABLE table_test ADD constraint Tb_test_p_name primary key (NAME);

When the datasheet is created, execute the following SQL statement:
Select
A.column_name field name, A.data_type data type, a.data_length length, a.data_precision integer digit,
A.data_scale decimal places, a.nullable allow null values, a.data_default default values, B.comments notes
From
User_tab_columns a,user_col_comments B
where
A.table_name = B.table_name
and a.column_name = B.column_name
and a.table_name = ' table_test '

We can draw the result:


Field name

Data type

Length

Integer digit

Decimal places

Allow null values

Default value

Note

NAME

VARCHAR2

40





N

<Long>

Name

SEX

VARCHAR2

1





N

<Long>

Gender

Birthday

DATE

7





N

<Long>

Birthday

HEIGHT

Number

22

3

2

Y

<Long>

Height

WEIGHT

Number

22

3

2

Y

<Long>

Weight

MEMO

Blob

4000





Y

<Long>

Note

In this way, we can export the complete database table dictionary document for the end user by using a simple SQL statement and then invoking Word through OLE.
Then execute the following SQL statement:
Select
Index_name index name, index_type index type, uniqueness index category
From
User_indexes
where
table_name = ' table_test '

The results are as follows (note: The sys_il0000031226c00006$$ index is created automatically by the system when creating database tables for maintenance of database table content):

　

Index name

Index type

Index category

1

sys_il0000031226c00006$$

Lob

UNIQUE

2

Tb_test_p_name

NORMAL

UNIQUE

　

Execute the following SQL statement and we'll get more information about the structure of the database table:
Select
A.column_name field name, A.data_type data type, a.data_length length, a.data_precision integer digit,
A.data_scale decimal places, a.nullable allow null values, a.data_default default values, B.comments notes,
Number of C.indexcount indexes
From
User_tab_columns A,
User_col_comments B,
(SELECT COUNT (*) Indexcount,column_name from user_ind_columns where table_name = ' table_test ' GROUP by column_name) C
where
A.table_name = B.table_name
and a.column_name = B.column_name
and A.column_name = C.column_name (+)
and a.table_name = ' table_test '
The results were as follows:

Field name

Data type

Length

Integer digit

Decimal places

Allow null values

Default value

Note

Number of indexes

Birthday

DATE

7





N

<Long>

Birthday



HEIGHT

Number

22

3

2

Y

<Long>

Height



MEMO

Blob

4000





Y

<Long>

Note



NAME

VARCHAR2

40





N

<Long>

Name

1

SEX

VARCHAR2

1





N

<Long>

Gender



WEIGHT

Number

22

3

2

Y

<Long>

Weight



　

Of course the application of Oracle data dictionaries is much more than this, with the support of Oracle database dictionaries, we can get all the information of Oracle database structure, the famous database development tool Pl/sql Developer is entirely based on Oracle database dictionary. The author has also written a database design tool of his own, in the Oracle database dictionary based on a number of extensions, if interested can send mail to LiangShengHong@163.com to contact the author.

[Note: The author uploaded several times to upload unsuccessful, do not know whether the original is still in]