Does a Commit Write My Data to the Datafiles? A Back-To-Basics Guide

07 Apr Does a Commit Write My Data to the Datafiles? A Back-To-Basics Guide

Continuing the series Back-To-Basics Guide, I tackle the question of When I issue a commit, are the changes immediately written to the datafiles?

To answer this, we first need to understand some of Oracle’s processes and memory structures. I invite you to read the post Instance Equals Processes and Memory Structures : A Back-To-Basics Guide  to get a clearer idea of the components we’ll be discussing here.

The first thing to understand is that the DBWn (Database Writer) is a lazy process. This means it doesn’t write the data from the Buffer Cache to the datafiles every few seconds. Instead, it gets triggered by other processes—like CKPT (Checkpoint), for example.

Let’s walk through an example:
Imagine I, in all my divine power, give myself a raise from 1 USD to 5 USD in the employees table, and immediately after, I issue a commit.

Question: If I’ve already committed the data, would I see the 5 USD value in the datafiles?

Before answering, let’s consider a different scenario. Instead of committing the change right after the raise, I decide to wait until after the weekend to reflect on my decision. I just leave the session open without committing the data.
Come Monday, I go directly to the datafiles to check the value. What would I find—1 USD or 5 USD?

Logic would say 1 USD, since I never committed the change, right?

One more question in this same context. Remember, I haven’t committed the data yet. If I were to check the value in the Online Redo Log, what would I see—1 or 5 USD?

If your answer was 1 USD, that would make logical sense—because uncommitted data doesn’t need to be present in the redo logs used during recovery.
But if you answered 5 USD, then how would you explain Oracle’s recovery process? When a recovery occurs, Oracle reads from the Online Redo Logs, and it does find the 5 USD value there.

Let’s pause here—so your answers should be:

• 1 USD (datafiles)
• 5 USD (redo logs)
• and 5 USD (after commit).

Let’s break it down with a practical demo:

Step-by-step Example

Create a small tablespace (1MB), and under the HR user, create a table called prueba_salario, and insert a salary value.

TESTDB > create tablespace prueba datafile '/mount/u01/oracle/TESTDB/data/pruebas01.dbf' size 1m;
-- Tablespace created.

TESTDB > create table hr.prueba_salario (salario varchar2(200)) tablespace prueba;
-- Table created.

TESTDB > insert into hr.prueba_salario values ('1_USD');
-- 1 row created.

TESTDB > commit;
-- Commit complete.

Now restart the database so nothing remains in memory. You’ll notice that the value 1_USD can be found directly in the datafile:

$ strings /mount/u01/oracle/TESTDB/data/pruebas01.dbf

z{|}
5wXTESTDB
PRUEBA
1_USD

Next, update the value and immediately commit. Then check the datafile again. You’ll notice the file still shows 1_USD:

TESTDB > update hr.prueba_salario set salario = '5_USD';
-- 1 row updated.

TESTDB > commit;
-- Commit complete.

$ strings /mount/u01/oracle/TESTDB/data/pruebas01.dbf

z{|}
5wXTESTDB
PRUEBA
1_USD

When we call the CKPT process, it triggers DBWn to write dirty buffers to disk. After executing a checkpoint, check the datafile again—you’ll now see 5_USD:

TESTDB > alter system checkpoint;
-- System altered.

$ strings /mount/u01/oracle/TESTDB/data/pruebas01.dbf

z{|}
5wXTESTDB
PRUEBA
5_USD

Now let’s try a different approach. Drop the table, recreate it, insert 1_USD again, and recycle the database to ensure no memory remnants. Then update the salary to 5_USD but do not commit. Just trigger the CKPT process:

TESTDB > update hr.prueba_salario set salario = '5_USD';
-- 1 row updated.

TESTDB > alter system checkpoint;
-- System altered.

When you inspect the datafile, you’ll see 5_USD, even though the change wasn’t committed:

$ strings /mount/u01/oracle/TESTDB/data/pruebas01.dbf

z{|}
5wXTESTDB
PRUEBA
5_USD

Now, open a new session and query the table—you’ll get 1_USD, the correct value, because the change wasn’t committed:

TESTDB > select * from hr.prueba_salario;

SALARIO
--------
1_USD

You might be wondering: How does Oracle know to show 1 USD even though the datafile has 5 USD?

Oracle achieves this using something called Read Consistency.

Understanding Read Consistency and SCNs

Before continuing, let’s define SCN (System Change Number): it represents a logical point in time when a change is made to the database.

Read Consistency works with SCNs to ensure transaction order. When you run a query in a new session, Oracle uses the SCN at the start of the query to determine the consistent view of the data. Oracle takes the current blocks, copies them into a new buffer, and applies undo to recreate the previous state of the data—this is called a Consistency Read Clone.

To know whether a transaction has been committed, Oracle uses a structure called the Interested Transaction List (ITL), which lives in the header of each block.

Wrapping Up

So, what’s the takeaway?

Since DBWn is a lazy process, even if you commit a change, it may not be written to the datafiles immediately. So where does Oracle get the committed data from if the instance crashes before DBWn writes to disk?

The answer: The Online Redo Log.

Once the redo log contains the commit marker, Oracle can recover the transaction. That’s why the redo logs are criticalfor maintaining data consistency.

Conclusion

I hope this explanation helped clarify how vital Online Redo Logs are. If you lose them, you risk losing committed data. We also explored how undo and read consistency allow Oracle to maintain a correct view of uncommitted data across sessions.

P.S. Special thanks to Arup Nanda for letting me use material from his article 100 Things You Probably Didn’t Know as a foundation for this post.