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:[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 ?
> >
> > --
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
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).
