Skip to Main Content
  • Questions

  • oracle commit writes to redo, but not to datafiles

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gyan Bahadur.

Asked: January 10, 2018 - 12:31 pm UTC

Last updated: January 12, 2018 - 12:11 am UTC

Version: 11.2.04

Viewed 1000+ times

You Asked

Hi Tom,

I want to verify once the commit the transaction, only writes to redo log, but does not write to data files. How the verify the situation??

and Connor said...

We can do a simple demo

SQL> drop tablespace demo including contents and datafiles;

Tablespace dropped.

SQL>
SQL> create tablespace demo datafile 'C:\ORACLE\ORADATA\DB122\DEMO.DBF' size 2m autoextend on
  2  extent management local uniform size 1m;

Tablespace created.

SQL>
SQL> select to_char(ascii('X'),'XX') from dual;

TO_
---
 58


So if I create a table with lots of "X" characters in a column, I'd expect to see lots of hex code 58 in the datafile.

SQL> create table t tablespace demo
  2  as select rpad('X',100,'X') col
  3  from dual
  4  connect by level <= 1000;

Table created.

C:\> od -x | grep "58 "

...
...
4105200 5858 5858 5858 5858 5858 5858 5858 5858
*
4105320 5858 5858 5858 5858 5858 5858 002c 6401
4105340 5858 5858 5858 5858 5858 5858 5858 5858
*
4105500 5858 5858 002c 6401 5858 5858 5858 5858
4105520 5858 5858 5858 5858 5858 5858 5858 5858
*
4105640 5858 5858 5858 5858 5858 5858 002c 6401
4105660 5858 5858 5858 5858 5858 5858 5858 5858
*
4106020 5858 5858 002c 6401 5858 5858 5858 5858
4106040 5858 5858 5858 5858 5858 5858 5858 5858
*
4106160 5858 5858 5858 5858 5858 5858 002c 6401
4106200 5858 5858 5858 5858 5858 5858 5858 5858
*
4106340 5858 5858 002c 6401 5858 5858 5858 5858
4106360 5858 5858 5858 5858 5858 5858 5858 5858
*
4106500 5858 5858 5858 5858 5858 5858 002c 6401
4106520 5858 5858 5858 5858 5858 5858 5858 5858
*

...
...


Now I'll update (and commit) a change to all of those rows, to see the characters to "A" (hex code 41). Before I do that, lets what the datafile currently has in it for 41:

C:\>od -x DEMO.DBF | grep "41 "
2020000 a200 0000 0041 0000 0000 0000 0000 0501
2020020 a741 0000 0000 0000 0000 0000 0000 0000


Almost nothing. Now here comes my update:

SQL> update t
  2  set col = rpad('A',100,'A');

1000 rows updated.

SQL> commit;

Commit complete.


yet still the datafile shows nothing

C:\>od -x DEMO.DBF | grep "41 "
2020000 a200 0000 0041 0000 0000 0000 0000 0501
2020020 a741 0000 0000 0000 0000 0000 0000 0000


Now I do a checkpoint, or flush the buffer cache, or just wait for a while until the buffers are written out

C:\>od -x DEMO.DBF | grep "41 "
0540020 d341 0000 0008 0000 0080 0934 0000 0000
2020000 a21e 0000 0041 0200 84cd 958c 0d79 0401
4003640 0000 0000 0000 0000 0000 0000 4141 4141
4003660 4141 4141 4141 4141 4141 4141 4141 4141
4003740 4141 4141 4141 4141 4141 4141 0000 0000
4023640 0000 0000 0000 0000 0000 0000 4141 4141
4023660 4141 4141 4141 4141 4141 4141 4141 4141
4023740 4141 4141 4141 4141 4141 4141 0000 0000
4102140 4141 4141 4141 4141 4141 4141 4141 4141
4102300 4141 4141 022c 6401 4141 4141 4141 4141
4102320 4141 4141 4141 4141 4141 4141 4141 4141
4102440 4141 4141 4141 4141 4141 4141 022c 6401
4102460 4141 4141 4141 4141 4141 4141 4141 4141
4102620 4141 4141 022c 6401 4141 4141 4141 4141
4102640 4141 4141 4141 4141 4141 4141 4141 4141
4102760 4141 4141 4141 4141 4141 4141 022c 6401
4103000 4141 4141 4141 4141 4141 4141 4141 4141
4103140 4141 4141 022c 6401 4141 4141 4141 4141
4103160 4141 4141 4141 4141 4141 4141 4141 4141
4103300 4141 4141 4141 4141 4141 4141 022c 6401
4103320 4141 4141 4141 4141 4141 4141 4141 4141
4103460 4141 4141 022c 6401 4141 4141 4141 4141
4103500 4141 4141 4141 4141 4141 4141 4141 4141
4103620 4141 4141 4141 4141 4141 4141 022c 6401
4103640 4141 4141 4141 4141 4141 4141 4141 4141
4104000 4141 4141 022c 6401 4141 4141 4141 4141
4104020 4141 4141 4141 4141 4141 4141 4141 4141
4104140 4141 4141 4141 4141 4141 4141 022c 6401
4104160 4141 4141 4141 4141 4141 4141 4141 4141
4104320 4141 4141 022c 6401 4141 4141 4141 4141
4104340 4141 4141 4141 4141 4141 4141 4141 4141
4104460 4141 4141 4141 4141 4141 4141 022c 6401
4104500 4141 4141 4141 4141 4141 4141 4141 4141
4104640 4141 4141 022c 6401 4141 4141 4141 4141
4104660 4141 4141 4141 4141 4141 4141 4141 4141
4105000 4141 4141 4141 4141 4141 4141 022c 6401
4105020 4141 4141 4141 4141 4141 4141 4141 4141
4105160 4141 4141 022c 6401 4141 4141 4141 4141
4105200 4141 4141 4141 4141 4141 4141 4141 4141
4105320 4141 4141 4141 4141 4141 4141 022c 6401
4105340 4141 4141 4141 4141 4141 4141 4141 4141
4105500 4141 4141 022c 6401 4141 4141 4141 4141
4105520 4141 4141 4141 4141 4141 4141 4141 4141
...
...
...


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

windows environment

A reader, January 11, 2018 - 5:12 am UTC

how to test same situation in windows 7 or windows server os?
Connor McDonald
January 12, 2018 - 12:08 am UTC

This *was* done under Windows.

I'm just using some unix commands from here

http://unxutils.sourceforge.net/

Possibilities...

J. Laurindo Chiappa, January 11, 2018 - 5:30 pm UTC

As far as I know, Windows have no native binary dump commands : you can write one in Powershell (see http://www.itprotoday.com/management-mobility/get-hex-dumps-files-powershell ), or you can download and use some binary file editor ( http://frhed.sourceforge.net/en/ is a good one), or you can download some unix-like package of utils : http://gnuwin32.sourceforge.net/ and https://sourceforge.net/projects/gnuwin64/ are know ones, https://sourceforge.net/projects/unxutils/ is yet another, and Microsoft have one for Windows 7 in https://www.microsoft.com/en-us/download/details.aspx?id=2391 Another very good/complete package is CygWin, see https://www.cygwin.com/ ...

Regards,

J. Laurindo Chiappa
Connor McDonald
January 12, 2018 - 12:11 am UTC

nice input
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database