ORA-01643 etc

I have used ALTER TABLESPACE to change the status of a tablespace in several other posts. However, in some situations, you cannot change a tablespace's status. You can see what I mean in the examples below, which I tried out in an Oracle 10 database running on Linux.

You cannot make the SYSTEM, UNDO or TEMPORARY tablespaces READ ONLY:

SQL> alter tablespace system read only

  2  /
alter tablespace system read only
*
ERROR at line 1:
ORA-01643: system tablespace can not be made read only

SQL> alter tablespace undotbs1 read only
  2  /
alter tablespace undotbs1 read only
*
ERROR at line 1:
ORA-30021: Operation not allowed on undo tablespace

SQL> alter tablespace temp read only
  2  /
alter tablespace temp read only
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY
TABLESPACE

You cannot make a tablespace READ WRITE if is already ONLINE (READ WRITE and ONLINE have the same meaning):

SQL> select tablespace_name, status
  2  from dba_tablespaces
  3  /

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE

SQL> alter tablespace users read write
  2  /
alter tablespace users read write
*
ERROR at line 1:
ORA-01646: tablespace 'USERS' is not read only -
cannot make read write

SQL> alter tablespace users read only
  2  /

Tablespace altered.

SQL>

... and once a tablespace is READ ONLY, you cannot make it READ ONLY again!

SQL> alter tablespace users read only
  2  /
alter tablespace users read only
*
ERROR at line 1:
ORA-01644: tablespace 'USERS' is already read only

SQL>

ORA-00376

This was tested on an Oracle 10 database running on Linux. If you get an ORA-00376, your DBA may have taken a tablespace offline. If that is the case, the problem should go away once the tablespace is back on line again:

SQL> create table andrew
  2  tablespace users
  3  as select * from dba_tables
  4  /

Table created.

SQL> alter tablespace users offline
  2  /

Tablespace altered.

SQL> select count(*) from andrew
  2  /
select count(*) from andrew
                     *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/home/oracle/DB1/users01.dbf'

SQL> alter tablespace users online
  2  /

Tablespace altered.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
      1521

SQL>

P.S. I forgot to add that you can check the status of a tablespace (and change its status if you wish) as shown below:

SQL> select status from dba_tablespaces

  2  where tablespace_name = 'USER_DATA';

STATUS

---------

ONLINE

SQL> alter tablespace user_data offline;

Tablespace altered.

SQL> select status from dba_tablespaces

  2  where tablespace_name = 'USER_DATA';

STATUS

---------

OFFLINE

SQL> alter tablespace user_data online;

Tablespace altered.

SQL> select status from dba_tablespaces

  2  where tablespace_name = 'USER_DATA';

STATUS

---------

ONLINE

SQL>

How to Move an Oracle Datafile

This worked example shows how to move a datafile. First create a tablespace:

SQL> create tablespace andrew

  2  datafile '/usr/users/oracle/andrew/file1'

  3  size 1m;

 

Tablespace created.

 

SQL>

Create a table in the tablespace then describe it and count the rows. This is only to check that the contents of the tablespace are OK after the datafile has been moved:

 

SQL> create table andrews_table

  2  tablespace andrew

  3  as select * from dba_objects;

 

Table created.

 

SQL> desc andrews_table

Name                       Null?    Type

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

OWNER                               VARCHAR2(30)

OBJECT_NAME                         VARCHAR2(128)

SUBOBJECT_NAME                      VARCHAR2(30)

OBJECT_ID                           NUMBER

DATA_OBJECT_ID                      NUMBER

OBJECT_TYPE                         VARCHAR2(18)

CREATED                             DATE

LAST_DDL_TIME                       DATE

TIMESTAMP                           VARCHAR2(19)

STATUS                              VARCHAR2(7)

TEMPORARY                           VARCHAR2(1)

GENERATED                           VARCHAR2(1)

SECONDARY                           VARCHAR2(1)

 

SQL> select count(*) from andrews_table;

 

  COUNT(*)

----------

      7933

 

SQL>

Note the name(s) of the tablespace's datafile(s).

SQL> select file_name from dba_data_files

  2  where tablespace_name = 'ANDREW';

 

FILE_NAME

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

/usr/users/oracle/andrew/file1

SQL>

Take the tablespace offline:

 

SQL> alter tablespace andrew offline;

 

Tablespace altered.

 

SQL>

Copy the file to its new location in the operating system. Normally you would be doing this to move the datafile(s) to a different disk but this is only an example on a test database so I will just copy the file within the same directory: 

UNIX /usr/users/oracle/andrew >cp file1 file2

UNIX /usr/users/oracle/andrew >

Rename the datafile at the Oracle level in SQL*Plus:

 

  1  alter tablespace andrew rename datafile

  2  '/usr/users/oracle/andrew/file1'

  3  to

  4* '/usr/users/oracle/andrew/file2'

SQL> /

 

Tablespace altered.

SQL>

Put the tablespace online again:

 

SQL> alter tablespace andrew online;

 

Tablespace altered.

 

SQL>

See that the new file name appears in dba_data_files: 

SQL> l

  1  select file_name from dba_data_files

  2* where tablespace_name = 'ANDREW'

SQL> /

 

FILE_NAME

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

/usr/users/oracle/andrew/file2

 

SQL>

Check that the table created above is still in the tablespace, describe it and count the rows again then compare the figures with those above:

SQL> select table_name from dba_tables

  2  where tablespace_name = 'ANDREW';

 

TABLE_NAME

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

ANDREWS_TABLE

 

SQL> desc andrews_table

Name                       Null?    Type

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

OWNER                               VARCHAR2(30)

OBJECT_NAME                         VARCHAR2(128)

SUBOBJECT_NAME                      VARCHAR2(30)

OBJECT_ID                           NUMBER

DATA_OBJECT_ID                      NUMBER

OBJECT_TYPE                         VARCHAR2(18)

CREATED                             DATE

LAST_DDL_TIME                       DATE

TIMESTAMP                           VARCHAR2(19)

STATUS                              VARCHAR2(7)

TEMPORARY                           VARCHAR2(1)

GENERATED                           VARCHAR2(1)

SECONDARY                           VARCHAR2(1)

 

SQL> select count(*) from andrews_table;

 

  COUNT(*)

----------

      7933

 

SQL>

You cannot use this method for system, undo or temporary tablespaces as you cannot take them offline. I will cover them in a future post:

SQL> alter tablespace system offline;

alter tablespace system offline

*

ERROR at line 1:

ORA-01541: system tablespace cannot be brought

offline; shut down if necessary

 

SQL> alter tablespace undo_1 offline;

alter tablespace undo_1 offline

*

ERROR at line 1:

ORA-30042: Cannot offline the undo tablespace

 

SQL> alter tablespace temp offline;

alter tablespace temp offline

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY

TABLESPACE

 

SQL>