Shrinking a Locally Managed Temporary Tablespace

Posted: June 14, 2012 in Space

Just stumbled on the shrink command for the temp tablespace, I normally just do a reisze of the datafile but just tested the command and it works great!

Test

SYS@testdb1> Select tablespace_name, file_name, bytes/1024/1024 “Size MB”,AUTOEXTENSIBLE, MAXBYTES/1024/1024 “Auto MB” from dba_temp_files;

TABLESPACE_NAME    FILE_NAME                                                                                              Size MB AUT Auto MB
—————————— —————————————————————–        ———-  —    ———-
TEMP                                  /wload/test1/app/oracle/u01/oradata/testdb1/temp.dbf    400      YES        2000

Now shrink the tablespace

SYS@testdb1> alter tablespace TEMP SHRINK SPACE KEEP 300M;

Tablespace altered.

Size of the tablespace now 300M

SYS@testdb1> Select tablespace_name, file_name, bytes/1024/1024 “Size MB”,AUTOEXTENSIBLE, MAXBYTES/1024/1024 “Auto MB” from dba_temp_files;

TABLESPACE_NAME    FILE_NAME                                                                                              Size MB AUT Auto MB
—————————— —————————————————————–        ———-  —    ———-
TEMP                                  /wload/test1/app/oracle/u01/oradata/testdb1/temp.dbf    300      YES        2000

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s