\n
In some cases, you may need to move the TEMP tablespace to another diskgroup if there is no space left in the ASM or because of other reasons.<\/p>\n
In this article we will do this step by step.<\/p>\n
First we query the empty spaces in the diskgroups in ASM by using the following script.<\/p>\n
In this way, you can decide which diskgroup the TEMP tablespace will be moved to.<\/p>\n
SELECT name, usable_file_mb FROM v$asm_diskgroup;<\/pre>\n<\/p>\n
We then create a new temp tablespace on the new DiskGroup using the following script.<\/p>\n
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE\r\n'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,\r\n'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,\r\n'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,\r\n'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,\r\n'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,\r\n'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED\r\nTABLESPACE GROUP ''\r\nEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;<\/pre>\n<\/p>\n
Then we query the default temp tablespace of the database using the following script.<\/p>\n
SELECT * FROM DATABASE_PROPERTIES\r\nWHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';<\/pre>\n<\/p>\n
And we use the following script to set the newly created temp tablespace as the default temp tablespace.<\/p>\n
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;<\/pre>\n<\/p>\n
With the help of the following script, we look at the users whose default tablespace is the old <\/span>temp <\/span>tablespace.<\/span><\/p>\n
SELECT * FROM dba_users\r\nWHERE TEMPORARY_TABLESPACE = 'TEMP';<\/pre>\n<\/p>\n
If so, we change the default temp tablespace of these users with the following script.<\/p>\n
ALTER USER username DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE temp_tablespace_name;<\/pre>\n<\/p>\n
The old temp tablespace will be used for a while.<\/p>\n
We use the following query to check whether the old temp tablespace is being used.<\/p>\n
If there is a session using the old temp tablespace, you can kill the sessions using the kill script in the query.<\/p>\n
Before you kill the session, you need to contact the owner of the session.<\/p>\n
SELECT tu.tablespace,\r\ntu.username,\r\ns.sid,\r\ns.serial#,\r\n'ALTER SYSTEM KILL SESSION '''\r\n|| s.sid\r\n|| ','\r\n|| s.serial#\r\n|| ',@'\r\n|| s.inst_id\r\n|| ''' IMMEDIATE;'\r\nFROM gv$tempseg_usage tu, gv$session s\r\nWHERE tu.session_addr = s.saddr AND tu.tablespace = 'TEMP';<\/pre>\n<\/p>\n
Finally, we delete the old temp tablespace using the following script.<\/p>\n
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;<\/pre>\n\n<\/div>\n