Re: Partitions of table read only

Jack

I am also using almost similar kind of Partition Management where I am keeping only six months of data. In mine configuration , I have the following requirement Daily partition created in the evening and dropping the 7day old partition.e.g  I am creating Wednesday Partition on Sunday so as to have the 3days future partition available in advance. Weekly Partition Created every thursday and dropping the 15 week old partition. Same like Daily partition, I am creating 2 week Future partition in advance so as to be ready for any error correction if the automated job for Partition creations failed Monthly partition created on the First of each month and dropping 6 month old partition.

I had written a package that take care of all of the Daily/Weekly/Monthly partition. Note I am using Local Indexes and had CLOB columns in the Tables. Each monthly partition is around 25 Gig and Daily partition is around 3 Gig.  The package also take care of Tablespace creation for Data and Index segments as well as dropping the Tablespace for partitioned table and indexes. I am calling the Package thru Cron Job and getting the Email for success as well as failure with full syntax.

I had written the package around two year back  with Oracle 817 but working fine and it might need small improvement with Oracle 9i  but can be done easily. Reply back to me on mine direct email Id and then I will send you the  code as I don't think we can attach any message to this list

Thanks

Indy Johal
Manager, Database Administration
PR Newswire
[EMAIL PROTECTED]
http://www.prnewswire.com

(201) 946-5687 [W]
(201) 400-3960 [M]

"We tell your story to the world."

"Arup Nanda" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/18/03 02:59 PM
Please respond to ORACLE-L

       
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: 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/ Received on Wed Jun 18 2003 - 15:30:42 CDT