RE: Offline status in dictionary

From: Ankur Godambe <agodambe_at_ketera.com>
Date: Sat, 14 Jul 2007 01:53:00 -0700
Message-ID: <F2B0430DD90A1C4383C1F887FBAC45DB0407EA5D@kusex2.ketera.com>

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-l

Received on Sat Jul 14 2007 - 03:53:00 CDT