Data dictionary is the place where Oracle stores database information. Almost all system information and object information can be queried in the data dictionary. Data dictionary is the information core of Oracle database system. It is a set of tables and views that provide information about database. These tables and views are read-only. It is established with the establishment of the database, and the data dictionary will be automatically updated when the database performs specific actions. Data list and data dictionary to record, verify and manage the operation in progress.
In Oracle, the sys user is the owner of the data dictionary. The data dictionary guarantees that in the system table space system of all databases, no user has the right to change the schema object or the row in the data dictionary under the sys mode. In other words, the data dictionary can only be queried and cannot be modified manually.
1、 Data dictionary usage
By accessing the data dictionary, Oracle can easily obtain the information about user’s object and storage structure. When the system executes DDL statement, Oracle will modify the data dictionary in time. Any user can only use the data dictionary to get the database information in the form of reading.
2、 Information stored in data dictionary
- The name of the data user
- Permissions and roles granted to users
- The name of the schema object.
- The specific information of integrity constraints;
- The default value of each field;
- Usage of database space;
- Store audit information
- Strict management of objects and users (applicable to highly confidential management);
- Other general database information
3、 Data dictionary view of four prefixes
- user_ The view that any user can read is different from each other. It only provides the object information under something of the current user.
To query all objects in the current mode: select object_name, object_type from user_objects;
- all_ A user view that can be read by all users. It provides information about objects related to users.
To query all the objects that the current user can access: select owner, object_name, object_type from all_objects;
- dba_ : provides views that can only be read by database administrators, including object information in all user views.
For example: select owner, object_name, object_type from sys.dba_objects;
- V $: dynamic performance view
The dynamic performance view is used to record the activity information of the current routine. When the Oracle server is started, the system will establish the dynamic performance view. When the Oracle server is stopped, the system will delete the dynamic performance view. All dynamic performance views of Oracle are in the form of V_ , and Oracle provides the corresponding synonyms for each dynamic performance view, and the synonyms start with V $, for example_ The synonym of $datefile is v $datefile; the owner of dynamic performance view is sys. In general, DBA or privileged users query dynamic performance view.
4、 Query data dictionary example
- Query user related data dictionary
Query users select username from dba_users; --Only users with administrator rights can query select username from all_users; --Available to current or any user --View the default tablespace for the current user select username, default_tablespace from user_users; --Current user role select * from user_role_privs; --System permissions and table level permissions of the current user select * from user_sys_privs; select * from user_tab_privs;
- Query table space related data dictionary (only users with DBA permission can query)
select * from dba_data_files; select * from dba_tablespaces; --Table space select tablespace_name, sum(bytes), sum(blocks) from dba_free_space group by tablespace_name; --Free table space select * from dba_data_files where tablespace_name='USERS'; --Data file for table space select * from dba_segments where tablespace_name='USERS'; --Query the size of used space for user mode objects select name, type, source_size, code_size from user_object_size;
- Query database objects (only users with DBA permission can query)
select * from dba_objects select * from dba_objects where object_type = upper('package body'); select * from dba_objects where OBJECT_TYPE='TABLE' and OWNER='SCOTT'