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

When attempting to delete a column from a table owned by a SYS user, the error “ORA-12988: cannot drop column from table owned by SYS” occurs. This is because SYS does not allow column deletion operations on tables owned by its users.

The way to perform this operation is to create the same table under a different schema using CTAS, delete the relevant column, drop the table from the SYS schema, and then, again using CTAS, create a new table under the SYS schema from the table in the other schema.

[oracle@orcldb ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 6 12:13:20 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

 
SQL> CREATE TABLE TEST
(
AD         VARCHAR2(10 BYTE),
SOYAD      VARCHAR2(10 BYTE),
KURUM      VARCHAR2(10 BYTE)
);

Table created.

SQL> insert into TEST values ('AHMET','DURUÖZ','İNTERİVA');

1 row created.

SQL> commit;

Commit complete.

SQL> ALTER TABLE TEST DROP COLUMN KURUM;
ALTER TABLE TEST DROP COLUMN KURUM
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

In this case, you can delete the relevant column as shown below.

SQL> create table ADURUOZ.TEST as select * from TEST;

Table created.

SQL> ALTER TABLE ADURUOZ.TEST DROP COLUMN KURUM;

Table altered.

SQL> drop table TEST purge;

Table dropped.

SQL> create table TEST as select * from ADURUOZ.TEST;

Table created.

SQL> desc TEST;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 AD						    VARCHAR2(10)
 SOYAD						    VARCHAR2(10)

It is not recommended to edit any SYS’s objects. But if you need to edit , you use the below method.