Oracle Data Dictionary

Select * from dictionary;

-- Data Dictionary

A data dictionary is a place where Oracle stores database information. It is used to describe data.

For example, the Creator information, creation time information, tablespace information, and user access permission information of a table.

A database data dictionary is a group of tables and view structures. They are stored in the SYSTEM tablespace.

You can access the data dictionary to view detailed information when you encounter difficulties in performing operations on the data in the database.

You can use SQL statements to access the database data dictionary.

The data dictionary includes:

1. Information about all schema objects in the database, such as tables, views, clusters, and indexes.

2. How much space is allocated and how much space is currently used.

3. Column default value.

4. Constraints on information integrity.

5. Oracle user name.

6. permissions granted to users and roles.

7. Audit information accessed or used by the user.

8. Other generated database information.

Data dictionaries in Oracle are static and dynamic.

1. static data dictionary --> it does not change when users access the data dictionary,

-- For example, a table created by a user

2. The dynamic data dictionary is dependent on the performance of the database and reflects some internal information about the database operation. Therefore, it is not always the same when accessing such data dictionaries.

-- The currently locked object

Static Data Dictionary: This type of data dictionary is mainly composed of tables and views.

The tables in the data dictionary cannot be directly accessed, but the views in the data dictionary can be accessed.

The views in the static data dictionary are divided into three types, which have three prefixes: user _ *, all _ *, and dba _*.

User _*

This view stores information about the objects owned by the current user. (All objects in this user mode)

All _*

This attempt stores the information of objects accessible to the current user. (Compared with user _ *, all _ * does not need to own this object. You only need to have the permission to access this object)

Dba _*

This view stores information about all objects in the database. (The premise is that the current user has the permission to access these databases. Generally, the user must have the Administrator permission)

Bytes -----------------------------------------------------------------------------------------------------------

Select * from dictionary;

-- Query tables owned by the user

-- User_tables mainly describes information about all tables owned by the current user,

---- Mainly includes the table name, tablespace name, and cluster name. Through this view, you can clearly understand what tables can be operated by the current user

Desc user_tables;

Select table_name from user_tables;

Select * from user_tables;

-- Query the indexes owned by the user

Select index_name from user_indexes;

-- Query the views owned by the user

Select view_name from user_views;

-- Query the database objects owned by the user, including tables, views, stored procedures, triggers, packages, indexes, sequences, and JAVA files.

Select object_name from user_objects;

-- Describes information about the current user, including the current user name, account id, account status, tablespace name, and creation time.

Select * from user_users;

Bytes -----------------------------------------------------------------------------------------------------------

-- User _/all:

---- The information listed in all _ is the objects that the current user can access, rather than the objects owned by the current user.

-- Query all tables, processes, and functions of a user.

Select owner, object_name, object_type from all_objects

Bytes -----------------------------------------------------------------------------------------------------------

-- Administrator permissions are required for dba,

-- Query the table space information (the current user must have the DBA role ).

Select * from dba_data_files

Bytes -----------------------------------------------------------------------------------------------------------

Dynamic Data Dictionary

Oracle contains some potential tables and views maintained by system administrators, such as SYS. Since these tables and views are constantly updated when the database is running, therefore, they are called dynamic data dictionaries (or dynamic performance views ). These views provide information about the memory and disk operations, so we can only read-only access them, but not modify them.

In Oracle, these dynamic performance views all start with v $.

V $ access

This view displays the database objects locked in the database and the session objects accessing these objects (session objects ).

Select * from v $ access

V $ session

This view lists the details of the current session.

V $ active_instance

This view mainly describes the information of active instances in the current database. You can still use the select statement to observe this information.

V $ context

This view lists the attributes of the current session. For example, namespace and attribute value