Rename Temp datafiles in 11G

Sorry all its big gap I took to post any update here. Biggest problem is office security. Now I can't access my blog site from office network. So I have to wait till I get some time at home. And as you might know this is festival season here in India to took so long. Any ways come to the point. Recently we have installed R 12.1.1 fresh installation on solaris and had to change many things after install. Temp datafiles location is one of them. But I got confused initially. Becuase when I was checking tablespace_name, file_name from dba_temp_files, I was getting TEMP1 and TEMP2 as tablespace names. But when I checked temporary_tablespace in dba_users I saw it as TEMP. So was worried how come? where is the link between these two miss matched things. Temporary Tablespace Group were the cause for it. Or I would say solution for this.

Here is the brief information about them:

1. A temporary tablespace group must consist of at least one tablespace. There is no explicit max number of tablespaces.

2. If you delete all members from a TTG, the group is automatically deleted as well.

3. When you assign a temporary tablespace to a user, you can use the TTG name instead of the actual temporary tablespace name. you can also use the TTG name when you assign the default temporary tablespace for the database.

This is how we can create one TTG;

1. Create Temporary Tablespace temp1 tempfile '/u02/oracle/temp1_01.dbf' size 100M tablespace group temp;

2. Add more via : Alter tablespace temp2 tablespace group temp;

3. Setting group as default temporary tablespace for the database:

Alter database Default Temporary Tablespace Temp;

4. Query dba_tablespace_groups for information about them.

------------------------------------------------------------------

Now coming to our topic how to rename tempfiles:

1. Create another temp tablespace to make it default.

Create Temporary Tablepspace temp_sd tempfile '/u02/oracle/temp/temp_sd_01.dbf' size 2000M;

2. Make this as default Temp Tablespace:

Alter database default temporary tablespace temp_sd;

3. Check database properties to be sure:

Select property_name, property_value from database_properties where property_name like '%Default_TEMP_TABLESPACE%';

4. Alter database tempfile '/u02/oracle/temp01.dbf' offline;

5. Copy file from old location to new location using OS commands.

cp /u02/oracle/temp01.dbf /u02/oracle/temp/temp01.dbf

6. Rename file

Alter database rename file '/u02/oracle/temp01.dbf' to '/u02/oracle/temp/temp01.dbf';

7. Bring it online

Alter database tempfile '/u02/oracle/temp/temp01.dbf' online;

8. Again change the default:

Alter database default temporary tablespace temp;

9. Drop the newly create temp tablespace

Drop Tablespace temp_sd including contents and datafiles;

Happy Troubleshooting !!!

Happy Diwali !!!