How to archive data with Oracle Partitioning

View All Scripts Login to Run Script

Script Name How to archive data with Oracle Partitioning
Description Scripts for SQL Ask TOM Office Hours session "How to archive old data" on 17th May 2022 https://asktom.oracle.com/pls/apex/asktom.search?oh=17423
Area Partitioning
Contributor Chris Saxon (Oracle)
Created Monday May 16, 2022

Statement 1

Create parent table

create table orders (  
  order_id       int   
    primary key  
    not null,   
  order_datetime date  
    not null,  
  customer_id    int   
    not null  
)

Table created.

Statement 2

Note the ON DELETE CASCADE clause for the foreign key. You can use reference partitioning without this, but we'll need it for the archive processes.

Create child table

create table order_items (   
  order_id    
    constraint order_fk    
    references orders ( order_id )    
    on delete cascade -- we'll need this! 
    not null,   
  product_id integer   
    not null,   
  deprecated_column integer,   
  primary key (    
    order_id, product_id    
  )    
)

Table created.

Statement 3

Deprecate a column by setting it unused

alter table order_items  
  set unused column deprecated_column

Table altered.

Statement 4

Add partitioning to parent table

alter table orders   
  modify partition by range ( order_datetime )  
  interval ( interval '1' month ) (  
    partition p0 values less than ( date'2022-01-01' )  
  )

Table altered.

Statement 5
This partitions the child table using the foreign key back to ORDERS. This ensures child rows are in the same partition as the row in the parent table
Add reference partitioning to child table
```
alter table order_items  
  modify partition by reference ( order_fk )
```
```
Table altered.
```

Statement 6

Verify both tables are partitioned

select table_name, partition_name   
from   user_tab_partitions  
where  table_name like 'ORDER%'


TABLE_NAME PARTITION_NAME
ORDERS P0
ORDER_ITEMS P0

2 rows selected.

TABLE_NAME	PARTITION_NAME
ORDERS	P0
ORDER_ITEMS	P0

Statement 7

Create a table to swap with partitions in the child

create table order_items_stage as  
  select * from order_items  
  where  1 = 0

Table created.

Statement 8
To use exchange partition the tables must have identical structures. This includes invisible and unused columns. ORDER_ITEMS has an unused column; this is not copied when using CREATE TABLE .. AS SELECT. So the exchange fails.
```
alter table order_items  
  exchange partition p0  
  with table order_items_stage
```
```
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION 
```
More Details: https://docs.oracle.com/error-help/db/ora-14097

Statement 9

Check columns in ORDER_ITEMS tables

select table_name, column_name, hidden_column  
from   user_tab_cols  
where  table_name like 'ORDER_ITEM%'  
order  by column_name


TABLE_NAME COLUMN_NAME HIDDEN_COLUMN
ORDER_ITEMS ORDER_ID NO
ORDER_ITEMS_STAGE ORDER_ID NO
ORDER_ITEMS PRODUCT_ID NO
ORDER_ITEMS_STAGE PRODUCT_ID NO
ORDER_ITEMS SYS_C00003_22051708:52:34$ YES

5 rows selected.

TABLE_NAME	COLUMN_NAME	HIDDEN_COLUMN
ORDER_ITEMS	ORDER_ID	NO
ORDER_ITEMS_STAGE	ORDER_ID	NO
ORDER_ITEMS	PRODUCT_ID	NO
ORDER_ITEMS_STAGE	PRODUCT_ID	NO
ORDER_ITEMS	SYS_C00003_22051708:52:34$	YES

Statement 10

drop table order_items_stage  
  cascade constraints purge

Table dropped.

Statement 11
CREATE TABLE ... FOR EXCHANGE clones the source table
Create staging parent table for exchange
```
create table orders_stage  
  for exchange with table orders
```
```
Table created.
```

Statement 12

Create staging child table for exchange

create table order_items_stage   
  for exchange with table order_items

Table created.

Statement 13

Add parent constraints

alter table orders_stage  
  add primary key ( order_id )

Table altered.

Statement 14

Add constraint to child staging table

alter table order_items_stage   
  add foreign key ( order_id )  
  references orders_stage ( order_id )  
  on delete cascade

Table altered.

Statement 15

Load data

begin  
  insert into orders values ( 1, date'2022-05-01', 1 ); 
  insert into order_items values ( 1, 1 ); 
  commit; 
end;

Statement processed.

Statement 16
This (tries to) swap the partition in the parent table with the corresponding staging table. This fails because there are matching rows in the child table.
Exchange partition
```
alter table orders  
  exchange partition for ( date'2022-05-01' )  
  with table orders_stage
```
```
ORA-02266: unique/primary keys in table referenced by enabled foreign keys 
```
More Details: https://docs.oracle.com/error-help/db/ora-02266

Statement 17

This swaps the parent & child partitions to the staging tables at the same time

Cascade partition exchange

alter table orders  
  exchange partition for ( date'2022-05-01' )  
  with table orders_stage  
  cascade

Table altered.

Statement 18
The source table is now empty
```
select * from order_items
```
```
no data found
```
Statement 19
...and the rows have been transferred to the child table.
```
select * from order_items_stage
```
```
ORDER_ID PRODUCT_ID
1 1
```

ORDER_ID	PRODUCT_ID
1	1

Statement 20

This creates a permanent, partitioned store to move the data to

Create a partitioned archive table

create table orders_archive   
  partition by range ( order_datetime )  
  interval ( interval '1' month ) (  
    partition p0 values less than ( date'2022-01-01' )  
  )  
  for exchange with table orders

Table created.

Statement 21
This table will be referenced partitioned. Constraint clauses are invalid in this statement, so you need to add these and partition this table separately.
Create child archive table
```
create table order_items_archive   
  for exchange with table order_items
```
```
Table created.
```

Statement 22

alter table orders_archive  
  add primary key ( order_id )

Table altered.

Statement 23

alter table order_items_archive  
  add constraint order_archive_fk 
  foreign key ( order_id ) 
  references orders_archive ( order_id ) 
  on delete cascade

Table altered.

Statement 24

Partition child archive table

alter table order_items_archive  
  modify partition by reference ( order_archive_fk )

Table altered.

Statement 25

Transfer the data from the staging tables to the archives

alter table orders_archive  
  exchange partition for ( date'2022-05-01' )  
  with table orders_stage  
  cascade

Table altered.

Statement 26

The staging table is now empty

select * from order_items_stage

no data found

Statement 27
The rows have now been fully transferred to the archive tables
```
select * from order_items_archive
```
```
ORDER_ID PRODUCT_ID
1 1
```

ORDER_ID	PRODUCT_ID
1	1

Statement 28

Partition exchange is a metadata operation - the rows stay where they are. Notice that the DATA_OBJECT_IDs are cycled around for the ORDERS & ORDER_ITEMS tables.

How is partition exchange so fast?!

select object_name, subobject_name, object_id, data_object_id  
from   user_objects 
where  object_name like 'ORDER%' 
and    data_object_id is not null 
and    (  
  object_type = 'TABLE' or  
  object_type = 'TABLE PARTITION' and subobject_name like 'SYS%' 
) 
order  by substr ( object_name, 1, 6 ), object_id


OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
ORDERS_STAGE  - 123954182 123954215
ORDERS SYS_P443420 123954187 123954182
ORDERS_ARCHIVE SYS_P443421 123954215 123954187
ORDER_ITEMS_STAGE  - 123954185 123954216
ORDER_ITEMS SYS_P443420 123954188 123954185
ORDER_ITEMS_ARCHIVE SYS_P443421 123954216 123954188

6 rows selected.

OBJECT_NAME	SUBOBJECT_NAME	OBJECT_ID	DATA_OBJECT_ID
ORDERS_STAGE	-	123954182	123954215
ORDERS	SYS_P443420	123954187	123954182
ORDERS_ARCHIVE	SYS_P443421	123954215	123954187
ORDER_ITEMS_STAGE	-	123954185	123954216
ORDER_ITEMS	SYS_P443420	123954188	123954185
ORDER_ITEMS_ARCHIVE	SYS_P443421	123954216	123954188

Statement 29

The parent table is interval partitioned, so the generated partitions have SYS% names

View the partitions

select table_name, partition_name   
from   user_tab_partitions  
where  table_name like 'ORDER%'


TABLE_NAME PARTITION_NAME
ORDERS P0
ORDERS SYS_P443420
ORDERS_ARCHIVE P0
ORDERS_ARCHIVE SYS_P443421
ORDER_ITEMS P0
ORDER_ITEMS SYS_P443420
ORDER_ITEMS_ARCHIVE P0
ORDER_ITEMS_ARCHIVE SYS_P443421

8 rows selected.

Statement 30
This wipes the partitions from the source table. The child table is referenced partitioned, so this removes the child partition too.
Remove the archived partitions
```
alter table orders  
  drop partition for ( date'2022-05-01' )
```
```
Table altered.
```

TABLE_NAME	PARTITION_NAME
ORDERS	P0
ORDERS	SYS_P443420
ORDERS_ARCHIVE	P0
ORDERS_ARCHIVE	SYS_P443421
ORDER_ITEMS	P0
ORDER_ITEMS	SYS_P443420
ORDER_ITEMS_ARCHIVE	P0
ORDER_ITEMS_ARCHIVE	SYS_P443421

Statement 31

The SYS* partitions have now gone from ORDERS & ORDER_ITEMS

select table_name, partition_name   
from   user_tab_partitions  
where  table_name like 'ORDER%'


TABLE_NAME PARTITION_NAME
ORDERS P0
ORDERS_ARCHIVE P0
ORDERS_ARCHIVE SYS_P443421
ORDER_ITEMS P0
ORDER_ITEMS_ARCHIVE P0
ORDER_ITEMS_ARCHIVE SYS_P443421

6 rows selected.

TABLE_NAME	PARTITION_NAME
ORDERS	P0
ORDERS_ARCHIVE	P0
ORDERS_ARCHIVE	SYS_P443421
ORDER_ITEMS	P0
ORDER_ITEMS_ARCHIVE	P0
ORDER_ITEMS_ARCHIVE	SYS_P443421

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database