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: Arup Nanda <>
Date: Wed, 18 Jun 2003 11:18:54 -0700
Message-ID: <>


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.


Arup Nanda
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

  Sent: Wednesday, June 18, 2003 10:49
  Subject: Partitions of table read

  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? <FONT   face=Arial size=2>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 <FONT face=Arial color=#0000ff

  size=2>Fax: +31 (0) 251 - 268 269<FONT face=Arial color=#000080 
  size=2>  <FONT face=Arial color=#000080 
  size=2>E-mail: <A 
  href="mailto:[EMAIL PROTECTED]"><FONT face=Arial color=#0000ff 

  Visit our web site at
  <FONT face=Arial
  color=#0000ff size=2>    Received on Wed Jun 18 2003 - 13:18:54 CDT

Original text of this message