Fundamental of Data Dictionary

Now let’s have look on the fundamental query of Oracle database. It will add some understanding in Oracle’s knowledge treasure. Ordinarily in any interviews a number of these questions request this subject example what’s the distinction between dynamic performance views and data dictionary views? , what can you mean by data dictionary views?     Let’s have look on the definition. An important part of an Oracle database is its own data dictionary, which can be a read-only set of tables which offers administrative metadata about the database.   The data dictionary stores information concerning the physical characteristics of objects, users, the database, and dynamic performance metrics. A DBA has to have a professional understanding of the data dictionary.

A data dictionary contains information such as the following:

  • With the support of information dictionary, we could have information on what users are in the database and should some of their passwords expired.
  • It exhibits the proprietors of each table and related privileges.
  • Data dictionary views show the settings of various database parameters.
  • We could determine which columns have foreign key constraints defined on these.
  • It exhibits tablespaces and related data files and space utilization.
  • The definitions of each schema item in the database, including default values for columns and integrity limitation information
  • The Quantity of space allocated for and currently used by the schema objects
  • The names of Oracle Database users, privileges and functions granted to users, and auditing information related to customers

Types of Data dictionary viewpoints:-

Static Perspectives:- The contents of your database, such as tables, users, indexes, constraints, and privileges.These are occasionally called the inactive CDB/DBA/ALL/ /USER data dictionary views, and they are based on internal tables saved in the SYSTEM tablespace. The expression inactive, in this way, means that the data within these viewpoints changes as you make changes such as creating a desk adding a user, or changing a column.

Ahead of Oracle Database 12c, you will find 3 degrees of static views:

  • USER:- The USER perspectives contain information available to the current user. For example, the USER_TABLES perspective
    includes information about tables possessed by the present user. No special privileges are expected to select from
    that the USER-level viewpoints.
  • ALL:- The ALL views show you all item information the present user has
    accessibility to. For example, the ALL_TABLES view displays all database tables where the present user can carry out any
    form of DML operation.
  • DBA:- The DBA perspectives contain metadata describing all objects in the database (regardless
    of possession or access liberty). To get the DBA views, a DBA function or SELECT_CATALOG_ROLE has to be allowed to the
    current user.

Starting with Oracle Database 12c, There’s a fourth degree That’s applicable when using the container/pluggable
Database attribute:

  • CDB:- The CDB-level views are only applicable if you’re using the pluggable database attribute. This degree offers
    information about all pluggable databases inside a container database (hence the acronym CDB).

Dynamic Perspectives:- A real-time view of activity in the database, such as users connected to the database, SQL presently implementing, memory utilization, locks, and I/O data. These perspectives are referred to and also are based on memory tables. As events take place the info in these viewpoints is upgraded by Oracle. The perspectives are occasionally referred to as the GV$ or V$ viewpoints.   The V$ and GV$ perspectives are indirectly based on tables, that can be once you start your Oracle instance memory structures which are instantiated. Some of those V$ perspectives can be found the instant the Oracle instance is launched.

Notice:-  For those who use Oracle RAC, you need to be familiar with the GV$ global views.These perspectives give global energetic performance information regarding all instances in a cluster (whereas the V$ viewpoints are instance-specific).

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

Data Dictionary viewpoints V$ viewpoints
Data Won’t be lost even after instance is shutdowned Data will be lost if a person is shutdown
Will be available only if instance is OPENED (a few are) Will be available even if case is in bracket or nomount point (STARTED)
Data dictionary watch names are plural V$ view names are striking

Thank you for giving your time to browse our post.