Oracle Drop Table 释放空间测试

　　Drop Table会释放所占segment的空间，而数据文件占用OS空间不变

一.创建表空间，表，插入300条数据

 1 SQL> create tablespace tbs datafile '/opt/oracle/oradata/nwom/TEST_TBS.dbf' size 1m autoextend off;
 2 
 3 Tablespace created.
 4 
 5 SQL> create table t1 (a char(2000)) tablespace tbs;
 6 
 7 Table created.
 8 
 9 --插入300条数据
10 SQL> begin
11   2     for i in 1..300 loop
12   3             insert into t1 values('a');
13   4     end loop;
14   5  end;
15   6  /
16 
17 PL/SQL procedure successfully completed.
18 
19 SQL> commit;
20 
21 Commit complete.
22 
23 SQL> select count(*) from t1;
24 
25   COUNT(*)
26 ----------
27        300

二.查看表所占用的segment

 1 --300条数据占用段0.9375M
 2 SQL> col SEGMENT_NAME format a10;
 3 SQL> col SEGMENT_TYPE format a20
 4 SQL> col TABLESPACE_NAME format a20
 5 SQL> select segment_name, segment_type, tablespace_name, bytes/1024/1024 "SIZE(M)"
 6   2  from user_segments where segment_name='T1';
 7 
 8 SEGMENT_NA SEGMENT_TYPE         TABLESPACE_NAME         SIZE(M)
 9 ---------- -------------------- -------------------- ----------
10 T1         TABLE                TBS                       .9375
11 
12 SQL>

三.Drop Table

1 SQL> drop table t1;
2 
3 Table dropped.

四.Drop Table后，查看表所占用的segment

1 SQL> select segment_name, segment_type, tablespace_name, bytes/1024/1024 "SIZE(M)"
2   2  from user_segments where segment_name='T1';
3 
4 no rows selected

五.重新建表t1，插入数据100条

 1 SQL> create table t1 (a char(2000)) tablespace tbs;
 2 
 3 Table created.
 4 
 5 --插入100条数据
 6 SQL> begin
 7   2     for i in 1..100 loop
 8   3             insert into t1 values('a');
 9   4     end loop;
10   5  end;
11   6  /
12 
13 PL/SQL procedure successfully completed.
14 
15 SQL> commit;
16 
17 Commit complete.
18 
19 
20 SQL> select count(*) from t1;
21 
22   COUNT(*)
23 ----------
24        100

六.查看表t1所占用segment

1 SQL> select segment_name, segment_type, tablespace_name, bytes/1024/1024 "SIZE(M)"
2   2  from user_segments where segment_name='T1';
3 
4 SEGMENT_NA SEGMENT_TYPE         TABLESPACE_NAME         SIZE(M)
5 ---------- -------------------- -------------------- ----------
6 T1         TABLE                TBS                       .3125
7 
8 SQL>

结论：对比二与六的size(M)，可知Drop Table会释放表占用的segment。