Till today date normally we all know about Oracle Database Architecture. As we take one step forward in Oracle world we get introduces new terms and technology in Oracle. In this post let’s have a look on data dictionary architecture. Which might be new for you but I am sure it will be more interesting as Oracle Database architecture. We all know about the data dictionary in Oracle database. It plays an important role in Oracle database. There is two kind of data dictionary views. first one is static views another one dynamic. Now let’s have look on its work.
Static Views:- The static views are based on internal Oracle tables, such as USER$, TAB$, and IND$. If you have access to the SYS schema, you can view underlying tables directly via SQL. For most situations, you only need to access the static views that are based on the underlying internal tables.
The data dictionary tables (such as USER$, TAB$, IND$) are created during the execution of the CREATE DATABASE command. As part of creating a database, the sql.bsq file is executed, which builds these internal data dictionary tables. The sql.bsq file is generally located in the ORACLE_HOME/RDBMS/admin directory; you can view it via an OS editing utility (such as vi, in Linux/Unix, or Notepad, in Windows). The static views are created when you run the catalog.sql script (usually, you run this script once the CREATE DATABASE operation succeeds). The catalog.sql script is located in the ORACLE_HOME/RDBMS/admin directory.
NOTE:- If you manually create a database (not using the dbca utility), you must be connected as the SYS schema when you run the catalog.sql and catproc.sql scripts. The SYS schema is the owner of all objects in the data dictionary.
You can view the creation scripts of the static views by querying the TEXT column of DBA_VIEWS; for example,
SQL > set long 5000 SQL > select text from dba_views where view_name = 'DBA_VIEWS';
Dynamic Views:- The dynamic performance data dictionary views are colloquially referred to as the V$ and GV$ views. These views are constantly updated by Oracle and reflect the current condition of the instance and database. Dynamic views are critical for diagnosing real-time performance issues.
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.
For example, V$PARAMETER contains meaningful data after the STARTUP NOMOUNT command has been issued, and doesn’t require the database to be mounted or open. Other dynamic views (such as V$CONTROLFILE) depend on information in the control file and therefore contain significant information only after the database has been mounted. Some V$ views (such as V$BH) provide kernel-processing information and thus have useful results only after the database has been opened.
At the top layer, the V$ views are actually synonyms that point to underlying SYS.V_$ views. At the next layer down, the SYS.V_$ objects are views created on top of another layer of SYS.V$ views. The SYS.V$ views, in turn, are based on the SYS.GV$ views. At the bottom layer, the SYS.GV$ views are based on the X$ memory structures. The top-level V$ synonyms and SYS.V_$ views are created when you run the catalog.sql script, which you usually do after the database is initially created.
In an interview, any interviewers ask about data dictionary. Kindly share your deep knowledge, explain how we get data dictionary in Oracle Database and print your best impression on him.
Thank you for considering our website to add better knowledge in the world of Oracle database.
For More Detail , You can join us as follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
