Re: Partition Exchange Newbie Question
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "christopherdtaylor1994@xxxxxxxxx" <christopherdtaylor1994@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Jul 2013 10:55:51 -0700 (PDT)
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@xxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
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*
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
