title: ORA-01741 for DBMS_REDEFINITION with invisible fields and implicit constraints
body: <p>I recently noticed an odd failure pattern recently in one database (12cR1) when using <code>DBMS_REDEFINITION</code>.  <code>CAN_REDEF_TABLE</code> completes fine, as does <code>START_REDEF_TABLE</code>, but <code>COPY_TABLE_DEPENDENTS</code> fails with a bewildering: </p>
  
  <blockquote>
    <p>ORA-01741: illegal zero-length identifier</p>
  </blockquote>
  
  <p>After some debugging, the exception appears to be related to the <code>ORIG_TABLE</code> carrying <strong>both</strong> <code>INVISIBLE</code> column(s) <strong>and</strong> 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 <code>INVISIBLE</code> called out in the <a href="https://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPLS042" rel="nofollow noreferrer">docs</a>.  </p>
  
  <p>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?  </p>
  
  <p>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.  </p>
  
  <p>Thanks</p>
  
  <p>Example:
  Below are three versions of the same <code>ORIG_TABLE</code> for redefinition.  The first two both undergo the far-below redefinition ok with the given <code>INT_TABLE</code>, but the third throws the <code>ORA-01741</code> during <code>COPY_TABLE_DEPENDENTS</code>.  </p>
  
  <p>Version 1: All columns visible, implicit system-generated constraints:</p>
  
  <pre><code>CREATE TABLE REDEF_TARGET (
    THE_KEY  INTEGER NOT NULL PRIMARY KEY ,
    THE_DATE DATE    NOT NULL
  );
  </code></pre>
  
  <p>Version 2: INVISIBLE column present, explicit constraint (given an absurd name here to poke at if <code>DBMS_REDEFINITION</code> is instrumenting existing names)</p>
  
  <pre><code>CREATE TABLE REDEF_TARGET (
    THE_KEY  INTEGER NOT NULL PRIMARY KEY ,
    THE_DATE DATE INVISIBLE ,
    CONSTRAINT SYS_C02583271 CHECK (THE_DATE IS NOT NULL)
  );
  </code></pre>
  
  <p>Version 3:  <code>INVISIBLE</code> column and implicit constraint both present</p>
  
  <pre><code>CREATE TABLE REDEF_TARGET (
    THE_KEY  INTEGER NOT NULL PRIMARY KEY ,
    THE_DATE DATE INVISIBLE NOT NULL
  );
  </code></pre>
  
  <p>Running either of the first against the below will work, while the third will fail during copy-deps.</p>
  
  <pre><code>CREATE TABLE REDEFINER (
    THE_KEY  INTEGER ,
    THE_DATE DATE
  );
  
  DECLARE
    V_NUM INTEGER;
  BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME =&gt; USER , TNAME =&gt; 'REDEF_TARGET');
    DBMS_REDEFINITION.START_REDEF_TABLE(UNAME =&gt; USER , ORIG_TABLE =&gt; 'REDEF_TARGET' , INT_TABLE =&gt; 'REDEFINER');
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME =&gt; USER , ORIG_TABLE =&gt; 'REDEF_TARGET' , INT_TABLE =&gt; 'REDEFINER' , NUM_ERRORS =&gt; V_NUM);
    DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME =&gt; USER , ORIG_TABLE =&gt; 'REDEF_TARGET' , INT_TABLE =&gt; 'REDEFINER');
  END;
  /
  </code></pre>