Oracle 中檢查臨時表空間的方法

--單個臨時檔案
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oracle/TEST/oradata/temp_1.dbf' SIZE 1000M; 
--多個臨時檔案
CREATE TABLESPACE TEMP TEMPFILE 
'/u01/oracle/TEST/oradata/temp_1.dbf' SIZE 1000M 
'/u01/oracle/TEST/oradata/temp_2.dbf' SIZE 1000M 
;
--使用 ASM 
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 1000M;

select tablespace_name,status,contents from dba_tablespaces where contents='TEMPORARY';

select file_name, tablespace_name, bytes from dba_temp_files
或
select a.name,b.name, a.bytes from v$tempfile a, v$tablespace b where a.ts#=b.ts#

select tablespace_name,sum(bytes)/1024/1024/1024 temp_gb
from dba_temp_files group by tablespace_name;
or
select b.name, sum(a.bytes)/1024/1024/1024 from v$tempfile a, v$tablespace b where a.ts#=b.ts# group by b.name;

select tsh.tablespace_name,
dtf.bytes/1024/1024/1024 total_GB,
sum(nvl(tsh.bytes_used,0))/1024/1024/1024 used_GB,
sum(nvl(tsh.bytes_free,0))/1024/1024/1024 free_GB,
(1-sum(nvl(tsh.bytes_free,0))/dtf.bytes)*100 pct,
from v$temp_space_header tsh,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) dtf
where dtf.tablespace_name = tsh.tablespace_name(+)
group by tsh.tablespace_name, dtf.bytes/1024, dtf.bytes
order by 1
/

SELECT * FROM dba_temp_free_space;

select inst_id, tablespace_name, segment_file, total_blocks,
used_blocks, free_blocks, max_used_blocks, max_sort_blocks
from gv$sort_segment;
​
select inst_id, tablespace_name, blocks_cached, blocks_used
from gv$temp_extent_pool;
​
select inst_id,tablespace_name, blocks_used, blocks_free
from gv$temp_space_header;
​
select inst_id,free_requests,freed_extents
from gv$sort_segment;

select tablespace_name, ' %free='|| round(sum(free_blocks) / sum(total_blocks) * 100 ,0)
from gv$sort_segment group by tablespace_name;

set pagesize 50000 echo off verify off feedback off lines 80 heading on
col username format a15 head 'Username'
col osuser format a15 head 'OS User'
col sid format 99999 head 'Sid'
col logon_time format a14 head 'Logon Time'
col tablespace format a15 head 'Tablespace Name'
select
tablespace
, a.osuser
, a.username
, a.sid
, to_char(logon_time,'MM/DD/YY HH24:MI') logon_time
from v$session a, v$sort_usage b
where a.saddr = b.session_addr;

SELECT s.sid、s.username、s.status、u.tablespace、u.segfile#、u.contents、u.extents、u.blocks 
FROM v$session s、v$sort_usage u 
WHERE s.saddr=u。 session_addr 
ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text 
FROM v$session a, v$tempseg_usage b, v$sqlarea c 
WHERE a.saddr = b .session_addr 
AND c.address= a.sql_address 
AND c.hash_value = a.sql_hash_value 
ORDER BY b.tablespace, b.blocks;

col inst_id format 999
col sid format 99999
col status format a11
col program format a20 trunc
col machine format a17 trunc
col action format a39
col module format a39
col blocks heading "TEMP BLOCKS"
SELECT b.blocks, s.inst_id, s.sid, s.status,
s.action,
s.program,
s.machine,
s.module
FROM gv$session s,
gv$sort_usage b
WHERE s.saddr = b.session_addr
and s.inst_id = b.inst_id
and b.blocks > 100000
order by b.blocks desc;

alter database tempfile '+DATA/test/tempfile01.dbf' resize 10000m ;
or
alter tablespace TEMP add tempfile '+DATA/test/tempfile02.dbf' resize 10000m ;

alter database tempfile '+DATA/test/tempfile01.dbf' resize 10000m ;

ALTER DATABASE TEMPFILE '<temp file>' DROP INCLUDING DATAFILES;