Description:-
In this article we are going to see how to rezize Temp Tablespace on PDB database level
Demo:-
Login in to PDB Database
SQL> alter session set container=ORCLPDB;
Session altered.
First Check Temporary Tablespace Usage
From PDB Level:-
SELECT A.tablespace_name tablespace, D.GB_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_used,D.GB_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 / 1024 GB_total
FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size
) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.GB_total;
TABLESPACE GB_TOTAL GB_USED GB_FREE
—————————— ———- ———- ———-
TEMP .034179688 0 .034179688
From CDB level:-
To find CDB and PDB temporary tablespace files
set lines 100 pages 100
col db_name for a20
col tablespace_name for a20
col file_name for a40
SELECT vc2.name “db_name”,tf.file_name, tf.tablespace_name,
autoextensible, maxbytes/1024/1024/1024 “Max_GB”, SUM(tf.bytes)/1024/1024/1024 “GB_SIZE”
FROM v$containers vc2, cdb_temp_files tf WHERE vc2.con_id = tf.con_id
GROUP BY vc2.name,tf.file_name, tf.tablespace_name, autoextensible, maxbytes
ORDER BY 1, 2;
Increase Temp Tablespace 2 options
Alter Database Tempfile Resize
SQL> select name from v$tempfile;
NAME
——————————————————————————–
/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf
SQL> alter database tempfile ‘/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf’ resize 50m;
Database altered.
Add Tempfile to the tablespace
SQL> alter tablespace TEMP add tempfile ‘/u01/app/oracle/oradata/ORCL/orclpdb/temp02.dbf’ SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
——————————————————————————–
/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/temp02.dbf
How to find Current Size of Temporary Tablespace
SQL> select sum(bytes)/1024/1024/1024 gb from dba_temp_files where tablespace_name = ‘TEMP’;
GB
———-
1.034179688
Information about Temporary Tablespaces and Tempfiles
To find which sessions are using temp space
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;
To check which Temporary Tablespaces using database level
select property_name, property_value from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE’;
To find the objects held in the TEMP tablespace.
select srt.tablespace, srt.segfile#,srt.segblk#, srt.blocks,a.sid, a.serial#, a.username,a.osuser, a.status
from v$session a,v$sort_usage srt
where a.saddr = srt.session_addr
order by srt.tablespace, srt.segfile#, srt.segblk#,srt.blocks;
SELECT S.sid || ‘,’ || S.serial# sid_serial,S.username, T.blocks * 8192 / 1024 / 1024 /1024 gb_used,T.tablespace, Q.sql_fulltext, q.sql_id FROM v$sort_usage T join v$session S on T.session_addr = S.saddr left join v$sqlarea Q on T.sqladdr = Q.address ORDER BY mb_used desc, sid_serial;
Connect with me on:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba
