重庆思庄技术分享

11G新特性 -- 收缩临时表空间
当大任务执行完毕，并不会立即释放临时表空间。有时候通过删除然后重建临时表空间的速度可能更快。不过对于在线系统可能不会那么容易删除重建，所以11g中可以在线收缩临时表空间或单个临时数据文件。
收缩临时表空间
SQL> select name,blocks,bytes from v$tempfile;
NAME BLOCKS BYTES
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/dbt/temp01.dbf 40832 334495744
SQL> alter tablespace temp shrink space;
Tablespace altered.
SQL> select name,blocks,bytes from v$tempfile;
NAME BLOCKS BYTES
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/dbt/temp01.dbf 255 2088960
SQL>
收缩单个临时数据文件
SQL> select name,blocks,bytes from v$tempfile;
NAME BLOCKS BYTES
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/dbt/temp01.dbf 262144 2147483648
SQL> alter tablespace temp shrink tempfile '/u01/app/oracle/oradata/dbt/temp01.dbf';
Tablespace altered.
SQL> select name,blocks,bytes from v$tempfile;
NAME BLOCKS BYTES
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/dbt/temp01.dbf 255 2088960
SQL>
收缩到指定大小
SQL> select name,blocks,bytes from v$tempfile;
NAME BLOCKS BYTES
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/dbt/temp01.dbf 262144 2147483648
SQL> alter tablespace temp shrink space keep 1g;
Tablespace altered.
SQL> select name,blocks,bytes from v$tempfile;
NAME BLOCKS BYTES
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/dbt/temp01.dbf 131072 1073741824
SQL>