No question at this time
DBA Top 10
1 M. Cadot 15800
2 A. Kavsek 11800
3 B. Vroman 10800
4 M. Hidayathullah ... 7600
5 T. Boles 4600
6 P. Wisse 3800
7 J. Schnackenberg 3200
8 K. Van Nieuwenhov... 2100
9 A. Hudspith 700
9 T. P 700
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48127
Total active users1707
Act. users last 24h10
Act. users last hour0
Registered user hits last week279
Registered user hits last month1240
Go up

Procedure Partition exchange
Next thread: Physical DR on RAC
Prev thread: Query

Message Score Author Date
Hi all, Maybe someone has already a procedure t...... John Baet Jan 08, 2018, 14:40

Follow up by mail Click here

Subject: Procedure Partition exchange
Author: John Baet, Netherlands
Date: Jan 08, 2018, 14:40, 15 days ago
Os info: Linux
Oracle info:
Message: Hi all,

Maybe someone has already a procedure that does the following steps and he/she is so friendly to share it with me or tell me how to create a procedure?

What I need is:

I have two schemas, RO and ARC schema
The oldest partition in RO schema is max. 2x years old. older partitions will be moved to ARC schema.

I have a driver table (with plus 2000 tables, all interval partitions) each table has own RETENTION_DURATION (yes last question in Forum).

I know how to do this manually, steps are:

1- find partition names, from driver table. (query thanks to Michel)
2- create a non-partition table:
create table ro.EXCH_<table_name> as select * from <table_name> where 1=0;

alter table ro.<table_name> exchange partition <partition_name> with table EXCH_<table_name> without validation;

insert into arc.<table_name>  select * from ro.exch_<table_name>  where rownum=1;


(step 3 and 4, to create an empty partition on ARC schema. this command "exchange partition for" is not really works)
5- to get newest partition on ARC schema, using below script:
select table_owner, table_name, max(partition_name) keep (dense_rank last order by partition_position) 

from dba_tab_partitions
where table_owner='ARC'
and table_name='<TABLE_NAME>'
group by table_owner, table_name
order by 1,2;

alter table arc.<table_name> exchange partition <partition_name> with table ro.exch_<table_name> without validation;

ALTER TABLE ro.<table_name> DROP PARTITION <partition_name>;

Hopefully I am not asking too much:)


Regards, J
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here