Data in database can be manipulated, accessed or added if the description of the stored data objects is provided. Every database system must provide description of the available user data objects created and which are to be used for user applications. Such requirement is fulfilled by a Data Dictionary or a system catalog. This catalog is also called metadata-data about data. The information stored in a data dictionary is also needed to optimize the use of stored data.
Users, Application developers and database administrators (DBA) all access the system catalog.
When it is created?
Data dictionary is basically a read-only set of information about DB objects. It is created whenever a new database is created by a DBA. It gets updated whenever a DDL statement is executed on the database. It includes CREATE, DROP and ALTER statements. It is also updated with DML statements like INSERT, UPDATE and DELETE executed on user tables.
The DDL statements are executed on special table(s) making the data dictionary. It can be accessed and modified by the database system when a user or administrator executes these DDL commands. On execution of any DDL command on an existing database, database system checks and then updates the existing data of catalog.
You have the access to following objects in data dictionary –
- The definitions schema objects created in a database. These objects are tables, views, indexes, synonyms, sequences, procedures, functions, packages, triggers etc.
- Amount of space allocated to various schema objects and the current usage
- Default values for table columns
- Details of all Integrity constraint applied on all tables
- Users created for the current Oracle Database
- Granted user Privileges and roles
- Log information about the access of schema objects by different database users created. This is the audit information needed to secure a database
Data Dictionary in Oracle
Oracle provides information about the schemas, objects and users of a database. It contains very useful data required to develop an application for storing and accessing data. It contains all the metadata about the database i.e. the data behind the data inside the database that the user has created.
To access the meta data Oracle provides SYSTEM tablespace. It contains the information for the entire database. So, to access the system catalog a developer must access the relevant data from SYSTEM tablespace. The data dictionary is stored inside the datafile.
It comprises of several views categorized as User views, DBA views and All views. These views can be used to access data dictionary. The function of these views is to manage and monitor the database structure that was created earlier using the oracle server.
SYS is the primary owner and manager for all the tables available in the data dictionary. Data Dictionary in Oracle is maintained by Oracle server. These tables cannot be altered as a high degree of security is associated with them, which is defined by security administrator.
Data Dictionary is accessed with SQL statements against tables and views owned by SYS. The following command can be used to access all the table names created by users and stored in the data dictionary.
SELECT table_name FROM user_tables;
Oracle provides an easy way to find any information quickly with the help of data dictionary. The data dictionary is exclusively owned by the SYS user.
LIKE operator can also be used to search for specific tables inside the database. The user can also use the DICT or DICTIONARY view to search for a table.
Sometime, the user may need to create several views and joins to get the information that is required to fulfill desired views on the database.