Getting rid of runaway temporary tablespace - 8 Gb reclaimed Oracle Headquarters Redwood Shores1 e1698667100526

Getting rid of runaway temporary tablespace – 8 Gb reclaimed

Even though we keep telling ourselves that iron these days is cheap, I was having difficulties with my laptop’s hard disk. It was almost full – to the brim. I discovered a while back that my Oracle database files took up an alarming amount of diskspace, especially given the pathetically small volume of data I work with – only demo applications. The Temporary Tablespace’s datafile had grown to close to 8 Gb. That’s what autoextend can do for you….

I am not much of a DBA, and to be frank I had no clue as to how to get rid of most of that 8 Gb. Google came to the rescue, and more specifically this article: Resizing Temporary Tablespace by Jeff Hunter. With his very clear explanation and straightforward instructions, I had fixed this problem that had been staring me in the face for quite some time now in no time at all:

SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'TEMP'
/
 
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------
     BYTES
----------
TEMP
C:\ORACLE\ORADATA\ORCL\TEMP01.DBF
8148484096

ALTER DATABASE TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF' DROP INCLUDING DATAFILES;

ALTER TABLESPACE temp ADD TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF' 
SIZE 512m AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Thanks Jeff!

10 Comments

  1. Ravi Bhatia November 5, 2008
  2. Marco Gralike October 9, 2006
  3. Marco Gralike October 9, 2006
  4. Jan Benjamins October 8, 2006
  5. Jan Benjamins October 8, 2006
  6. Lucas Jellema October 8, 2006
  7. Marco Gralike October 7, 2006
  8. Marco Gralike October 7, 2006
  9. John Flack October 5, 2006
  10. Patrick Sinke October 5, 2006