vicker313 tech blog

December 28, 2009

Reduce Size of TEMP Tablespace

Filed under: Oracle — Tags: , — vicker313 @ 8:00 am

Similar to Reduce Size of UNDO Tablespace, you need to recreate temporary tablespace in order to reduce the size. Issue a resize statement might result error below:

alter database tempfile '/u01/database/temp1.dbf' resize 250M
ORA-03297: file contains used data beyond requested RESIZE value

To recreate the temporary tablespace, you need to create a second temporary tablespace:

  1. Create second temporary tablespace
    create temporary tablespace temp2 TEMPFILE '/u01/database/temp2.dbf'size 5M reuse autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
  2. Set second temporary tablespace as default
    alter database default temporary tablespace temp2;
  3. Drop the first temporary tablepsace
    drop tablespace temp including contents and datafiles;
  4. Recreate first temporary tablespace
    create temporary tablespace temp tempfile '/u01/database/TEMP01.dbf' size 300M reuse autoextend on next 50M maxsize unlimited extent management local uniform size 1M;
  5. Set first temporary tablespace as default
    alter database default temporary tablespace temp;
  6. Drop the second tablespace
    drop tablespace temp2 including contents and datafiles;
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: