Oracle 11g--測試temporary datafile被刪除

找出TEMPORARY的Datafile位置
select a.file#,a.ts#,a.status,b.name,a.name
    from v$tempfile a,
              v$tablespace b
    where a.ts#=b.ts#
    and b.name =  
        (select property_value
             from database_properties
             where property_name='DEFAULT_TEMP_TABLESPACE') ;

關閉資料庫
SQL> shutdown abort ;
ORACLE instance shut down.

到作業系統底下將datafile更名
$ mv temp01.dbf temp01.dbf.bk

重新開啟資料庫
SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size    1344840 bytes
Variable Size  394267320 bytes
Database Buffers   54525952 bytes
Redo Buffers    6008832 bytes
Database mounted.
Database opened.
SQL>

發現可以正常啟動資料庫，檢查一下實際的Datafile，Oracle新增了一個2G的temp01.dbf
$ ls -l temp*
-rw-rw---- 1 oracle oracle   20979712 May 11 19:48 temp01.dbf
-rw-r----- 1 oracle oracle  165683200 May 11 19:34 temp01.dbf.bk

先找出11g alert log的位置
SQL>select value from v$diag_info where name ='Diag Trace';

VALUES
-----------------------------------------------------------
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace


檢查alert log的訊息
$ more /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
可以發現Oracle自動重建了tempfile
Re-creating tempfile /home/oracle/app/oracle/oradata/orcl/temp01.dbf