Re: Partition Exchange Newbie Question
Date: Tue, 16 Jul 2013 12:19:20 -0700 (PDT)
Message-ID: <1374002360.91807.YahooMailNeo_at_web121602.mail.ne1.yahoo.com>
I was not entirely correct in my last post; allow me to correct that.
It is true that, using the default behavior of alter table ... exchange partition ... the reported error will be displayed. It's also true that using the WITHOUT VALIDATION clause will allow the partition to be exchanged with the caveat that ALL of the data (regardless of partition key value) will go into the partition being exchanged:
SQL> alter table target exchange partition p1 with table bing.source without validation
2 /
Table altered.
SQL>
SQL> select * from target partition(p1)
2 /
PK_COL1 OTHER_COL1 OTHER_COL OTHER_COL3
------------------------------ ------------------------------ --------- --------------------...
288 288 30-APR-14 120
289 289 01-MAY-14 121
290 290 02-MAY-14 122
291 291 03-MAY-14 123
292 292 04-MAY-14 124
293 293 05-MAY-14 125
294 294 06-MAY-14 126
1981 1981 18-DEC-18 7BD
1982 1982 19-DEC-18 7BE
1983 1983 20-DEC-18 7BF
1984 1984 21-DEC-18 7C0
1985 1985 22-DEC-18 7C1
1986 1986 23-DEC-18 7C2
1987 1987 24-DEC-18 7C3
1988 1988 25-DEC-18 7C4
1989 1989 26-DEC-18 7C5
1990 1990 27-DEC-18 7C6
1991 1991 28-DEC-18 7C7
1992 1992 29-DEC-18 7C8
1993 1993 30-DEC-18 7C9
1994 1994 31-DEC-18 7CA
1995 1995 01-JAN-19 7CB
PK_COL1 OTHER_COL1 OTHER_COL OTHER_COL3
------------------------------ ------------------------------ --------- --------------------1999 rows selected.
1996 1996 02-JAN-19 7CC
1997 1997 03-JAN-19 7CD
1998 1998 04-JAN-19 7CE
1999 1999 05-JAN-19 7CF
SQL>
SQL> select * from target partition(p2)
2 /
no rows selected
SQL> I had to create a second partitioned table and perform an insert into ... select * from ... to put the data in its respective partition:
SQL> create table target2(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 ) enable row movement
10 /
Table created.
SQL>
SQL> insert into target2
2 select * From target;
1999 rows created.
SQL>
SQL> select * from target2 partition(p1)
2 /
PK_COL1 OTHER_COL1 OTHER_COL OTHER_COL3
------------------------------ ------------------------------ --------- --------------------...
288 288 30-APR-14 120
289 289 01-MAY-14 121
290 290 02-MAY-14 122
291 291 03-MAY-14 123
292 292 04-MAY-14 124
293 293 05-MAY-14 125
294 294 06-MAY-14 126
295 295 07-MAY-14 127
296 296 08-MAY-14 128
999 rows selected.
SQL> select * from target2 partition(p2)
2 /
PK_COL1 OTHER_COL1 OTHER_COL OTHER_COL3
------------------------------ ------------------------------ --------- --------------------...
1000 1000 11-APR-16 3E8
1001 1001 12-APR-16 3E9
1002 1002 13-APR-16 3EA
1003 1003 14-APR-16 3EB
1004 1004 15-APR-16 3EC
1005 1005 16-APR-16 3ED
1006 1006 17-APR-16 3EE
1007 1007 18-APR-16 3EF
1008 1008 19-APR-16 3F0
1009 1009 20-APR-16 3F1
1010 1010 21-APR-16 3F2
1011 1011 22-APR-16 3F3
1012 1012 23-APR-16 3F4
1013 1013 24-APR-16 3F5
1014 1014 25-APR-16 3F6
1000 rows selected.
SQL> I also realize I missed the boat by overlooking the expected use of a global temporary table. As already reported you cannot use such a table for partition exchange.
My apologies to the list for my mistakes.
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-lReceived on Tue Jul 16 2013 - 21:19:20 CEST