We have a fast-growing table that requires keeping only two months' worth of data.
We plan to take advantage of the interval partition feature in Oracle 11g, in which
Oracle automatically creates an interval partition as data for that partition is inserted.
Therefore, we will not worry about data load failure situation due to the possibility of
forgetting to add new partitions by DBA.
In our environment, this table will also be in Oracle GoldenGate replication configuration.
So I try to do tests to confirm whether the GG DDL replication supports the following two
operations:
1. Create interval partition
2. Drop partition
I used a
test GG environment, in which a two-way GG replication with DDL replication enabled is set up
between two databases:
WESTDB and
EASTDB; and the tables are in different schemas
called
west and
east respectively.
The following are the testing steps:
1. create a table named interval_tab in the source db
denis@WESTDB>> CREATE TABLE west.interval_tab
2 ( prod_id NUMBER(6)
3 , cust_id NUMBER
4 , time_id DATE
5 )
6 PARTITION BY RANGE (time_id)
7 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
8 ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2013', 'MM-DD-YYYY')),
9 PARTITION p1 VALUES LESS THAN (TO_DATE('2-1-2013', 'MM-DD-YYYY')),
10 PARTITION p2 VALUES LESS THAN (TO_DATE('3-1-2013', 'MM-DD-YYYY')),
11 PARTITION p3 VALUES LESS THAN (TO_DATE('4-1-2013', 'MM-DD-YYYY')) );
Table created.
Verify it is replicated in target:
denis@EASTDB>> desc east.interval_tab;
Name Null? Type
----------------------------------------------------------------------------- -------- ---------------------
PROD_ID NUMBER(6)
CUST_ID NUMBER
TIME_ID DATE
2. create indexes and contraint
At source:
denis@WESTDB>> create unique index west.interval_tab_pk on west.interval_tab(prod_id, time_id) local;
Index created.
denis@WESTDB>> create index west.interval_tab_ix1 on west.interval_tab(cust_id) local;
Index created.
denis@WESTDB>> alter table west.interval_tab add constraint interval_tab_pk primary key (prod_id, time_id) using index;
Table altered.
Verify index and constraints creation are replicated at target:
denis@EASTDB>> @tabix
Enter value for tabowner: east
Enter value for tabname: interval_tab
TABLE_NAME INDEX_NAME COLUMN_NAME COL_POS UNIQUENES
-------------------- -------------------- -------------------- ---------- ---------
INTERVAL_TAB INTERVAL_TAB_IX1 CUST_ID 1 NONUNIQUE
INTERVAL_TAB INTERVAL_TAB_PK PROD_ID 1 UNIQUE
INTERVAL_TAB TIME_ID 2 UNIQUE
denis@EASTDB>> select owner,table_name, constraint_name from dba_constraints where table_name='INTERVAL_TAB';
OWNER TABLE_NAME CONSTRAINT_NAME
--------------- -------------------- --------------------
EAST INTERVAL_TAB INTERVAL_TAB_PK
3. Insert data at source that will cause new interval partition created automatically
Before the insert, check the current partitions:
denis@WESTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------- -------------------- -------------------- ---------------------------------------------
WEST INTERVAL_TAB P0 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P1 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P3 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
Perform the following insert at source and check the partitions:
insert into interval_tab values (1004,1, sysdate);
denis@WESTDB>> insert into west.interval_tab values (1004,1, sysdate);
1 row created.
denis@WESTDB>> commit;
Commit complete.
denis@WESTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ -------------------- -------------------- ---------------------------------------------
WEST INTERVAL_TAB P0 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P1 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P3 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB SYS_P81 TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
5 rows selected.
4. Check partitions at target database
denis@EASTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------------- -------------------- -------------------- ---------------------------------------------
EAST INTERVAL_TAB P0 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB P1 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB P2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB P3 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB SYS_P123 TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
5 rows selected.
denis@EASTDB>> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='INTERVAL_TAB_IX1';
INDEX_OWNER INDEX_NAME PARTITION_NAME
------------------------------ -------------------- --------------------
EAST INTERVAL_TAB_IX1 P0
EAST P1
EAST P2
EAST P3
EAST SYS_P123
5 rows selected.
5. Drop a partition at source
alter table west.interval_table drop partition P0;
denis@WESTDB>> alter table west.interval_tab drop partition P0;
Table altered.
Verified, it is replicable
denis@EASTDB>> /
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ -------------------- -------------------- ---------------------------------------------
EAST INTERVAL_TAB P1 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB P2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB P3 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB SYS_P123 TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
4 rows selected.
In conclusion, the tests confirmed that GG DDL replication support the interval partition automatic creation and drop partition operations.
It is worth noting that when we use DBMS_METADATA package to get the definitions of interval partitioned table and its associated
local indexes, we won't see the interval partitions that are created automatically. To verify the existance of the partitions, we shall use
dba_tab_partitions and dba_ind_partitions views.
P.S.
Note from Oracle docs:
You cannot explicitly add a partition to an interval-partitioned table unless you first lock the partition,
which triggers the creation of the partition. The database automatically creates a partition for an interval when data for that interval is inserted. In general, you only need to explicitly create interval partitions for a partition exchange load scenario.
update Apr 9, 2014 -
I may have misunderstood the relationship of DDL replication and interval partition creation. DDL replication may have no relationship with interval partition creation at all. Even without DDL replication enabled. Interval partition will be created due to the data insertion DML statement.
