一. Temporary
Tablespacs 说明
A
temporary tablespace contains transient data that persists only for
the duration of a session. No permanent schema objects can reside
in a temporary tablespace. The database stores temporary tablespace
data in temp files.
Temporary
tablespaces can improve the concurrency of multiple sort operations
that do not fit in memory. These tablespaces also improve
the efficiency of space management operations during
sorts.
When
the SYSTEM tablespace is locally managed, a default temporary
tablespace is included in the database by default during database
creation. A
locally managed SYSTEM tablespace cannot serve as default temporary
storage.
– 本地管理的system 表空间，不能作为默认的临时表空间。
You
cannot make a default temporary tablespace
permanent.
You
can specify a user-named default temporary tablespace when you
create a database by using the DEFAULT TEMPORARY TABLESPACE
extension to the CREATE DATABASE statement.If
SYSTEM is dictionary managed, and if a default temporary tablespace
is not defined at database creation, then SYSTEM is the default
temporary storage. However, the database
writes a warning in the alert
log saying
that a default temporary tablespace is recommended.
– 当SYSTEM 表空间是数据字典管理时，并且没有定义默认的临时表空间，那么SYSTEM 表空间会作为默认的temporary
storage。
关于表空间的两种类型： locally
managed 和 dictionary
managed的区别，参考我的Blog：
Oracle 自动段空间管理(ASSM:auto
segment space management)
http://davedba.com/oracle-automatic-segment-space-management-assmauto-segment-space-management.html
A
temporary tablespace contains schema objects only for the duration
of a session. Locally managed temporary
tablespaces have temporary files (temp
files), which
are special files designed to store data in hash, sort, and other
operations. Temp
files also store result set data when insufficient space exists in
memory.
Temp files are similar to permanent data files, with the following
exceptions:
（1）Permanent database objects such as tables are never stored in
temp files.
（2）Temp
files are always set to NOLOGGING
mode, which means that they never have redo
generated for them. Media recovery does not recognize temp
files.
（3）You
cannot make a temp file read-only.
（4）You cannot create a temp file with the ALTER DATABASE
statement.
（5）When
you create or resize temp files, they are not always guaranteed
allocation of disk space for the file size
specified. On file systems such as Linux
and UNIX, temp files are created as sparse files. In this case,
disk blocks are allocated not at file creation or resizing, but as
the blocks are accessed for the first time.
Caution:
Sparse
files enable fast temp file creation and resizing; however, the
disk could run out of space later when the temp files are
accessed.
（6）Temp
file information is shown in the data dictionary view
DBA_TEMP_FILES and the dynamic performance view
V$TEMPFILE, but not in DBA_DATA_FILES or
the V$DATAFILE view.
临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create
index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。注意这里的释放，仅仅是将这些空间标记为空闲，并可重用，真正占用的磁盘空间并没有释放。所以Temp表空间可能会越来越大。
排序是很耗资源的，Temp表空间满了,关键是优化你的语句，尽量使排序减少才是上策.
二. Temp 表空间的操作
You
cannot use the ALTER TABLESPACE statement, with the TEMPORARY
keyword, to change a locally managed permanent tablespace into a
locally managed temporary tablespace. You must use the CREATE
TEMPORARY TABLESPACE statement to create a locally managed
temporary tablespace.
You
can use ALTER TABLESPACE to add a tempfile, take a tempfile
offline, or bring a tempfile online, as
illustrated in the following examples:
ALTER TABLESPACE lmtemp
ADD TEMPFILE
'/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;
SQL>ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;
SQL>ALTER TABLESPACE lmtemp TEMPFILE ONLINE;
Note:
You
cannot take a temporary tablespace offline. Instead, you
take its tempfile offline. The view V$TEMPFILE
displays online status for a tempfile.
– 不可以将Temp 表空间offline，但是可以将tempfile
offline。V$TEMPFILE 显示了tempfile 的状态。
The ALTER DATABASE statement can be used to alter
tempfiles.
The following statements take offline and bring online tempfiles.
They behave identically to the last two ALTER TABLESPACE statements
in the previous example.
SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf'
OFFLINE;
SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf'
ONLINE;
The following statement resizes a tempfile: —
resize 表空间
SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf'
RESIZE 18M;
The following statement drops a tempfile and deletes its operating
system file:
SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf'
DROP
INCLUDING
DATAFILES;
– drop tempfile 和它的物理文件。
The
tablespace to which this tempfile belonged
remains. A message is written to the alert
log for the tempfile that was deleted. If an operating system error
prevents the deletion of the file, the statement still succeeds,
but a message describing the error is written to the alert
log.
– 当我们把temp 表空间的数据文件文件删除之后，表空间的信息还会存在，但是在alert
log里会有错误信息。
It
is also possible to use the ALTER DATABASE statement to enable or
disable the automatic extension of an existing tempfile, and to
rename a tempfile. See Oracle
Database SQL Language Reference for
the required syntax.
Note:
To
rename a tempfile, you take the tempfile offline, use operating
system commands to rename or relocate the tempfile, and then use
the ALTER DATABASE RENAME FILE command to update the database
controlfiles.
tempfile 数据文件重命名的步骤：
（1）将tempfile
offline
（2）在操作系统上重命名tempfile
（3）使用alter
database rename file 更新控制文件。
三. 临时表空间满时的处理方法
3.1 添加数据文件
如果Temporary
tablespace还不大，那么我们可以增加一些数据文件。SQL 语句如下：
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE 'D:/ORADATA/NEWCCS/TEMP02.DBF' SIZE 100M AUTOEXTEND OFF;
一般来说，Temp
tablespace 和 Undo
Tablespace 是不建议设置为自增长，设置自增长可能会把磁盘给撑满。
3.2 修改数据文件大小
可以将原来的数据文件改大一点，如：
SQL>ALTER DATABASE TEMPFILE 'D:/
ORADATA/NEWCCS/TEMP02.DBF'
RESIZE 100M;
四.
Temp 表空间过大的处理方法
Temp 表空间过大，会占用很多的磁盘空间，这时候，我们可以用一下2中方法来缩小temp 表空间的大小。
4.1 替换Temp 表空间
4.1.1 查看目前Temp 表空间的信息
SQL> select name from v$tempfile;
NAME
———————————————————————
D:/ORACLE/ORADATA/TEST/TEMP01.DBF
SQL> select username,temporary_tablespace from
dba_users;
USERNAME TEMPORARY_TABLESPACE
—————————— ——————————
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
关于用户这块是要特别注意的，如果我们将默认的Temp 表空间指向其他的名称，那么这些用户的信息就会失效，所以，我们特换时，要么创建一个临时的Temp 表空间中转一下，这样切换之后，我们的temp表空间名称不变，要么改变名称，同时更新相关用户的default
temp 表空间。
这里用中转的方法来测试。
4.1.2 创建中转临时表空间
UNIFORM
specifies that the tablespace is managed
with uniform extents of SIZE bytes.The
default SIZE is 1 megabyte. All
extents of temporary tablespaces are of uniform size, so
this keyword is optional for a temporary tablespace. However, you
must specify UNIFORM in order to specify
SIZE. You
cannot specify UNIFORM for an undo
tablespace.
–
Temp 表空间必须是uniform 的，undo 必须是autoallocate的。默认情况下uniform 是1M。
If
you do not specify AUTOALLOCATE or
UNIFORM, then
the default is UNIFORM for temporary
tablespaces and AUTOALLOCATE
for all other types of tablespaces.
更多内容参考：
Oracle 表空间 创建参数 说明
http://davedba.com/oracle-tablespace-creation-parameter-description.html
创建SQL 如下：
SQL>CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
'D:/APP/ADMINISTRATOR/ORADATA/NEWCCS/TEMP03.DBF' SIZE 10M AUTOEXTEND OFF;
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
上面是默认情况，等于一下SQL:
SQL>CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
'D:/APP/ADMINISTRATOR/ORADATA/NEWCCS/TEMP03.DBF' SIZE 10M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
4.1.3 修改Temp2为默认临时表空间
SQL>alter database default temporary tablespace
temp2;
4.1.4. 删除原来临时表空间
SQL>drop tablespace temp including contents and
datafiles;
4.1.5. 重新创建临时表空间
SQL>CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'D:/APP/ADMINISTRATOR/ORADATA/NEWCCS/TEMP03.DBF' SIZE 10M AUTOEXTEND OFF;
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
4.1.6. 重置缺省临时表空间为新建的temp表空间
SQL>alter database default temporary tablespace temp;
4.1.7. 删除中转用临时表空间
SQL>drop tablespace temp2 including contents and
datafiles;
4.1.8 如果有必要，重新指定用户表空间为重建的临时表空间
SQL>alter user dave temporary tablespace
temp;
4.2 Shrinking
a Locally
Managed Temporary Tablespace
Large
sort operations performed by the database may result in a temporary
tablespace growing and occupying a considerable amount of disk
space. After
the sort operation completes, the extra space is not released; it
is just marked as free and available for
reuse.
– 当排序操作完成， 占用的空间并没有释放，仅仅是将它标记为空闲，并可重用。
Therefore,
a single large sort operation might result in a large amount of
allocated temporary space that remains unused after the sort
operation is complete. For this
reason, the
database enables you to shrink locally managed temporary
tablespaces and release unused space.
–可以使用shrink 来释放没有使用的空间。
（1）You
use the SHRINK SPACE clause of the ALTER TABLESPACE statement to
shrink a temporary tablespace,
or （2）the
SHRINK TEMPFILE clause of the ALTER TABLESPACE statement to shrink
a specific tempfile of a temporary
tablespace. Shrinking frees as much space
as possible while maintaining the other attributes of the
tablespace or tempfile. The
optional KEEP clause defines a minimum size for the tablespace or
tempfile.
Shrinking
is an online operation, which means that
user sessions can continue to allocate sort extents if needed, and
already-running queries are not affected.
–
shrink 是一个online 的操作，不影响其他的查询。
示例一：
The
following example shrinks the locally managed temporary tablespace
lmtmp1 to a size of 20M.
SQL>ALTER
TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;
示例二：
The
following example shrinks the tempfile lmtemp02.dbf of the locally
managed temporary tablespace lmtmp2. Because the KEEP clause is
omitted, the database attempts to shrink the tempfile to the
minimum possible size.
SQL>ALTER
TABLESPACE lmtemp2 SHRINK TEMPFILE
'/u02/oracle/data/lmtemp02.dbf';
