Oracle临时表空间过大怎么办？如何解决表空间的临时报错问题？

数据世界并不令人惊讶，我们经常遇到常识之外的问题。面对一些奇怪的问题，需要大多数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数据库的信息，请继续关注中培教育吧。