TBTL,
Oracle's "new-style" TEMPORARY tablespaces tend to run absolutely hog-wild on space consumption. That is why I still use Oracle's "traditional" TEMPORARY tablespaces. You can tell which type you have by doing a:
Code:
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
If you see your temporary tablespace in the results, you are using traditional temporary tablespaces. If you don't see your temp tablespace listed there, then you are using "new-style".
If you are using "new-style", then the only successful process for resizing your temp tablespace is:
1) Plan to do this during low-usage (night) time.
2) Create a new temporary tablespace. (It will be a "place holder".)
3) Make the new temporary tablespace the default temporary tablespace.
4) Remove the old (runaway) temp tablespace.
5) Recreate another new temp tablespace with the old temp tablespace name.
6) Make the newer temp tablespace the default temporary tablespace.
7) Remove the "place holder" temporary tablespace.
8) Remove the disk file for the "place holder".
Here is prototypical code to do the above steps (presuming that your existing, runaway TEMP tablespace is named "TEMP" and the placeholder tablespace is "TEMP2"):
Code:
(Step 2) SQL> CREATE TEMPORARY TABLESPACE temp2
2 TEMPFILE '<file_name>' SIZE 10M REUSE
3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Tablespace created.
(Step 3) SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Database altered.
(Step 4) SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
(Step 5) SQL> CREATE TEMPORARY TABLESPACE temp
2 TEMPFILE '<file_name of old TEMP ts file>' SIZE 10M REUSE
3 AUTOEXTEND ON NEXT 10M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Tablespace created.
(Step 6) SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Database altered.
(Step 7) SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
(Step 8) (execute o/s commands to remove "the placeholder" data file.)
You can redo this whenever your temp tablespace consumes too much disk space. Let us know your satisfaction with this method.
![[santa] [santa] [santa]](/data/assets/smilies/santa.gif)
Mufasa
(aka Dave of Sandy, Utah, USA)
[
Providing low-cost remote Database Admin services]
Click here to join
Utah Oracle Users Group on Tek-Tips if you use
Oracle in
Utah USA.