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

Question

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?

Richard Fitzgerald · Answer

The dba_temp_files view will show any temp tablespaces in the database.
column name format a55
column info format a65
select t.TABLESPACE_NAME,t.FILE_NAME,  to_char(t.BYTES,'999,999,999,999')||' Bytes, '||t.STATUS||' '||d.STATUS||' '|| d.CONTENTS Info
  from dba_temp_files t, dba_tablespaces d
  where t.TABLESPACE_NAME = d.TABLESPACE_NAME ;

You can then 'offline' the tablespace (if you have another temp online) and drop it and delete the datafile.

Gryu · Answer

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;

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

2 Answers

Add your own answers!

Ask a Question