OraFAQ Forum: Server Administration » SYS

Home » RDBMS Server » Server Administration » SYS_NC columns (database, particularly release 12.x)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SYS_NC columns [message #607269]

Tue, 04 February 2014 07:11

John Watson
Messages: 8963
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

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: SYS_NC columns [message #607288 is a reply to message #607272]

Tue, 04 February 2014 10:05

John Watson
Messages: 8963
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.

Report message to a moderator

Re: SYS_NC columns [message #686322 is a reply to message #607288]

Wed, 27 July 2022 10:01

John Watson
Messages: 8963
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>

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 21 22:25:34 CST 2025