Fundamental of Data Dictionary in oracle database

Today let’s have look on the basic question of Oracle database. It will add some good knowledge in Oracle’s knowledge treasure. Normally in any interviews some of the questions ask for this topic example What is the difference between data dictionary views and dynamic performance views?, What do you mean by data dictionary views?   Let’s have look on the technical definition.

An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database. The data dictionary stores critical information about the physical characteristics of the database, users, objects, and dynamic performance metrics. A senior-level DBA must possess an expert knowledge of the data dictionary.

A data dictionary contains information such as the following:

• With the help of data dictionary, we can have information about what users are in the database and if any of their passwords expired.
• It displays the owners of each table and associated privileges.
• Data dictionary views show the settings of various database parameters.
• We can determine which columns have foreign key constraints defined on them.
• It displays tablespaces and associated data files and space usage.
• The definitions of every schema object in the database, including default values for columns and integrity constraint information
• The amount of space allocated for and currently used by the schema objects
• The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users

Types of Data dictionary views:-

Static Views:- The contents of your database, such as users, tables, indexes, constraints, and privileges.These are sometimes referred to as the static CDB/DBA/ALL/USER data dictionary views, and they’re based on internal tables stored in the SYSTEM tablespace. The term static, in this sense, means that the information within these views only changes as you make changes to your database, such as adding a user, creating a table, or modifying a column.

Prior to Oracle Database 12c, there were only three levels of static views:

• USER:- The USER views contain information available to the current user. For example, the USER_TABLES view
  contains information about tables owned by the current user. No special privileges are required to select from
  the USER-level views.
• ALL:- The ALL views show you all object information the current user has access to. For example, the ALL_TABLES view displays all database tables on which the current user can perform any type of DML  operation.
• DBA:- The DBA views contain metadata describing all objects in the database (regardless of ownership or access privilege). To access the DBA views, a DBA role or SELECT_CATALOG_ROLE must be granted to the current user.

Starting with Oracle Database 12c, there is a fourth level that is applicable when using the container/pluggable database feature:

• CDB:- The CDB–level views are only applicable if you’re using the pluggable database feature. This level provides information about all pluggable databases within a container database (hence the acronym CDB).

Dynamic Views:- A real-time view of activity in the database, such as users connected to the database, SQL currently executing, memory usage, locks, and I/O statistics. These views are based on virtual memory tables and are referred to as the dynamic performance views. The information in these views is continuously updated by Oracle as events take place within the database. The views are also sometimes called the V$ or GV$ views. The V$ and GV$ views are indirectly based on underlying X$ tables, which are internal memory structures that are instantiated when you start your Oracle instance. Some of the V$ views are available the moment the Oracle instance is started.

Note:- If you work with Oracle RAC, you should be familiar with the GV$ global views.These views provide global dynamic performance information regarding all instances in a cluster (whereas the V$ views are instance-specific).

The differences between data dictionary views and V$ views, in Oracle. 

Thank you for giving your valuable time to read the above information.

For More Detail , You can join us as follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp