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.

Create table jkosales as select * from sales;

Add the in-database archiving feature for the table

alter table jkosales row archival;

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

 

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;

 

Start the redefinition phase.

Check if possible online with no primary key.

begin
dbms_redefinition.can_redef_table (uname => 'SH', tname=>'JKOSALES',
options_flag=>dbms_redefinition.cons_use_rowid);
end;
/

Start the redefinition…

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.

 

And then finish the redefinition…

begin
dbms_redefinition.finish_redef_table (uname => 'SH',
orig_table=>'JKOSALES',
int_table=>'JKOSALES_INT');
end;
/

Check table.

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

The drop the interim table

drop table jkosales_int purge;

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…

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.

Suppose we want to decommission the product 144

Update products set decommissioned=1 where prod_id=144;

Then we run again the archiving…

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.

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

Lets check…

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

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

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

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

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

 That’s it.

Very easy archiving feature that works pretty well!

Enjoy

jko

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

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

  1. Auric

    Found out, you can’t do this if you ever need to split the partitions. You can use add partition, but that precludes having a default range with maxvalue.

    If you sub partition on the ora column, then alter table split partitions:
    ORA-14030: non-existent partitioning column in CREATE TABLE statement

    You can’t make it visible:
    ORA-38398: DDL not allowed on the system ILM column

    I even tried to create a virtual column referencing the hidden one
    alter table test_archive add (active NUMBER GENERATED ALWAYS AS (case when ORA_ARCHIVE_STATE = 0 then 0 else 1 end) VIRTUAL and change out the sub-partition.

    Finally, changed sub partitioning to a normal number column. I thought hey, well it just means two updates, and it still failed with a core dump and Address not mapped to object in the trace.

    Turns out, oracle 12.2.0.2 won’t allow any splitting of partitions if row archival is turned on for that table. I logged a support ticket with oracle to resolve / patch at next minor.

    Opened a support ticket for this.

    Reply

Leave a Reply

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