If you're going to split a table into multiple tables, then exchange them for partitions, why not just create the partitioned table and copy the original table right into that? I'd only use the "exchange" route when converting partitioned views into a partitioned table.
"Joseph S. Testa" <[EMAIL PROTECTED]> wrote:
to do an exchange, you must have ONLY the rows that pertain to that
partition in the table, hence my original message:
>>the easiest is to figure out your partition key, split the data into
>>separate table based on the partition key, then do a alter table
>>exchange partition command.
NOTICE, split the data into separate table(s) based on the partition
key, the error you received is why i said what i said.
Besides did you look up:
ORA-14099: all rows in table do not qualify for specified partition
14099, 00000, "all rows in table do not qualify for specified partition"
// *Cause: There is at least one row in the non partitioned table which
// does not qualify for the partition specified in the ALTER
TABLE
// EXCHANGE PARTITION
// *Action: Ensure that all the rows in the segment qualify for the
partition.
// P! ! erform the alter table operation with the NO CHECKING
option.
// Run ANALYZE table VALIDATE on that partition to find out the
// invalid rows and delete them.
Does this make sense?
Joe
VIVEK_SHARMA wrote:
>
> Joe
>
> AIM A Sample Test to Convert a NON-partitioned Table into a Partitioned
> Table Failing :-
>
> - Table sol1 = NON-partitioned Table
> -------------------------------------
> select sol_id from sol1;
> SOL_ID
> --------
> 0004
> 0018
> 0026
> 0028
> 0032
> 0036
> 0038
>
> 7 rows selected.
>
> - Table sol2 = Partitioned Table - Created in the SAME Tablespace as sol1
> --------------------------------------------------------------------------
> create table SOL2
> (
> sol_id varchar(8),
> del_flg char(1),
> - - -
> - - -
> )
> TABLESPACE TBA_TEMP
! ! > storage (initial 2 )
> partition by range ( sol_id )
> (partition solrg1 values less than ('0028'),
> partition solrg2 values less than ('0039'));
>
> - Exchange Partition FAILING :-
> -------------------------------
> SQL> alter table sol2
> 2 exchange partition solrg1 with table sol1 with validation;
> exchange partition solrg1 with table sol1 with validation
> *
> ERROR at line 2:
> ORA-14099: all rows in table do not qualify for specified partition
>
> Qs. WHERE IS THE PROBLEM ? Where am i making a mistake ?
>
> NOTE - OBSERVATIONS - Following Commands Succeeding though
> ------------------------------------------------------------
>
> SQL> insert into sol2 select * from sol1;
> 7 rows created.
>
> SQL> select sol_id from sol2 partition (solrg1);
> SOL_ID
> --------
> 0004
> 0018
> 0026
> ! ! 3 rows selected.
>
> SQL> select sol_id from sol2 partition (solrg2);
> SOL_ID
> --------
> 0028
> 0032
> 0036
> 0038
> 4 rows selected.
>
> SQL> select sol_id from sol2;
> SOL_ID
> --------
> 0004
> 0018
> 0026
> 0028
> 0032
> 0036
> 0038
>
> 7 rows selected.
>
> Qs. Is the Above Approach Taken for Conversion from NON-partitioned to
> partitioned Table Correct OR are there better ways ?
>
> Qs. What Happens after the Exchange partition is Successful ?
> Should the Original Table be Dropped & the NEW partitioned Table be
> RENAMED as the Original
> NON-partitioned Table ?
>
> Qs. Is as much FREE SPACE Required for The Partitioned Table as is Taken Up
> by the Original NON-partitioned Table OR Will the Used Space in the
> NON-partitioned Table be Simply RE-Assig! ! ned to the NEW Partitioned Table
> Assuming that Both the NON-partitioned Table & the partitioned Tableexist in
> the SAME Tablespace ?
>
> > -----Original Message-----
> > From: Joseph S. Testa [SMTP:[EMAIL PROTECTED]]
> > Sent: Wednesday, February 14, 2001 5:40 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Breaking a Table Data into Partitions
> >
> > the easiest is to figure out your partition key, split the data into
> > separate table based on the partition key, then do a alter table
> > exchange partition command.
> >
> > joe
> > VIVEK_SHARMA wrote:
> > >
> > > To Convert a Regular NON-Partitioned Table into Partitions , what
> > approaches
> > > may be Taken ?
> > >
> > > --
>
--
Joe Testa http://www.oracle-dba.com
Performing! ! Remote DBA Services, need some backup DBA support?
For Sale: Oracle-dba.com domain, its not going cheap but feel free to
ask :)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joseph S. Testa
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Do You Yahoo!?
- Get personalized email addresses from Yahoo! Mail Personal Address - only $35 a year!
