Database Reference
In-Depth Information
REFERENCE_INSTANCE, COMPOSITE_INSTANCE_FAULT,
COMPOSITE_SENSOR_VALUE, COMPONENT_INSTANCE,
REJECTED_MESSAGE, REJECTED_MSG_NATIVE_PAYLOAD,
INSTANCE_PAYLOAD
, and
COMPOSITE_INSTANCE_ASSOC
, as they are part of
the same component.
Let's start with the
INSTANCE_PAYLOAD
table. Create a completely new table
that is partitioned by the year range on the
CREATED_TIME
column:
CREATE TABLE instance_payload_temp PARTITION BY RANGE (created_time) (
PARTITION P01_2011 VALUES LESS THAN ('01-JAN-12 12.00.00.000000 AM') TABLESPACE soa_ts_2011,
PARTITION P01_2012 VALUES LESS THAN ('01-JAN-13 12.00.00.000000 AM') TABLESPACE soa_ts_2012
)
NOLOGGING
AS
SELECT * FROM instance_payload;
As shown in the preceding command, the entire data is selected from the base
table
INSTANCE_PAYLOAD
and copied into our newly created, partitioned table
called
INSTANCE_PAYLOAD_TEMP
.
Now rename the existing product table, maintaining it temporarily for backup
purposes:
RENAME instance_payload TO instance_payload_delete;
Then rename the temp table to replace the original table:
RENAME instance_payload_temp TO instance_payload;
Finally, drop the original table if all goes well and it is no longer needed:
DROP TABLE instance_payload_temp;
Now repeat these same instructions for all tables within the component.
