在Oracle9i中,Oracle引入了真正意义上的临时文件,在创建临时表空间时Oracle增加了一个新的关键字tempfile , 在Oracle9i的创建数据库的脚本中,我们可以看到Oracle的这一改变:CREATE DATABASE eygle
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/opt/oracle/oradata/eygle/system01.dbf' SIZE 250M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/opt/oracle/oradata/eygle/temp01.dbf' SIZE 40M REUSE
AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/opt/oracle/oradata/eygle/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
………
以上粗体显示部分包含了Oracle9i的一个新特性,Default子句为数据库指定一个默认的临时表空间,这个临时表空间是全局性的。
从如下视图可以查询到当前默认临时表空间:SQL> SELECT *
2 FROM database_properties
3 WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
----------------------- ------------- ---------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
这里创建的临时表空间可以通过dba_temp_files视图查询得到:[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Jan 6 23:56:05 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> select file_name from dba_data_files where tablespace_name='TEMP';
no rows selected
SQL> select file_name from dba_temp_files where tablespace_name='TEMP';
FILE_NAME
---------------------------------------------------------------------
/opt/oracle/oradata/eygle/temp01.dbf
在Oracle9i中,如果创建用户时不指定临时表空间,则缺省的临时表空间会指向默认的临时表空间:SQL> create user eyglee identified by eygle;
User created.
SQL> select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
2 from dba_users where username='EYGLEE';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------- ------------------------ ------------------------------
EYGLEE SYSTEM TEMP
而且需要注意的是,从Oracle9i开始,用户的临时表空间必须是TEMPORARY的(在9i之前没有这个限制,可以是PERMANENT):SQL> alter user eygle temporary tablespace system;
alter user eygle temporary tablespace system
*
ERROR at line 1:
ORA-12911: permanent tablespace cannot be temporary tablespace
临时表空间一旦创建,就不能转化为永久表空间了,不能执行离线(offline)操作,而且当前默认的临时表空间不能被删除:SQL> drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
ORA-12906: cannot drop default temporary tablespace
在Linux/Unix上,如果不指定reuse关键字,新创建出来的临时文件都是稀疏文件,也就是说空间不是马上分配的,所以会有很快的创建速度。