Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the data dictionary of Oracle". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the data dictionary of Oracle".

Data dictionary is the place where oracle stores information about database, and almost all system information and object information can be queried in data dictionary. Data dictionary is the information core of oracle database system. It is a collection of tables and views that provide information about the database. These tables and views are read-only. It is established with the establishment of the database, and the data dictionary is automatically updated when the database performs specific actions. A list of data and a data dictionary to record, check and manage ongoing operations.

In Oracle, sys users are the owners of data dictionaries, which guarantee that no user has the right to change schema objects or rows in data dictionaries in the system tablespace system of all databases. In other words, the data dictionary can only be queried and cannot be modified manually.

I. use of data dictionaries

Oracle can easily get information about something, object and storage structure of the user by accessing the data dictionary. When the system executes the DDL statement, oracle will modify the data dictionary in time. Any user can only use the data dictionary to obtain database information in the form of reading.

Second, the information stored in the data dictionary

Name of the data user

Permissions and roles granted to the user

The name of the schema object.

Specific information about integrity constraints

Default value for each field

Usage of database space

Store audit information

Strict management of objects and users (suitable for highly confidential management)

Other general database information

Data dictionary view of three and four prefixes

User_: a view that can be read by any user, which is different for each user. It only provides object information under something for the current user.

For example, query all objects in the current mode:

Select object_name, object_type from user_objects

All_: a user view that is readable to all users and provides object information about the user.

For example, 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.

Such as:

Select owner, object_name, object_type from sys.dba_objects

Vstocks: dynamic performance view

The dynamic performance view is used to record the activity information of the current routine. When oracle server is started, the dynamic performance view is established; when oracle server is stopped, the dynamic performance view is deleted. All dynamic performance views of oracle start with v_KaTeX parse error: Expected 'EOF', got' open'at position 1: open, and oracle for each action. At the beginning, for example, the'at position 9: datefile 'synonym for _ KaTeX parse error: Expected' EOF', got'is vdatefile;. The owner of the dynamic performance view is sys. In general, the dynamic performance view is queried by DBA or privileged users.

Fourth, query data dictionary example

Query user-related data dictionaries

Query users

Select username from dba_users;-only users with administrator privileges can query

Select username from all_users;-available to current or any user

-- View the current user's default tablespace

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 tablespace-related data dictionaries (only users with DBA permissions can query)

Select * from dba_data_files

Select * from dba_tablespaces;-Tablespace

Select tablespace_name, sum (bytes), sum (blocks) from dba_free_space group by tablespace_name;-free tablespace

Select * from dba_data_files where tablespace_name='USERS';-the data file for the tablespace

Select * from dba_segments where tablespace_name='USERS'

-- query the size of the space in use used by the user mode object

Select name, type, source_size, code_size from user_object_size

Query database objects (only users with DBA permissions 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' Thank you for your reading. The above is the content of "what is Oracle's data Dictionary". After the study of this article, I believe you have a deeper understanding of what the Oracle data Dictionary is, and the specific usage needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.