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

A data dictionary is where oracle stores information about databases. Almost all system information and object information can be queried in the data dictionary. A data dictionary is the information core of an 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 built as the database is created, and the data dictionary is automatically updated when the database performs certain actions. Data listings and data dictionaries record, verify, and manage ongoing operations.

In Oracle, the sys user is the owner of the data dictionary, which guarantees that no user has the right to change schema objects or rows in the data dictionary in sys schema within the system tablespace system of all databases. That is to say, the data dictionary can only be queried and cannot be modified manually.

I. Purpose of Data Dictionary

Oracle gets information about user objects and storage structures conveniently by accessing data dictionaries. Oracle modifies the data dictionary as soon as the DDL statement is executed. Any user can access database information only in read form using the data dictionary.

II. Information stored in data dictionary

Name of data user

Privileges and roles granted to users

The name of the schema object.

Specific information on integrity constraints;

Default values for each field;

Database space usage;

Store audited information

Strict management of objects and users (applicable to highly confidential management);

Other general database information

Data dictionary view of three or four prefixes

user_: A view that can be read by any user, each user reads differently, and it only provides information about objects under something for the current user.

Select object_name, object_type from user_objects;

all_: A user view that is readable by all users and provides information about objects related to the user.

For example, query all objects accessible to the current user: select owner, object_name, object_type from all_objects;

dba_: provides a view that only database administrators can read, including object information in all user views.

For example: select owner, object_name, object_type from sys.dba_objects;

v$: Dynamic Performance View

Dynamic performance view is used to record the activity information of the current instance. When oracle server is started, the system will establish dynamic performance view; when oracle server is stopped, the system will delete dynamic performance view. All dynamic performance views of oracle start with v$, and oracle provides corresponding synonyms for each dynamic performance view, and synonyms start with v$, for example, the synonym of $datefile is v$datefile; The owner of the dynamic performance view is sys, and the dynamic performance view is typically queried by DBAs or privileged users.

IV. Example of query data dictionary

Query user-related data dictionary

query user select username from dba_users; --only users with administrator privileges can query select username from all_users; --can be used by current or any user--view default table space of current user select username, default_table space from user_users;--current user role select * from user_role_privs;--system privileges and table-level privileges of current user select * from user_sys_privs;select * from user_tab_privs;

Query tablespace related data dictionary (only users with DBA authority can query)

select * from dba_data_files;select * from dba_tablespaces; --tablespaces select namespace_name, sum(bytes), sum(blocks) from dba_free_space group by namespace_name; --free tablespaces select * from dba_data_files where namespace_name ='USERS '; --tablespaces select * from dba_segments where namespace_name ='USERS'; select name, type, source_size, code_size from user_object_size;

Query database objects (only users with DBA authority can query)

select * from dba_objectsselect * from dba_objects where object_type = upper('package body');select * from dba_objects where OBJECT_TYPE='TABLE' and OWNER='SCOTT'

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.