临时表空间的管理与受损恢复
Oracle 临时表空间是Oracle数据库的重要组成部分，尽管该部分并没有cont体系结构上得以展现，但其重要地位也是不容忽视的。尤其是对于大型的频繁操作，如创建索引，排序等等都需要在临时表空间完成来减少内存的开销。当然对于查询性能要求较高的应尽可能的避免在磁盘上完成这些操作。本文主要描述的是临时表空间的管理与受损恢复。
一、临时表空间的特性与注意事项
1、特性
用户存储临时数据的表空间
临时数据通常只在一个数据库会话期间内存在的数据，分为两种形式，排序数据和全局临时表
临时数据不会被写入存储对象的普通表空间内，而是存储在临时表空间的临时段中
临时表空间临时性导致不需要备份该类型的表空间，RMAN也不支持对临时表空间的备份
对于临时数据的处理，不会生成重做，也不会生成撤销数据
临时表空间的数据文件不能置为只读、不能重命名
监时表空间的数据文件的日志方式总是NOLOGGING
使用临时表空间的主要操作
CREATE INDEX，ALTER INDEX ...REBUILD，ORDER BY，GROUP BY，DISTINCT，UNION，INTERSECT，MINUS，SORT-MERGER，JOIN，ANALYZE
2、临时表空间使用的注意事项
a）每个用户都有一个缺省的临时表空间，对于临时表空间使用较高的系统，建议将临时表空间数据文件分布到不同的磁盘
b）对于大型操作频繁，(大型查询，大型分类查询，大型统计分析等)，应指定单独的临时表空间，以方便管理
c）分配用户单独临时表空间，一般是针对大型产品数据库，OLTP数据库，数据库仓库
d）对于小型产品不需要单独制定临时表空间，使用默认临时表空间
e）对于临时表空间建议关闭自动扩展功能，避免过度扩展所致的空间压力
二、临时表空间的管理
1、查看缺省的临时表空间
SQL> select property_name，property_value from database_properties 2 where property_name like 'DEFAULT%'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ -------------------------------- DEFAULT_TEMP_TABLESPACE TEMP DEFAULT_PERMANENT_TABLESPACE USERS DEFAULT_TBS_TYPE SMALLFILE
2、查看临时表空间的大小及位置
SQL> select s.name tbsname，t.name，(t.bytes/1024/1024) bytes，status 2 from v$tablespace s，v$tempfile t 3 where s.ts# = t.ts#; TBSNAME NAME BYTES STATUS ---------- --------------------------------------------- ---------- ------- TEMP /u01/app/oracle/oradata/orcl/temp01.dbf 30 ONLINE SQL> select tablespace_name，file_name，bytes/1024/1024 Size_MB from dba_temp_files; TABLESPACE FILE_NAME SIZE_MB ---------- --------------------------------------------- ---------- TEMP /u01/app/oracle/oradata/orcl/temp01.dbf 30 SQL> select tablespace_name，logging，allocation_type 2 from dba_tablespaces where tablespace_name='TEMP'; TABLESPACE LOGGING ALLOCATIO ---------- --------- --------- TEMP NOLOGGING UNIFORM
3、临时表文件大小和已使用空间
SELECT t1."Tablespace" "Tablespace"， t1."Total (G)" "Total (G)"， nvl(t2."Used (G)"， 0) "Used(G)"， t1."Total (G)" - nvl(t2."Used (G)"， 0) "Free (G)" FROM (SELECT tablespace_name "Tablespace"， to_char((SUM(bytes / 1024 / 1024 / 1024))， '99，999，990.900') "Total (G)" FROM dba_temp_files GROUP BY tablespace_name UNION SELECT tablespace_name "Tablespace"， to_char((SUM(bytes / 1024 / 1024 / 1024))， '99，999，990.900') "Total (G)" FROM dba_data_files WHERE tablespace_name LIKE 'TEMP%' GROUP BY tablespace_name) t1， (SELECT tablespace， round(SUM(blocks) * 8 / 1024 /1024) "Used (G)" FROM v$sort_usage GROUP BY tablespace) t2 WHERE t1."Tablespace" = t2.tablespace(+); Tablespace Total (G) Used(G) Free (G) ------------------------------ --------------- ---------- ---------- GOEX_TEMP 31.999 1 30.999 FIX_TEMP 0.098 0 .098 TEMP 0.195 0 .195
4、查看当前临时表使用空间大小与正在占用临时表空间的sql语句
-->使用临时段的SQL语句 SELECT sess.SID， segtype， blocks * 8 / 1000 "MB"， sql_text FROM v$sort_usage sort， v$session sess， v$sql SQL WHERE sort.SESSION_ADDR = sess.SADDR AND SQL.ADDRESS = sess.SQL_ADDRESS ORDER BY blocks DESC; -->下面的查询也可以查询谁正在使用临时段 col username format a15 col machine format a15 col program format a30 col tablespace format a15 set linesize 160 SELECT s.username ，s.sid ，s.serial# ，s.sql_address ，s.machine ，s.program ，su.tablespace ，su.segtype ，su.contents FROM v$session s， v$sort_usage su WHERE s.saddr = su.session_addr; SELECT 'the ' || NAME || ' temp tablespaces ' || tablespace_name || ' idle ' || round(100 - (s.tot_used_blocks / s.total_blocks) * 100， 3) || '% at ' || to_char(SYSDATE， 'yyyymmddhh24miss') FROM (SELECT d.tablespace_name tablespace_name， nvl(SUM(used_blocks)， 0) tot_used_blocks， SUM(blocks) total_blocks FROM v$sort_segment v， dba_temp_files d WHERE d.tablespace_name = v.tablespace_name(+) GROUP BY d.tablespace_name) s， v$database;
5、修改默认的临时表空间
alter database default temporary tablespace tablespace_name;
6、对于过度使用临时表空间，在允许的情况下，可以杀掉其session
alter system kill session 'sid，serial#';
三、临时表空间故障
前面已提到，临时表空间不需要备份，如果对临时表空间备份将收到错误提示
SQL> alter tablespace temp begin backup; alter tablespace temp begin backup * ERROR at line 1: ORA-03217: invalid option for alter of TEMPORARY TABLESPACE SQL> alter temporary tablespace temp begin backup; alter temporary tablespace temp begin backup * ERROR at line 1: ORA-00940: invalid ALTER command
临时表空间出错的错误提示(位于告警日志中alert_orcl.log)
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_2230.trc: ORA-01186：file 3 failed verification tests ORA-01157：cannot identify/lock data file 3 - see DBWR trace file ORA-01110：data file 3:'/u01/app/oracle/oradata/orcl/temp01.dbf'
四、还原受损的临时表空间
1、在session 1中删除临时表空间的数据文件
SQL> show user; USER is "SYS" SQL> ho rm /u01/app/oracle/oradata/orcl/temp01.dbf SQL> alter system checkpoint; -->执行检查点进程 System altered.
2、在session 2中执行排序查询，提示临时数据文件错误
SQL> show user; USER is "SCOTT" SQL> set autotrace traceonly; SQL> select owner，object_name from big_table order by id，owner，object_name; select owner，object_name from big_table order by id，owner，object_name * ERROR at line 1: ORA-01565: error in identifying file '/u01/app/oracle/oradata/orcl/temp01.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3
3、在session 1中为临时表空间增加数据文件
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' 2 size 30m autoextend on next 1m maxsize 2g; Tablespace altered.
4、在session 2中继续执行排序操作，此次执行成功
SQL> select owner，object_name from big_table order by id，owner，object_name; 1000000 rows selected.
5、查询临时数据文件状态及文件存在性
SQL> set linesize 160 --> session 1中查看临时数据文件状态，大小为0，但仍然为online状态 SQL> select s.name tbsname，t.name，(t.bytes/1024/1024) bytes，t.status 2 from v$tablespace s，v$tempfile t 3 where s.ts# = t.ts#; TBSNAME NAME BYTES STATUS -------------------- --------------------------------------------- ---------- ------- TEMP /u01/app/oracle/oradata/orcl/temp01.dbf 0 ONLINE TEMP /u01/app/oracle/oradata/orcl/temp02.dbf 30 ONLINE SQL> ho ls /u01/app/oracle/oradata/orcl/temp01.dbf ls: /u01/app/oracle/oradata/orcl/temp01.dbf: No such file or directory
6、清除控制文件内记录的不存在的临时文件信息
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf'; Tablespace altered.
7、基于表空间级别的恢复操作（下面给出主要步骤，不在演示）
a）创建一个新的临时表空间
b）使用alter database 将用户切换到新的临时表空间，即设置新的默认的临时表空间
c）删除受损的临时表空间
