How DATA Dictionary is created in Oracle?

I feel silly when I presumed the below information was already available in the blog/internet. However later I realized that it is NOT and hence it prompted me to add this blog.

Question : What is DATA Dictionary in an Oracle Database?


Answer : DATA Dictionary is also called METADATA. Oracle documentation tells that METADATA is created by running catalog.sql. While this is partially TRUE, the catalog.sql creates only the view/synonyms and few additional package/procedure/functions..also few more objects. However it certainly doesn't create the actual metadata. These views are created to access the metadata in a readable format. Metadata doesn't reside in the VIEWs rather it resides in the $(dollar) tables. For example, whatever information that you see by querying dba_objects view is majorly sourced from the dollar table called obj$. Likewise dba_tables view is sourced from tab$

In summary all the metadata resides in the DOLLAR table and they are not views.

Few examples,
obj$ - For objects
tab$ - For tables
ts$ - For tablespace
file$ - For datafiles
seg$ - For segments

Question : How & When DATA Dictionary is created in an Oracle Database?


Answer : DATA Dictionary is actually created during the Database creation time. After NOMOUNTING the instance you run the CREATE DATABASE command. This command, after creating the SYSTEM datafile it calls a SQL file ?/rdbms/admin/sql.bsq which creates the metadata

Different ways to invalidate a cursor in memory for Oracle database

Purpose : Whilst you may have a various different situation and need to invalidate a cursor from the oracle cache/memory at the instance level, I always had the need after pinning a SQL profile.

Note : Pinning a plan through SQL profile doesn't call for an explicit hard parsing.

Simple method/way to invalidate a cursor

1. Flush the SHARED_POOL

Eg.
alter system flush shared_pool;

2. Delete or Gather statistics of the related objects (table/index) which are referenced in the cursor (execution plan) using DBMS_STATS

3. Grant or Revoke privilege on the related objects (table) which are referenced in the cursor

Eg.
I always use the following commands together on the tables which are referenced in the SQL query/cursor

grant select on scott.emp to system;
revoke select on scott.emp from system;

4. Alter the related objects (table/index) which are referenced in the cursor using DDL command.

5. You can also PURGE the cursor from the shared pool

Eg.
exec dbms_shared_pool.purge(',','C');

The above methods helped me in the situation which I described in the PURPOSE section.

However the below alternate methods may also be considered depending on your situation.

6. You can even add a hint to the SQL query to create a new sql_id. Hence it will be a fresh parse.

Eg.
select /* query1 */ empno, ename from emp;
select /* query2 */ empno, ename from emp;


7. Ofcourse, always database BOUNCE will never let you down :-) Just an extreme step.

Keywords : parse, hard, soft, invalid cursor, invalidate cursor, memory, cache, oracle