Friday, 27 February 2015

Changing Temp tablespace Location to new RAC ASM disk group

+++ Changing Temp tablespace Location to new disk group.
This can be done by creating new temp tablespace and dropping the old one

++ Checking the current file and size of the files.
select file_name, bytes/1024/1024 sz_in_mb from dba_temp_files;

++ Creating the new temp tablespace in the new ASM disk group.

create temporary tablespace TEMPNEW tempfile '+BW_TEMP_01' size 12g; -- 80Gb we need to create
alter tablespace TEMPNEW  add tempfile '+BW_TEMP_01' size 12g;
alter tablespace TEMPNEW  add tempfile '+BW_TEMP_01' size 12g;
alter tablespace TEMPNEW  add tempfile '+BW_TEMP_01' size 12g;
alter tablespace TEMPNEW  add tempfile '+BW_TEMP_01' size 12g;

++ Changing the default temp tablespace to new tablespace.

alter database default temporary tablespace TEMPNEW;
select tablespace_name from dba_temp_files;
DROP TABLESPACE TEMP;

Note if the above command hang check for the soring operation and Kill Inactive sessions.

No comments: