In-database archiving with partitioning on non-partitioned table without recreating the table.

Test do with jkosales table that is a copy using CTAS from sh.sales table.

[code language=”sql”]
Create table jkosales as select * from sales;
[/code]

Add the in-database archiving feature for the table

[code language=”sql”]
alter table jkosales row archival;
[/code]

Use dbms_redefinition to convert the table to partitioned table.

Creating interim table: The table will have two list partitions with 4 ranges subpartitions by year in each partitions

 

[code language=”sql”]
alter session set nls_date_format=’dd-mm-yyyy’;
Create table jkosales_int
(
PROD_ID NUMBER not null,
CUST_ID NUMBER not null,
TIME_ID DATE not null,
CHANNEL_ID NUMBER not null,
PROMO_ID NUMBER not null,
QUANTITY_SOLD NUMBER(10,2) not null,
AMOUNT_SOLD NUMBER(10,2)
)
partition by list (ORA_ARCHIVE_STATE)
subpartition by range (TIME_ID)
(
partition actives values (0)
(
subpartition pa_1999 values less than (’01-01-1999′),
subpartition pa_2000 values less than (’01-01-2000′),
subpartition pa_2001 values less than (’01-01-2001′),
subpartition pa_2002 values less than (’01-01-2002′)
),
partition archived values (1)
(
subpartition pr_1999 values less than (’01-01-1999′),
subpartition pr_2000 values less than (’01-01-2000′),
subpartition pr_2001 values less than (’01-01-2001′),
subpartition pr_2002 values less than (’01-01-2002′)
)
) enable row movement row archival;
[/code]

 

Start the redefinition phase.

Check if possible online with no primary key.

[code language=”sql”]
begin
dbms_redefinition.can_redef_table (uname => ‘SH’, tname=>’JKOSALES’,
options_flag=>dbms_redefinition.cons_use_rowid);
end;
/
[/code]

Start the redefinition…

[code language=”sql”]
begin
dbms_redefinition.start_redef_table (uname => ‘SH’,
orig_table=>’JKOSALES’,
int_table=>’JKOSALES_INT’,
options_flag=>dbms_redefinition.cons_use_rowid);
end;
/
PL/SQL procedure successfully completed.
[/code]
 

And then finish the redefinition…

[code language=”sql”]
begin
dbms_redefinition.finish_redef_table (uname => ‘SH’,
orig_table=>’JKOSALES’,
int_table=>’JKOSALES_INT’);
end;
/
[/code]

Check table.

[code language=”sql”]
select partition_name, subpartition_name from user_tab_subpartitions where table_name=’JKOSALES’;
Partition Sub-Partition
ACTIVES PA_1999
ACTIVES PA_2000
ACTIVES PA_2001
ACTIVES PA_2002
ARCHIVED PR_1999
ARCHIVED PR_2000
ARCHIVED PR_2001
ARCHIVED PR_2002
[/code]

The drop the interim table

[code language=”sql”]
drop table jkosales_int purge;
[/code]

Create a archiving function that will return 0 or 1 whenever the condition is ok to archive or not a row.

For this test, the condition is under a decommissioned product (flag decommissioned = 1 in products tables)

This function could be much complex to check more business rules…

[code language=”sql”]
Create or replace function Archive_row_jkosales (pprod_id number) return number as
lval number;
begin
select decommissioned into lVal from products where prod_id=pprod_id;
return lVal;
end;
/
update jkosales set ora_archive_state=dbms_ilm.archivestatename(Archive_row_jkosales (prod_id)) where prod_id in (select prod_id from products where decommissioned=1);
0 rows updated.
[/code]

Suppose we want to decommission the product 144

[code language=”sql”]
Update products set decommissioned=1 where prod_id=144;
[/code]

Then we run again the archiving…

[code language=”sql”]
update jkosales set ora_archive_state=dbms_ilm.archivestatename(Archive_row_jkosales (prod_id)) where prod_id in (select prod_id from products where decommissioned=1);
4091 rows updated.
[/code]

Now we have archived and moved to different partitions/sub-partitions 4091 rows.

Lets check…

[code language=”sql”]
exec dbms_stats.gather_table_stats (‘SH’,’JKOSALES’);
select partition_name, subpartition_name,num_rows from user_tab_subpartitions;
Partition Sub_Partition Count
ACTIVES PA_1999 178834
ACTIVES PA_2000 247245
ACTIVES PA_2001 231357
ACTIVES PA_2002 257316
ARCHIVED PR_1999 0
ARCHIVED PR_2000 700
ARCHIVED PR_2001 1289
ARCHIVED PR_2002 2102
[/code]

It’s possible, at a session level, to see all rows again.

[code language=”sql”]
alter session set row archival visibility=all;
Session altered.
Elapsed: 00:00:00.01
select count (*) from jkosales;
COUNT(*)
———-
918843
alter session set row archival visibility=active;
select count (*) from jkosales;
COUNT(*)
———-
914752
[/code]

Now purge rules are, if archived rows are older than max year -2, do purge.

In our case, it’s very easy: truncate subpartitions pr_1999 and pr_2000

[code language=”sql”]
alter table jkosales truncate subpartition pr_1999, pr_2000;
exec dbms_stats.gather_table_stats (‘SH’,’JKOSALES’);
select partition_name, subpartition_name,num_rows from user_tab_subpartitions;
Partition Sub_Partition Count
ACTIVES PA_1999 178834
ACTIVES PA_2000 247245
ACTIVES PA_2001 231357
ACTIVES PA_2002 257316
ARCHIVED PR_1999 0
ARCHIVED PR_2000 0
ARCHIVED PR_2001 1289
ARCHIVED PR_2002 2102
[/code]

 That’s it.

Very easy archiving feature that works pretty well!

Enjoy

jko


     

Leave a Reply

Your email address will not be published. Required fields are marked *