Managing Historical and Current Data

[Michael42]

Hello,

In an Oracle 9i R2 database on Solaris 8 I am investigating some architecure changes. I have a tablespace that holds one table parititioned by year. My goal is to place older data (2000-2005) in a read-only tablespace.

I do not want to split the table partitions over multiple tablespaces.

I was hoping to have two tablespaces, functionally CURRENT (regular read-write) and HISTORICAL (read-only). My thought was to use a view with a corresponding PUBLIC synonym so user's SQL was transparent to which tablespace they are accessing. I think you can only do SELECT's on views though (i.e. no INSERT, UPDATE, DELETE's etc.). I may be wrong.

What can you recommend?

Thanks,

Michael42

 

[SantaMufasa]

One test is worth 100 expert opinions

I recommend that you try it, and if it works, it works. Then, let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Michael,

there is no need for a view, if you partition the table correctly.

In
Oracle9i Database Administrator's Guide
Release 2 (9.2)
Part Number A96521-01 it says

Partitions and subpartitions of a table or index all share the same logical attributes. For example, all partitions (or subpartitions) in a table share the same column and constraint definitions, and all partitions (or subpartitions) of an index share the same index options. They can, however, have different physical attributes (such as TABLESPACE).

I would draw your attention to the explicit statement that a table can have different tablespaces. This means that your views are not needed.

In the following sample (from the same document) the table is partitioned into four different tablespaces.

CREATE TABLE sales 
     ( invoice_no NUMBER,
       sale_year  INT NOT NULL,
       sale_month INT NOT NULL,
       sale_day   INT NOT NULL )
   STORAGE (INITIAL 100K NEXT 50K) LOGGING 
   PARTITION BY RANGE ( sale_year, sale_month, sale_day)
     ( PARTITION sales_q1 VALUES LESS THAN ( 1999, 04, 01 )
        TABLESPACE tsa STORAGE (INITIAL 20K, NEXT 10K),
       PARTITION sales_q2 VALUES LESS THAN ( 1999, 07, 01 )
        TABLESPACE tsb,
       PARTITION sales_q3 VALUES LESS THAN ( 1999, 10, 01 )
        TABLESPACE tsc,
       PARTITION sales q4 VALUES LESS THAN ( 2000, 01, 01 )
        TABLESPACE tsd)
   ENABLE ROW MOVEMENT;

SantaMufasa has already advised in other threads about moving tables, rebuilding indexes etc. and I recommend this information to you.

I hope the above helps.

Regards

Tharg

Grinding away at things Oracular