管理临时表空间与临时文件

管理临时表空间与临时文件

如果说oracle 8i以下（包括8i）还是使用的是有数据文件特性的临时数据文件，Oracle 9i已经是真正意义的临时文件了，这样的临时文件不需要备份与恢复，也不会记录任何redo log信息.因此，9i才有了一个新的关键字tempfile，而在8i的时候，这个都是datafile。如要添加一个新的临时文件，8i是
alter tablespace temp add datafile 'temp file name' size 2000M;
8i也有临时文件tempfile的，参考v$tempfile.文章有误。。。。。
而9i是
alter tablespace temp add tempfile 'temp file name' size 2000M;
这个是9i一个新的特性，就是真正意义的临时数据文件。而且从9i开始，数据库将有默认的临时表空间，新建用户如果不特殊指定将用默认的临时表空间（在老的版本中如果不特殊指定，将默认system表空间为临时表空间），从如下视图可以查询到当前默认临时表空间。
select*from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
如我们可以通过如下命令要创建与设置临时表空间
create temporary tablespace temp tempfile'temp file name'size 100m;
alter databasedefaulttemporary tablespace temp;
在linux/unix上，如果不指定reuse关键字，新创建出来的临时文件都是稀疏文件，也就是说空间不是马上分配的，所以有很快的创建速度。临时表空间一旦创建，就不能转化为永久表空间了，不能执行离线（offline）操作，而且当前默认的临时表空间不能被删除。
SQL>drop tablespace temp including contentsanddatafiles;
drop tablespace temp including contents and datafiles
ORA-12906:cannot dropdefaulttemporary tablespace
如果是非当前默认的临时表空间，则可以删除，另外，临时文件可以随时增加或者是删除。
SQL>alter tablespace tbs_temp add tempfile'temp file name'size 100M;
SQL>alter database tempfile'temp file name'drop;

from:http://ora110.itpub.net/post/33978/477492

临时表空间的监控

经常出现临时表空间不够的情况，除了增加临时表空间，最好还是找出有问题的sql语句。
视图v$sort_usage显示oracle在如何使用和分配临时段的.

select username,session_addr,sqladdr,sqlhash,segtype,segfile#,segblk#,extents,blocks from v$sort_usage;

可以根据session_addr查询v$session得到当前登录用户的session信息,
可以通过sqladdr和sqlhash查询v$sqltext查询这条语句的详细信息
下面的语句可以比较好的监控正在使用的临时段的信息
select   se.username
        ,se.sid
        ,su.extents
        ,su.blocks * to_number(rtrim(p.value)) as Space
        ,tablespace
        ,segtype
        ,sql_text
from     v$sort_usage su
        ,v$parameter  p
        ,v$session    se
        ,v$sql s
where    p.name          = 'db_block_size'
and      su.session_addr = se.saddr
and         s.hash_value = su.sqlhash and s.address = su.sqladdr
order by se.username, se.sid

from:http://liubin.itpub.net/post/325/4602

//

重建临时表空间 & 监控临时表空间 III

5.1 监控临时表空间

全局临时表的统计信息。一种方法是通过动态采样（只是 Oracle9i Release 2 及以上版本中新增的特性），另一种方法是使用 DBMS_STATS 包，它有两种做法。下面首先来看动态采样。
动 态采样（ dynamic sampling ）是优化器的一种功能，硬解析一个查询时，会扫描数据库中的段（ 采样），收集有用的统计信息，来完成这个特定查询的优化。这与硬解析期间 完成一个 “ 缩型收集统计 ” 命令很类似。 Oracle 10g 中大量使用了动态采样，因为默认设置已经从 level 1 提升到 level 2 ，采用 lev el 2 ，优化器在结算查询计划之前，会对优化器处理的查询中引用的所有未分析的对象完成动态采样。

6.1 重建临时表空间
99和37服务器实验均成功！
--查询用户的缺省表空间
select username,temporary_tablespace from dba_users
--查询临时表空间对应的数据文件
SELECT * FROM dba_temp_files
--创建临时表空间 temp2
create temporary tablespace temp2
tempfile '/opt/oracle/oradata/simply/temp02.dbf' size 10M

alter tablespace temp2 add
tempfile '/opt/oracle/oradata/simply/temp022.dbf' size 20M;

--很重要!!!!!!!!!!

alter database　
tempfile '/opt/oracle/oradata/simply/temp02.dbf '
autoextend on

alter database　
tempfile '/opt/oracle/oradata/simply/temp022.dbf '
autoextend on

--切换数据库临时表空间为 temp2
alter database default temporary tablespace temp2

--如果原临时表空间无用户使用,我们可以删除该表空间
drop tablespace temp

问题：用上面的命令删除表空间以后，该表空间对应的数据文件temp01.dbf还是存在的。所以我把它手工给删除了rm -rf temp01.dbf ;结果重启数据库没问题！

参考文档：http://www.eygle.com/archives/2006/04/oracle9i_default_temporary_tablespace.html
7.1 参考文献
《编程艺术》 473～480页
http://www.eygle.com/faq/temp_table.htm
http://www.eygle.com/archives/2006/04/oracle9i_default_temporary_tablespace.html

from:http://hi.baidu.com/bkeep/blog/item/fcb802fc41098b4cd6887def.html

///

Oracle9i中如何重建与切换临时表空间

Oracle9i引入了全局缺省临时表空间,缺省的如果不指定用户临时表空间,Oracle会为用户指定这个缺省临时表空间.
首先查询用户的缺省临时表空间:
[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 12 11:11:43 2006
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS                                 TEMP2
SYSTEM                         TEMP2
OUTLN                           TEMP2
EYGLE                            TEMP2
CSMIG                            TEMP2
TEST                               TEMP2
REPADMIN                    TEMP2
......

13 rows selected.

SQL> select name from v$tempfile;

NAME
---------------------------------------------------------------------
/opt/oracle/oradata/conner/temp02.dbf
/opt/oracle/oradata/conner/temp03.dbf

重建新的临时表空间并进行切换:

SQL> create temporary tablespace temp tempfile '/opt/oracle/oradata/conner/temp1.dbf' size 10M;

Tablespace created.

SQL> alter tablespace temp add tempfile '/opt/oracle/oradata/conner/temp2.dbf' size 20M;

Tablespace altered.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS                                 TEMP
SYSTEM                         TEMP
OUTLN                           TEMP
EYGLE                            TEMP
CSMIG                           TEMP
TEST                               TEMP
REPADMIN                    TEMP
.......

13 rows selected.
 
如果原临时表空间无用户使用,我们可以删除该表空间:

SQL> drop tablespace temp2;

Tablespace dropped.

SQL>
SQL> select name from v$tempfile;

NAME
---------------------------------------------------------------
/opt/oracle/oradata/conner/temp1.dbf
/opt/oracle/oradata/conner/temp2.dbf

SQL> select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible
  2  from dba_temp_files
  3  /

FILE_NAME                              TABLESPACE_NAME              MB AUTOEXTENSIBLE
-------------------------------------- -------------------- ---------- --------------
/opt/oracle/oradata/conner/temp2.dbf   TEMP                         20 NO
/opt/oracle/oradata/conner/temp1.dbf   TEMP                         10 NO
 
http://www.eygle.com/archives/2006/04/oracle9i_default_temporary_tablespace.html

///

Oracle临时表空间过大的解决办法

Temporary tablespace是oracle里临时表空间，临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。但有些有侯我们则会遇到临时段没有被释放，TEMP表空间几乎满的状况，甚至是我们重启了数据库仍没有解决问题。
排序是很耗资源的，Temp表空间满了，关键是优化你的语句，尽量使排序减少才是上策。
Temp表空间满时的处理方法：
　　一、修改参数(仅适用于8i及8i以下版本)
　　修改一下TEMP表空间的storage参数，让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。
　　SQL>alter tablespace temp increase 1;
　　SQL>alter tablespace temp increase 0;
　　二：kill session
　　1、 使用如下语句a查看一下认谁在用临时段
            SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,
                               su.segtype,  su.CONTENTS from v$session se, v$sort_usage su
                                WHERE se.saddr = su.session_addr
　　2、kill正在使用临时段的进程
　　SQL>Alter system kill session 'sid,serial#';
　　3、把TEMP表空间回缩一下
　　SQL>Alter tablespace TEMP coalesce;
注：
这处方法只能针对字典管理表空间(Dictionary Managed Tablespace)。于本地管理表空间（LMT:Local Managed Tablespace），不需要整理的。9i以后只能创建本地管理的表空间。
CREATE TABLESPACE TEST DATAFILE 'D:/TEST01.dbf' SIZE 5M EXTENT MANAGEMENT DICTIONARY
CREATE TABLESPACE TEST DATAFILE 'D:/TEST0 ......

from:http://www.ej38.com/showinfo/Oracle-109859.html