Wednesday, March 12, 2014

ORA-02495

This was tested on Oracle 11.2. If you try to resize a datafile in a read only tablespace, you get an ORA-02495. Changing the tablespace to read write makes the problem go away:
 
SQL> create tablespace andrew
  2  datafile '/tmp/andrew.dbf'
  3  size 100m
  4  /
 
Tablespace created.
 
SQL> alter tablespace andrew read only
  2  /
 
Tablespace altered.
 
SQL> alter database datafile '/tmp/andrew.dbf'
  2  resize 200m
  3  /
alter database datafile '/tmp/andrew.dbf'
*
ERROR at line 1:
ORA-02495: cannot resize file /tmp/andrew.dbf,
tablespace ANDREW is read only
 
SQL> alter tablespace andrew read write
  2  /
 
Tablespace altered.
 
SQL> alter database datafile '/tmp/andrew.dbf'
  2  resize 200m
  3  /
 
Database altered.
 
SQL>

No comments: