Search code examples
oracle-databaseplsqloracle12credefinitionora-01741

ORA-01741 for DBMS_REDEFINITION with invisible fields and implicit constraints

I recently noticed an odd failure pattern recently in one database (12cR1) when using DBMS_REDEFINITION. CAN_REDEF_TABLE completes fine, as does START_REDEF_TABLE, but COPY_TABLE_DEPENDENTS fails with a bewildering:

ORA-01741: illegal zero-length identifier

After some debugging, the exception appears to be related to the ORIG_TABLE carrying both INVISIBLE column(s) and implicit system-named constraints. I'll include an example below to demonstrate the problem, but I hoped to gain some understanding of the behavior, and didn't see anything notable about INVISIBLE called out in the docs.

It seems there is some nuance to the creation of system-generated constraints I'd like to understand better. Apologies for the "why" question, but, Why do implicit system constraints behave any differently than explicitly-defined constraints during redefinition? I had thought that after being assigned a system-generated name, a constraint was just a constraint. Are system-generated objects different in other ways from client-named constraints beyond their names?

I also hoped to see if anyone has another workaround to recommend beyond just renaming implicit constraints or un-hiding the columns before the redifinition.

Thanks

Example: Below are three versions of the same ORIG_TABLE for redefinition. The first two both undergo the far-below redefinition ok with the given INT_TABLE, but the third throws the ORA-01741 during COPY_TABLE_DEPENDENTS.

Version 1: All columns visible, implicit system-generated constraints:

CREATE TABLE REDEF_TARGET (
  THE_KEY  INTEGER NOT NULL PRIMARY KEY ,
  THE_DATE DATE    NOT NULL
);

Version 2: INVISIBLE column present, explicit constraint (given an absurd name here to poke at if DBMS_REDEFINITION is instrumenting existing names)

CREATE TABLE REDEF_TARGET (
  THE_KEY  INTEGER NOT NULL PRIMARY KEY ,
  THE_DATE DATE INVISIBLE ,
  CONSTRAINT SYS_C02583271 CHECK (THE_DATE IS NOT NULL)
);

Version 3: INVISIBLE column and implicit constraint both present

CREATE TABLE REDEF_TARGET (
  THE_KEY  INTEGER NOT NULL PRIMARY KEY ,
  THE_DATE DATE INVISIBLE NOT NULL
);

Running either of the first against the below will work, while the third will fail during copy-deps.

CREATE TABLE REDEFINER (
  THE_KEY  INTEGER ,
  THE_DATE DATE
);

DECLARE
  V_NUM INTEGER;
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME => USER , TNAME => 'REDEF_TARGET');
  DBMS_REDEFINITION.START_REDEF_TABLE(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER');
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER' , NUM_ERRORS => V_NUM);
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER');
END;
/
Solution

  • First of all you do not need to use NOT NULL with PRIMARY KEY clause. Get rid of such NOT NULLs.

    Let's run your statement for version 3 on DB version 12cR1 as in your case :

    Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 
Connected as hr

SQL> CREATE TABLE REDEF_TARGET (
  2    THE_KEY  INTEGER PRIMARY KEY ,
  3    THE_DATE DATE INVISIBLE NOT NULL
  4  );

Table created

SQL> CREATE TABLE REDEFINER(
  2    THE_KEY  INTEGER,
  3    THE_DATE DATE
  4  );

Table created

SQL> DECLARE
  2    V_NUM INTEGER;
  3  BEGIN
  4    DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME => USER , TNAME => 'REDEF_TARGET');
  5    DBMS_REDEFINITION.START_REDEF_TABLE(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER');
  6    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER' , NUM_ERRORS => V_NUM);
  7    DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER');
  8  END;
  9  /

ORA-01741: illegal zero-length identifier
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2502
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3803
ORA-06512: at line 7

SQL> DROP TABLE REDEF_TARGET;

Table dropped

SQL> DROP TABLE REDEFINER;

DROP TABLE REDEFINER

ORA-12083: must use DROP MATERIALIZED VIEW to drop "HR"."REDEFINER"

SQL> DROP MATERIALIZED VIEW REDEFINER;

Materialized view dropped

SQL> DROP TABLE REDEFINER;

Table dropped

SQL> CREATE TABLE REDEF_TARGET (
  2    THE_KEY  INTEGER PRIMARY KEY ,
  3    THE_DATE DATE INVISIBLE NOT NULL
  4  );

Table created

SQL> CREATE TABLE REDEFINER(
  2    THE_KEY  INTEGER,
  3    THE_DATE DATE INVISIBLE
  4  );

Table created

SQL> DECLARE
  2    V_NUM INTEGER;
  3  BEGIN
  4    DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME => USER , TNAME => 'REDEF_TARGET');
  5    DBMS_REDEFINITION.START_REDEF_TABLE(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER');
  6    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER' , NUM_ERRORS => V_NUM);
  7    DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER');
  8  END;
  9  /

ORA-01741: illegal zero-length identifier
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2502
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3803
ORA-06512: at line 7

    and DB version 12cR2 :

    Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 
Connected as hr

SQL> CREATE TABLE REDEF_TARGET (
  2    THE_KEY  INTEGER PRIMARY KEY ,
  3    THE_DATE DATE INVISIBLE NOT NULL
  4  );

Table created

SQL> CREATE TABLE REDEFINER(
  2    THE_KEY  INTEGER,
  3    THE_DATE DATE
  4  );

Table created

SQL> DECLARE
  2    V_NUM INTEGER;
  3  BEGIN
  4    DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME => USER , TNAME => 'REDEF_TARGET');
  5    DBMS_REDEFINITION.START_REDEF_TABLE(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER');
  6    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER' , NUM_ERRORS => V_NUM);
  7    DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER');
  8  END;
  9  /

ORA-042067: invalid column mapping with invisible columns on original or interim table
ORA-06512: at "SYS.DBMS_REDEFINITION", line 109
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3887
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5208
ORA-06512: at line 5

SQL> DROP TABLE REDEF_TARGET;

Table dropped

SQL> DROP TABLE REDEFINER;

Table dropped

SQL> CREATE TABLE REDEF_TARGET (
  2    THE_KEY  INTEGER PRIMARY KEY ,
  3    THE_DATE DATE INVISIBLE NOT NULL
  4  );

Table created

SQL> CREATE TABLE REDEFINER(
  2    THE_KEY  INTEGER,
  3    THE_DATE DATE INVISIBLE
  4  );

Table created

SQL> DECLARE
  2    V_NUM INTEGER;
  3  BEGIN
  4    DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME => USER , TNAME => 'REDEF_TARGET');
  5    DBMS_REDEFINITION.START_REDEF_TABLE(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER');
  6    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER' , NUM_ERRORS => V_NUM);
  7    DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME => USER , ORIG_TABLE => 'REDEF_TARGET' , INT_TABLE => 'REDEFINER');
  8  END;
  9  /

PL/SQL procedure successfully completed.

    So, Results follow :

    • Version 12c Release 1 suppresses the main problem of being ORA-042067 instead of ORA-01741. So, INVISIBLE option needed to be added for THE_DATE (DATE) column of REDEFINER table for true column mappings between original and interim tables.
    • Even if INVISIBLE option added for above mentioned column, you'll get still the same error code ( ORA-01741 ) for version R1, while you'll be successful for version R2. So, upgrade seems to be necessary.
    • By the way every time we try to drop REDEFINER table, materialized view needed to be dropped for R1, but no for R2. Interesting, might be a bug ...