My Oracle Support Banner

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

Last updated on APRIL 03, 2020

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: The document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

  

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.