2 Replies Latest reply: Apr 25, 2014 10:34 AM by rp0428 RSS

    12c Possible Bug when using ALTER TABLE myTable ROW ARCHIVAL - unexpected, non-hidden system column created

    rp0428

      Jocelyn Simard posted a question earlier today in this forum asking about a system generated column (SYS_NC00004$)when using ALTER TABLE. The column is a VIRTUAL, but NOT hidden RAW (126).

      https://community.oracle.com/thread/3551629

       

      That is the same column name used when a functional index is created. For that use case the datatype is typically NUMBER and the column is both HIDDEN and VIRTUAL. See Richard Foote's blo where he says that the column is used to store optimizer statistics:

      https://richardfoote.wordpress.com/category/function-based-indexes/

       

      Simard's use case (see link) first creates an ordinary, simple, table and then enables archiving using :

      ALTER TABLE myTable ROW ARCHIVAL

      That ALTER statement creates the expected ORA_ARCHIVE_STATE column as VARCHAR2(4000) and the column is HIDDEN and NOT VIRTUAL. The statement also creates a column named SYS_NC00004$ as RAW(126) that is also HIDDEN and NOT VIRTUAL.

       

      Simard ask what that SYS column was.

       

      I replied in the other thread that it may be a bug  because simple testing showed that the SYS column is NOT always created. LImited testing shows that the SYS_NC00004$ column is ONLY created if you use to ALTER statement to enable row archiving on an existing (even if empty) table.

       

      That column is NOT created if you create the table with archiving. Just as interesting is that a CTAS using ROW ARCHIVAL actually fails.

       

      -- succeeds with NO SYS_NC00004$ column
      create table tmp_objects1 (owner varchar2(128), object_name varchar2(128), created date) row archival

       

      -- succeeds but creates a SYS_NC00004$ column that is NOT virtual

      create table tmp_objects2 (owner varchar2(128), object_name varchar2(128), created date)

      alter table tmp_objects2 row archival

       

      -- fails with 'ORA-01722: invalid number'
      create table tmp_objects3 row archival as select owner, object_name, created from all_objects

      Simard's question was what the column is.

       

      My question is whether this is a bug and whether anyone can reproduce this on other platforms.

      BANNER,CON_ID

      Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production,0

      PL/SQL Release 12.1.0.1.0 - Production,0

      CORE 12.1.0.1.0 Production,0

      TNS for Linux: Version 12.1.0.1.0 - Production,0

      NLSRTL Version 12.1.0.1.0 - Production,0

        • 1. Re: 12c Possible Bug when using ALTER TABLE myTable ROW ARCHIVAL - unexpected, non-hidden system column created
          JohnWatson

          I have slightly different result on Windows,

           

          orclz> create table tmp_objects1 (owner varchar2(128), object_name varchar2(128), created date) row archival ;

           

          Table created.

           

          orclz> select column_name,hidden_column,virtual_column,data_default from user_tab_cols where table_name='TMP_OBJECTS1';

           

          COLUMN_NAME                      HID VIR DATA_DEFAULT

          -------------------------------- --- --- ----------------------------------------

          CREATED                          NO  NO

          OBJECT_NAME                      NO  NO

          OWNER                            NO  NO

          ORA_ARCHIVE_STATE                YES NO  0

           

          orclz> create table tmp_objects2 (owner varchar2(128), object_name varchar2(128), created date);

           

          Table created.

           

          orclz> alter table tmp_objects2 row archival;

           

          Table altered.

           

          orclz> select column_name,hidden_column,virtual_column,data_default from user_tab_cols where table_name='TMP_OBJECTS2';

           

          COLUMN_NAME                      HID VIR DATA_DEFAULT

          -------------------------------- --- --- ----------------------------------------

          ORA_ARCHIVE_STATE                YES NO  0

          SYS_NC00004$                     YES NO

          CREATED                          NO  NO

          OBJECT_NAME                      NO  NO

          OWNER                            NO  NO

           

          orclz> create table tmp_objects3 row archival as select owner, object_name, created from all_objects;

          create table tmp_objects3 row archival as select owner, object_name, created from all_objects

          *

          ERROR at line 1:

          ORA-01722: invalid number

           

           

          orclz>

          orclz>

          orclz> select * from v$version;

           

          BANNER                                                                               CON_ID

          -------------------------------------------------------------------------------- ----------

          Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

          PL/SQL Release 12.1.0.1.0 - Production                                                    0

          CORE    12.1.0.1.0      Production                                                                0

          TNS for 64-bit Windows: Version 12.1.0.1.0 - Production                                   0

          NLSRTL Version 12.1.0.1.0 - Production                                                    0

           

          orclz>

           

           

          I tried to work this out some time ago here, OraFAQ Forum: Server Administration » SYS_NC columns

          but didn't get any further.

          • 2. Re: 12c Possible Bug when using ALTER TABLE myTable ROW ARCHIVAL - unexpected, non-hidden system column created
            rp0428
            I have slightly different result on Windows,

            That looks to be the same to me.