No question at this time
DBA Top 10
1 M. Cadot 15900
2 B. Vroman 15500
3 A. Kavsek 10000
4 T. Boles 6800
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3900
7 P. Wisse 1800
8 T. P 700
9 B. Derous 500
9 R. Wauben 500
9 H. Steijntjes 500
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48209
Total active users1645
Act. users last 24h6
Act. users last hour0
Registered user hits last week186
Registered user hits last month812
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, 164 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