Data Dictionary 1

[Rmcta]

On one book I read: "The data dictionary is updated by the Oracle server whenever a data definition language command is executed. In addition, data manipulation language commands, such as one that causes a table to extend, can update the data dictionary."

On another book I read: "The Oracle server does not modify
the data dictionary when DML statements are executed. The Oracle server modifies the data dictionary when a DDL statement is executed."

I am confused! Can someone help me understand when the data dictionary is modified? Is it in response to DML, DLL or both?

Thanks.

Rama

 

[SantaMufasa]

Rama,

The Oracle data dictionary contains information about objects in the database such as the names, location(s), and other characteristics about tables, indexes, views, procedures, functions, et cetera. We build, modify, and destroy database objects via Data Definition Language (DDL)commands (CREATE, ALTER, and DROP). But, as we do Data Manipulation Language (DML) commands (particularly INSERT and UPDATE) to build and modify ROWS of information in tables, they can cause the expansion of tables, and at some point, the need for new EXTENTS (i.e., contiguous blocks) to accommodate those expansions. When allocation of the new extents occur, we see evidence of that activity via changes in rows in such DBA views as DBA_EXTENTS, DBA_FREE_SPACE, DBA_DATA_FILES, V$DATAFILE, et cetera. When changes to ROWS occur, that is DML; these changes resulted from application-table DML. Therefore, application-table DML truly CAN cause data dictionary DDL.

Therefore, the book you read that states, "The Oracle server does not modify the data dictionary when DML statements are executed..." is incorrect as you can see from the expansion/change examples, above.

Dave

 

[Rmcta]

I truly appreciate your clear explanation.
Thank you very much,
Rama

 

[SantaMufasa]

My apologies, Rama, to you and other readers: I meant one of my lines to read, "Therefore, application-table DML truly CAN cause data dictionary DML." Thanks for your indulgence.

Dave