Re: Commit -> write to data file immediately???

Although I don't know the answer to your question, I would suggest an experiment to add to the confusion.

Get a test database that no one else is using. (Mine happens to be on Win2000 Oracle 8.1.7.) Pick a tablespace and note the datafiles in the tablespace. Place command prompts on the directories with your datafiles and redo logs. Do a "dir" or "ls -l" to get the current time. Connect as sysdba. Now you are set up to perform the test.

(1) Wait a minute or so to make sure you are at a later time. Create a table in a tablespace where you have identified all the datafiles. (create table abc (a number) tablespace <name>;) Do a "dir" or "ls -l" again. Does the timestamp change on the redo logs or the datafiles holding the new table? (Mine doesn't.)

(2) Wait a minute or so make sure you are at a later time. Add a record or two to the new table and commit. (insert into abc values (100);commit;) Does the timestamp change on the redo logs or the datafiles holding the new data? (Mine doesn't.)

(3) Switch logfiles (alter system switch logfile;). Does the timestamp change on the redo logs or the datafile? (If your experience is like mine, this is the first time I notice a change in the timestamp of the two redo logs affected by the switch even though commits have occurred in the previous two steps. There is no change to the data or control files.)

(4) Switch logfiles again to see if you can generate some change in the datafiles. Does the timestamp change on the redo logs or the datafiles? (If your experience is like mine, now all the datafiles and control files have a new timestamp.)

I've read a lot of the theoreies, like those that others have posted, trying to explain what should happen. I would have expected, unless I misunderstood, that the redo logs should show a change after the table creation and commit in steps 1 and 2. They did not. I would have expected that switching logfiles the first time would have forced at least the data to be written to the tablespace in which I created the new table. It did not.

It makes me wonder if it is not possible to create an experiment where I could crash Oracle and lose committed data. I tried "shutdown abort" after committing another record to the table, but data got written to disk right after the shutdown abort command. (Although I am tempted, it is too late in the day to pull the plug on the box after a commit.)

Frank Hubeny

David wrote:

> Hi all,
>
> When user issues "commit", does Oracle writes all changed data (of
> this user) to data file immediately? Or Oracle writes to data file only
> when database buffer is full or during checkpoint? IF Oracle not writing
> to data file immediately, and at that time, Oracle is crashed and log
> files (all) are corrupted, how does Oracle to recover the "committed"
> data?
>
> Thanks,
> David
  Received on Fri Mar 30 2001 - 01:42:03 CST