Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partitions of table read only

Re: Partitions of table read only

From: Richard Foote <>
Date: Thu, 19 Jun 2003 08:28:06 -0700
Message-ID: <>

> Jack, maybe this has been covered. I seem to recall from the B&R module
> (knew it would prove useful sometime) that after you make a tablespace
> read-only that you should take a backup. Recovering a database with
> tablespaces that were read-write when backed up but are read-only now
> requires an extra step or two (something I never like in a recovery).

Hi Dennis

Backing up the tablespace files (and lets not forget the control file) is certainly not a bad idea.

Something else that many don't consider is to select from all objects within the tablespace with full scans *before* making the tablespace read only. This has the effect of performing all the necessary block cleanouts (ie. for all the blocks written to disk before they could be committed and cleaned out in memory) while Oracle still can.

If the tablespace is made read only and some poor blocks haven't been cleaned out, upon reading the block Oracle has no choice but to go to the rollback/undo segments in it's attempt to confirm the consistency of the block. However upon confirming that indeed the transaction is long gone and block cleanout can take place with the "latest possible SCN", it can't actually perform the necessary block changes because, you guessed it, the tablespace is currently read only.

This means that the overhead of checking for consistency but failing to actually perform the block cleanout continues on and on and on ...

Hence the suggestion to guarantee block cleanout while the tablespace is in a position to do so (in read/write mode).


Richard Foote

Please see the official ORACLE-L FAQ:
Author: Richard Foote

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Jun 19 2003 - 10:28:06 CDT

Original text of this message