This discussion is archived
3 Replies Latest reply: Aug 12, 2012 4:38 PM by stuartu RSS

enableversioning failing with ORA-44003

stuartu Explorer
Currently Being Moderated
I'm running Oracle 11.2.0.3 on RHEL5.7. I have a schema I want all the tables version enabled for the purpose of auditing DML changes.

When I try to version enable any of the 13 tables in the schema, I get an ORA-44003 error.
DEV: HORIZON > select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE PUBLIC SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE INDEXTYPE

10 rows selected.

DEV: HORIZON > select * from session_roles;

ROLE
------------------------------
STUDY_OWNER
READ_ONLY_ALL
WM_ADMIN_ROLE

3 rows selected.

DEV: HORIZON > exec dbms_wm.enableversioning('CONTACT','VIEW_WO_OVERWRITE');
BEGIN dbms_wm.enableversioning('CONTACT','VIEW_WO_OVERWRITE'); END;

*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "WMSYS.LT", line 9179
ORA-06512: at line 1

DEV: HORIZON > !oerr ora 44003
44003, 0000, "invalid SQL name"
// *Document : Yes
// *Cause    : The input parameter string was not a valid simple SQL name.
// *Action   : Check with the DBMS_ASSERT spec to verify that the parameter 
//             string is a valid simple SQL name.
I get the same error if I try from another schema that has DBA privileges....
DEV: HORIZON > conn /
Connected.
DEV: OPS$IT_DBA > select * from session_roles;

ROLE
------------------------------
DBA
WM_ADMIN_ROLE
...
...

DEV: OPS$IT_DBA > exec dbms_wm.enableversioning('HORIZON.CONTACT','VIEW_WO_OVERWRITE');
BEGIN dbms_wm.enableversioning('HORIZON.CONTACT','VIEW_WO_OVERWRITE'); END;

*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "WMSYS.LT", line 9179
ORA-06512: at line 1


DEV: OPS$IT_DBA > exec dbms_wm.enableversioning('HORIZON.CONTACT');
BEGIN dbms_wm.enableversioning('HORIZON.CONTACT'); END;

*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "WMSYS.LT", line 9179
ORA-06512: at line 1
I can do it on a table in this schema (with the DBA role) without problem...
DEV: OPS$IT_DBA > create table my_objects as select * from all_objects;

Table created.

DEV: OPS$IT_DBA > alter table my_objects add constraint my_object_id_pk primary key ( object_id );

Table altered.

DEV: OPS$IT_DBA > exec dbms_wm.enableversioning('MY_OBJECTS','VIEW_WO_OVERWRITE');

PL/SQL procedure successfully completed.
The table is as follows....
CREATE TABLE HORIZON.CONTACT
  (
    ID                   NUMBER           NOT NULL,
    CREATED_DATE         TIMESTAMP,
    CREATED_USER         VARCHAR2(32),
    EMAIL                VARCHAR2(255),
    MOBILE_NUMBER        VARCHAR2(20),
    MODIFIED_DATE        TIMESTAMP,
    MODIFIED_USER        VARCHAR2(32),
    REGISTERED_DATE      TIMESTAMP,
    REMOTE_INDEX1        VARCHAR2(64),
    REMOTE_INDEX2        VARCHAR2(64),
    REMOTE_INDEX3        VARCHAR2(64),
    STATUS               VARCHAR2(32),
    TRANS_ID             NUMBER,
    PRIMARY KEY (ID) USING INDEX TABLESPACE HORIZON_DATA,
    UNIQUE (MOBILE_NUMBER) USING INDEX TABLESPACE HORIZON_DATA
TABLESPACE HORIZON_DATA ;
Everything looks fine from what I can see.
DEV: OPS$IT_DBA > select comp_name, version, status from dba_registry;

COMP_NAME                            VERSION                 STATUS
-------------------------------------------------- ------------------------------ --------------------------------------------
Oracle Workspace Manager                  11.2.0.3.0                 VALID
Oracle Enterprise Manager                  11.2.0.3.0                 VALID
Oracle XML Database                       11.2.0.3.0                 VALID
Oracle Expression Filter                  11.2.0.3.0                 VALID
Oracle Rules Manager                       11.2.0.3.0                 VALID
Oracle Database Catalog Views                  11.2.0.3.0                 VALID
Oracle Database Packages and Types             11.2.0.3.0                 VALID
JServer JAVA Virtual Machine                  11.2.0.3.0                 VALID
Oracle XDK                            11.2.0.3.0                 VALID
Oracle Database Java Packages                  11.2.0.3.0                 VALID

10 rows selected.

DEV: OPS$IT_DBA > select count(*) from dba_objects where status = 'INVALID';

  COUNT(*)
----------
      0

1 row selected.

DEV: OPS$IT_DBA > select synonym_name from dba_synonyms where table_owner = 'HORIZON';

no rows selected
I'd normally look at the source but the WMSYS.LT package is encrypted. Does anyone have suggestions what might be causing this?
  • 1. Re: enableversioning failing with ORA-44003
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    That errors means that a column in the table or a dependent/related object has a name that fails a sql injection test. Since, you listed the columns and they look fine, it is probably an index,trigger,constraint,etc that is causing the problem. If you are unable to find it, then I would execute the following prior to enableversioning and post the stack trace.

    SQL> alter session set events '44003 trace name errorstack level 1, forever';

    Regards,
    Ben
  • 2. Re: enableversioning failing with ORA-44003
    stuartu Explorer
    Currently Being Moderated
    Below is the stack trace.

    I can't see any reference to a particular object,... but I do see text like 'bad magic number', 'bad size', 'bad header type'. Hope you can make some sense of it (if not, then it looks like an SR...)

    It is interesting that the enableversioning command gives the same error on all 13 of the tables in this schema. The only common denominator is that each table has a primary key called 'ID', but that doesn't seem to be an issue
    DEV: HORIZON > create table test ( id number primary key );
    
    Table created.
    
    DEV: HORIZON > exec dbms_wm.enableversioning('TEST');
    
    PL/SQL procedure successfully completed.
    I've put that portion that appears applicable below...
    dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1,
    mask=0x0)
    ----- Error Stack Dump -----
    ORA-44003: invalid SQL name
    ----- Current SQL Statement for this session (sql_id=2c1ky28kzrrq6) -----
    BEGIN dbms_wm.enableversioning('HORIZON.CONTACT','VIEW_WO_OVERWRITE'); END;
    ----- PL/SQL Stack -----
    ----- PL/SQL Call Stack -----
      object      line  object
      handle    number  name
    0x43fd8108       160  package body SYS.DBMS_ASSERT
    0x4666ca70        54  package body WMSYS.OWM_ASSERT_PKG
    0x41b5e86c      4830  package body WMSYS.WM_DDL_UTIL
    0x3a140098       334  package body WMSYS.LTDDL
    0x3a140098      1159  package body WMSYS.LTDDL
    0x3a140098      1142  package body WMSYS.LTDDL
    0x41935ba4      9173  package body WMSYS.LT
    0x4a7ae710         1  anonymous block
    
    <  ---- snipped ---->
    
    ----- End of Call Stack Trace -----
    Edited: removed stack trace
  • 3. Re: enableversioning failing with ORA-44003
    stuartu Explorer
    Currently Being Moderated
    Now that I'm at work and able to investigate this more, I can see the problem...

    Some time ago, one of our developers accidentally dropped the tables in the schema. A flashback table was performed, but it looks like the constraints were never renamed (they still have the BIN$... name).
    I became aware of this after I purged the schema's recyclebin but still saw the BIN$ objects still there.
    DEV: HORIZON > select constraint_name, constraint_type, search_condition, index_name
      2  from all_constraints where owner = 'HORIZON' and table_name = 'CONTACT';
    
    CONSTRAINT_NAME             C SEARCH_CONDITION                 INDEX_NAME
    ------------------------------ - ---------------------------------------- ------------------------------
    BIN$xSHKDXW0KLzgQw542IJOOw==$0 U                           SYS_C0047201
    BIN$xSHKDXWzKLzgQw542IJOOw==$0 P                           SYS_C0047200
    BIN$xSHKDXWyKLzgQw542IJOOw==$0 C "ID" IS NOT NULL
    
    3 rows selected.
    After renaming the constraints...
    DEV: HORIZON > alter table contact rename constraint "BIN$xSHKDXWzKLzgQw542IJOOw==$0" to contact_id_pk;
    
    Table altered.
    
    DEV: HORIZON > alter table contact rename constraint "BIN$xSHKDXW0KLzgQw542IJOOw==$0" to mobile_id_uidx;
    
    Table altered.
    
    DEV: HORIZON > alter table contact rename constraint "BIN$xSHKDXWyKLzgQw542IJOOw==$0" to contact_id_chk;
    
    Table altered.
    
    DEV: HORIZON > exec dbms_wm.enableversioning('CONTACT','VIEW_WO_OVERWRITE');
    BEGIN dbms_wm.enableversioning('CONTACT','VIEW_WO_OVERWRITE'); END;
    
    *
    ERROR at line 1:
    ORA-20101: child table must be version enabled
    ORA-06512: at "WMSYS.LT", line 9179
    ORA-06512: at line 1
    Okay, now we are getting somewhere. I fixed up the rest of the constraints in the schema that had a recyclebin name,...

    Now to version enable the child tables, then the parent...
    DEV: HORIZON > select constraint_name, table_name from all_constraints where owner = 'HORIZON'
      2  and r_constraint_name = (select constraint_name from all_constraints where owner = 'HORIZON' and table_name = 'CONTACT' and constraint_type = 'P');
    
    CONSTRAINT_NAME             TABLE_NAME
    ------------------------------ ------------------------------
    FK_CONTACT_PROGRAM_CONTACT_ID  CONTACT_PROGRAM
    FK_OUTBOX_CONTACT_ID            OUTBOX
    FK_INBOX_CONTACT_ID            INBOX
    FK_CONTACT_TIMESLOT_CONTACT_ID CONTACT_TIMESLOT
    
    4 rows selected.
    
    DEV: HORIZON > exec dbms_wm.enableversioning('CONTACT_PROGRAM','VIEW_WO_OVERWRITE');
    
    PL/SQL procedure successfully completed.
    
    DEV: HORIZON > exec dbms_wm.enableversioning('OUTBOX','VIEW_WO_OVERWRITE');
    
    PL/SQL procedure successfully completed.
    
    DEV: HORIZON > exec dbms_wm.enableversioning('INBOX','VIEW_WO_OVERWRITE');
    
    PL/SQL procedure successfully completed.
    
    DEV: HORIZON > exec dbms_wm.enableversioning('CONTACT_TIMESLOT','VIEW_WO_OVERWRITE');
    
    PL/SQL procedure successfully completed.
    
    DEV: HORIZON > exec dbms_wm.enableversioning('CONTACT','VIEW_WO_OVERWRITE');
    
    PL/SQL procedure successfully completed.
    
    DEV: HORIZON > 
    The lesson learned is to properly fix up the object names after a flashback table operation.

    Thanks Ben for pointing me in the right direction.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points