How to resize TEMP

  Рет қаралды 61

DBTechNinja

DBTechNinja

Күн бұрын

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;

Пікірлер
Lab 8 , Lab 9 , Phase2(B)
55:05
ASHRAF
Рет қаралды 9 М.
Beat Ronaldo, Win $1,000,000
22:45
MrBeast
Рет қаралды 154 МЛН
“Don’t stop the chances.”
00:44
ISSEI / いっせい
Рет қаралды 56 МЛН
Oracle DBA Part 2  Hackers University APC
37:34
Hackers University, APC Learning Solutions
Рет қаралды 7
What is HTTP? How the Internet Works! #1
17:26
FollowAndrew
Рет қаралды 159 М.
Conceptual, Logical & Physical Data Models
13:45
DataAcademy.in
Рет қаралды 485 М.
Starting a New Digital Forensic Investigation Case in Autopsy 4.19+
38:59
Web Server Concepts and Examples
19:40
WebConcepts
Рет қаралды 264 М.
Beat Ronaldo, Win $1,000,000
22:45
MrBeast
Рет қаралды 154 МЛН