ORA-12985 Tablespace Users is Read Only, Cannot Drop Column

 ORA-12985 Tablespace Users is Read Only, Cannot Drop Column 

Introduction

If you try to Drop a column from a table in a read only ,you got an error like this ORA-12985 Tablespace Users is Read Only, Cannot Drop Column if you really drop the column ,you must put the tablespace into READ WRITE mode  

Probem:

While dropping a column, in a paticular owner and particlar object below got an errorORA-12985 Tablespace Users is Read Only, Cannot Drop Column ,while drop a column in tablespace

 SQL> alter table chaitanyadba.master03 drop (OWNER,OBJECT_NAME);

alter table chaitanyadba.master03 drop (OWNER,OBJECT_NAME)

*

ERROR at line 1:

ORA-12985 Tablespace Users is Read Only, Cannot Drop Column 

Now let us Start the process to find out the Solution

The Object which is trying to drop belongs to a table sapce is in read only mode

Step 1 : Find the tablespace of that particular table

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

 

TABLESPACE_NAME

------------------------------

USERS

Step 2 : Find the staus of the tablespace

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

 

TABLESPACE_NAME                STATUS

------------------------------ --------------------

USERS                                       READ ONLY

 

Step 3: Inorder to Drop a column ,We need to make the tablespace READ and WRITE mode only  

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 chaitanyadba.master03 drop (OWNER,OBJECT_NAME);

 

Table altered.

Note : Info on ORA-12985 Tablespace Users is Read Only, Cannot Drop Column  it may differin your environment like production,testing,development and naming conventions etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME