Tuesday, October 02, 2012

Shrinking Tempfiles

In Oracle 9, you could make a tempfile smaller when it was first created:
 
SQL> create temporary tablespace andrew
  2  extent management local
  3  tempfile '/cisdpt/livdpt1/temp/andrew.dbf'
  4  reuse;
 
Tablespace created.
 
SQL> alter database tempfile
  2  '/cisdpt/livdpt1/temp/andrew.dbf'
  3  resize 500m;
 
Database altered.
 
SQL>
 
But once you had used it, that was no longer an option:
 
SQL> alter user oracle
  2  temporary tablespace andrew;

User altered.

SQL> select count(*)
  2  from dba_tables a, dba_tables b
  3  order by a.table_name;
order by a.table_name
                    *
ERROR at line 3:
ORA-01652: unable to extend temp segment by 128 in tablespace ANDREW

SQL> alter database tempfile
  2  '/cisdpt/livdpt1/temp/andrew.dbf'
  3  resize 400m;
alter database tempfile
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL>
 
So if you wanted to reclaim space from a tempfile which had grown too large, you had to rebuild the temporary tablespace it was in. In Oracle 11.2, you seem to have various options to get round this problem:
 
SQL> create temporary tablespace andrew
  2  extent management local
  3  tempfile '/cisdpt/busdpt1/bus_temp/andrew.dbf'
  4  size 500m;

Tablespace created.

SQL> alter user oracle
  2  temporary tablespace andrew;

User altered.

SQL> select count(*)
  2  from dba_tables a, dba_tables b
  3  order by a.table_name;

  COUNT(*)
----------
   9375844

SQL>
 
Resizing the tempfile seems to work:
 
SQL> alter database tempfile
  2  '/cisdpt/busdpt1/bus_temp/andrew.dbf'
  3  resize 10m;

Database altered.

SQL>
 
There is also an ALTER TABLESPACE ... SHRINK SPACE command, which I read about on page 45 of the book advertised below:



SQL> alter tablespace andrew
  2  shrink space keep 5m;

Tablespace altered.

SQL>
 
... and an ALTER TABLESPACE ... SHRINK TEMPFILE command:
 
SQL> l
  1  alter tablespace andrew shrink tempfile
  2  '/cisdpt/busdpt1/bus_temp/andrew.dbf'
  3* keep 5m
SQL> /

Tablespace altered.

SQL>
 
Apparently you can also run this command without the KEEP option but I could not get this to work:
 
SQL> alter database tempfile
  2  '/cisdpt/busdpt1/bus_temp/andrew.dbf'
  3  resize 2000m;

Database altered.

SQL> select file_name, bytes from dba_temp_files
  2  where tablespace_name = 'ANDREW';

FILE_NAME                                     BYTES
---------------------------------------- ----------
/cisdpt/busdpt1/bus_temp/andrew.dbf      2097152000

SQL> alter tablespace andrew shrink tempfile
  2  '/cisdpt/busdpt1/bus_temp/andrew.dbf'
  3  /
alter tablespace andrew shrink tempfile
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

SQL>

No comments: