数据库打开情况下删除数据文件会发生什么(unix)

创建测试表空间及表

SQL 10G>create tablespace testearse datafile '/opt/oracle/oradata/dbtest/testearse.dbf' size 1m;

Tablespace created.

SQL 10G>create table testearse(a number) tablespace testearse;

Table created.

看看有哪些进程关联到这个数据文件

SQL 10G>!
[oracle@csdba ~]$ lsof |grep testearse
oracle 4424 oracle 33uW REG 8,9 1056768 852911 /opt/oracle/oradata/dbtest/testearse.dbf
oracle 25121 oracle 15u REG 8,9 1056768 852911 /opt/oracle/oradata/dbtest/testearse.dbf

删除这个数据文件

[oracle@csdba ~]$ rm /opt/oracle/oradata/dbtest/testearse.dbf
[oracle@csdba ~]$

再观看lsof的结果，发现相关的状态已经变成deleted，但是文件还是保持打开状态

[oracle@csdba ~]$ lsof |grep testearse
oracle 4424 oracle 33uW REG 8,9 1056768 852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)
oracle 25121 oracle 15u REG 8,9 1056768 852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)

oracle 4424 1 0 Sep14 ? 00:00:35 ora_dbw0_dbtest
oracle 25121 25893 0 14:41 ? 00:00:00 oracledbtest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

SQL 10G>insert into testearse values(10);

1 row created.

SQL 10G>commit;

Commit complete.

SQL 10G>alter system checkpoint;

System altered.

SQL 10G>insert into testearse values(10);

1 row created.

SQL 10G>commit;

SQL 10G>select * from testearse;

         A
----------
        10
        10

SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';

Session altered.

SQL 10G>select * from testearse;

         A
----------
        10
        10

由于本身sqlplus的process和testearse.dbf还建立连接，所以这时仍然可以对这个表进行操作

退出sqlplus，重新开启sqlplus

SQL 10G>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining Scoring Engine options

[oracle@csdba ~]$ lsof |grep testearse
oracle 4424 oracle 33uW REG 8,9 1056768 852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)

这时只剩下dbwr进程和testearse.dbf还建立连接

[oracle@csdba bdump]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 10 15:47:58 2005

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining Scoring Engine options

SQL 10G>conn test/test
Connected.
SQL 10G>select * from testearse;

         A
----------
         10
         10

SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';

Session altered.

刷新buffer cache，写入数据文件

这时由于原来sqlplus和testearse.dbf的连接已经关闭，新连接由于数据文件被删除而无法建立

，所以这时候不能对testearse进行操作

SQL 10G>select * from testearse;
select * from testearse
*
ERROR at line 1:
ORA-01116: error in opening database file 13
ORA-01110: data file 13: '/opt/oracle/oradata/dbtest/testearse.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL 10G>alter system checkpoint;

System altered.

[oracle@csdba ~]$ lsof |grep testearse

ITPub博客

数据库打开情况下删除数据文件会发生什么(unix)

数据库打开情况下删除数据文件会发生什么(unix)