Exchanging the partition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Exchanging the partition

  1. #1
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245

    Exchanging the partition

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Parag

    Code:
     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

  3. #3
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    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

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Parag

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

    regards
    Hrishy

  5. #5
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    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

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    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

  7. #7
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    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

  8. #8
    Join Date
    May 2005
    Posts
    129
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width