asked on

Need to shrink massive temp file in Oracle database

We have an Oracle 11g database running on HP UX. A temporary file suddenly went from 4GB to 9GB and filled up the disk. How do I shrink the file down?
The output from BDF and the db directory listing (db02/DATA) are below:

# bdf
Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol3 1376256 425760 943160 31% /
/dev/vg00/lvol1 1835008 204800 1617552 11% /stand
/dev/vg00/lvol8 8912896 1626104 7239672 18% /var
/dev/vg00/lvol7 7798784 3366744 4397480 43% /usr
/dev/vg00/lvol10 12582912 6210586 5974795 51% /usr/oracle
/dev/vg00/lvol11 524288 110703 387854 22% /usr/cognos
/dev/vg00/lvol9 5242880 2199235 2853451 44% /users
/dev/vg00/lvol4 524288 27368 493368 5% /tmp
/dev/vg00/lvol6 11468800 6081280 5345552 53% /opt
/dev/vg00/lvol5 6291456 1404456 4848880 22% /home
/dev/vg00/lvol19 10485760 3250489 6783072 32% /db08
/dev/vg00/lvol18 10485760 4821905 5309870 48% /db07
/dev/vg00/lvol17 10485760 745704 9131308 8% /db06
/dev/vg00/lvol16 10485760 1266390 8643163 13% /db05
/dev/vg00/lvol15 10485760 2970481 7045579 30% /db04
/dev/vg00/lvol14 10485760 772136 9106528 8% /db03
/dev/vg00/lvol13 10485760 10485760 0 100% /db02
/dev/vg00/lvol12 10485760 5353696 4811954 53% /db01
/dev/vg00/lvol20 10485760 814600 9068795 8% /archive

-rw-r----- 1 oracle dba 20972544 Jan 22 02:06 log2b.dbf
-rw-r----- 1 oracle dba 3149824 Jan 22 15:29 sec_table_01.dbf
-rw-r----- 1 oracle dba 9070186496 Jan 22 08:56 temp_01.dbf
-rw-r----- 1 oracle dba 838311936 Jan 22 15:29 vis_idx_hivol3_01.dbf
-rw-r----- 1 oracle dba 504864768 Jan 22 15:29 vis_idx_inv_01.dbf
-rw-r----- 1 oracle dba 409059328 Jan 22 15:29 vis_sales_01.dbf

johnsone

And that's why autoextend is bad.

Easiest way with no down time, is to create a new temp tablespace with the size you want. Then alter every user to use the new temp space. Then once the old temp space is no longer being used, drop it.

You can easily generate the statements to change the temp space with something like this (untested):

select 'alter user ' || username || ' temporary tablespace temp2;' from dba_users where temporary_tablespace = 'TEMP';

That assumes the new tablespace is TEMP2. Spool that output to a file, then just run it.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsone

I thought that if there was space in use that SHRINK wouldn't necessarily shrink it to the size you wanted.

I could totally be incorrect about that.

Mark Geerlings

I agree that AUTOEXTEND on temp tablespaces is usually a bad idea. One bad query where someone is joining records from a large table to at least one other table, but they forget to include the proper join conditions can lead to a "cartesion join" where all of the records from the large table are joined to all of the records from even a small table. This can quickly cause a huge result set!

It should be no problem to create a new TEMP tablespace with a new name (assuming you can find some free disk space somewhere in your system!). And you will then be able to make this the default TEMP tablespace for your system. You may not even then have to do an "alter user..." command for each user, but those aren't difficult to generate or run as johnsone suggested.

Shrinking or dropping the large TEMP space may not be so easy though. You may have to wait until all sessions that had used space in the current TEMP tablespace have closed their sessions before you will be able to shrink or drop the large TEMP tablespace. (If you have the option of shutting down the database, it will be easy to shrink or drop the large TEMP tablespace following the restart)

glennmgray

ASKER

I did the shrink with no size and it reduced the temp file from 9GB (the backup refused to touch this because it was over 8GB) to a little over 2MB.

Thanks

johnsone

You don't need to back up the temporary tablespace files.

Mark Geerlings

And did you turn autoextend off now on this TEMP tablespace, so you won't get the same problem again?