数据库

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

2020-09-01 18:37:45　|　来源：中培企业IT培训网

数据世界并不令人惊讶，我们经常遇到常识之外的问题。面对一些奇怪的问题，需要大多数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