Re: Partitions of table read only

[Arup Nanda]

Title: Partitions of table read only

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 ----- From: Jack van Zanen To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 18, 2003 10:49 AM Subject: Partitions of table read only 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: [EMAIL PROTECTED] Visit our web site at http://www.quantsystems.nl/