50 lines
1.5 KiB
Plaintext
Executable File
50 lines
1.5 KiB
Plaintext
Executable File
created: 20190816092600823
|
|
creator: vplesnila
|
|
modified: 20190816123958688
|
|
modifier: vplesnila
|
|
tags: Oracle
|
|
title: TEMPORARY tablespaces
|
|
type: text/plain
|
|
|
|
-- temporary tablespace size
|
|
select tablespace_name, sum(bytes)/1024/1024 MB
|
|
from dba_temp_files
|
|
group by tablespace_name;
|
|
|
|
-- temporary tablespace allocation/usage report
|
|
select tablespace_name, sum(bytes_used) /1024/1024 mb_used, sum(bytes_cached) /1024/1024 mb_allocated
|
|
from gv$temp_extent_pool
|
|
group by tablespace_name;
|
|
|
|
select tablespace_name,
|
|
file_id,
|
|
extents_cached extents_allocated,
|
|
extents_used,
|
|
bytes_cached/1024/1024 mb_allocated,
|
|
bytes_used/1024/1024 mb_used
|
|
from gv$temp_extent_pool;
|
|
|
|
|
|
--Sort Space Usage by Session--
|
|
set lines 200
|
|
col SID_SERIAL for a10
|
|
col USERNAME for a15
|
|
col OSUSER for a10
|
|
col SPID for a10
|
|
col MODULE for a25
|
|
col PROGRAM for a25
|
|
col TABLESPACE for a15
|
|
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
|
|
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
|
|
COUNT(*) sort_ops
|
|
FROM gv$sort_usage T, gv$session S, dba_tablespaces TBS, gv$process P
|
|
WHERE T.session_addr = S.saddr
|
|
AND S.paddr = P.addr
|
|
AND T.tablespace = TBS.tablespace_name
|
|
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
|
|
S.program, TBS.block_size, T.tablespace
|
|
ORDER BY sid_serial;
|
|
|
|
|
|
-- temp space uded by a query
|
|
select temp_space from gv$sql_plan where sql_id = '&sql_id'; |