We're here to help!

Partitioning the Event Streaming Audit Table for Maintenance of Old Records

Follow

Read our docs This article explores, by means of an example, using range partitioning on the auditing option (DATE_RANGE) as one of the ways to purge old event streaming data.


Problem Details

Problem Statement

One of the most complex issues in Data Warehousing is how to detect changes on your source system. Most data warehouses do a complete dump and load of all the data during the nightly load. With Dbvisit Replicate and the event streaming functionality, only data that has been changed can be loaded real time into the Data Warehouse. This allows for reduced processing and real time updates of the Data Warehouse. The event streaming can also be used to capture auditing information for analysis purpose. However, these table will naturally grow very quickly and the challenge then becomes how to purge the data after the ETL process has used it. One solution is to delete each row after it has been processed. However, this solution might not fit all requirements and the data may need to be kept for a longer duration. It is this latter scenario that we will explore in this article and enable partitioning on the Target table containing the Event Streaming data. In Oracle 12cR2 it is possible to partition a non-partitioned table in a single step with the ALTER TABLE MODIFY command and can be made completely online by adding the ONLINE keyword. This example will detail the pre-12.2 method of using DBMS_REDEFINITION. 

Environment Details

Using the steps detailed in the Article: https://support.dbvisit.com/hc/en-us/articles/115001689214 we have enabled event streaming with auditing on the SCOTT.EMP schema.  

Replication version :  2.9.02

Oracle Version : 11.2.0.4

The following steps take the Target Table created by Dbvisit Replicate and Partition Online it without impacting the existing replication. 

Steps Performed

Step 1 :

Using the steps detailed in the Article: https://support.dbvisit.com/hc/en-us/articles/115001689214 we have enabled event streaming with auditing on the SCOTT.EMP schema. This has produced a Target Table with the following columns.

SOURCE : SCOTT.EMP


Name					   Null?    Type
------------------------ -------- ----------------------------
EMPNO					   NOT NULL NUMBER(4)
ENAME						    VARCHAR2(10)
JOB						    VARCHAR2(9)
MGR						    NUMBER(4)
HIREDATE					    DATE
SAL						    NUMBER(7,2)
COMM						    NUMBER(7,2)
DEPTNO 					    	    NUMBER(2)

TARGET : SCOTT.EMP


 Name					   Null?    Type
 ------------------------- -------- ----------------------------
 OLDEMPNO					    NUMBER(4)
 NEWEMPNO					    NUMBER(4)
 OLDENAME					    VARCHAR2(10)
 NEWENAME					    VARCHAR2(10)
 OLDJOB 					    VARCHAR2(9)
 NEWJOB 					    VARCHAR2(9)
 OLDMGR 					    NUMBER(4)
 NEWMGR 					    NUMBER(4)
 OLDHIREDATE					    DATE
 NEWHIREDATE					    DATE
 OLDSAL 					    NUMBER(7,2)
 NEWSAL 					    NUMBER(7,2)
 OLDCOMM					    NUMBER(7,2)
 NEWCOMM					    NUMBER(7,2)
 OLDDEPTNO					    NUMBER(2)
 NEWDEPTNO					    NUMBER(2)
 OPERATION					    CHAR(1)
 TRANSACTION_ID 				    VARCHAR2(30)
 DATE_CHANGE					    DATE
 DATE_COMMIT					    DATE
 SID						    NUMBER
 SERIAL 					    NUMBER
 CURR_USER					    VARCHAR2(40)
 LOGON_USER					    VARCHAR2(40)
 CLIENT_INFO					    VARCHAR2(1000)
 OS_USER					    VARCHAR2(1000)
 MACHINE					    VARCHAR2(1000)
 OS_TERM					    VARCHAR2(1000)
 OS_PROC_ID					    VARCHAR2(1000)
 OS_PROG					    VARCHAR2(1000)
 SCN						    NUMBER
 TRANSACTION_NAME				    VARCHAR2(256)

Run a few updates and inserts to create change in the EMP table that will be replicated to the Target table. Create a Partitioned Table with the Same Columns as SCOTT.EMP on the Target. This will be the interim table used by the dbms_redefinition package.

CREATE TABLE "SCOTT"."EMP_PART" 
   ( "OLDEMPNO" NUMBER(4,0), 
	"NEWEMPNO" NUMBER(4,0), 
	"OLDENAME" VARCHAR2(10 BYTE), 
	"NEWENAME" VARCHAR2(10 BYTE), 
	"OLDJOB" VARCHAR2(9 BYTE), 
	"NEWJOB" VARCHAR2(9 BYTE), 
	"OLDMGR" NUMBER(4,0), 
	"NEWMGR" NUMBER(4,0), 
	"OLDHIREDATE" DATE, 
	"NEWHIREDATE" DATE, 
	"OLDSAL" NUMBER(7,2), 
	"NEWSAL" NUMBER(7,2), 
	"OLDCOMM" NUMBER(7,2), 
	"NEWCOMM" NUMBER(7,2), 
	"OLDDEPTNO" NUMBER(2,0), 
	"NEWDEPTNO" NUMBER(2,0), 
	"OPERATION" CHAR(1 BYTE), 
	"TRANSACTION_ID" VARCHAR2(30 BYTE), 
	"DATE_CHANGE" DATE, 
	"DATE_COMMIT" DATE, 
	"SID" NUMBER, 
	"SERIAL" NUMBER, 
	"CURR_USER" VARCHAR2(40 BYTE), 
	"LOGON_USER" VARCHAR2(40 BYTE), 
	"CLIENT_INFO" VARCHAR2(1000 BYTE), 
	"OS_USER" VARCHAR2(1000 BYTE), 
	"MACHINE" VARCHAR2(1000 BYTE), 
	"OS_TERM" VARCHAR2(1000 BYTE), 
	"OS_PROC_ID" VARCHAR2(1000 BYTE), 
	"OS_PROG" VARCHAR2(1000 BYTE), 
	"SCN" NUMBER, 
	"TRANSACTION_NAME" VARCHAR2(256 BYTE)
   ) 
   PARTITION BY RANGE (DATE_CHANGE)
   INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
   (
   PARTITION EMP_PART_1 values less than (to_date('01/09/2017','DD/MM/YYYY')),
   partition EMP_PART_2 values less than (to_date('01/10/2017','DD/MM/YYYY'))
   );  
    
Table created.

Step 2 :

Confirm that the EMP table can be partitioned. As this table does not have a primary key the option DBMS_REDEFINITION.CONS_USE_ROWID must be used.

begin
dbms_redefinition.can_redef_table
('SCOTT','EMP',options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/

PL/SQL procedure successfully completed.

There are no objections against the online redefinition of the table here – else an error message would appear.

Step 3

Start the Redefinition Process. The next step will basically insert all the rows from the original table into the interim table, while DML during that period is recorded:
begin
dbms_redefinition.start_redef_table
('SCOTT','EMP','EMP_PART',options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end; 
/

PL/SQL procedure successfully completed.

Step 4

Next step is now to add the dependent objects/privileges to the interim table.

vari num_errors number
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
 (uname=>'SCOTT',
 orig_table=>'EMP',
 int_table=>'EMP_PART',
 num_errors=>:num_errors);
END;
/

PL/SQL procedure successfully completed.
print num_errors

NUM_ERRORS
----------
	 0

Step 5

There was no problem with this step. Until now the original table is still an ordinary heap table – only the interim table is partitioned:


select table_name from user_part_tables;
TABLE_NAME
------------------------------
EMP_PART

In the last step, the two tables change their names and the recorded DML that occured in the meantime gets used for actualization:


SQL> begin
dbms_redefinition.finish_redef_table
 (uname=>'SCOTT',
 orig_table=>'EMP',
 int_table=>'EMP_PART');
end;
/  
PL/SQL procedure successfully completed.

Step 6

Lets check now the partitioned table and the number of rows.

SQL> select table_name from user_part_tables;

TABLE_NAME
------------------------------
EMP
SQL> exec dbms_stats.gather_table_stats ('SCOTT','EMP');

PL/SQL procedure successfully completed.

SQL> select table_name, partition_name, num_rows from user_tab_partitions where table_name = 'EMP';
TABLE_NAME		       PARTITION_NAME			NUM_ROWS
------------------------------ ------------------------------ ----------
EMP			       EMP_PART_1			       0
EMP			       EMP_PART_2			      14

Step 7

Let run some updates/inserts into the Source now that we are in a different month, to ensure that the interval partitioning is working with Dbvisit replicate.

SQL> exec dbms_stats.gather_table_stats ('SCOTT','EMP');

PL/SQL procedure successfully completed.

SQL> select table_name, partition_name, num_rows from dba_tab_partitions where table_name = 'EMP';

TABLE_NAME		       PARTITION_NAME			NUM_ROWS
------------------------------ ------------------------------ ----------
EMP			       EMP_PART_1			       0
EMP			       EMP_PART_2			      14
EMP			       SYS_P41				      14

Step 8

The purging of records now from the EMP table in the Target can now be by Partition.

Have more questions? Submit a request

Comments