Description:-
In this article we are going to see how to Create new Temp Tablespace on PDB database level.
Demo:-
To Find space allocation in a temporary tablespace On CDB Database level
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;
Step 1:- Creating a new temporary tablespace
SQL> alter session set container=ORCLPDB;
Session altered.
CREATE TEMPORARY TABLESPACE TEMP_ORCLPDB TEMPFILE ‘/u01/app/oracle/oradata/ORCL/orclpdb/temp_orclpdb01.dbf’ SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;
Tablespace created.
Step 2:- To check which Temporary Tablespaces using database level
col PROPERTY_NAME for a30
col PROPERTY_VALUE for a20
select property_name, property_value from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE’;
PROPERTY_NAME PROPERTY_VALUE
—————————— ——————–
DEFAULT_TEMP_TABLESPACE TEMP
Step 3:- set default temporary tablespace of database. (which we created above one temp_orclpdb)
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_ORCLPDB;
Database altered.
SQL> select property_name, property_value from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE’;
PROPERTY_NAME PROPERTY_VALUE
—————————— ——————–
DEFAULT_TEMP_TABLESPACE TEMP_ORCLPDB
Step 4:- To find which objects and sessions are using the old temporary tablespace.
select tu.tablespace,tu.username,s.sid,s.serial#,s.inst_id from gv$tempseg_usage tu, gv$session s where tu.session_addr=s.saddr;
Step 5:- We need to kill session and drop the old temp tablespace
select ‘ALTER SYSTEM KILL SESSION ”’||s.sid||’,’||s.serial#||’,@’||s.inst_id||”’;’ from gv$tempseg_usage tu, gv$session s where tu.session_addr=s.saddr and tu.tablespace=’TEMP’;
Step 6:- Drop the old temp tablespace.
Make sure that any session and objects don’t use this tablespace.
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
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
