Oracle临时表空间满的处理步骤/释放临时表空间方法

最后更新：24/10/25

相关查询语句：
 
--查询数据库用户的临时表空间
select username,default_tablespace,temporary_tablespace from dba_users du
order by du.temporary_tablespace,du.username

select username,default_tablespace,temporary_tablespace from dba_users du
where du.USERNAME like '%DBA' or du.USERNAME like '%APP1'
order by du.temporary_tablespace,du.username
 
--查询数据库的默认临时表空间
select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';
 
--检查表空间
SELECT * FROM DBA_TABLESPACES dt
order by dt.tablespace_name
 
--查看临时数据文件
select a.ts#,a.name as table_space, c.ts#,c.file# as tempF#,c.name as temp_file,c.bytes/1024/1024 '' 'M' as TSize
from v$tablespace a, v$tempfile c
where a.ts#=c.ts#
order by a.ts#,c.file#
 
+++0
释放系统默认临时表空间(TMEP)
 
--创建中转临时表空间TEMP_T01
create temporary tablespace TEMP_T01 TEMPFILE 'e:\HS01\TMP\TEMP_T01.ORA' size 4096M;
 
--改变缺省临时表空间为刚刚创建的新临时表空间
alter database default temporary tablespace TEMP_T01;

--删除原来临时表空间temp
drop tablespace temp including contents and datafiles;
 
--重新创建临时表空间
create temporary tablespace temp tempfile 'e:\hs01\tmp\temp01.ora' SIZE 8192M;
ALTER DATABASE tempFILE 'e:\HS01\tmp\temp01.ora' AUTOEXTEND OFF;
 
--重置缺省临时表空间为新建的temp表空间
alter database default temporary tablespace temp;

--删除中转用临时表空间
drop tablespace TEMP_T01 including contents and datafiles cascade constraint;

--也可改变大小
--ALTER DATABASE tempFILE 'E:\HS01\TMP\TEMP_T0.ORA' resize 10240M;
+++1
释放其它临时表空间tmp10
 
--创建中转临时表空间
create temporary tablespace tmp_t1 TEMPFILE 'E:\HS01\TMP\tmp_t1.ORA' size 4096M;
 
--修改用户临时表空间为中转临时表空间(要查询出使用tmp10的用户，然后修改)
alter user ENAPP1 temporary tablespace tmp_t1;
alter user ENDBA temporary tablespace tmp_t1;
alter user pNAPP1 temporary tablespace tmp_t1;
alter user pNDBA temporary tablespace tmp_t1;
 
--删除原临时表空间
drop tablespace tmp10 including contents and datafiles;
 
--重新创建原临时表空间
create temporary tablespace tmp10
tempfile 'E:\hs01\tmp\tmp11.ora' SIZE 4096M
tablespace group tmpgrp1;
ALTER DATABASE tempFILE 'E:\HS01\tmp\tmp11.ora' AUTOEXTEND OFF;
 
--修改用户临时表空间为原临时表空间
alter user ENAPP1 temporary tablespace tmp10;
alter user ENDBA temporary tablespace tmp10;
alter user pNAPP1 temporary tablespace tmp10;
alter user pNDBA temporary tablespace tmp10;
 
--删除中转用临时表空间
drop tablespace tmp_t1 including contents and datafiles;
 
+++2
--释放其它临时表空间tmp20
 
--创建中转临时表空间
create temporary tablespace tmp_t2 TEMPFILE 'e:\HS01\TMP\tmp_t2.ORA' size 8192M;
--ALTER TABLESPACE tmp_t2 ADD DATAFILE 'E:\HS01\TMP\tmp_t21.ORA' SIZE 4096M;
 
--修改用户临时表空间为中转临时表空间(要查询出使用tmp10的用户，然后修改)
alter user ARDBA temporary tablespace tmp_t2;
alter user ARAPP1 temporary tablespace tmp_t2;
alter user BKENAPP1 temporary tablespace tmp_t2;
alter user BKENDBA temporary tablespace tmp_t2;
alter user CMAPP1 temporary tablespace tmp_t2;
alter user CMDBA temporary tablespace tmp_t2;
alter user COAPP1 temporary tablespace tmp_t2;
alter user CODBA temporary tablespace tmp_t2;
alter user DSDBA temporary tablespace tmp_t2;
alter user DSAPP1 temporary tablespace tmp_t2;
alter user NFAPP1 temporary tablespace tmp_t2;
alter user NFDBA temporary tablespace tmp_t2;
alter user TLDBA temporary tablespace tmp_t2;
alter user TLAPP1 temporary tablespace tmp_t2; 
alter user VEDBA temporary tablespace tmp_t2;
alter user VEAPP1 temporary tablespace tmp_t2; 
 
--删除原临时表空间
drop tablespace tmp20 including contents and datafiles;
 
--重新创建原临时表空间
create temporary tablespace tmp20
tempfile 'e:\hs01\tmp\tmp21.ora' SIZE 4096M
tablespace group tmpgrp2;
ALTER DATABASE tempFILE 'e:\HS01\tmp\tmp21.ora' AUTOEXTEND OFF;

--修改用户临时表空间为原临时表空间
alter user ARDBA temporary tablespace TMP20;
alter user ARAPP1 temporary tablespace TMP20;
alter user BKENAPP1 temporary tablespace TMP20;
alter user BKENDBA temporary tablespace TMP20;
alter user CMAPP1 temporary tablespace TMP20;
alter user CMDBA temporary tablespace TMP20;
alter user COAPP1 temporary tablespace TMP20;
alter user CODBA temporary tablespace TMP20;
alter user DSDBA temporary tablespace TMP20;
alter user DSAPP1 temporary tablespace TMP20;
alter user NFAPP1 temporary tablespace TMP20;
alter user NFDBA temporary tablespace TMP20;
alter user TLDBA temporary tablespace TMP20;
alter user TLAPP1 temporary tablespace TMP20;
alter user VEDBA temporary tablespace TMP20;
alter user VEAPP1 temporary tablespace TMP20; 

--删除中转用临时表空间
drop tablespace tmp_t2 including contents and datafiles;


+++3
--释放其它临时表空间tmp30
 
--创建中转临时表空间
create temporary tablespace tmp_t3 TEMPFILE 'e:\HS01\TMP\tmp_t3.ORA' size 8192M;
 
--修改用户临时表空间为中转临时表空间(要查询出使用tmp10的用户，然后修改)
alter user CNAPP1 temporary tablespace tmp_t3;
alter user CNDBA temporary tablespace tmp_t3;
alter user DCAPP1 temporary tablespace tmp_t3;
alter user DCDBA temporary tablespace tmp_t3;
alter user EMAPP1 temporary tablespace tmp_t3;
alter user EMDBA temporary tablespace tmp_t3;
alter user POAPP1 temporary tablespace tmp_t3;
alter user PODBA temporary tablespace tmp_t3;
alter user SDAPP1 temporary tablespace tmp_t3;
alter user SDDBA temporary tablespace tmp_t3;
alter user WBDBA temporary tablespace tmp_t3;
alter user WBAPP1 temporary tablespace tmp_t3;
 
--删除原临时表空间
drop tablespace tmp30 including contents and datafiles;
 
--重新创建原临时表空间
create temporary tablespace tmp30
tempfile 'E:\hs01\tmp\tmp31.ora' SIZE 4096M
tablespace group tmpgrp3;
ALTER DATABASE tempFILE 'E:\HS01\tmp\tmp31.ora' AUTOEXTEND OFF;
 
--修改用户临时表空间为原临时表空间
alter user CNAPP1 temporary tablespace TMP30;
alter user CNDBA temporary tablespace TMP30;
alter user DCAPP1 temporary tablespace TMP30;
alter user DCDBA temporary tablespace TMP30;
alter user EMAPP1 temporary tablespace TMP30;
alter user EMDBA temporary tablespace TMP30;
alter user POAPP1 temporary tablespace TMP30;
alter user PODBA temporary tablespace TMP30;
alter user SDAPP1 temporary tablespace TMP30;
alter user SDDBA temporary tablespace TMP30;
alter user WBDBA temporary tablespace TMP30;
alter user WBAPP1 temporary tablespace TMP30;
 
--删除中转用临时表空间
drop tablespace tmp_t3 including contents and datafiles;
 
+++4
--释放其它临时表空间tmp40
 
--创建中转临时表空间
create temporary tablespace tmp_t4 TEMPFILE 'E:\HS01\TMP\tmp_t4.ORA' size 4096M;
 
--修改用户临时表空间为中转临时表空间(要查询出使用tmp10的用户，然后修改)
alter user INAPP1 temporary tablespace tmp_t4;
alter user INDBA temporary tablespace tmp_t4;
alter user QCAPP1 temporary tablespace tmp_t4;
alter user QCDBA temporary tablespace tmp_t4;
alter user SHAPP1 temporary tablespace tmp_t4;
alter user SHDBA temporary tablespace tmp_t4;
alter user SYAPP1 temporary tablespace tmp_t4;
alter user SYDBA temporary tablespace tmp_t4;
alter user exAPP1 temporary tablespace tmp_t4;
alter user exDBA temporary tablespace tmp_t4;
alter user BKPODBA temporary tablespace tmp_t4;
alter user BKPOAPP1 temporary tablespace tmp_t4;
alter user BKINDBA temporary tablespace tmp_t4;
alter user BKINAPP1 temporary tablespace tmp_t4;
alter user hsprd temporary tablespace tmp_t4;
alter user hseng temporary tablespace tmp_t4;
alter user FJTESTDBA temporary tablespace tmp_t4;

 
--删除原临时表空间
drop tablespace tmp40 including contents and datafiles;
 
--重新创建原临时表空间
create temporary tablespace tmp40
tempfile 'E:\hs01\tmp\tmp41.ora' SIZE 4096M
tablespace group tmpgrp4;
ALTER DATABASE tempFILE 'E:\HS01\tmp\tmp41.ora' AUTOEXTEND OFF;
 
--修改用户临时表空间为原临时表空间
alter user INAPP1 temporary tablespace TMP40;
alter user INDBA temporary tablespace TMP40;
alter user QCAPP1 temporary tablespace TMP40;
alter user QCDBA temporary tablespace TMP40;
alter user SHAPP1 temporary tablespace TMP40;
alter user SHDBA temporary tablespace TMP40;
alter user SYAPP1 temporary tablespace TMP40;
alter user SYDBA temporary tablespace TMP40;
alter user exAPP1 temporary tablespace TMP40;
alter user exDBA temporary tablespace TMP40;
alter user BKPODBA temporary tablespace TMP40;
alter user BKPOAPP1 temporary tablespace TMP40;
alter user BKINDBA temporary tablespace TMP40;
alter user BKINAPP1 temporary tablespace TMP40;
alter user hsprd temporary tablespace TMP40;
alter user hseng temporary tablespace TMP40;
alter user FJTESTDBA temporary tablespace TMP40;

--删除中转用临时表空间
drop tablespace tmp_t4 including contents and datafiles;

+++其他处理
--查询临时表空间的使用率
select c.tablespace_name,
to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,
to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,
to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use
from  (select tablespace_name,sum(bytes) bytes
from dba_temp_files GROUP by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool GROUP by tablespace_name) d
where c.tablespace_name = d.tablespace_name;
...
--查询那些用户在使用
select a.username,
       a.sql_id,
       a.SEGTYPE,
       b.BYTES_USED / 1024 / 1024 / 1024 || 'G',
       b.BYTES_FREE / 1024 / 1024 / 1024
  from V$TEMPSEG_USAGE a
  join V$TEMP_SPACE_HEADER b
    on a.TABLESPACE = b.tablespace_name;
...
--查询实例中时候是否有大字段在使用临时表空间： 
select *  from V$TEMPORARY_LOBS;
...
select * from v$sqlarea ds
where ds.sql_id='9m7787camwh4m'
...
select * from v$sqltext st
where st.sql_id='9m7787camwh4m'
...
select * from (
select sess.SID,  segtype, blocks*8/1000 "MB" ,sql_text
from v$sort_usage sort, v$session sess ,v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sort.SQLADDR (+)
order by blocks desc
) a
where rownum<10
...
select * from dba_users dt
where dt.username like '%DBA'
…
--首先查看谁在使用临时段
SELECT su.username,sid,serial#,sql_address,machine,
program,tablespace,segtype,contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr
…
--然后用alter system kill session 'sid,serial#';
--再回缩临时表
alter tablespace temp_t0 coalesce;