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
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)