Re: New bie Information of redo and datafile

From: Dennis Williams <>
Date: Fri, 4 Apr 2008 12:31:26 -0500
Message-ID: <>


Since I don't see where anyone replied to your questions, I'll take a crack at it.

  1. I'm not sure what question you are really trying to ask. At a high level, Oracle writes enough information to the redo log that it can repeat the change if needed for recovery purposes. If you really want to know more specifics, learn the LogMiner, since a lot of the redo log information is made visible with this tool.

Oracle doesn't directly write to a data file. It first writes to a data block in memory. If this is an update operation and the block isn't already in memory, Oracle will have to retrieve it from disk first. If it is an insert, Oracle will check the free list first and create a new block if the free list is empty. Many people have the misconception that Oracle immediately writes the data block back to disk. Since you didn't ask that question, I'll leave it for your further study :-)

2. In Oracle, a tablespace may have more than one data file. So you measure the free space at the tablespace level, not the data file level. When you create a datafile, you specify the tablespace that datafile is assigned to. Oracle measures free space in the DBA_FREE_SPACE view, which lists free space by tablespace. As a test, add a data file, checking the free space before and after and you'll see an immediate increase.

I hope this addresses your questions.
Consider reading the Oracle Concepts Manual, which addresses most of these concepts. It isn't large, and is clearly written, easy to understand, and best of all FREE!

Dennis Williams

On 4/3/08, nilesh kumar <> wrote:
> Hello All,
> Good Morning!,
> Hope every one is doing good.
> I have a very simple two questions:-
> 1. If I issue a sql statement to insert value or delete value or modify
> the value in the table. What exact information goes in the redolog file and
> datafile. I know that redo entries goes in the redolog file, but I need to
> know what exactly get stored in the redolog file and datafile.
> 2. Suppose a dba gets a pager that datafile threshold has reached 90%
> and he goes and add a datafile . How does oracle comes to know that after
> the current datafile is full it needs to store the data in the new datafile
> added.
> Thanks
> Nilesh

Received on Fri Apr 04 2008 - 12:31:26 CDT

Original text of this message