数据世界并不令人惊讶，我们经常遇到常识之外的问题。面对一些奇怪的问题，需要大多数DBA对数据库的内部机制有深刻的了解，消除迷雾并找到问题的所在。在众多数据库当中，Oracle数据库是我们比较熟悉的。但是在Oracle数据库使用过程中，总是会出现这样或者那样的问题，今天我们一起来盘点一下Oracle数据库中，Oracle临时表空间过大怎么办？如何解决Oracle临时表空间的报错问题？
Oracle临时表空间过大怎么办？
临时表空间主要使用在：
- 索引创建或重创建。
- ORDER BY or GROUP BY 。
- DISTINCT 操作。
- UNION & INTERSECT & MINUS 。
- Sort-Merge joins。
- Analyze 操作。
- 有些异常将会引起temp暴涨 。
下面是重新创建一个临时表空间，把原来的默认临时表空间drop掉(包括里面的临时数据文件)再重新建立。
SQL> create temporary tablespace temp2
2 tempfile '/home/oracle/oracle/product/10.2.0/oradata/hatest/temp02.pdf' size 512M reuse
3 autoextend on next 640k maxsize unlimited;
Tablespace created.
SQL> alter database default temporary tablespace temp2;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
注意：由于临时表空间的数据文件比较大，所以这步可能会花费比较长的时间
SQL> create temporary tablespace temp
2 tempfile '/home/oracle/oracle/product/10.2.0/oradata/hatest/temp01.pdf' size 512M reuse
3 autoextend on next 640K maxsize unlimited;
Tablespace created.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> drop tablespace temp2 including contents and datafiles;
Tablespace dropped.
SQL> exit
以上的方法只是暂时释放了临时表空间的磁盘占用空间，是治标但不是治本的方法，真正的治本的方法是找出数据库中消耗资源比较大的sql语句，然后对其进行优化处理。
如何解决Oracle 临时表空间的报错问题？
1、 查询临时表空间状态：
SQL> col file_name for a20;
SQL> select tablespace_name,file_name,bytes/1024/1024file_size,autoextensible from dba_temp_files;
2、 扩展临时表空间
SQL> alter database tempfile '/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf'resize 8192m;
或也可增加临时表空间文件
alter tablespace temp add tempfile‘/u01/app/oracle/oradata/CP7PV1DB/temp02.dbf’ size 8192m;
注：临时表空间文件如果已经32G，达到最大文件大小，只能添加文件。
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/CP7PV1DB/temp03.dbf' size 4G autoextend on next 128M maxsize 6G;
SQL> ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/CP7PV1DB/temp03.dbf';
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/CP7PV1DB/temp03.dbf' RESIZE 6G;
3、 设置自动扩展
SQL> alter database tempfile'/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf' autoextend on next 10m maxsizeunlimited;
4、 扩展表空间时的报错
ERROR atline 1:
ORA-00376:file 201 cannot be read at this time
ORA-01110:data file 201: '/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf'
原因是临时表空间不知道什么原因offline了，修改为online后修改成功。
SQL> alter database tempfile ‘/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf’online;
Database altered.
5、 删除临时表空间
SQL>drop tablespace temp01 including contents and datafiles;
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf' DROPINCLUDING DATAFILES;
Database altered.
注意：删除临时表空间的临时数据文件时，不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件，否则需要手工删除物理文件。也不能直接删除当前用户的默认表空间，否则会报ORA-12906错误。如果需要删除某一个默认的临时表空间，则必须先创建一个临时表空间，然后指定新创建的表空间为默认表空间，然后删除原来的临时表空间。
6、 更改系统默认的临时表空间
--查询默认临时表空间
SQL> select *from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
-------------------------------------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
--修改默认临时表空间
SQL> alterdatabase default temporary tablespace temp02;
Databasealtered.
我们可以查询是否切换为TEMP02：
SQL> select *from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
-------------------------------------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP02 Name of default temporary tablespace
7、 查看临时表空间的使用率
SQL>SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 /total, 3) "Free percent"
FROM (SELECT tablespace_name,SUM(bytes_used) / 1024 / 1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) /1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHEREtemp_used.tablespace_name = temp_total.tablespace_name;
TABLESPACE_NAME Free Total Free percent
---------------------------------------- ---------- ------------
TEMP 6876 8192 83.936
8、 查找消耗资源比较多的sql语句
Select se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) asSpace,
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;
先创建一个临时表空间，把这个表空间设置为默认的临时表空间，然后把以前的临时表空间删掉，再把数据文件删掉，很简单。
上述就是关于Oracle临时表空间过大怎么办，以及如何解决表空间的临时报错问题的全部内容介绍，想了解更多关于Oracle数据库的信息，请继续关注吧。
