Exchanging the partition

[paragp1981]

Hi,
We have one partition table which has got 30 partitions as days of month.This is a MIS report related table. We pull data from several other database into external tables.then we will pull data in temp table from external table. Then we will exchange the temp table with partition of that day.(easiest way and fast also). Now my question is. If one partition contains some data and i exchanged it with temp table what will happen to data in partition.will it append data or replace fully.
logically i think exchanging includes only data dict updates so it will fully replace. i searched on google didnt find anything convincing.

Thanks in advance

Rgds
Parag

[hrishy]

Hi Parag

 CREATE TABLE table2000
      ( x int,
   y int,
      z DATE
    )
    PARTITION BY RANGE (z)
    (
    PARTITION tab_1999_h1 VALUES LESS
    THAN(to_date('30-jun-1999','dd-mon-yyyy')),
   PARTITION tab_1999_h2 VALUES LESS
   THAN(to_date('31-dec-1999','dd-mon-yyyy')),
   PARTITION tab_2000_h1 VALUES LESS
   THAN(to_date('30-jun-2000','dd-mon-yyyy')),
   PARTITION tab_2000_h2 VALUES LESS
   THAN(to_date('31-dec-2000','dd-mon-yyyy'))
   )
   /

Table created.

insert into table2000  values ( 1, 1, '15-jun-1999' );
insert into table2000  values ( 2, 2, '15-dec-1999' );
insert into table2000  values ( 3, 3, '15-jun-2000' );
insert into table2000  values ( 4, 4, '15-dec-2000' );


 CREATE TABLE new_table2000
  ( x int,
      y int,
      z DATE
    )
    PARTITION BY RANGE (z)
    (
    PARTITION newtab_1999_h1 VALUES LESS
    THAN(to_date('30-jun-1999','dd-mon-yyyy')),
   PARTITION newtab_1999_h2 VALUES LESS
   THAN(to_date('31-dec-1999','dd-mon-yyyy'))
   )
   /

Table created.

insert into NEW_table2000  values ( -1, -1, '15-jun-1999' );
insert into NEW_table2000  values ( -2, -2, '15-dec-1999' );


 create table temp ( x int, y int, z date );
Table created.

 alter table new_table2000
 exchange partition newtab_1999_h1
 with table temp;

Table altered.

 
 alter table table2000
 exchange partition tab_1999_h1
 with table temp;

Table altered.

 
 rename temp to table2000_tab_1999_h1;

Table renamed.

 
 select * from table2000;

         X          Y Z
---------- ---------- ---------
        -1         -1 15-JUN-99
         2          2 15-DEC-99
         3          3 15-JUN-00
         4          4 15-DEC-00

so the old data is actually overwritten not appended.

regards
Hrishy

[paragp1981]

Hrishikesh,
Thanks man i got what happens now . actually what i was thinking was correct that data will not get append it will replace tha data. Actually we dont have test env here. So i was unable to try and i dont want trial and error on PROD db. but thanks man.
First thing i will do now is i will make small test env. so that i can do R&D like this .


Rgds
Parag

[hrishy]

Hi Parag

Just curious how did you get my complete name :-)

regards
Hrishy

[paragp1981]

hrishi,
It was a just wild guess +
Its common and widely used shortform for Hrishikesh
in Maharashatra. (Like mike for michael in foreign countries)
I dont know from which state you are. Anyways thanks man

Rgds
Parag

[hrishy]

Hi Parag

Hmm i see you are good at the guessing game..ever tried your fortune at stock markets :-).You will be surprised how many millions you can make.

regards
Hrishy

[paragp1981]

Hrishi,
I dont have much knowledge on share market. just have seen mumbai stock exchange building from outside. and i dont make guesses which will cost my wallet .but still good point to give thought to.I am from pune and currently in coimbatore.

Rgds
Parag

[roadwarriorDBA]

Originally posted by hrishy
Hi Parag

Hmm i see you are good at the guessing game..ever tried your fortune at stock markets :-).You will be surprised how many millions you can make.

regards
Hrishy

Buy CKCM, got in at 13, its now threatening 25. Will go to 100.

RFID super stock, gonna have a 10 year run.

http://clearstation.etrade.com/cgi-b...on.etrade.com/

It fell to 8 on false rumors and earnings were tripled so it zoomed from 8 to 22 in a week.

I hung on and rode it out.