Oracle安装在centos系统上,系统磁盘空间本身不是很大,运行一段时间后发现Oracle的临时表空间占用磁盘越来越大,以至于系统处于崩溃的边缘,解决该问题的方法如下:


第一步:
alter database tempfile '/opt/oracle/oradata/orcl/temp01.dbf' drop;
第二步:
alter tablespace temp add tempfile
'/opt/oracle/oradata/orcl/temp01.dbf'
size 2048M reuse autoextend on next 100M;
第三步:
select d.file_name, d.file_id, d.tablespace_name, d.bytes
from dba_temp_files d;
第四步:
alter database tempfile '/opt/oracle/oradata/orcl/temp01.dbf' autoextend off;
(只是为了解决小问题,不深究。)
 
正常来说,在完成Select语句、create index等一些使用TEMP表空间的排序操作后,Oracle是会自动释放掉临时段a的。但有些有侯我们则会遇到临时段没有被释放,TEMP表空间几乎满的状况,甚至是我们重启了数据库仍没有解决问题


在检查centos系统的磁盘空间时,发现临时表空间所在临时数据文件已经达到35G,已经占用了100%。

因为是正式数据库服务器,不能随便重启数据库。

以下的操作是用数据库的sys超级用户操作

刚开始打算把临时表空间的数据文件重新缩小就好了

执行:

SQL> alter database tempfile '/opt/oracle/oradata/orcl/temp01.dbf' resize 10240M;

数据库报错,重新设置的空间大小不能满足需要。

看来需要重新建立新的临时表空间替换当前的表空间了

1、首先查看当前的数据库默认表空间:

SQL>select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

确认当前的临时表空间为TEMP

2、查看目前临时表空间的大小:

SQL>select file_name,tablespace_name,bytes/1024/1024 "MB",autoextensible from dba_temp_files;

3、创建新的临时表空间:

SQL> create temporary tablespace temp02 tempfile '/opt/oracle/oradata/orcl/temp02.dbf' size 512M;

4、把新建的临时表空间却换成数据库的默认临时表空间

SQL> alter database default temporary tablespace temp02;

5、确认目前数据库的默认临时表空间

SQL>select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

确认temp02为当前的数据库默认表空间

6、在删除temp临时表空间之前,先把运行在temp临时表空间的sql语句kill掉,这样的sql语句多为排序的语句

SQL>Select se.username,se.sid,se.serial#,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;

查询出来之后,kill掉这些sql语句:

SQL>alter system kill session '524,778'; (假如某一条运行的sql语句的SID为524,serial#为778)

确认在temp临时表空间中没有运行的sql语句之后,则可以删除temp临时表空间数据文件了

7、删除temp临时表空间

SQL> drop tablespace temp including contents and datafiles;

这样很快就可以删除了临时表空间的数据文件

8、现在temp02临时表空间占据了别人的磁盘空间,需要重新把临时表空间建立在原来的位置,重新建立temp临时表空间

SQL> create temporary tablespace temp tempfile '/opt/oracle/oradata/orcl/temp01.dbf' size 512M autoextend on maxsize 15G;


新建一个512M的自动扩展临时表空间,最大的扩展为15G。

查看新建的temp临时表空间是否正确:

SQL>select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files;


9、把新建的temp临时表空间却换成数据库的默认临时表空间

SQL> alter database default temporary tablespace temp;

10、确认目前数据库的默认临时表空间

SQL>select * from database_properties

where property_name='DEFAULT_TEMP_TABLESPACE';

确认temp为当前的数据库默认表空间

11、目前把原来的temp临时表空间变成了512M,把剩余的磁盘空间空了出来,temp02临时表空间就没有用了,删除temp02临时表空间
SQL> drop tablespace temp02 including contents and datafiles;