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

cuixian9402

于 2008-08-12 22:22:24 发布

阅读量72

点赞数

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

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的文件，不用紧张，只要重启数据库就可以了。不需要手工的添加。十分方便。