Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Breaking a Table Data into Partitions

RE: Breaking a Table Data into Partitions

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
Date: Mon, 19 Feb 2001 01:40:18 -0800
Message-ID: <F001.002B7B0B.20010219000521@fatcity.com>

Chuck / Joe

Qs. Is Following the BEST / Correct Way ? :-

AIM A Sample Test to Convert a NON-partitioned Table "sol1" into a Partitioned Table "sol2" :-

7 rows selected.

CAUSE This Will Allow to Exchange partition of the COMPLETE NON-partitioned Table with the partitioned Table

Table altered.

SQL> ALTER TABLE SOL2 SPLIT PARTITION
solrg1 at ('0028') INTO
( PARTITION solrg1 storage (initial 5M next 5M pctincrease 0), PARTITION solrg2 storage (initial 5M next 5M pctincrease 0));

Table altered.

NOTE - We Continue to use the SAME partition Name "solrg1" when Splitting the
Single partition "solrg1" into 2 partitions "solrg1" & "solrg2" .

Qs. Is there Any Space management Advantage gained by doing the Same ?

RESULT - The Size of Each partition is Explicitly Specified as 5M , therefore the Size of
the Total Table CONTINUES to be 10M i.e. Same as it's Size in the NON-partitioned State

Qs. Is 10M taken from FREE SPACE (sys.dba_free_space) or is the Existing Already Allocated
Space to Table SOL2 RE-Used Suitably ?

Chuck : I Could NOT Follow what you meant by COPY . Can COPY be used in the Above Aimed Scenario ?

> -----Original Message-----
> From: Chuck Hamilton [SMTP:chuck_hamilton_at_yahoo.com]
> Sent: Friday, February 16, 2001 8:06 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:      Re: Breaking a Table Data into Partitions
> 
> 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" <teci_at_oracle-dba.com> 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: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 Mon Feb 19 2001 - 03:40:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US