10g的temp表空间会自动创建

前几天发现一个有趣的问题！

10g的库，如果你的temp文件丢失的话，会自动创建！

试验如下：

idle> select tablespace_name,file_name from dba_data_files
  2  union all
  3  select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME FILE_NAME
--------------- -----------------------------------------------
USERS           /u01/oracle/oradata/TenG/TenG/users01.dbf
SYSAUX          /u01/oracle/oradata/TenG/TenG/sysaux01.dbf
DEMO1           /u01/oracle/oradata/TenG/TenG/demo02.dbf
SYSTEM          /u01/oracle/oradata/TenG/TenG/system01.dbf
DEMO            /u01/oracle/oradata/TenG/TenG/demo01.dbf
TRANS_TEST      /u01/oracle/oradata/TenG/TenG/trans_test01.bdf
UNDOTBS01       /u01/oracle/oradata/TenG/TenG/undotbs01.dbf
TEMP            /u01/oracle/oradata/TenG/TenG/temp01.dbf

8 rows selected.

idle> !
[oracle@node2 ~]$ rm /u01/oracle/oradata/TenG/TenG/temp01.dbf
[oracle@node2 ~]$ cd /u01/oracle/oradata/TenG/TenG/
[oracle@node2 TenG]$ ll
total 1234648
-rw-r-----  1 oracle oinstall   7159808 Aug  5 05:37 control01.ctl
-rw-r-----  1 oracle oinstall   7159808 Aug  5 05:37 control02.ctl
-rw-r-----  1 oracle oinstall   7159808 Aug  5 05:37 control03.ctl
-rw-r-----  1 oracle oinstall 157294592 Aug  5 05:36 demo01.dbf
-rw-r-----  1 oracle oinstall 150872064 Aug  5 05:30 demo02.dbf
-rw-r-----  1 oracle oinstall  52429312 Aug  5 05:30 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Aug  5 05:30 redo03.log
-rw-r-----  1 oracle oinstall 262152192 Aug  5 05:36 sysaux01.dbf
-rw-r-----  1 oracle oinstall 503324672 Aug  5 05:36 system01.dbf
-rw-r-----  1 oracle oinstall   5251072 Aug  5 05:30 trans_test01.bdf
-rw-r-----  1 oracle oinstall  52436992 Aug  5 05:36 undotbs01.dbf
-rw-r-----  1 oracle oinstall   5251072 Aug  5 05:30 users01.dbf
[oracle@node2 TenG]$

临时表空间的数据文件被删除了。

eason@TENG> insert into test01 select * from test01;

45896 rows created.

eason@TENG> commit;

Commit complete.

eason@TENG> select count(*) from test01;

  COUNT(*)
----------
     91792

eason@TENG> select * from test01 order by 1,2,3,4,5,6,7,8;
select * from test01 order by 1,2,3,4,5,6,7,8
              *
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/oracle/oradata/TenG/TenG/temp01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

eason@TENG>

确实大的排序，因为临时文件的删除，而导致失败。

这时我们shutdown immediate

sys@TENG> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TENG>

然后在startup

先来看看部分alert文件

ARC1: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: Becoming the heartbeat ARCH
ARC1 started with pid=18, OS id=3883
Tue Aug  5 05:48:15 2008
Successfully onlined Undo Tablespace 11.
Tue Aug  5 05:48:15 2008
SMON: enabling tx recovery
Tue Aug  5 05:48:16 2008
Re-creating tempfile /u01/oracle/oradata/TenG/TenG/temp01.dbf
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=3886
Tue Aug  5 05:48:18 2008
Completed: ALTER DATABASE OPEN
[oracle@node2 bdump]$

其中有一行表明oracle在自动创建tempfile。

然后再看看文件系统：

[oracle@node2 bdump]$ cd /u01/oracle/oradata/TenG/TenG/
[oracle@node2 TenG]$ ll
total 1234728
-rw-r-----  1 oracle oinstall   7159808 Aug  5 05:50 control01.ctl
-rw-r-----  1 oracle oinstall   7159808 Aug  5 05:50 control02.ctl
-rw-r-----  1 oracle oinstall   7159808 Aug  5 05:50 control03.ctl
-rw-r-----  1 oracle oinstall 157294592 Aug  5 05:48 demo01.dbf
-rw-r-----  1 oracle oinstall 150872064 Aug  5 05:48 demo02.dbf
-rw-r-----  1 oracle oinstall  52429312 Aug  5 05:48 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Aug  5 05:48 redo03.log
-rw-r-----  1 oracle oinstall 262152192 Aug  5 05:48 sysaux01.dbf
-rw-r-----  1 oracle oinstall 503324672 Aug  5 05:48 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Aug  5 05:48 temp01.dbf
-rw-r-----  1 oracle oinstall   5251072 Aug  5 05:48 trans_test01.bdf
-rw-r-----  1 oracle oinstall  52436992 Aug  5 05:48 undotbs01.dbf
-rw-r-----  1 oracle oinstall   5251072 Aug  5 05:48 users01.dbf
[oracle@node2 TenG]$

确实已经创建好了。

再看看刚才那个sql是否能够运行呢

eason@TENG> select * from test01 order by 1,2,3,4,5,6,7,8;

391f2b28_MetalTitlePaneWindow
            12480                JAVA CLASS          30-JUN-05 30-JUN-05
2005-06-30:19:24:57 VALID   N N N

/3926fff3_NodeAssocFactory

6750 rows selected.

eason@TENG>

能够成功地运行。

看来在10g的库中，如果以外损失了temp tablespace的文件，不用紧张，只要重启数据库就可以了。不需要手工的添加。十分方便。