ORACLE 11G--使用SHRINK方法压缩表空间-优快云博客

本文链接：https://blog.youkuaiyun.com/zhangshengdongly/article/details/96427908

本文通过实例展示了如何使用Oracle数据库中的shrink命令来压缩表空间，并观察压缩前后表空间的状态变化，包括表空间大小、已分配空间及空闲空间的变化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1)查看压缩前的表空间状态

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP 2.1934E+10 2.1934E+10 2.1920E+10
EAS_T_EAS1605_STANDARD 62914560 62914560 0
EAS_T_ARPEAS_STANDARD 52428800 5242880 47185920

2)使用shrink命令压缩表空间
SQL> alter tablespace temp shrink space;

Tablespace altered.

3)查看压缩后的表空间状态
SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP 2.1934E+10 13623296 2.1920E+10
EAS_T_EAS1605_STANDARD 62914560 62914560 0
EAS_T_ARPEAS_STANDARD 52428800 5242880 47185920

结论，可以看出ALLOCATED_SPACE 的数据明显降低。

------------生产环境的例子，实践如下---------------
SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP 3.8654E+10 3.4360E+10 3.8652E+10
EAS_T_ARPEAS_STANDARD 73400320 73400320 36700160
EAS_T_ARPDEMO_STANDARD 52428800 52428800 0
EAS_T_EAS1605_STANDARD 52428800 20971520 31457280

SQL> alter tablespace temp shrink space;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP 4177920 2080768 2097152
EAS_T_ARPEAS_STANDARD 73400320 73400320 36700160
EAS_T_ARPDEMO_STANDARD 52428800 52428800 0
EAS_T_EAS1605_STANDARD 52428800 20971520 31457280