Re: Add column to BIG IOT (9.2.0.5)

From: <fitzjarrell_at_cox.net>
Date: Tue, 16 Oct 2007 08:05:10 -0700
Message-ID: <1192547110.036743.87450@e9g2000prf.googlegroups.com>

Comments embedded.
On Oct 16, 4:44 am, Nonor <ora.emp..._at_gmail.com> wrote:
> Hello
>
> The problem is to adding a column to a big IOT table, 23 millions
> records, when trying
> the simple "add column.." we get too much undo,

Define 'too much undo', please. And provide the Oracle release you're using, all four numbers, as this behaviour MAY be release-specific.

> the DDL is logged
> (even with nologging clause).

Specifying NOLOGGING when creating an IOT used to throw an error:

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

This may also be a bug in the release of Oracle you're using, but we won't know that until you post that information.

> I try the to create a empty IOT with the new column,
> then a
> INSERT /* append parallel...*/ INTO <new_iot> SELECT /*
> parallel ..*/ * from <orig_iot>,
> but I get a "ORA-00947 not enough value"....seem that oracle need a
> value for the new column,

SQL> create table coltest
2 as select * from dba_objects
3 where 0=1;

Table created.

SQL> alter table coltest
2 add mydummy number;

Table altered.

SQL> insert into coltest
2 select * from dba_objects;
insert into coltest

*
ERROR at line 1:
ORA-00947: not enough values

How do you expect to populate x+1 columns with only x columns of data? You need to either list the columns explicitly which you want to select:

SQL> insert into coltest
  2 select
  3 OWNER,
  4 OBJECT_NAME,
  5 SUBOBJECT_NAME,
  6 OBJECT_ID,
  7 DATA_OBJECT_ID,
  8 OBJECT_TYPE,
  9 CREATED,
10 LAST_DDL_TIME,
11 TIMESTAMP,
12 STATUS,

 13  TEMPORARY,
 14  GENERATED,
 15  SECONDARY,

16 1
17 from dba_objects;

11046 rows created.

or list the columns you CAN populate in your insert statement:

insert into coltest
(OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
TIMESTAMP,
STATUS,
TEMPORARY,
GENERATED,
SECONDARY)
select *
from dba_objects;

You cannot simply add a column to a table (changing its definition) then blindly go and attempt to populate that now non-matching structure with a simple select * from some other table.

> how to proceed ? we need direct-insert
> nologging and parallel clause to speed the process..
>

Follow the example provided (which you could have found on google.com had you decided to look) and modify it according to your needs. The task is simple IF you think it through first.

> thank yu

David Fitzjarrell Received on Tue Oct 16 2007 - 10:05:10 CDT