Re: ORA - 14642 : partition exchange error

From: Senthil Ramanujam <sramanujam_at_comcast.net>
Date: Mon, 01 Mar 2004 13:36:47 -0500
Message-ID: <404382BF.7000806@comcast.net>

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_at_comcast.net>
>To: <oracle-l_at_freelists.org>
>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_at_freelists.org 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
-----------------------------------------------------------------

Received on Mon Mar 01 2004 - 12:33:58 CST