RE: Partitions of table read only

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).

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] -----Original Message-----
Sent: Wednesday, June 18, 2003 1:59 PM
To: Multiple recipients of list ORACLE-L

Jack,  

It is possible to have some partitions of a table read only and some read write. Possible even if they are subpartitions. They are requird, say, in a DW environment, where the current quarter's data is read write but the rest are read only.  

You can backup the read only tablespace only once, and then <important> as long as you never make it read write </important> you can recover it.  

Since you are probably referring to a sort of archival system, you can follow an approach I am using here. Our requirement is to hold data online for three years, actually 12 quarters. So, in the beginning of a quarter, I make the oldest partition of the tables a table (alter table exchange partition) and make that tablespace read only. Then I "transport" the tablespace to an optical jukebox using export/transportable, and drop the tablespace. The tablespaces are named in a format with the year and quarter in their names, so they are always unique. When the time comes to use these older partitions, I simply plug them in and drop them after the rowrk is done. This makes the process transparent to the user, actually to the tools used by the user.  

HTH.   Arup Nanda

• Original Message ----- To: Multiple <mailto:[EMAIL PROTECTED]> recipients of list ORACLE-L Sent: Wednesday, June 18, 2003 10:49 AM

Hi,

I would like to know if it is possible and what the pitfalls are if I do the following.

Partition a large table into partitions based on date. Data is only entered and read and never altered, so I would like to move older partitions to read only tablespaces and possible read only devices so the backup will be made quicker.

Is it possible to have parttions of the same table spread across read only and read/write tablespaces?
Am I correct in assuming that once you backup a read only tablespace there is no need to backup the same again. (provided you don't make it read/write add data and make it read only again).?

Does anybody have a procedure already that automatically creates the new partitions let say every month?

TIA Jacob A. van Zanen
Oracle DBA
Quant Systems Europe b.v.
Tel : +31 (0) 251 - 268 268
Mobile: +31 (0) 6 51308813
Fax: +31 (0) 251 - 268 269
E-mail: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] Visit our web site at <http://www.quantsystems.nl/> http://www.quantsystems.nl/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
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).