Troubleshoot fix resolve ORA-12988: cannot drop column from table owned by SYS. How to drop a column of a table owned by SYS

advertisements

_____________________________________________________________________________________________________________________

Example

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> show user

USER is "SYS"

SQL> create table example as select * from scott.example;

Table created.

SQL> desc example

 Name                                      Null?    Type

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

 ID                                        NOT NULL NUMBER(10)

 UID                                                VARCHAR2(40)

 PIX                                                VARCHAR2(40)

 FNAME                                              VARCHAR2(100)

 MNAME                                              VARCHAR2(100)

 LNAME                                              VARCHAR2(100)

 SFIX                                               VARCHAR2(40)

 JTITLE                                             VARCHAR2(100)

 COMNAME                                            VARCHAR2(40)

 ADD1                                               VARCHAR2(50)

 ADD2                                               VARCHAR2(50)

 ADD3                                               VARCHAR2(50)

 CTY                                                VARCHAR2(30)

 STAT                                               VARCHAR2(20)

 POSTCODE                                           VARCHAR2(10)

 COUNTY                                             VARCHAR2(40)

 COUNTRY                                            VARCHAR2(40)

 PH_NUM                                             VARCHAR2(20)

 FX_NUM                                             VARCHAR2(15)

SQL> alter table example drop column fx_num;

alter table example drop column fx_num

*

ERROR at line 1:

ORA-12988: cannot drop column from table owned by SYS

SQL> alter table example set unused(fx_num);

alter table example set unused(fx_num)

*

ERROR at line 1:

ORA-12988: cannot drop column from table owned by SYS

Error Description:

You cannot drop a column from table which is owned by the sys user even if it is created by manually or it is data dictionary table. If you try to drop a column from sys owned table you will get ORA-12988: cannot drop column from table owned by SYS error message. Also you cannot make the column as used.

There is a workaround to drop column of sys owned table.  

1. Create the table in different schema using CTAS (Create Table As Select) method
2. Drop the column from the newly created table
3. Drop the table from sys schema
4. Recreate the table in sys schema from the other schema.

_____________________________________________________________________________________________________________________

Website Stats