Usage Scenario - Transporting and Attaching Partitions for Data Warehousing

Goal: Employ trasportable tablespace feature to Load monthly sales data into a datawarehouse

Ref: This usage scenario is taken from Oracle9i Database Administrator's Guide

1. Suppose every month you would like to load one month's worth of data into
   the data warehouse. There is a large fact table in the data warehouse 
   called sales, which has the following columns:

CREATE TABLE sales (invoice_no NUMBER,
  sale_year  INT NOT NULL,
  sale_month INT NOT NULL,
  sale_day   INT NOT NULL)
  PARTITION BY RANGE (sale_year, sale_month, sale_day)
    (partition jan98 VALUES LESS THAN (1998, 2, 1),
     partition feb98 VALUES LESS THAN (1998, 3, 1),
     partition mar98 VALUES LESS THAN (1998, 4, 1),
     partition apr98 VALUES LESS THAN (1998, 5, 1),
     partition may98 VALUES LESS THAN (1998, 6, 1),
     partition jun98 VALUES LESS THAN (1998, 7, 1));

2. You create a local nonprefixed index:
 CREATE INDEX sales_index ON sales(invoice_no) LOCAL;

3. Initially, all partitions are empty, and are in the same default tablespace.
 Each month, you want to create one partition and attach it to the
 partitioned sales table.

4. Suppose it is July 1998, and you would like to load the July sales data into
 the partitioned table.

5. In a staging database, you create a new tablespace, ts_jul.  You also create
 a table, jul_sales, in that tablespace with exactly the same column types as
 the sales table. You can create the table jul_sales using the
 CREATE TABLE ... AS SELECT statement.

6. After creating and populating jul_sales,
 you can also create an index, jul_sale_index, for the table, indexing the same
 column as the local index in the sales table.

7. After building the index, transport the tablespace ts_jul to the data warehouse.

8. In the data warehouse, add a partition to the sales table for the July sales data.
 This also creates another partition for the local nonprefixed index:

 ALTER TABLE sales ADD PARTITION jul98 VALUES LESS THAN (1998, 8, 1);

9. Attach the transported table jul_sales to the table sales by exchanging it
 with the new partition:

 ALTER TABLE sales EXCHANGE PARTITION jul98 WITH TABLE jul_sales
  INCLUDING INDEXES
  WITHOUT VALIDATION;

 Notes: This statement places the July sales data into the new partition jul98,
 attaching the new data to the partitioned table. This statement also converts
 the index jul_sale_index into a partition of the local index for the sales table.
 This statement should return immediately, because it only operates on the structural
 information and it simply switches database pointers.
 If you know that the data in the new partition does not overlap with data in
 previous partitions, you are advised to specify the WITHOUT VALIDATION option.
 Otherwise, the statement goes through all the new data in the new partition in
 an attempt to validate the range of that partition.

 If all partitions of the sales table came from the same staging database
 (the staging database is never destroyed), the exchange statement always succeeds.
 In general, however, if data in a partitioned table comes from different databases,
 it's possible that the exchange operation may fail. For example, if the jan98 partition
 of sales did not come from the same staging database, the above exchange operation
 can fail, returning the following error:

 ORA-19728: data object number conflict between table JUL_SALES and partition
 JAN98 in table SALES

 To resolve this conflict, move the offending partition by issuing the following statement:

 ALTER TABLE sales MOVE PARTITION jan98;

 Then retry the exchange operation.

10. After the exchange succeeds, you can safely drop jul_sales and jul_sale_index
  (both are now empty). Thus you have successfully loaded the July sales data
  into your data warehouse.