ORA-947 ON IMPDP IMPORTING SUBSCRIBER TABLES (Doc ID 1926656.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 12.1.0.2 [Release 11.2 to 12.1]
Information in this document applies to any platform.

Symptoms

Data Pump Import (impdp) reports ORA-947 in the alert log while importing Subscriber Tables.
There's no error is reported by the Impdp logfile.

 

Alert Log

Tue Jun 10 07:50:00 2014
populate_sys_subscriber_table for TEST.TN_S_EX Error: ORA-00947: not enough values
populate_sys_subscriber_table for TEST.TN_DI Error: ORA-00947: not enough values

 

Trace file
When running the impdp while event 947 was enabled, the trace file confirms, that the´issue occurs while inserting into SYS.AQ$_SUBSCRIBER_TABLE.

Event
  alter system set events='947 trace name errorstack level 3';

 

Error Stack Dump

----- Error Stack Dump -----
ORA-00947: not enough values
----- Current SQL Statement for this session (sql_id=7tu3vh67kpkbt) -----
 INSERT INTO SYS.AQ$_SUBSCRIBER_TABLE VALUES  ( :1, :2, :3, :4, :5, :6, :7,
:8, :9, :10, :11,  :12, :13, :14, :15, :16, :17, :18, :19 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x321b7584f0      3524  package body SYS.DBMS_PRVTAQIS
0x31956bb590       718  package body SYS.DBMS_AQ_SYS_IMP_INTERNAL
0x31bbc006a8        60  package body SYS.DBMS_AQ_IMP_INTERNAL
0x31dc9bc928         2  anonymous block
0x323b9cd148     18895  package body SYS.KUPW$WORKER
0x323b9cd148     18051  package body SYS.KUPW$WORKER
0x323b9cd148     17667  package body SYS.KUPW$WORKER
0x323b9cd148      4058  package body SYS.KUPW$WORKER
0x323b9cd148     10450  package body SYS.KUPW$WORKER
0x323b9cd148      1824  package body SYS.KUPW$WORKER
0x319f9fb460         2  anonymous block


Call Stack Trace

qcuSigErr qcsInsertInitWithTableCols qcsinspre qcspqaf qcspqbDescendents ...




Changes

The errorstack based on event 947 confirms, that the issue occurs while updating SYS.AQ$_SUBSCRIBER_TABLE

----- Error Stack Dump -----
ORA-00947: not enough values
----- Current SQL Statement for this session (sql_id=7tu3vh67kpkbt) -----
 INSERT INTO SYS.AQ$_SUBSCRIBER_TABLE VALUES  ( :1, :2, :3, :4, :5, :6, :7,
:8, :9, :10, :11,  :12, :13, :14, :15, :16, :17, :18, :19 )


The table SYS.AQ$_SUBSCRIBER_TABLE owns 21 columns while the insert into SYS.AQ$_SUBSCRIBER_TABLE only inserts 19 columns:

SQL> descr SYS.AQ$_SUBSCRIBER_TABLE

 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 SCHEMA                              NOT NULL VARCHAR2(30)
 TABLE_NAME                          NOT NULL VARCHAR2(30)
 TABLE_OBJNO                         NOT NULL NUMBER
 QUEUE_ID                            NOT NULL NUMBER
 SUBSCRIBER_ID                       NOT NULL NUMBER
 QUEUE_NAME                          NOT NULL VARCHAR2(30)
 NAME                                         VARCHAR2(30)
 ADDRESS                                      VARCHAR2(1024)
 PROTOCOL                                     NUMBER
 SUBSCRIBER_TYPE                              NUMBER
 RULE_NAME                                    VARCHAR2(30)
 TRANS_NAME                                   VARCHAR2(65)
 RULESET_NAME                                 VARCHAR2(65)
 NEGATIVE_RULESET_NAME                        VARCHAR2(65)
 CREATION_TIME                                TIMESTAMP(6) WITH TIME ZONE
 MODIFICATION_TIME                            TIMESTAMP(6) WITH TIME ZONE
 DELETION_TIME                                TIMESTAMP(6) WITH TIME ZONE
 SCN_AT_REMOVE                                NUMBER
 SCN_AT_ADD                                   NUMBER
 CLIENT_SESSION_GUID                          VARCHAR2(36)
 INSTANCE_ID                                  NUMBER
.
==> 21 columns

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms