Oracle11g--解决临时表空间过大问题

背景：

        线上的生产数据库的临时表空间增长到了32GB，增长至最高上限，为了保证数据库的正常运行，有两套临时思路可供选项。

        1）增加临时表空间的大小。
        2)  重建临时表空间，解决临时表空间过大的问题。

今天，在测试库上进行测试，操作步骤如下：

        1）增加临时表空间的大小：

        * 查看临时表空间的大小

###########临时表空间的使用情况#############

    SELECT d.tablespace_name "Name",

                TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",

                TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)",

                TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,

                TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",

          TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"

           FROM sys.dba_tablespaces d,

                (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,

                (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t

          WHERE d.tablespace_name = a.tablespace_name(+)

            AND d.tablespace_name = t.tablespace_name(+)

            AND d.extent_management like 'LOCAL'

            AND d.contents like 'TEMPORARY'

###########查看临时表空间的总大小和最大扩展大小(能看到数据文件)##########

select file_name,tablespace_name,

bytes/1024/1024 MB,autoextensible,maxbytes/1024/1024 MAX_MB from dba_temp_files

        * 增加临时表空间的大小
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oradata/ARPDB/temp02.dbf' SIZE 4G 
AUTOEXTEND ON
NEXT 128M
MAXSIZE 16384M;

        2)  重建临时表空间，解决临时表空间过大的问题。

0.查看目前默认的临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

1.创建中转临时表空间
create temporary tablespace temp1 tempfile '/u01/app/oradata/ARPDB/temp03.dbf' size 512m reuse autoextend on next 1m maxsize unlimited;

2.改变缺省临时表空间为刚刚创建的新临时表空间temp1
alter database default temporary tablespace temp1;

3.删除原临时表空间
drop tablespace temp including contents and datafiles;

###########如果删除表空间的时候，hang住的话，可以使用下列语句############
先把运行在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 '71,58031'; (假如某一条运行的sql语句的SID为524，serial#为778)

4.重建临时表空间
create temporary tablespace temp tempfile '/u01/app/oradata/ARPDB/temp01.dbf' size 512m reuse autoextend on next 1m maxsize unlimited;

5.重置缺省临时表空间为新建的temp表空间
alter database default temporary tablespace temp;

6.删除中转用临时表空间
drop tablespace temp1 including contents and datafiles;