Technique to Backup and Truncate a Table in One Moment in Oracle Database

I’ve used a technique to backup and truncate a table in one shot in less than a second.

It’s based on the partition exchange feature in Oracle.

The step as follows:

1- Create table with same structure and indexes as the original table
2- Create a partitioned table and indexes (hash partitioned with only one partition) with same structure as original table
3- Exchange the hash partition (currently empty) with the original table. Which is equivalent to backing up the original table to a temp table, and truncating it
4- Exchange the hash partition (now containing data) with the destination backup table
5- Drop the temp partitioned table

The only restriction is to create backup and temp tables/indexes in same tablespaces as source table/indexes

You may later export the backup table, or move it to another tablespace.

With this technique we achieve:
1- Saving down time (which is required to avoid inserting new records until backup is complete)
2- Saving I/O as we didn’t copy or export data.

below is a complete example

--
-- create a test table to show example on
--

create table test_tab (id number, info varchar2(100))
tablespace users
/
create unique index test_tab_pk on test_tab(id)
tablespace users
/
alter table test_tab add constraint test_tab_pk primary key (id)
/

--
-- insert few rows
--

insert into test_tab values (1, 'some info number 1')
/
insert into test_tab values (2, 'some info number 2')
/
insert into test_tab values (3, 'some info number 3')
/
commit
/

--
-- now suppose we want to backup the table and truncate it
-- in the shortest possible time
-- we will take the data/index segment of the table, and give it a free one!
-- we can do that using partition exchange with a temporary table
--

--
-- First: we need to create empty table (just like the original table)
-- to hold backup of the data
--

create table test_tab_bkup tablespace users
as
select * from test_tab where 1 = 2
/
create unique index test_tab_bkup_pk on test_tab_bkup(id)
tablespace users
/
alter table test_tab_bkup add constraint test_tab_bkup_pk primary key (id)
/

--
-- Second: we also create a temporary partitioned table just like the original
-- we use hash partitioning for simplicity (to avoid secifying ranges)
-- we a column from the primary key to be used as partitioning key
-- that allow us to create locally partitioned unique index
--

create table test_tab_temp tablespace users
partition by hash (id) (partition test_tab_temp_p1)
as
select * from test_tab where 1 = 2
/
create unique index test_tab_temp_pk on test_tab_temp(id)
local
tablespace users
/
alter table test_tab_temp add constraint test_tab_temp_pk primary key (id)
/

--
-- Third: we exchange the original table with the temporary partitioned table
-- after this step the original table appears as if it's truncated
-- while the data/index stay in the temporary partitioned table
--

alter table test_tab_temp exchange partition test_tab_temp_p1
with table test_tab including indexes without validation
/

--
-- Forth: do one more exchange to move the partition to the archive table
--

alter table test_tab_temp exchange partition test_tab_temp_p1
with table test_tab_bkup including indexes without validation
/

-- now the data in the destination archive table

-- finally drop the temp partitioned table

drop table test_tab_temp
/

--
-- cleanup example tables
--

-- drop table test_tab purge;
-- drop table test_tab_bkup purge;

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current day month ye@r *