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
Posts
