Oracle Dictionary Cache | Oracle Data Dictionary

Oracle server requires some metadata information to process SQL statements. This information is necessary to maintain data integrity, data consistency and data security. When a user submits a query, oracle needs detailed information about objects involved in the query, for example, whether the accessed objects exist and the user has required privileges.

“Data dictionary” in oracle database is the component which keeps information about database. Data Dictionary is collection of read-only tables. These tables are maintained by oracle database. Data inside these tables is modified when DDL commands are issued. This data is kept in encrypted form and should not be modified by any user. Data dictionary holds detailed information about Schema objects structure, space allocation, user privileges, auditing information and default vales of columns etc.

Structure of Data Dictionary Cache

Data dictionary is organized into Base Tables and User-accessible view. These tables and views are stored in SYSTEM tablespace and owned by oracle user sys. As system tablespace is always online, so data dictionary is always available.

As the data in Base Tables is in encrypted format so users can not and should not directly access information from these tables. Oracle has provided certain view to fetch information. These views decode encrypted data and provide us useful information. These views change with every new oracle release.

Oracle also contains a set of dynamic performance views. These views are continuously updated and contain latest information about database. These views are created in RAM and available even if the database in not open. The information from dynamic performance view is frequently accessed during database recovery process. For example, if you open database in no-mount stage, at this stage DBA_TBLESPACES view is not available as database is not open but you can query V$TABLESPACE dynamic view to get information about tablespaces.

Dynamic performance view start with V$ and GV$. While other Data Dictionary views start with USER, ALL or DBA prefix.

Oracle Dictionary Cache

Dictionary Cache is place in Shared Pool which contains Data Dictionary. Oracle frequently requires Data Dictionary. Most parts of Data Dictionary are cached into Dictionary Cache. Oracle utilizes Dictionary Cache information in query parsing.

Dictionary cache is also called Row Cache as data inside Dictionary Cache is maintained into rows instead of buffer.

2 thoughts on “Oracle Dictionary Cache | Oracle Data Dictionary”

  1. I would like to know more about…
    What are all the information that will be stored in Data Dictionary Cache? That is SCOTT fires a query on emp, DALE fires a query on emp.
    What information will be stored if the SCOTT accessing a SYS.EMP table and if accessing his own EMP table?
    Similarly if the DALE accessing a SYS.EMP table and if accessing his own EMP table?

  2. If SCOTT fires a query on any table, oracle will look into data dictionary cache to check whether SCOTT has SELECT privilege on accessed table.

Leave a Reply

Your email address will not be published. Required fields are marked *