Move TEMP datafile to ASM

Hi,

  I have my TEMP tablesapce as normal datafile using regular filesystem, and i want to move it to ASM.  How can i do that ?

Regards,
LVL 1
joe_echavarriaDatabase AdministratorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
if you can't make the files bigger, then add more files


ALTER TABLESPACE TEMP ADD TEMPFILE  SIZE 31G;
0
 
sdstuberCommented:
-- set size and other space options to whatever you need, specifying whatever ASM disk group you want to use

CREATE TEMPORARY TABLESPACE TEMP2 tempfile '+DG_DATA' size 256M;

-- assuming TEMP was the default change everyone to use the new temp space
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

-- get rid of the old one
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
0
 
sdstuberCommented:
---optionally can then rename the new one back to temp

alter tablespace temp2 rename to temp;
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
joe_echavarriaDatabase AdministratorAuthor Commented:
What  "Select" statament can i execute to confirm all this  configuration ?
0
 
joe_echavarriaDatabase AdministratorAuthor Commented:
how can i increase the size of the temp datafile in the ASM ?
0
 
sdstuberCommented:
select * from dba_temp_files;

alter database tempfile 'xxxxx' resize 100G; -- adjust size as needed

where xxxxx is the full path for the tempfile  (not datafile) you see in the query above.
0
 
joe_echavarriaDatabase AdministratorAuthor Commented:
I need the temp file to larger  , and i can 't make it larger.   Below the error getting.   The size can not be bigger than 31G.  How can i make it bigger ?, i just want to have one TEMP data file, no adding others datafiles.

SQL> alter database tempfile '+DATA_DEV/qms01dev/tempfile/temp3.269.863443027' r
esize 32G;
alter database tempfile '+DATA_DEV/qms01dev/tempfile/temp3.269.863443027' resize
 32G
*
ERROR at line 1:
ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks


SQL> alter database tempfile '+DATA_DEV/qms01dev/tempfile/temp3.269.863443027' r
esize 31G;

Database altered.
0
All Courses

From novice to tech pro — start learning today.