Re: Problems with bitmap index in import

From: Steve Phelan <stevep_at_no-spam.pmcgettigan.demon.co.uk>
Date: 1997/05/15
Message-ID: <337AC121.774471BF@no-spam.pmcgettigan.demon.co.uk>#1/1

Ed Jennings wrote:

> I have taken an export from a 7.3 database (originally built as 7.1.6
> &
> upgraded) and then tried to import it into another 7.3 database. The
> DDL for all of the non-unique indexes that include more than one
> column
> are built with the 'BITMAP' option. The SA guide clearly states that
> bitmap indexes can only be used on a single column. Needless to say,
> all of these indexes fail. Why is this happening? Is it a bug? Can
> I
> disable bitmap indexes?
>
> Ed Jennings
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> jenningse_at_mindspring.com

 Yes, this is a bug.

I got the following from Oracle:

---

Article-ID:         <PrSol:2070117.6>
For-Problem:        <Pr:1022066.6>
Subject:            EDIT THE CATEXP.SQL SCRIPT, RERUN IT AND REDO EXPORT
AND
                    IMPORT
Circulation:         ** PUBLIC **

Affected-Platforms:       - Generic: not platform specific

Affected-Products: Oracle7 Server

Solution Description:

---

The workaround to this problem is simple, you need to modify the catexp.sql
script. It is located in the rdbms/admin directory. Scroll to the following
section of the script and search for the block of text as given below. It is
several lines below the following header:

Rem

Rem  ****************************************

Rem Section 3: Views required by export ONLY

Rem  ****************************************

Rem

...

rem all indexes
rem add spare8 for bitmap index
CREATE OR REPLACE view exu7ind
  (iobjid, iname, iowner, iownerid, ispace, ifileno, iblockno,    btname, btobjid, btowner, btownerid, unique$,    cluster$, pctfree$, initrans, maxtrans, blevel, bitmap) AS    SELECT i$.obj#, i$.name, ui$.name, i$.owner#, ts$.name, ind$.file#,ind$.block#, t$.name, t$.obj#, ut$.name, t$.owner#,ind$.unique$, DECODE(t$.type, 3, 1, 0), ind$.pctfree$,ind$.initrans, ind$.maxtrans, NVL(ind$.blevel,-1),

          NVL(ind$.spare8,-1)
   FROM sys.obj$ t$, sys.obj$ i$, sys.ind$ ind$,

             sys.user$ ui$, sys.user$ ut$, sys.ts$ ts$    WHERE ind$.bo# = t$.obj# AND ind$.obj# = i$.obj# AND

             ts$.ts# = ind$.ts# AND i$.owner# = ui$.user# AND
      t$.owner# = ut$.user# AND (unique$=0 OR
             NOT EXISTS (SELECT * from sys.con$ c$
                         WHERE c$.owner# = i$.owner#
               AND   c$.name = i$.name))

...

1. You need to change ---> NVL(ind$.spare8,-1) To this ----> NVL(ind$.spare8, 0)
2. Rerun catexp.sql (as connect internal).
3. Redo your export and import.

Problem Explanation:

---

You have hit bug 368132. Once a database is upgraded from v7.2, the v7.3.2
export views convert the import of unique indexes to bitmap index, which

results in an error:

 ora 901, 00000, "invalid CREATE command"

In v7.2, the spare8 column in the dictionary table ind$ are set to null, in
v7.3, this column should be set to 0 for non bitmap indexes. Therefore, when
a database is upgraded, it causes the export views to see these indexes as
bitmapped indexes and thus converts the create unique index statement to

create bitmap index.

NOTE: this only occurs on databases upgraded from the previous version, this
has no effect on a database newly created in V7.3.2.

The migration scripts do NOT change the values of spare8 of 7.2 catalog to 0
for 7.3.2 because we want to maintain backward as well as upward compatibility.

---

Steve Phelan. Received on Thu May 15 1997 - 00:00:00 CDT