FreeLists / oracle-l / Re: ORA - 14642 : partition exchange error

[Senthil Ramanujam <sramanujam@xxxxxxxxxxx>]

i checked tab$.spare1 column and this is what i got back. Thanks a lot 
jonathan.

-- a table with char(4)
SQL> r
 1  select spare1
 2  from tab$, dba_objects do
 3  where do.object_id = tab$.obj#
 4* and  do.object_name = 'PT'

   SPARE1
----------
      364

-- a table with char(5)
SQL> r
 1  select spare1
 2  from tab$, dba_objects do
 3  where do.object_id = tab$.obj#
 4* and  do.object_name = 'PT'

   SPARE1
----------
    65900

SQL>


Jonathan Lewis wrote:

> I think you're running into a problem with the
> Hakan factor.  This is a number used by the
> bitmap index algorithms to limit the number
> of rows that Oracle assumes can be stored
> in a single block.  It is (generally) dictated at
> table creation time by the number and type
> of non-null columns.  Check tab$.spare1
> just after creating a table.
>
> Things can go wrong with partitioned tables,
> though, as the table has a Hakan factor, but
> in your case you've created a table to exchange
> with it that has a different Hakan factor.
>
> The closest reference to your problem comes
> from (a) minimize_records_per_block, which
> can only be used in the absence of bitmap indexes
> on a table and resets the Hakan factor, and (b) a
> note about dropping bitmap indexes before your
> first compress a data segment in a partitioned
> table.
>
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> March 2004 Hotsos Symposium - The Burden of Proof
>  Dynamic Sampling - an investigation
> March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
> April 2004 Iceland
> June  2004      UK - Optimising Oracle Seminar
>
>
> ----- Original Message ----- 
> From: "Senthil Ramanujam" <sramanujam@xxxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Monday, March 01, 2004 4:45 PM
> Subject: ORA - 14642 : partition exchange error
>
>
>  
>
> > Hi All,
> >
> > I came across an interesting issue and thought i could get some info
> > from the list.  i did google around and metalinked, but no luck.
> >
> > Environment: 9.2.0.1 - Solaris 8 / HP-11i
> >
> > Actually, i was getting ORA-14642 when i was trying to exchange
> > partition on one of my core tables. The example given below is how i can
> > reproduce with simple example table. it shows me that if the table row
> > length exceeds 8 characters, then it throws an error. If i reduce the
> > column(mytext) length from 5 to 4 or anything less than 5, then
> > everything runs fine.
> >
> > I know it doesnt makes sense to create table and then alter the same. it
> > was the old script and we were able to fix this issue by creating the
> > right column and constraints(i mean, no "alter" command run).
> >
> > I was just curious to know why it was failing and it seems like it was
> > failing based on row length in this case. Originally it was failing on
> > my hp(11i?) box and i tried the same on Solaris(8) and it failed there
> >    
> too.
>  
>
> > any thoughts? has anyone seen this before? am i running into any known
> > issues?
> >
> > appreciate it. thanks.
> >
> > senthil
> >    

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------