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
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.