Search code examples
oracle-databaseplsqlprivileges

ORACLE Database - Why SYS User can drop tables but not columns?

I am learning PLsql with Oracle 19c. I was wondering - why sys user can drop tables but not specific columns within a table? I found this workaround, where you create a copy of the table for a specific SCHEMA, drop the column in this newly created table, drop the original table, and finally create another copy of the (copied)-table without SCHEMA/USER ROLE.

Is there another, let's say a smoother way, to accomplish this? Or is there any specific reason Oracle implemented it this way?

Thanks.

SQL> SHOW USER;
USER is "SYS"
________________________________________

SQL> ALTER TABLE employees DROP (gender);
ALTER TABLE employees DROP (gender)
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

_______________________________________

SQL> DROP TABLE employees;
Table dropped.
Solution

  • "To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema."

    https://docs.oracle.com/database/121/ADMQS/GUID-CF1CD853-AF15-41EC-BC80-61918C73FDB5.htm#ADMQS12003