Solved

Move TEMP datafile to ASM

Posted on 2014-11-12
7
449 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 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

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

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

810 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