Oracle data dictionary
Oracle Database Dictionary Introduction Oracle Data Dictionary is a table and view composite, store some database objects for database structure information. The database dictionary describes how actual data organizes. They can be queried by treating other database tables or views, but no modifications can be made. Oracle Database Dictionary is typically created when creating and installing a database, the Oracle data dictionary is the basis of Oracle database system, no support for data dictionary, and Oracle database systems cannot do anything. In the Oracle Database Dictionary, many views have three different instances, and their prefix is "User_", "All_" and "DBA_", respectively. "User_" is a prefix database Dictionary view typically records information of the object owned by the query, "All_" is the prefix database Dictionary view typically records information and authorization to public accounts including the subjects that enable the query. The user's information, "DBA_" is a prefixed database dictionary view contains information of all database objects, regardless of its owner. The main thing in the other dictionary is V $ view, which is called because they all start with V $ or GV $. V $ view is based on X $ virtual view. V $ view is owned by the SYS user. Under the default situation, only SYS users and users with DBA system privileges can see all views, users without DBA privileges can see User_ and all_ view, but not See the DBA_ view. In contrast to DBA_, ALL, and User_ view, these views are visually given information. In the vast majority of Oracle's Data Dictionary view, there are view families like DBA_TABLES, All_Tables, and User_Tables. More than 100 view families in Oracle, the following table lists the most important and most common view families, you need to pay attention to each view family has a DBA_, an all_ a USER_ view.
View family
description
COL_PRIVS
Contains column permissions of the table, including grant, granted, and permissions
Extents
Data range information, such as data files, data segment names (segment_name) and size
Indexes
Index information, such as type, uniqueness, and table involved
Ind_columns
Index column information, such as the sorting method of the column on the index
Objects
Object information, such as status and DDL TIME
Role_privs
Role permissions, such as GRANT and Admin Options
Segments
Table and index data segment information, such as TableSpace and Storage
Sequecnces
Sequence information, such as Cache, Cycle and AST_NUMBER
Source
All built-in processes, functions, functions of the trigger
Synynyms
Alias information, such as referenced objects and database links DB_LINK
SYS_PRIVS
System permissions, such as Grantee, Privilege, Admin Options
Tab_columns
Table and view column information, including column data types
Tab_privs
Patient permissions, such as grantors, granted people and privileges
Tables
Table information, such as tablespace, storage parameters, and number of data lines
Triggers
Trigger information, such as type, event, trigger body (TRIGGER BODY)
Users
User information, such as temporary and default table space
Views
View information, including view definitions
There are also some unused data dictionary tables in Oracle, but these tables are not a real dictionary family, they are all important single views. These views are shown in the following table: View name
description
USER_COL_PRIVS_MADE
Users granted the permissions of others
User_col_privs_recd
User's permission
User_tab_privs_made
Users award others' list permissions
User_tab_privs_recd
User-giving
Oracle Database Dictionary Applying Oracle Data Dictionary, using Oracle's DDL statement, we can do a lot of things, almost all Oracle development aids are designed with this. The author will explain by 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
type of data
length
Default
Allow air
Primary key
Comment
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
body weight
Memo
BLOB
0
Y
Note
Create a table's SQL statement as follows - Create Data Table Create Table Table_test (Name Varchar2 (40) Not Null, Sex Varchar2 (1) Default 'Y' Not Null, Birthday Date Not Null, Height Number (3, 2), Weight Number (3, 2), MEMO BLOB; - Add Remarks 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.HEIGHT is 'height'; comment on column TABLE_TEST.WEIGHT is 'weight'; comment on column TABLE_TEST.MEMO is 'Remarks'; - creates foreign key constraints other primary alter table TABLE_TEST add constraint TB_TEST_P_NAME primary key ( Name); Data table creation, perform the following SQL statement: SELECT A.COLUMN_NAME field name, A.Data_type data type, A.Data_ dengthth length, A.Data_precision integer, A. Data_Scale Digital, A. Null Allow null value, A.Data_default defaults, B.comments remarks from user_tab_columns A, user_col_comments bwhere A.Table_Name = B.Table_Name and A.Column_Name = B.Column_Name and A.Table_Name = 'TABLE_TEST' we can conclude at the results:
Field Name Data Type Length Integer fractional bits allow nulls Default Remarks NAMEVARCHAR240 N
Index Name Index Type Index Category 1SYS_IL0000031226C00006 $$ Lobunique2TB_Test_P_Namenormalunique
Execute the following SQL statement, we will get more information about the database table structure: SELECT A.COLUMN_NAME field name, A.Data_type data type, A.Data_Precision integer, A.Data_scalead, A. nullable allow nulls, A.Data_default default value, B.comments notes, C.IndexCount index number 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) Cwhere 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' results are as follows:
Field Name Data Type Length Integer fractional bits allow nulls Default Remarks index number BIRTHDAYDATE7 N
Of course, Oracle Data Dictionary is far more than this, through the Oracle Database Dictionary, we can get all information about the Oracle database structure, and the famous database development tool PL / SQL Developer is completely based on Oracle-based database dictionary. The author also wrote its own database design tool, some expansion on the basis of the Oracle database dictionary. If you are interested, you can send an email to liangshenghong@163.com. [Note: The author is uploaded several times and is not successful. I don't know if the original is still]
