Re: Partition Exchange Newbie Question

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Tue, 16 Jul 2013 10:55:51 -0700 (PDT)
Message-ID: <1373997351.2126.YahooMailNeo_at_web121606.mail.ne1.yahoo.com>

Yes, each table to be exchanged needs to have data matching the partition range:
 

SQL> create table source(pk_col1 number not null
  2          ,other_col1 number(9) not null
  3          ,other_col2 date
  4          ,other_col3 varchar2(20)
  5  )
  6  /

Table created.
SQL>
SQL> create unique index test_pki on source(pk_col1)
  2  /

Index created.
SQL>
SQL> alter table source add constraint test_pk primary key(pk_col1) using index
  2  /

Table altered.
SQL>
SQL> grant all on source to bong
  2  /

Grant succeeded.
SQL>
SQL> insert into source(pk_col1,other_col1,other_col2,other_col3)
  2  select l
  3  , l
  4  , trunc(sysdate+l)
  5  , to_char(l, 'XXX')
  6  from (select level l from dual connect by level < 2000)
  7  /

1999 rows created.
SQL>
SQL> commit
  2  /

Commit complete.
SQL>
SQL> conn bong/bing
Connected.

SQL>
SQL> set serveroutput on size 1000000 linesize 200 trimspool on echo on
SQL>
SQL> drop table target purge

  2  /

drop table target purge
           *

ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> create table target(pk_col1 number not null
  2          ,other_col1 number(9) not null
  3          ,other_col2 date
  4          ,other_col3 varchar2(20)
  5  )
  6  partition by range(pk_col1)
  7  (partition p1 values less than(1000)
  8          ,partition p2 values less than(2000)
  9  )
 10  /

Table created.
SQL>
SQL> create unique index test_pki on target(pk_col1) local
  2  /

Index created.
SQL>
SQL> alter table target add constraint test_pk primary key(pk_col1) using index
  2  /

Table altered.
SQL>
SQL> alter table target exchange partition p1 with table bing.source
  2  /

alter table target exchange partition p1 with table bing.source
                                                         *
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition SQL> David Fitzjarrell  

---

 From: Chris Taylor <christopherdtaylor1994_at_gmail.com> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, July 16, 2013 10:37 AM
Subject: Partition Exchange Newbie Question   

I want to test a partition exchange as an alternative method of doing some work in the database.

I was thinking about loading a global temporary table with the data and then doing the exchange however I'm curious about the following:

Let's say that Primary Partitioned Table is like this:

Table_Name, Partition_Name, Values

TABLEA......Values1.........Values < 10000

TABLEA......Values2.........Values < 20000

TABLEA......Values3.........Values < 30000

Now, if I load my TEMP table with **all** values, can I do a partition exchange and have the values automatically go into the correct partitions of the main partitioned table

**OR**

Do we have to load the temp table with each set of values and do the partition exchange one set of values at a time?

I hope that question is clear enough, if not I can try to put together a more detailed example of what I'm trying to ask.

Regards,

*Chris D. Taylor*

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 16 2013 - 19:55:51 CEST