Oracle 11g R2 – Understanding SYS_C00054_14031410:34:03$ Column

oracleoracle-11g-r2

Tried to do partition exchange and it failed because of 2 extra SYS_* columns in the table:

select NAME from SYS.COL$ WHERE obj# =41900658 and name like 'SYS_%';

SYS_C00054_14031410:34:03$
SYS_C00055_14031410:34:03$

ALL_TAB_COLUMNS show nothing on these

What are these? and why CTAS didn't pick them up?

If extended stats – how do i drop them?

Best Answer

Unused columns.

SQL> create table t1 as select * from dba_roles;

Table created.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLE                                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                                  VARCHAR2(8)
 AUTHENTICATION_TYPE                                VARCHAR2(11)

SQL> alter table t1 set unused (AUTHENTICATION_TYPE);

Table altered.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLE                                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                                  VARCHAR2(8)

SQL> select column_name from user_tab_cols where table_name = 'T1';

COLUMN_NAME
------------------------------
SYS_C00003_18012517:25:39$
PASSWORD_REQUIRED
ROLE

SQL> alter table t1 drop unused columns;

Table altered.

SQL> select column_name from user_tab_cols where table_name = 'T1';

COLUMN_NAME
------------------------------
PASSWORD_REQUIRED
ROLE

Edit:

Extra information: I used *_TAB_COLS, because *_TAB_COLUMNS does not list such columns.

Related Question