ORA-12985

If you try to drop a column from a table in a read only tablespace, you get an ORA-12985. If you really need to drop the column, you must put the tablespace back into read write mode first. You can see what I mean in the example below, which I tested in an Oracle 9 database:

 

SQL> create tablespace andrews_ts

  2  datafile '/tmp/andrew.dbf'

  3  size 1m

  4  /

 

Tablespace created.

 

SQL> grant dba to andrew identified by reid

  2  /

 

Grant succeeded.

 

SQL> alter user andrew default tablespace andrews_ts

  2  /

 

User altered.

 

SQL> conn andrew/reid

Connected.

SQL> create table tab1

  2  (col1 number, col2 number)

 3  /

 

Table created.

 

SQL> alter tablespace andrews_ts read only

  2  /

 

Tablespace altered.

 

SQL> alter table tab1 drop column col2

  2  /

alter table tab1 drop column col2

*

ERROR at line 1:

ORA-12985: tablespace 'ANDREWS_TS' is read only,

cannot drop column

 

SQL> alter tablespace andrews_ts read write

  2  /

 

Tablespace altered.

 

SQL> alter table tab1 drop column col2

  2  /

 

Table altered.

 

SQL> drop tablespace andrews_ts

  2  including contents and datafiles

  3  /

 

Tablespace dropped.

 

SQL>