Basic Content of Oracle Data Dictionary

By using the Oracle Data dictionary technology, database developers can better understand the full picture of the database, which is of great help to database optimization and management. The Oracle Data Dictionary stores database information in Oracle and is used to describe data. For example, the Creator information, creation time information, tablespace information, and user access permission information of a table. You can access the data dictionary to view detailed information when you encounter difficulties in performing operations on the data in the database.

Oracle data dictionaries are static and dynamic. The static data dictionary does not change when users access the Oracle Data Dictionary. However, the dynamic data dictionary depends on the performance of the database and reflects some internal information about the database operation, therefore, accessing such data dictionaries is often not static. The following two types of data dictionaries are discussed respectively.

1. Static Data Dictionary

This type of data dictionary is mainly composed of tables and views. It should be noted that the tables in the data dictionary cannot be directly accessed, but can access the views in the data dictionary. The views in the static data dictionary are divided into three types, which have three prefixes: user _ *, all _ *, and dba _*.
1) user _ * this view stores information about the objects owned by the current user. All objects in this user mode)
2) all _ * The object information that the current user can access is stored. Compared with user _ *, all _ * does not need to own this object. You only need to have the permission to access this object)
3) 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)

From the above description, we can see that the data stored between the three will certainly overlap. In fact, they have different access scopes because of different permissions, so they have different access object scopes. Specifically, because the data dictionary view is owned by SYS system users, only SYS and DBA system users can view all views while saving their resources. Users without DBA permissions can only view user _ * And all. If the SELECT permission is not granted to the user, the user cannot see the dba _ * view.

Because the three are similar, the following uses user _ as an example to introduce several common static views:

1) user_users view: describes the information of the current user, including the current user name, account id, account status, tablespace name, and creation time. For example, you can run the following command to return the information. Select * from user_users

2) user_tables view: describes information about all tables owned by the current user, including the table name, tablespace name, and cluster name. Through this view, you can clearly understand what tables can be operated by the current user. Run the following command: select * from user_tables.

3) user_objects view: describes information about all objects owned by the current user, including tables, views, stored procedures, triggers, packages, indexes, and sequences. This view is more comprehensive than the user_tables view. For example, to obtain the object type and status information named "package1", run the following command:
Select object_type, status
From user_objects
Where object_name = upper ('package1 ');

Note the usage of upper. All objects in the data dictionary are in uppercase format, while PL/SQL is not case sensitive. Therefore, you must pay attention to case-sensitive matching in actual operations.

4) user_tab_privs view: This view stores the permissions of the current user on all tables. For example, to understand the permission information of the current user on table1, you can run the following command: select * from user_tab_privs where table_name = upper ('table1 ')

After understanding the permissions of the current user on the table, you can clearly know which operations can be performed and which operations cannot be performed.

The preceding views start with user _. In fact, all _ starts with the same, but the information listed is the objects accessible to the current user rather than the objects owned by the current user. Administrator permissions are required for dba _. Other operations are the same, so we will not repeat them here.

2. 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, so 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 $, such as v $ access. The following describes several major dynamic performance views.

1) v $ access this view shows the database objects locked in the database and the session objects accessing these objects ).

Run the following command: select * from v $ access

The result is as follows: because there are many records, some records are extracted here)

SID
OWNER OBJECT TYPE
27 dkh v $ ACCESS CURSOR
27 public v $ ACCESS SYNONYM
27 SYS DBMS_APPLICATION_INFO PACKAGE
27 sys gv $ ACCESS VIEW

2) v $ session this view lists the details of the current session. Because there are many fields in this view, detailed fields are not listed here. For details, you can directly enter desc v $ session under the SQL * plus command line.

3) 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.

4) v $ context this view lists the attributes of the current session. For example, namespace and attribute value.

1. Oracle Data Dictionary practices
2. Which views are included in the Oracle Data dictionary?
3. Usage of Oracle Data Dictionary
4. Oracle System Administrator basics: Oracle Data Dictionary
5. Oracle Data Dictionary description