Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Offline status in dictionary
I too thought that if there is datafile corruption/missing the
tablespace would be put into offline mode by oracle. But when I tried
simulating it, the status in v$datafile or dba_tablespaces never
switched to offline. Database version is 9.2.0.7.
Here is an example:
SQL> create tablespace test datafile
'/opt/apps/oracle/oradata/neo/t.dbf' size 10M autoextend on extent
management local segment space management auto;
Tablespace created.
SQL> !ls -l /opt/apps/oracle/oradata/neo/t.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 14 13:35 /opt/apps/oracle/oradata/neo/t.dbf
SQL> create table test_table tablespace test as select * from dba_objects;
Table created.
SQL> select distinct tablespace_name from dba_segments where segment_name='TEST_TABLE';
TABLESPACE_NAME
TEST SQL> !rm /opt/apps/oracle/oradata/neo/t.dbf
SQL> SQL> insert into TEST_TABLE select * from dba_objects;
insert into TEST_TABLE select * from dba_objects
*
ERROR at line 1:
ORA-01565: error in identifying file
'/opt/apps/oracle/oradata/neo/t.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> select file_id,
SQL> SQL> select distinct status from v$datafile;
STATUS
ONLINE SYSTEM SQL> select distinct status from dba_data_files;
STATUS
AVAILABLE SQL> select distinct status from dba_tablespaces;
STATUS
ONLINE SQL> Thanks.
From: Andrew Kerber [mailto:andrew.kerber_at_gmail.com]
Sent: Saturday, July 14, 2007 1:42 AM
To: Ankur Godambe
Subject: Re: Offline status in dictionary
It can happen when the file becomes unusable, for example someone deletes it or it gets corrupted.
On 7/13/07, Ankur Godambe <agodambe_at_ketera.com> wrote:
Hi,
Apart from explicitly putting datafile or tabespace in offline mode though alter database..... or alter tablespace..... command , under what circumstances would STATUS column in v$datafile and dba_tablespaces change to OFFLINE?
Thanks.
-- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-lReceived on Sat Jul 14 2007 - 03:53:00 CDT