Re: Breaking a Table Data into Partitions

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.
// Perform 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-Assigned 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_at_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.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: teci_at_oracle-dba.com

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: ListGuru_at_fatcity.com (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).