Home » RDBMS Server » Server Administration » SYS_NC columns (database, particularly release 12.x)
SYS_NC columns [message #607269] Tue, 04 February 2014 07:11 Go to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Does anyone have a description of what SYS_NC columns are used for? When/why they are created? Looking at MOS they date back many releases, I've have just been looking at one that 12.1 creates when I enable ROW ARCHIVAL:
orclz>
orclz> create table t1(c1 number);

Table created.

orclz> insert into t1 values(1);

1 row created.

orclz> select column_name,hidden_column,data_default,data_type from user_tab_cols where table_name='T1';

COLUMN_NAME                      HID DATA_DEFAULT         DATA_TYPE
-------------------------------- --- -------------------- ---------------
C1                               NO                       NUMBER

orclz> alter table t1 row archival;

Table altered.

orclz> select column_name,hidden_column,data_default,data_type from user_tab_cols where table_name='T1';

COLUMN_NAME                      HID DATA_DEFAULT         DATA_TYPE
-------------------------------- --- -------------------- ---------------
C1                               NO                       NUMBER
SYS_NC00002$                     YES                      RAW
ORA_ARCHIVE_STATE                YES 0                    VARCHAR2

orclz> insert into t1(SYS_NC00002$) values (1);
insert into t1(SYS_NC00002$) values (1)
               *
ERROR at line 1:
ORA-14148: DML and DDL operations are not directly allowed on the guard-column.


orclz> update t1 set SYS_NC00002$=1;
update t1 set SYS_NC00002$=1
                           *
ERROR at line 1:
ORA-22839: Direct updates on SYS_NC columns are disallowed


orclz>
The ORA_ARCHIVE_STATE column is reasonably documented, but I can't find anything about the SYS_NC column or those error codes.
Any insight will be gratefully received.

(ps - perhaps I should have "The Reverse Engineer" as my epitaph)

[Updated on: Tue, 04 February 2014 07:38]

Report message to a moderator

Re: SYS_NC columns [message #607272 is a reply to message #607269] Tue, 04 February 2014 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

These columns are internally built to implement various features, for instance FBI:
SQL> create table t (v varchar2(10));

Table created.

SQL> select column_name,hidden_column,data_default,data_type from user_tab_cols where table_name='T';
COLUMN_NAME                    HID DATA_DEFAULT DATA_TYPE
------------------------------ --- ------------ ------------
V                              NO               VARCHAR2

1 row selected.

SQL> create index t_i on t(upper(v));

Index created.

SQL> select column_name,hidden_column,data_default,data_type from user_tab_cols where table_name='T';
COLUMN_NAME                    HID DATA_DEFAULT DATA_TYPE
------------------------------ --- ------------ ------------
V                              NO               VARCHAR2
SYS_NC00002$                   YES UPPER("V")   VARCHAR2

2 rows selected.

SQL> insert into t (SYS_NC00002$) values (1);
insert into t (SYS_NC00002$) values (1)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns


SQL> update t set SYS_NC00002$=1;
update t set SYS_NC00002$=1
          *
ERROR at line 1:
ORA-22839: Direct updates on SYS_NC columns are disallowed

As this is internal this is not documented but I bet their behaviour depends on the underlying feature.

Re: SYS_NC columns [message #607288 is a reply to message #607272] Tue, 04 February 2014 10:05 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
THank you for replying. For what it's worth, these are some system generated columns that I am aware of:
orclz> select column_name,virtual_column,hidden_column,data_default from user_tab_cols where table_name='EMP';

COLUMN_NAME                      VIR HID DATA_DEFAULT
-------------------------------- --- --- -----------------------------------
ORA_ARCHIVE_STATE                NO  YES 0
SYS_NC00009$                     NO  YES
DEPTNO                           NO  NO
COMM                             NO  NO
SAL                              NO  NO
HIREDATE                         NO  NO
MGR                              NO  NO
JOB                              NO  NO
ENAME                            NO  NO
EMPNO                            NO  NO
SYS_NC00012$                     YES YES UPPER("ENAME")
SYS_STUQ4GQC$8MAME#RCVUHISN5M5   YES YES SYS_OP_COMBINED_HASH("SAL","COMM")

12 rows selected.

orclz>
The last column is for extended statistics. The previous column is for a function based index. The two at the top are for in-database row archiving, and I do not yet know the purpose of the SYS_NC column there. I remember that there is another one set up by Oracle Label Security, but I don't have an example to hand.
Re: SYS_NC columns [message #686322 is a reply to message #607288] Wed, 27 July 2022 10:01 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Just found another one, object tables:
orclz> create type t1 as object(n1 number,v1 varchar2(10));
  2  /

Type created.

orclz> create table objtab(c1 t1);

Table created.

orclz> select column_name,hidden_column,virtual_column,data_type from user_Tab_cols where table_name='OBJTAB';

COLUMN_NAME                    HID VIR DATA_TYPE
------------------------------ --- --- --------------------
C1                             NO  NO  T1
SYS_NC00002$                   YES NO  NUMBER
SYS_NC00003$                   YES NO  VARCHAR2

orclz>
Previous Topic: Parse statistic values don't seem to add up
Next Topic: Trace Files purging through ADRCI
Goto Forum:
  


Current Time: Thu Mar 28 13:28:19 CDT 2024