How To Move The Temp Tablespace to Another Diskgroup On Oracle

dbtut

6 years ago

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.

In this article we will do this step by step.

First we query the empty spaces in the diskgroups in ASM by using the following script.

In this way, you can decide which diskgroup the TEMP tablespace will be moved to.

SELECT name, usable_file_mb FROM v$asm_diskgroup;

1	SELECT name, usable_file_mb FROM v$asm_diskgroup;

We then create a new temp tablespace on the new DiskGroup using the following script.

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE
'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE

'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,

'+RECO_SBA2' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

TABLESPACE GROUP ''

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Then we query the default temp tablespace of the database using the following script.

SELECT * FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

1 2	SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

And we use the following script to set the newly created temp tablespace as the default temp tablespace.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

1	ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

With the help of the following script, we look at the users whose default tablespace is the old temp tablespace.

SELECT * FROM dba_users
WHERE TEMPORARY_TABLESPACE = 'TEMP';

1 2	SELECT * FROM dba_users WHERE TEMPORARY_TABLESPACE = 'TEMP';

If so, we change the default temp tablespace of these users with the following script.

ALTER USER username DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE temp_tablespace_name;

1	ALTER USER username DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE temp_tablespace_name;

The old temp tablespace will be used for a while.

We use the following query to check whether the old temp tablespace is being used.

If there is a session using the old temp tablespace, you can kill the sessions using the kill script in the query.

Before you kill the session, you need to contact the owner of the session.

SELECT tu.tablespace,
tu.username,
s.sid,
s.serial#,
'ALTER SYSTEM KILL SESSION '''
|| s.sid
|| ','
|| s.serial#
|| ',@'
|| s.inst_id
|| ''' IMMEDIATE;'
FROM gv$tempseg_usage tu, gv$session s
WHERE tu.session_addr = s.saddr AND tu.tablespace = 'TEMP';

SELECT tu.tablespace,

tu.username,

s.sid,

s.serial#,

'ALTER SYSTEM KILL SESSION '''

|| s.sid

|| ','

|| s.serial#

|| ',@'

|| s.inst_id

|| ''' IMMEDIATE;'

FROM gv$tempseg_usage tu, gv$session s

WHERE tu.session_addr = s.saddr AND tu.tablespace = 'TEMP';

Finally, we delete the old temp tablespace using the following script.

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

1	DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;