Description:-
In this article we are going to see about Temporary Tablespace Groups.
Large transactions can sometimes run out of temporary space. Large sort jobs, especially those involving tables with many partitions, lead to heavy use of the temporary tablespaces, thus potentially leading to a performance issue.
Benefits of Temporary Tablespace Groups
- A single user can simultaneously use multiple temporary tablespaces in different sessions.
- You can specify multiple default temporary tablespaces at the database level.
- Parallel execution servers in a parallel operation will efficiently utilize multiple temporary tablespaces.
- SQL queries are less likely to run out of sort space because the query can now simultaneously use several temporary tablespaces for sorting.
- Reduce the contention in case you have multiple temporary tablespaces.
Temp 1 and Temp 2 Tablespaces -> Temp group Tablespace ->Scott,HR
Demo:-
Creating a Temporary Tablespace Group
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> create temporary tablespace temp_group01 tempfile ‘/u01/app/oracle/oradata/ORCL/orclpdb/tempgrp01.dbf’ size 50m tablespace group temptbsgroup;
Tablespace created.
SQL> create temporary tablespace temp_group02 tempfile ‘/u01/app/oracle/oradata/ORCL/orclpdb/tempgrp02.dbf’ size 50m tablespace group temptbsgroup;
Tablespace created.
Viewing Temporary Tablespace Group Information
SQL> select group_name, tablespace_name from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
—————————— ——————————
TEMPTBSGROUP TEMP_GROUP01
TEMPTBSGROUP TEMP_GROUP02
Assigning Temporary Tablespace Groups When Creating user
SQL> create user oracledbwr identified by oracle24 temporary tablespace temptbsgroup;
User created.
Altering Users
SQL> create user ram identified by ram;
User created.
SQL> alter user ram temporary tablespace temptbsgroup;
User altered.
Find out which temporary tablespaces or temporary tablespace groups are assigned to each user
SQL> select username, temporary_tablespace from dba_users where username in(‘ORACLEDBWR’,’RAM’);
USERNAME TEMPORARY_TABLESPACE
——————– ——————————
ORACLEDBWR TEMPTBSGROUP
RAM TEMPTBSGROUP
Setting a Group as the Default Temporary Tablespace for the Database
SQL> alter database default temporary tablespace temptbsgroup;
Database altered.
Find out the name of the current default temporary tablespace on PDB database
SQL> col PROPERTY_NAME for a25
SQL> col PROPERTY_VALUE for a20
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM database_properties WHERE property_name=’DEFAULT_TEMP_TABLESPACE’;
PROPERTY_NAME PROPERTY_VALUE
————————- ——————–
DEFAULT_TEMP_TABLESPACE TEMPTBSGROUP
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
