Running out of Disk Space:

A Query which is badly written may even fill up the disk drive even if the disk is large enough. We need to get back the system onto its feet as quickly as possible.
Temporary Tablespace:
Temporary tablespace generally it holds SQL Execution workspace, intermediate indexes, sub-query information, sorts etc… Generally it is released automatically, but there are situations where this may lead to problems. Issues such as fragmentation, DBMS bugs, badly written queries may cause temporary space to grow till you run out of disk space.

To housekeep the temporary space in oracle is a common activity. The temporary space is flexible and powerful but it comes with some administration overhead.

Bullet:

Let us assume our database is in /u01/oradata/Chaitanya/ and our temporary space is named as temp01.dbf. We have some free space in u02 to use some space for a specified period of time. A SYSDBA user can do this.

Find the name of the current default temporary tablespace

SELECT property_value FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
-----------------------------
TEMP


Create a new temporary tablespace to allow the system to operate while we are making the changes:

CREATE temporary tablespace t1
Tempfile ‘/u02/Chaitanya_temp/t01.dbf’
Size 512M reuse autoextend on next 10M maxsize unlimited
Extent management local uniform size 256K;

Bring the new temporary tablespace online

ALTER DATABASE
Tempfile ‘u02/Chaitanya_temp/t01.dbf’
ONLINE ;

Change the New temporary tablespce as the default temporary tablespace

Alter database default temporary tablespace t1;

Drop the old temporary tablespace which is using the disk space

Drop tablespace temp including the contents and datafiles;

We are running with the new temporary tablespace but what we really wanted to do is recreate the old space in old partition and use the same. While we are doing so, we can restrict the size of the new space as 0.5 GB / 1 Gb, and allow it to grow till some extent let it be 4 GB.

Create temporary tablespace temp
Tempfile ‘u01/oradata/chaitanya/temp01.dbf’
Size 512M reuse autoextend on next 10M maxsize 4096M
Extent management local uniform size 256K;

Now we have to switch every thing back to using this final temp tablespace, and delete the temporary tablespce which we have created for the temporary use (/u02/Chaitanya_temp/t1)

Alter database
Tempfile ‘u01/oradata/Chaitanya/temp01.dbf’
ONLINE;

Alter database default temporary tablespace temp;

Drop tablespace t1 including contents and datafiles;

Please post your comments on this Bullet.


Subscribe to Oracle Bullets by Email