Friday, April 04, 2014

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>

No comments: