Resizing the TEMP Tablespace

The TEMP tablespace can be resized with or without outage on the database. But resizing tablespace without outage may require few extra steps.

Example considering with outage

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

Considering we have a single temporary tablespace in the database, and the requirement is to resize it and add it into

temporary tablespace groups

1. Check the default temporary tablespace, this is just for validation

SQL>SELECT * FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

Also if you check the TEMPORARY_TABLESPACE column in dba_users table, all the users will have TEMP as temporary tablespace

2. Check the size of the temporary tablespace

SQL> select tablespace_name,sum(bytes)/1024/1024/1000 Size_in_GB from dba_temp_files group by tablespace_name;

TABLESPACE_NAME SIZE_in_GB

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

TEMP 40

3. To make effective use of the volumes available, we can create another temporary tablespace with smaller size and make

note of the volumes already being used by the temporary tablespace and use the same volumes to create our new resized temporary

tablespace

SQL> create temporary tablespace TEMP3

tempfile

'/dv/pper/ora_SCOTT_p7/' size 8600M',

'/dv/pper/ora_SCOTT_p8/' size 8600M

/

You cannot drop the temporary tablespace TEMP as it is still the default temporary tablespace. If you try then you will get

the below error

Hence let us change the default temporary tablespace as TEMP3

SQL> alter database default temporary tablespace TEMP3;

Database altered

SQL>SELECT * FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE TEMP3 Name of default temporary tablespace

4. Now we can drop and recreate the TEMP tablespace as per new sizes

SQL> drop tablespace TEMP;

Tablespace dropped

As we are going to also add the temporary tablespaces to group we can create as below

SQL> create temporary tablespace TEMP1

tempfile

'/dv/pper/ora_SCOTT_p7/' size 8600M',

'/dv/pper/ora_SCOTT_p8/' size 8600M

/

SQL> create temporary tablespace TEMP2

tempfile

'/dv/pper/ora_SCOTT_p7/' size 8600M',

'/dv/pper/ora_SCOTT_p8/' size 8600M

/

SQL> select tablespace_name,sum(bytes)/1024/1024/1000 Size_in_GB from dba_temp_files group by tablespace_name;

TABLESPACE_NAME SIZE_in_GB

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

TEMP1 16

TEMP2 16

5. Now we can add these into temporary tablespace group

SQL> alter tablespace TEMP1 tablespace group TEMP;

Tablespace altered

SQL> alter tablespace TEMP2 tablespace group TEMP;

Tablespace altered

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

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

TEMP TEMP1

TEMP TEMP2

6. Now change the default temporary tablespace to TEMP and drop TEMP3

SQL> alter database default temporary tablespace TEMP;

Database altered

SQL>SELECT * FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

SQL> drop tablespace temp3;

7. As we have retained the name of the tablespace group as TEMP which was same previously, we need not alter the users in the dba_users table.

If you change the name of the tablespace group as TEMP_A, then you need to change the temporary tablespace for all the already created users.

SQL> alter user XABC default temporary tablespace TEMP_A;

New users who will be created after this resize exercise will by default get TEMP_A as temporary tablespace.

Example considering without outage

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

The steps remain the same as above but with few changes

A. Follow steps from 1 to 3

B. Now as the database is in use, the users will use the temporary tablespace TEMP though we have changed the default temporary tablespace to TEMP3.

This is because all the already created users have TEMPORARY_TABLESPACE column in dba_users table updated as TEMP.

So before dropping tablespace TEMP. we need to run alter user commands and change the temporary tablespace to TEMP3 to all the users in dba_users

Once this change is made, any future sessions from this users will us TEMP3 for their operartions.

Now you can drop the TEMP tablespace. Before dropping check that no sessions are using TEMP tablespace.

C. Follow steps 4 and 5

D. As in step B, we now need to change the temporary tablespace of the users back to TEMP if you retained the same previous name, if not then change it

to the new temporary tablespace name.

E. Follow step 6