Re: Bookish Questions need Ideas

• Post for FREE via your newsreader at post.usenet.com ****

Arijit, Evey question you have asked can be answered by reading the Oracle Concepts manual. See comments within your post.

Arijit Chatterjee wrote:

> Thanks Sir,
> Thanks for your reply.I got your points.But one thing is there.
> I am explaining through an example
> =========================================
> Table1
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |1 |A |
> |----------|--------------|
> |2 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'

> Step1: Now I am updating the table
> ------------------------------------
> Update table Table1 set col1=col1+1
> ------------------------------------

> So before Commit
> ****************

> Rollback Segment Stores
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |1 |A |
> |----------|--------------|
> |2 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
> Database Table Store
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |2 |A |
> |----------|--------------|
> |3 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
> Whenever any user asking for any record
> he/she is getting for Rollback segment.

> After Commit
> ****************

> Database Table Remains
> But Users are now getting data from
> Database table only.
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |2 |A |
> |----------|--------------|
> |3 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'

Not true. If a user query started before the committing transaction when it comes to the committed row it will see that the data is newer than the query start time. Since Oracle returns all data within a statement (query) at one point in time Oracle will read the RBS segment for the data as it was at the start of the query.

> But
> Rollback Segment remain storing
> these records or releasing this.
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |1 |A |
> |----------|--------------|
> |2 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
> ==================================
> As per above scenario
> Q1.If rollback segment releasing then fine if not then why it is
> storing it because we can take the information from Redo Logs
> only.And user and do any undo operation.

Oracle does not read the online (or archived) redo log for rollback (undo) information. Redo logs are for forward recovery operations only.

> ==================================
> ----------------------------------
> RedoLog is storing
> ==================
> New information + Date

> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |2 |A |
> |----------|--------------|
> |3 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
> as well as previous information
> with the date because I can take
> retrive my previous data through
> Redo Logs.
> ====================
> Old Information + Date
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |1 |A |
> |----------|--------------|
> |2 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
> =========================================
> Q2.Why Oracle immediately updates tables in original database
> and keep the data in rollback segments.As a little programmer's
> point of view if it store the data temporarily in Rollback
> segment and after "commiting" it updates that orginal
> database what is the low sound in this concept.Because oracle
> is used for robust data storage.why it is making the prototype
> of huge data in Rollback segments.
> =========================================

Oracle does not necessarily immediately update tables/indexes for committed changes. The changes are immediately written to the redo logs for recovery purposes but database data file wites are done on a lazy write basis. After all additional changes may be made to the same table/index blocks so why write twice where one physical IO could cover multiple block updates.

> On the above expample if I am wrong any where
> please guide me.Thanks once again for your
> great guidances.
> Regards
> Arijit Chatterjee

Look up consistent read in the Oracle documentation. There is an entire chapter dedicated to Rollback segment operation in the Concepts manual.

HTH -- Mark D Powell --

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=