Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Sunday, April 15, 2007

Resizing Temporary Tablespaces

How do you resize temporary tablepaces ? This is how I do it in a 9.2.0.4 Oracle database.

Problem is the default temporary tablespace consists of only one tempfile
and that tempfile has grown up to the limit of its maxsize
and we need to compact it.

The following is our default temporary tablespace
SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
/*
PROPERTY_VALUE
TEMP
*/

The creation script of the temporary tablespace TEMP
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'/datac6/oradata/andlp2/temp_1.dbf' SIZE 500M AUTOEXTEND ON NEXT 128M MAXSIZE 8000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

This script is just for your information, to know the structure of the temporary tablespace

Find the tempfile of this temporary tablespace
SELECT tablespace_name, file_name, maxbytes, BYTES
FROM dba_temp_files
WHERE tablespace_name = 'TEMP';
/*
TABLESPACE_NAMEFILE_NAMEMAXBYTESBYTES
TEMP/datac6/oradata/andlp2/temp_1.dbf83886080008388608000
*/

As you can see from the maxbytes and bytes columns this datafile has grown up to its maxsize of 8000M

First of all, we create another temporary tablespace
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE
'/datac7/oradata/andlp2/temp1_1.dbf' SIZE 500M AUTOEXTEND ON NEXT 128M MAXSIZE 8000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Make the new temprary tablespace TEMP1 the default temporary tablespace of the database
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

Change the default temporary teblespaces of users to use he new TEMP1 temporary tablespace which are using the TEMP temporary tablespace before
SELECT 'ALTER USER '  username  ' TEMPORARY TABLESPACE temp1;'
FROM dba_users
WHERE temporary_tablespace = 'TEMP'
ORDER BY username;

Run the output of the above query as a script like below
ALTER USER USER1 TEMPORARY TABLESPACE temp1;
ALTER USER USER2 TEMPORARY TABLESPACE temp1;
ALTER USER USER3 TEMPORARY TABLESPACE temp1;
...

Check if any of the active sessions use the TEMP temporary tablespace
since there should not be any session using it, in order to change the size of it
You can examine the sessions using it and decide to kill them or wait for them to finish their jobs and disconnect
select *
from v$session
where saddr in (select session_addr
from v$tempseg_usage v
where v.tablespace = 'TEMP')

You can kill these sessions in your operating system with "kill -9" command (since the OS I am working on is IBM AIX)
select 'kill -9 'spid
from v$process
where addr in (select paddr
from v$session
where saddr in (select session_addr
from v$tempseg_usage v
where v.tablespace = 'TEMP'))

After killing the sessions you can drop the old TEMP temporary tablespace
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Then to make the structure the same as before, we need to recreate the TEMP temporary tablespace and drop the TEMP1 temporary tablespace
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'/datac6/oradata/andlp2/temp_1.dbf' SIZE 500M AUTOEXTEND ON NEXT 128M MAXSIZE 8000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

select 'ALTER USER 'username' TEMPORARY TABLESPACE TEMP;'
from dba_users
where temporary_tablespace = 'TEMP1'
order by username;
--run the output as script

DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;

No comments: