RE: Breaking a Table Data into Partitions

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
Date: Fri, 16 Feb 2001 01:06:52 -0800
Message-ID: <F001.002B6406.20010216004525@fatcity.com>

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 ?
> > 
> > --
> 
> 

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

Received on Fri Feb 16 2001 - 03:06:52 CST