HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How to increase size of temp tablespace in Oracle 12C RAC with ASM?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tablespacewithsizetemp12casmracincreasehoworacle

Problem

I have an Oracle 12c RAC consisting of two nodes (OL6) and ASM. I use it for learning. For some reason I want to increase its temporary tablespace size. It's about 50M now. I've considered it using SELECT * FROM dba_temp_free_space; command. I want to increase it to 2048M.

I know, how to increase it in usual configuration:

ALTER DATABASE TEMPFILE '/u02/oradata/my_sid/temp01.dbf' RESIZE 2048M;


knowing temporary tablespace file's path.

I've tried to get information about database files' to see if there are some files with the word "temp" in their names:

select * from dba_data_files;


but I've only got the next files' names:

+DATA/ORCL/DATAFILE/system.287.970304491
+DATA/ORCL/DATAFILE/sysaux.288.970304523
+DATA/ORCL/DATAFILE/users.290.970304549
+DATA/ORCL/DATAFILE/undotbs1.289.970304547
+DATA/ORCL/DATAFILE/undotbs2.295.970304621


So, how to get proper path to a tempXX.dbf file and resize it?

Solution

I've created a new temp file using command:

ALTER TABLESPACE temp
   ADD TEMPFILE '+DATA/ORCL/DATAFILE/temp02.dbf' SIZE 2048M REUSE;


After that SELECT * FROM dba_temp_free_space; command showed me much more space available. However I don't know where old temp file is located and what is its name.

I've found old tempfile name and resized it:

SQL> SELECT * FROM dba_temp_files;

SQL> ALTER DATABASE TEMPFILE '+DATA/ORCL/TEMPFILE/temp.XYZ.XXXXXXXXX' RESIZE 500M;

Code Snippets

ALTER TABLESPACE temp
   ADD TEMPFILE '+DATA/ORCL/DATAFILE/temp02.dbf' SIZE 2048M REUSE;
SQL> SELECT * FROM dba_temp_files;

SQL> ALTER DATABASE TEMPFILE '+DATA/ORCL/TEMPFILE/temp.XYZ.XXXXXXXXX' RESIZE 500M;

Context

StackExchange Database Administrators Q#240774, answer score: 4

Revisions (0)

No revisions yet.