Sunday, December 19, 2010

ORA-01647

I went on a course once. I think it was an Oracle 8 new features one in the late 90's but I could be wrong. The lecturer asked us if you could drop a table in a READ ONLY tablespace. For some reason I was the only person who knew the answer. What I didn't tell him was that I did not understand the answer. In fact, I don't understand it even now but never mind. Here is a tablespace in READ WRITE mode:

SQL> select status from dba_tablespaces
  2  where tablespace_name = 'USERS'
  3  /

STATUS
---------
ONLINE

SQL>


I created a table in it:

SQL> create table andrew1 (one_col number)
  2  tablespace users
  3  /

Table created.

SQL>

I made the tablespace READ ONLY:
 
SQL> alter tablespace users read only
  2  /

Tablespace altered.

SQL> select status from dba_tablespaces
  2  where tablespace_name = 'USERS'
  3  /

STATUS
---------
READ ONLY

SQL>

I tried to create a new table in this tablespace:
 
SQL> create table andrew2 (one_col number)
  2  tablespace users
  3  /
create table andrew2 (one_col number)
*
ERROR at line 1:
ORA-01647: tablespace 'USERS' is read only, cannot allocate
space in it

SQL>

That did not work, which seems reasonable. Then I tried to drop the table I created earlier:
 

SQL> drop table andrew1
  2  /

Table dropped.

SQL>

That worked, which has always seemed strange to me. Finally I put the tablespace back into READ WRITE mode for future use:
 

SQL> alter tablespace users read write
  2  /

Tablespace altered.

SQL> select status from dba_tablespaces
  2  where tablespace_name = 'USERS'
  3  /

STATUS
---------
ONLINE

SQL>

No comments: