RE: Offline status in dictionary
- From: "Ankur Godambe" <agodambe@xxxxxxxxxx>
- To: "Andrew Kerber" <andrew.kerber@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 14 Jul 2007 01:53:00 -0700
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@xxxxxxxxx]
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@xxxxxxxxxx> 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.'
Other related posts:
