重庆思庄Oracle、Redhat认证学习论坛

标题: 11G新特性 -- 收缩临时表空间 [打印本页]

作者: jiawang 时间: 2024-10-16 15:47
标题: 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>

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)