"Joseph S. Testa" <teci@oracle-dba.com> wrote: 
to do an exchange, you must have ONLY the rows that pertain to thatpartition 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 partitionkey, 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 partition14099, 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 ALTERTABLE// EXCHANGE PARTITION// *Action: Ensure that all the rows in the segment qualify for thepartition.// P!
!
erform the alter table operation with the NO CHECKINGoption.// Run ANALYZE table VALIDATE on that partition to find out the// invalid rows and delete them.Does this make sense?JoeVIVEK_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:teci@oracle-dba.com]> > 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.comPerforming!
!
 Remote DBA Services, need some backup DBA support?For Sale: Oracle-dba.com domain, its not going cheap but feel free toask :)-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Joseph S. TestaINET: teci@oracle-dba.comFat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso 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!