How DDL Statement Works ?

1. Server process sends the DDL statement to library cache.

2. Server process will handover the statements to “library Cache” (L.C) of shared pool.

3. A pre-check will be done for the existence of parsed statement.

4. If the server process doesn’t finds an already parsed statement, it will initiate the phase of parsing ( First phase in SQL execution).

5. Parsing will be done in three steps.

       a. Dividing the SQL statement into literals.
       b. Checking for syntax error.
       c. Checking for symantic error.

6. The statement will get executed in PGA (using the best execution plan provided by the optimizer) and redo entries will be generated.

7. Redo entries will be copied to log buffer cache by server process.

8. Server process ( After taking information from DDC ) will search for blocks in database buffer cache. If not found blocks will be copied to MRU END of LRU LIST of database buffer cache.

Note: As all DDL statements are auto commit ( no rollback ) undo blocks will not be copied by server process.

9. The blocks got copied to database buffer cache are “base table” blocks.

10.After modification blocks will become dirty and will be moved WRITE LIST,from where database writer writes to database.

 Note: Before DB writer writes to log writer writes.