Adding Partition Table to Streams

Steps for adding a Partition table to an existing single source Streams replication environment.

Source database (S): ORCL.ORACLEDB1.KRISH.COM
Target database (T): MYDEV.ORACLEDB2.KRISH.COM
Source & Target Schema: SCOTT
Table: SALES_PART

Note: Ensure that you have setup the streams for replication for the first time and created a streams administrator before executing following steps.

Steps for adding a Partitioned table:

1. Stop the capture process(S)
2. Setup logging & supplemental logging for the table (S)
3. Add capture rules on the table (S)
4. Add propagation rules on the table(S)
5. Set Instantiation of table from Source(S)
6. Export, Import of table(Data) from Source(S) to Target (T)
7. Add apply rules to the table(T)
8. Start the capture process(S)

1 Stop the capture process(S)

connect strmadmin/strmORCL@ORCL

SQL> BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'STREAMS_CAPTURE');
END;
/
2 Configure logging & supp. logging for the table (S)

connect strmadmin/strmORCL@ORCL

--Create Partition table if does not exist on the source database.

SQL> create table SCOTT.SALES_PART
(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 SALES_Q1 VALUES LESS THAN (1994, 04, 01) TABLESPACE TBS_USERDATA,
PARTITION SALES_Q2 VALUES LESS THAN (1994,07,01) TABLESPACE TBS_USERDATA)
/

alter table SCOTT.SALES_PART logging;
alter table SCOTT.SALES_PART add supplemental log data (primary key,unique,foreign key,all) columns;

select partition_name, logging from dba_tab_partitions where table_name='SALES_PART' and TABLE_OWNER='SCOTT';

3 Add capture rules on the table (S)
Source(ORCL):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmORCL@ORCL


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.SALES_PART',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

4 Add propagation rules on the table(S)

Source(ORCL):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmORCL@ORCL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.SALES_PART',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL',
inclusion_rule => true);
END;
/

5 Set Instantiation of table from Source (S)

--This script needs to be run as "strmadmin".

Source(ORCL):

connect strmadmin/strmORCL@ORCL

BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.SALES_PART');
END;
/

6 Export, import of table (Data) from Source(S) to Target (T)

Source(ORCL):


$exp parfile=exp_sales_part.par

#exp_sales_part.par
USERID=system@ORCL
FILE=exp_SALES_PART.dmp
LOG=exp_SALES_PART.log
ROWS=Y
GRANTS=Y
INDEXES=Y
TRIGGERS=N
CONSTRAINTS=Y
CONSISTENT=Y
STATISTICS=NONE
TABLES= SCOTT.SALES_PART

ftp the exp_SALES_PART.dmp file to target site

Target(MYDEV):

$imp parfile=imp_MYDEV_sales_part.par

#imp_MYDEV_ sales_part.par
USERID=system@MYDEV
FILE=exp_SALES_PART.dmp
LOG=imp_SALES_PART.log
ROWS=Y
IGNORE=Y
resumable=y
resumable_timeout=43200
commit=y
statistics=NONE
buffer=50000000
RECORDLENGTH=65535
STREAMS_INSTANTIATION=Y
FULL=Y

make sure that target table instantiated

select SOURCE_DATABASE,SOURCE_OBJECT_OWNER,SOURCE_OBJECT_NAME,
INSTANTIATION_SCN from dba_apply_instantiated_objects where SOURCE_OBJECT_NAME='SALES_PART';

7 Add apply rules to the table(T)

Target(MYDEV):

connect strmadmin/strmMYDEV@MYDEV


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.SALES_PART',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name =>
'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL',
inclusion_rule => true);
END;
/

-- grant all permissions on SALES_PART to streams administrator
conn / as sysdba

GRANT ALL ON SCOTT.SALES_PART to strmadmin;


8 Start the capture process(S)

connect strmadmin/strmORCL@ORCL

SQL> BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAMS_CAPTURE');
END;
/

Verification:

1. Add a partition

SQL> alter table SCOTT.SALES_PART add partition SALES_Q3
VALUES LESS THAN(1994,10,01) TABLESPACE TBS_USERDATA;

Check on the source and target databases.
-----------------------------------------
select TABLE_NAME,partition_name from dba_tab_partitions where table_name='SALES_PART' and TABLE_OWNER='SCOTT';

2. Drop partition
Alter table SCOTT.SALES_PART drop partition SALES_Q3;

3. Move partition to different tablespace
Alter table SCOTT.SALES_PART MOVE PARTITION SALES_Q2 TABLESPACE STREAMS01;

select TABLE_NAME,partition_name,tablespace_name from dba_tab_partitions where table_name='SALES_PART' and TABLE_OWNER='SCOTT';

4.Perform DML:
Insert into SCOTT.SALES_PART values (1,1994,02,05);
Insert into SCOTT.SALES_PART values (2,1994,05,10);
Commit;

DELETE FROM SCOTT.SALES_PART;
COMMIT;


5.Truncate Partition table:

Alter table SCOTT.SALES_PART TRUNCATE PARTITION SALES_Q1;
Alter table SCOTT.SALES_PART TRUNCATE PARTITION SALES_Q2;

6. SPLIT Table Partitions

Alter table SCOTT.SALES_PART SPLIT PARTITION SALES_Q2 AT(1994,06,01)
INTO (PARTITION SALES_Q2, PARTITION SALES_Q3);

select TABLE_NAME,partition_name,tablespace_name from dba_tab_partitions where table_name='SALES_PART' and TABLE_OWNER='SCOTT';

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ --------------------
SALES_PART SALES_Q1 TBS_USERDATA
SALES_PART SALES_Q2 STREAMS01
SALES_PART SALES_Q3 STREAMS01

7. Merge partition

Alter table SCOTT.SALES_PART MERGE PARTITIONS SALES_Q2, SALES_Q3 INTO PARTITION SALES_Q23;

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ -------------------
SALES_PART SALES_Q1 TBS_USERDATA
SALES_PART SALES_Q23 TBS_USERDATA

8. Rename Partition

Alter table SCOTT.SALES_PART RENAME PARTITION SALES_Q23 TO SALES_Q2;

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ --------------------
SALES_PART SALES_Q1 TBS_USERDATA
SALES_PART SALES_Q2 TBS_USERDATA

9. Create LOCAL index on SALES_PART

CREATE INDEX SCOTT.SALES_PART_IDX ON SCOTT.SALES_PART(INVOICE_NO,SALE_MONTH)
LOCAL
(PARTITION SALES_Q1 TABLESPACE TBS_USERIDX,
PARTITION SALES_Q2 TABLESPACE TBS_USERIDX);
select TABLE_NAME,INDEX_NAME,PARTITIONING_TYPE,LOCALITY,ALIGNMENT from dba_part_indexes where TABLE_NAME like 'SALES_PART';

TABLE_NAME INDEX_NAME PARTITI LOCALI ALIGNMENT
---------- ------------------------------ ------- ------ ------------
SALES_PART SALES_PART_IDX RANGE LOCAL NON_PREFIXED

select INDEX_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,STATUS,USER_STATS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME LIKE 'SALES_PART_IDX';

INDEX_NAME COM PARTITION_NAME HIGH_VALUE PARTITION_POSITION STATUS USE
------------------------------ --- ------------------------------ --------------- ------------------ -------- ---
SALES_PART_IDX NO SALES_Q1 1994, 04, 01 1 USABLE NO
SALES_PART_IDX NO SALES_Q2 1994, 07, 01 2 USABLE NO