I was managing a database that had a temporary tablespace which was having problems to hold the results of a sort. Then I solved this problem by creating a temporary tablespace group. And according to Oracle Corporation, “… enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.”. So how can I do that?
If you are managing a multitenant database you must have in mind what problem is occurring in one of your PDB or the CDB.
Let’s see if we are going to create this temporary tablespace group in the PDB. The first thing you have to do is to switch to the current PDB:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBTECH01 READ WRITE NO
SQL>alter session set container=PDBTECH01;
Session altered.
Check the current temporary tablespace:
SQL>desc DATABASE_PROPERTIES Name Null? Type ----------------------------------------- -------- ---------------------------- PROPERTY_NAME NOT NULL VARCHAR2(128) PROPERTY_VALUE VARCHAR2(4000) DESCRIPTION VARCHAR2(4000) SQL>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 2 PROPERTY_NAME -------------------------------------------------------------------------------- PROPERTY_VALUE -------------------------------------------------------------------------------- DEFAULT_TEMP_TABLESPACE TEMP
SQL> show con_name CON_NAME ------------------------------ PDBTECH01
Create the tablespaces that it will be part of the group:
SQL>create temporary tablespace tmp01 tempfile '/u01/app/oracle/oradata/CDBTECH/PDBTECH01/temp01new.dbf' size 10m autoextend on; Tablespace created. SQL> create temporary tablespace tmp02 tempfile '/u01/app/oracle/oradata/CDBTECH/PDBTECH01/temp02new.dbf' size 10m autoextend on tablespace group GRUPO; Tablespace created. SQL> alter tablespace tmp02 tablespace group GRUPO; Tablespace altered.
Checking the existing groups:
SQL>desc dba_tablespace_groups Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME NOT NULL VARCHAR2(30) SQL> SQL>select group_name, tablespace_name from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GRUPO TMP01 GRUPO TMP02
Defining the temporary tablespace group called GRUPO as the default temporary tablespace for this PDB:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE GRUPO; Database altered.
Checking the current default temporary tablespace of this PDB:
SQL>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 2 PROPERTY_NAME -------------------------------------------------------------------------------- PROPERTY_VALUE -------------------------------------------------------------------------------- DEFAULT_TEMP_TABLESPACE GRUPO
SQL> SHOW CON_NAME CON_NAME ------------------------------ PDBTECH01
You can also check that the temporary tablespace in the CDB remains unchanged:
SQL> conn /as sysdba Connected. SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 2 PROPERTY_NAME -------------------------------------------------------------------------------- PROPERTY_VALUE -------------------------------------------------------------------------------- DEFAULT_TEMP_TABLESPACE TEMP
*The views expressed here are my own and do not represent those of my employer.*
Hello, I’m Bruno — a dual citizen of Brazil and Sweden. I bring a global perspective shaped by experiences in both South America and Europe, with a strong focus on collaboration and innovation across cultures. I am a Computer Scientist, PhD Candidate in Information and Communication Technologies, focusing on Data Science and Artificial Intelligence, and hold dual Master’s degrees in Data Science and Cybersecurity. With over fifteen years of international experience spanning Brazil, Hungary, and Sweden, I have collaborated with global organizations such as IBM, Playtech, and Oracle, as well as contributed remotely to projects across multiple regions. My professional interests include Databases, Cybersecurity, Cloud Computing, Data Science, Data Engineering, Big Data, Artificial Intelligence, Programming, and Software Engineering, all driven by a deep passion for transforming data into strategic business value.