2

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?

2

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;
0
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.