Tuesday, April 17, 2012

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>

No comments: