Oracle中对临时表空间执行shrink操作

原创

wx5b8b656889613 2023-06-16 01:19:44 ©著作权

文章标签 java oracle sql 数据库 mysql 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者wx5b8b656889613的原创作品，请联系作者获取转载授权，否则将追究法律责任

《Oracle一个诡异的临时表空间不足的问题》中提到对临时表空间执行shrink space的操作，以前一直理解只有对表能做shrink space的操作，但从官方文档看，11g开始，就可以对临时表空间执行相同的操作。

可以简单测测，当前的临时表空间数据文件大小是512MB，

SQL> !ls -rlht /opt/oracle/oradata/BISALCDB/BISALPDB1/
total 1.2G
-rw-r----- 1 oracle oinstall 211M Jul 29 06:05 users01.dbf
-rw-r----- 1 oracle oinstall 5.1M Jul 29 06:05 tbs_32.dbf
-rw-r----- 1 oracle oinstall 201M Jul 29 06:05 tbs_32_01.dbf
-rw-r----- 1 oracle oinstall 321M Jul 29 15:57 sysaux01.dbf
-rw-r----- 1 oracle oinstall 311M Jul 29 16:27 system01.dbf
-rw-r----- 1 oracle oinstall 101M Jul 29 16:27 undotbs01.dbf
-rw-r----- 1 oracle oinstall 513M Jul 29 16:39 temp01.dbf

通过v$tempfile以及11g新引进的dba_temp_free_space视图，可以看到临时表空间的使用情况，

SQL> select file#,name,bytes/1024/1024 MB from v$tempfile;
     FILE# NAME                                                            MB
---------- ------------------------------------------------------- ----------
         3 /opt/oracle/oradata/BISALCDB/BISALPDB1/temp01.dbf              512


SQL> select * from dba_temp_free_space;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED           INST_ID
------------------------------ --------------- --------------- ---------- ------------- ----------
TEMP                                1610612736         3145728 1607467008 SHARED

执行收缩指令，

SQL> alter tablespace temp shrink space;
Tablespace altered.

可以看到临时表空间已经收缩至了2MB，

SQL> select file#,name,bytes/1024/1024 MB from v$tempfile;
     FILE# NAME                                                            MB
---------- ------------------------------------------------------- ----------
         3 /opt/oracle/oradata/BISALCDB/BISALPDB1/temp01.dbf        1.9921875


SQL> select * from dba_temp_free_space;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED           INST_ID
------------------------------ --------------- --------------- ---------- ------------- ----------
TEMP                                   6266880         3121152    3145728 SHARED

物理的数据文件同样收缩到2MB，

SQL> !ls -rlht /opt/oracle/oradata/BISALCDB/BISALPDB1/
total 1.2G
-rw-r----- 1 oracle oinstall 211M Jul 29 06:05 users01.dbf
-rw-r----- 1 oracle oinstall 5.1M Jul 29 06:05 tbs_32.dbf
-rw-r----- 1 oracle oinstall 201M Jul 29 06:05 tbs_32_01.dbf
-rw-r----- 1 oracle oinstall 321M Jul 29 15:57 sysaux01.dbf
-rw-r----- 1 oracle oinstall 311M Jul 29 16:27 system01.dbf
-rw-r----- 1 oracle oinstall 101M Jul 29 16:27 undotbs01.dbf
-rw-r----- 1 oracle oinstall 2.0M Jul 29 16:39 temp01.dbf

同样支持对临时数据文件的收缩，先增加个临时表空间数据文件，

SQL> alter tablespace temp add tempfile '/opt/oracle/oradata/BISALCDB/BISALPDB1/temp02.dbf' size 512m reuse autoextend on next 1m maxsize unlimited;
Tablespace altered.

初始大小是512MB，

SQL> !ls -rlht /opt/oracle/oradata/BISALCDB/BISALPDB1/
total 1.2G
-rw-r----- 1 oracle oinstall 211M Jul 29 06:05 users01.dbf
-rw-r----- 1 oracle oinstall 5.1M Jul 29 06:05 tbs_32.dbf
-rw-r----- 1 oracle oinstall 201M Jul 29 06:05 tbs_32_01.dbf
-rw-r----- 1 oracle oinstall 321M Jul 29 15:57 sysaux01.dbf
-rw-r----- 1 oracle oinstall 2.0M Jul 29 16:39 temp01.dbf
-rw-r----- 1 oracle oinstall 311M Jul 29 16:40 system01.dbf
-rw-r----- 1 oracle oinstall 101M Jul 29 16:40 undotbs01.dbf
-rw-r----- 1 oracle oinstall 513M Jul 29 16:40 temp02.dbf

从数据库端，可以看到其容量，

SQL> select file#,name,bytes/1024/1024 MB from v$tempfile;
     FILE# NAME                                                            MB
---------- ------------------------------------------------------- ----------
         3 /opt/oracle/oradata/BISALCDB/BISALPDB1/temp01.dbf        1.9921875
         5 /opt/oracle/oradata/BISALCDB/BISALPDB1/temp02.dbf              512


SQL> select * from dba_temp_free_space;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED           INST_ID
------------------------------ --------------- --------------- ---------- ------------- ----------
TEMP                                1616879616         6266880 1610612736 SHARED

对临时数据文件进行收缩，

SQL> alter tablespace temp shrink tempfile '/opt/oracle/oradata/BISALCDB/BISALPDB1/temp02.dbf';
Tablespace altered.

临时表空间已经进行了收缩，

SQL> select file#,name,bytes/1024/1024 MB from v$tempfile;
     FILE# NAME                                                            MB
---------- ------------------------------------------------------- ----------
         3 /opt/oracle/oradata/BISALCDB/BISALPDB1/temp01.dbf        1.9921875
         5 /opt/oracle/oradata/BISALCDB/BISALPDB1/temp02.dbf        1.9921875


SQL> select * from dba_temp_free_space;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED           INST_ID
------------------------------ --------------- --------------- ---------- ------------- ----------
TEMP                                  12533760         6242304    6291456 SHARED

临时数据文件，同样进行了收缩，

SQL> !ls -rlht /opt/oracle/oradata/BISALCDB/BISALPDB1/
total 1.2G
-rw-r----- 1 oracle oinstall 211M Jul 29 06:05 users01.dbf
-rw-r----- 1 oracle oinstall 5.1M Jul 29 06:05 tbs_32.dbf
-rw-r----- 1 oracle oinstall 201M Jul 29 06:05 tbs_32_01.dbf
-rw-r----- 1 oracle oinstall 2.0M Jul 29 16:39 temp01.dbf
-rw-r----- 1 oracle oinstall 101M Jul 29 16:42 undotbs01.dbf
-rw-r----- 1 oracle oinstall 321M Jul 29 16:43 sysaux01.dbf
-rw-r----- 1 oracle oinstall 311M Jul 29 16:43 system01.dbf
-rw-r----- 1 oracle oinstall 2.0M Jul 29 16:46 temp02.dbf

alter tablespace ... shrink还支持KEEP子句，可以指定收缩到的最小容量(MB)，如果未指定，则会尽可能收缩至最小的容量，就像上面的操作。

具体操作信息，还可以参考官方文档《SQL Language Reference》的说明。