PROBLEM:

While dropping a column , got below error.

SQL> alter table dbaclass.test89 drop (OWNER,OBJECT_NAME);
alter table dbaclass.test89 drop (OWNER,OBJECT_NAME)
*
ERROR at line 1:
ORA-12985: tablespace 'USERS' is read only, cannot drop column

SOLUTION:

The object which is trying to drop belongs to a tablespace is in read only mode.

1. Find the tablespace of that table

SQL> select tablespace_name from dba_segments where segment_name='TEST89';

TABLESPACE_NAME
------------------------------
USERS

2. Find the status of the tablespace

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

3. To be able to drop the column, we need to make the tablespace read write.

SQL> ALTER TABLESPACE users READ WRITE;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          ONLINE

SQL> alter table dbaclass.test89 drop (OWNER,OBJECT_NAME);

Table altered.