shrink space释放空间

 

1.查询数据库表大小
SQL> select bytes/1024/1024/1024 as "GB" from dba_segments where segment_name='TB_TEST_OBJ';

GB
----------
2.40203857

 

2.删除数据后查询表大小
delete from tb_test_obj;

SQL> select bytes/1024/1024/1024 as "GB" from dba_segments where segment_name='TB_TEST_OBJ';

GB
----------
2.40203857

发现表大小没有变化,下面使用shrink space释放空间

 

3.释放空间
SQL> alter table tb_test_obj shrink space;

一直执行很久

同时开启另外的窗口执行如下语句:
SQL> insert into tb_test_obj(object_name) values('a');

1 row created.

SQL> update tb_test_obj set object_name = 'teste' where object_name='I_USER1';

0 rows updated.

SQL> alter table tb_test_obj add name9 varchar(32) DEFAULT 'aaa' not null;
alter table tb_test_obj add name9 varchar(32) DEFAULT 'aaa' not null
*
ERROR at line 1:
ORA-14411: The DDL cannot be run concurrently with other DDLs

说明:
shrink space期间可以对表进行dml,但是不能ddl

4.查看空间释放情况
SQL> select bytes/1024/1024/1024 as "GB" from dba_segments where segment_name='TB_TEST_OBJ';

GB
----------
.000061035

发现空间以及释放

 

说明:

alter table tb_test_obj shrink space compact; ##compact只对空间进行紧缩(第一阶段,可以在业务不繁忙的时候做),高水位不下降,不回收空间
alter table tb_test_obj shrink space; ##整理碎片并回收空间。

 

-- The End --