Oracle - SPLessons

Chapter 21

Oracle Data Dictionary

Oracle Data Dictionary

The one of the most important segment in the Oracle database is Oracle Data Dictionary. The information saved in the information dictionary is called Metadata. Though it is generally the domain of database administrator, the Oracle Data Dictionary is a expensive cause of information for the developers and end users. Oracle Data Dictionary deals with the data about the items and structures such as

The Oracle Data Dictionary contain 2 stages.

  • The first stage encloses all the basic details about a tables that are used by the different database software segments and it cannot be  accessed by the end users.
  • The second stage gives numerous views on these basic tables to easily access information contained in the structures and objects at various levels.

Oracle Data Dictionary Tables

While installing the Oracle Database, the creation of some specific standard users like sys, system and public is also included.

SYS users

SYS users are the share holders of all the views and data dictionary tables. This user contains the entire privileges to govern objects and structures of an Oracle database. For example creating new users in the database.

System users

System users are the share holders of tables used by various tools such Oracle database forms, Oracle database reports.This system user is having less privileges than SYS user.

public users

Public users are the duplicate users in the Oracle database.Whatever the  privileges authorised to this user, are indirectly allowed to all the clients into database.

Contents of Oracle Data Dictionary

It consisting of basic table and views.Which are normally used in the oracle database.

Basic tables

These holds instructions or information about the database.Oracle database administrator is having the overall permissions to read, write or alter these tables. Clients often access these common tables precisely by the cause of   normalized and maximum information is saved in a script configuration.


View is a database object that can be created on a table to improve the performance of MySQL Server.A view has unique look on data from one or more tables. It can organize data in  unique order, focus or hide some data.

A view contains stored query available for essential table self assured of the conclusion arrangement.Beside ordinary tables a view does’t form a natural schema. It is a essential table, dynamic in the database.

View is a saved query,and can attributed like a table. Views translate the common table information into appropriate data, like table names, joins and where clauses are used to clarify the data contained in common table. The views contains the description and names of all the objects in data dictionary. Few views access by the users of database, and others privileges are applicable only for the administrators.

Dynamic performance views

Database determines an arrangement of crucial views and are managed by the Oracle server and are obtainable to the  controller client SYS. And  are called Dynamic performance views for the reason they are constantly changing when the oracle server is in working stage.

Despite these views seems to be regularly used in the database tables, these views provides data on memory and internal disk structures. Views can be used in select command, and cannot be used in performing update or alter commands. And this views are called as v$ view as there names starts with v$. This views are determined by prefix v_$. Public equivalent consists of prefix v$. So, admin and different users will allow to access v$ objects.

These views holds the instructions about System and sessions parameters,usage of memory allocation and backup files.This views are also called fixed views as this dynamic performance views cannot be modified or eliminated even by the database administrator.Yet administrator can question and generate views on the tables and grant permissions to access  these views to different clients. SYS user holds tables prefixed with v$.

For instance, the v$data record enclose information regarding data records. v$fixed_tables view enclose data about  the tables and views.


  • Data dictionary – Contains set of files and records in the database and this data is called as meta data.
  • Dynamic performance view – Collection of basic views which officially attainable to the SYS user and controlled by the server.