PROBLEM:

While trying to drop a column from a table, owned by SYS, got below error.(ORA-12988)

SQL> show user
USER is "SYS"


SQL>  alter table test3 drop ( CREATED);
alter table test3 drop ( CREATED)
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

SOLUTION:

oerr ora 12988
12988, 00000, “cannot drop column from table owned by SYS”
// *Cause: An attempt was made to drop a column from a system table.
// *Action: This action is not allowed

We can’t drop a column from a table owned by SYS user. There is an alternative method to achieve this.

1. Create the table under different schema(using CTAS) let’s say DBACLASS user.

SQL> create table dbaclass.test4 as select * from test3;

Table created.

2. Now drop the column from the newly created table

SQL>  alter table dbaclass.test4 drop ( CREATED);

Table altered.

3. Now drop the original table owned by SYS

SQL> show user
USER is "SYS"

SQL> drop table test3;

Table dropped.

4. Again create the table using CTAS from the dbaclass.test4 table.

SQL> create table test3 as select * from dbaclass.test4;

Table created.

In this case, if any indexes were present, then those need to be recreated again.