Скачать или смотреть How to resize TEMP

How to resize TEMP in oracle DB

--get TEMP tablespace usage
select tablespace_name,(tablespace_size/1024)/1024/1024 ,(allocated_space/1024)/1024/1024 ,(free_space/1024)/1024/1024 from dba_temp_free_space where tablespace_name like '%TEMP%';


--get list of sessions which are using high TEMP from ash
select inst_ID,session_id,sql_exec_start,max(temp_space_allocated)/1024/1024/1024 from gv$active_session_history where (sql_exec_start)##(sysdate-10) group by inst_id,session_id,sql_id,sql_exec_start having max(temp_space_allocated)/1024/1024/1024##5 order by sql_exec_start;

--current TEMP usage in DB by each session
col tablespace for a10
col username for a20
col status for a10
col program for a20
col osuser for a10
select b.tablespace,b.segfile#,b.segblk#,round(((b.blocks * p.value)/1024/1024),2) size_mb,a.inst_id,a.sid,a.serial#,a.username,a.osuser,a.program,a.status from gv$session a,gv$sort_usage b,gv$process c,gv$parameter p where p.name='db_block_size' and a.saddr=b.session_addr and a.paddr=c.addr order by a.inst_id,b.tablespace,b.segfile#,b.segblk#,b.blocks;

--get TEMP size
select sum(bytes)/1024/1024/1024 gb from dba_temp_files where tablespace_name = 'TEMP';
select name,BYTES/1024/1024 from v$tempfile;

--Check which TEMP is using at DB level by default.
col PROPERTY_VALUE for a20
col PROPERTY_NAME for a30
select property_name, property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

--how to resize the TEMP data file
alter database tempfile '/u02/oradata/dbtech/temp01.dbf' resize 300m;

--how to add temp file to TEMP tablespace
alter tablespace TEMP add tempfile '/u02/oradata/dbtech/temp02.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;