Solved

Move TEMP datafile to ASM

Posted on 2014-11-12
7
550 Views
Last Modified: 2014-11-13
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,
0
Comment
Question by:joe_echavarria
  • 4
  • 3
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40438233
-- 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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40438236
---optionally can then rename the new one back to temp

alter tablespace temp2 rename to temp;
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 40438364
What  "Select" statament can i execute to confirm all this  configuration ?
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 1

Author Comment

by:joe_echavarria
ID: 40438381
how can i increase the size of the temp datafile in the ASM ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40438407
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
 
LVL 1

Author Comment

by:joe_echavarria
ID: 40440277
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40440383
if you can't make the files bigger, then add more files


ALTER TABLESPACE TEMP ADD TEMPFILE  SIZE 31G;
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help on model clause 5 48
best datatype for oracle table email creation 8 78
oracle query 3 26
Pass multiple values or string arrays in java as a parameter 3 48
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question