Solved

Move TEMP datafile to ASM

Posted on 2014-11-12
7
728 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

630 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