3 .TEMP TABLESPACE

❮ Previous Next ❯

1.Create a temporary tablespace


CREATE TEMPORARY TABLESPACE TEMP1 '/u01/dbaclass/tempfile/temp01.dbf' size 1G autoextend on next 200M;

2. Adding a tempfile:


alter tablespace TEMP1 add tempfile '/u01/dbaclass/tempfile/temp02.dbf' size 1G autoextend on next 200M;

3. Resize tempfile:


alter database tempfile '/u01/dbaclass/tempfile/temp02.dbf' resize 2G;

4. Drop tempfile:

It will drop and remove the tempfile from physical location. 

 ALTER DATABASE TEMPFILE '/u01/dbaclass/tempfile/temp02.dbf' DROP INCLUDING DATAFILES;

5. Change default temporary tablespace:

When we create a new user, we assign a default temporary tablespace. If nothing is assigned, then it will use the default temporary tablespace set a database level.
Get the current default temporary tablespace for the database 

SQL❯ SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP
Set New default temp tablespace 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
Check the new default temp tablespace 

SQL❯ SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP2

6.Recreate Temp tablespace:

If you want to reclaim the large space consumed by the TEMP TABLESPACE, then we need to drop and recreate the temp tablespace.Find the existing temp tablespace details 

SQL❯ select tablespace_name,file_name from dba_temp_files
 
TABLESPACE_NAME 	       FILE_NAME
------------------------------ --------------------------------------------------------
TEMP			       /home/oracle/app/oracle/oradata/cdb1/orcl/temp.dbf
Create another Temporary Tablespace TEMP1 

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE  '/u01/app/oradata/DBACLASS/temp01' SIZE 2G;
Make the New temporary tablespace as default temp tablespace 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
If any of the old sessions holding temp , then kill them 

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;
 
ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;
Now drop the old tablespace:

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
❮ Previous Next ❯