No question at this time
DBA Top 10
1 B. Vroman 15100
2 M. Cadot 14200
3 A. Kavsek 9300
4 J. Schnackenberg 6100
5 T. Boles 5500
6 M. Hidayathullah ... 5400
7 P. Wisse 2500
8 T. P 900
9 R. Wauben 500
9 H. Steijntjes 500
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 users48179
Total active users1662
Act. users last 24h2
Act. users last hour0
Registered user hits last week193
Registered user hits last month1054
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, 104 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