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

Nice experiment - but it does rather assume, does it not, that the updates the oracle processes make are the type that will cause updates to files using routines that update the files date/times.

I'm none too sure whether this is the case.

It may well be that the date/time are updated just when the code of the specific Oracle OS platform combination agree it should happen. You need an expert in each platform to describe this one further I would guess.

Regards

Kevin - Also still learning !!

--
Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich United
Kingdom)
                        <Kevin_A_Lewis_at_Hotmail.com>

The views expressed herein by the author of this document
are not necessarily those of BOCM PAULS Ltd.
"Frank Hubeny" <fhubeny_at_ntsource.com> wrote in message
news:3AC438CB.D420AE81_at_ntsource.com...


> 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

>