Index Organized Table -- ORA-25182

Chris Hamilton

unread,

Oct 15, 1999, 3:00:00 AM10/15/99

to

I'm trying to create an index-organized table. The table will have
two columns, which form a composite PK.

The statement looks like this:

create table holdings_static
(memberid number(10) not null,
symbol varchar2(10) not null,
constraint holdings_static_pk
primary key (memberid, symbol))
organization index tablespace wss_data
storage (initial 64m next 64m);

When I try to run this, I get the following Oracle error:

ORA-25182: feature not currently available for index-organized tables

So I looked this up in the docs, and it says:

*****************

Cause: An attempt was made to use one or more of the following
feature(s) not currently supported for index-organized tables:

CREATE TABLE with LOB/BFILE/VARRAY columns
partitioning/PARALLEL/CREATE TABLE AS SELECT options
ALTER TABLE with ADD/MODIFY column options, CREATE INDEX

Action: Do not use the disallowed feature(s) in this release.

*****************

I'm using 8.0.6.0.0, my compatible parameter is set to 8.0.5.1.0 ...

The columns involved, as you can see, are simply NUMBER and VARCHAR2,
no parallel, no CTAS.

Any ideas???

Chris
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Christopher Hamilton
Oracle DBA -- Sandbox.com
cham...@sandbox-inc.com
http://www.sandbox.com/

Jonathan Lewis

unread,

Oct 15, 1999, 3:00:00 AM10/15/99

to

Might be a bug;
I cut your text,
changed 64M to 64K
changed the tablespace name
and ran it under 8.0.5
Table created.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Chris Hamilton wrote in message ...

Chris Hamilton

unread,

Oct 15, 1999, 3:00:00 AM10/15/99

to

On Fri, 15 Oct 1999 19:33:56 +0100, "Jonathan Lewis"
<jona...@jlcomp.demon.co.uk> wrote:

>Might be a bug;
>I cut your text,
>changed 64M to 64K
>changed the tablespace name
>and ran it under 8.0.5
>Table created.

Thanks, Jonathan. I'll call Oracle ... MetalLink was bombing out on
me so I thought I'd try here.

I tried the 64K trick, didn't work under 8.0.6. On to bug-land?

Chris Hamilton

unread,

Oct 15, 1999, 3:00:00 AM10/15/99

to

On Fri, 15 Oct 1999 19:33:56 +0100, "Jonathan Lewis"
<jona...@jlcomp.demon.co.uk> wrote:

>Might be a bug;
>I cut your text,
>changed 64M to 64K
>changed the tablespace name
>and ran it under 8.0.5
>Table created.

I just tested it under 8.1.5, and it worked perfectly as is.

Jonathan Lewis

unread,

Oct 15, 1999, 3:00:00 AM10/15/99

to

Just an idle one but you could tty:
a) Putting it in the default tablespace
b) Putting it in the SYSTEM tablespace (Yes I do mean it)
c) Swapping the order of the TABLESPACE
and ORGANIZATION clauses
(and other permutations including STORAGE)

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Chris Hamilton wrote in message ...

Joerg Lanzinger

unread,

Oct 25, 1999, 3:00:00 AM10/25/99

to

Hello Chris,

I once ask Oracle Germany what to do when working with IOTs.
Simple answer: Upgrade to Oracle8i (i.e. 8.1.5).

A unvaluable advantage of 8i is that IOTs are now known to the optimizer
and it will no longer try to use a FFS for each query.

Hope that helps,
Joerg

Chris Hamilton

unread,

Oct 26, 1999, 3:00:00 AM10/26/99

to

Thank you, Joerg. An Oracle support guy I spoke with had no problem
creating it on his 8.0.6 system, but oh well.

I have 8i installed on a test server and will upgrade soon.