0

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?

2

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.

  • great, thank you. how do i get the real hidden column name? any way to select from "SYS_"? – olekb Jan 25 '18 at 16:30
  • @olekb The moment you set it to unused, it's renamed to a system-generated name, and you lost that information. In my example, it is still possible to retreive the column name with flashback query, but your columns were set to unused almost 4 years ago based on the timestamp in the column name, so that is not possible in your case. – Balazs Papp Jan 25 '18 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.