| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Breaking a Table Data into Partitions
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
 |
 |