Implementing Interval Partition with Enable Row Movement

Implementing Interval Partition with Enable Row Movement

In this article , we will learn how to implement interval partition with enable row movement.

Table Creation with interval partition (without interval Row Movement.

Create user 

Now create table with interval partition.

Now try to insert records into table and write null value in MIC_TAG_ID (interval partition Column).

You will get following error.

Which means on virtual column we can’t insert Null values .

This issue occurs because the column on which the partition condition was created is a virtual column created in IDR and the value is updated using SQL Expression. It is not a column originated from source.

On using virtual column, Applier first inserts physical columns and then updates virtual columns with the values calculated based on physical columns.

Thus, the partition column is inserted with NULL value first which invalidates the partition condition and results an Oracle error​.

In order resolve that issue the work around follows below steps.

Now insert values into same table with actual values and try to update those records 

Now check partitions

 

Select the records.

Now update the record.

 

 

Create table with default values  and row movement.

Now insert records with default values

Check data available on table.

Check the partitions on table.

Insert one more value into same table

Commit the records.

Check the partitions with same query.

Yes its creating new query

Now update one record with latest format.

Check records in a table.

Commit the records

Check partitions

When ever we updated new partition clause will be created.

 

 

 

 

 

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment