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, the DDL is logged
(even with nologging clause).
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, how to proceed ? we need direct-insert
nologging and parallel clause to speed the process..

thank yu

Comments embedded.
On Oct 16, 4:44 am, Nonor <ora.emp... (AT) gmail (DOT) com> wrote:
Define 'too much undo', please. And provide the Oracle release you're
using, all four numbers, as this behaviour MAY be release-specific.

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.

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.

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.


David Fitzjarrell