delete不释放表空间

只是delete行，原来所占用的空间不能被别的对象所用。

只有drop了对象之后，才能被别的对象使用。 

只是删除数据，表空间使用率，不会变化。对象占用的大小也不会变化

SQL> create tablespace t_test datafile '/data/t_test01.dbf' size 5m autoextend  off;

Tablespace created.

SQL> drop table test purge;

drop table test purge

           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> create table test tablespace t_test as select * from dba_objects where 1=0 ;

Table created.

SQL> insert into test select * from dba_objects where rownum<1000;

999 rows created.

SQL> /

999 rows created.

SQL> /

999 rows created.

SQL> /

insert into test select * from dba_objects where rownum<1000

*

ERROR at line 1:

ORA-01653: unable to extend table SONG.TEST by 128 in tablespace T_TEST

SQL> /

insert into test select * from dba_objects where rownum<1000

*

ERROR at line 1:

ORA-01653: unable to extend table SONG.TEST by 128 in tablespace T_TEST

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SONG','TEST');

PL/SQL procedure successfully completed.

SQL> select bytes/1024/1024 from dba_segments where segment_name='TEST';

BYTES/1024/1024

---------------

              4

SQL> CREATE TABLE T2 TABLESPACE T_TEST AS SELECT * FROM DBA_OBJECTS WHERE 1=0;

Table created.

SQL> INSERT INTO T2 SELECT * FROM DBA_OBJECTS WHERE ROWNUM<500;

INSERT INTO T2 SELECT * FROM DBA_OBJECTS WHERE ROWNUM<500

            *

ERROR at line 1:

ORA-01658: unable to create INITIAL extent for segment in tablespace T_TEST

SQL> delete from test;       --删除表中所有的数据

38961 rows deleted.

SQL> commit;

Commit complete.

SQL> INSERT INTO T2 SELECT * FROM DBA_OBJECTS WHERE ROWNUM<500;    --删除的空间不能为别的对象所用

INSERT INTO T2 SELECT * FROM DBA_OBJECTS WHERE ROWNUM<500

*

ERROR at line 1:

ORA-01658: unable to create INITIAL extent for segment in tablespace T_TEST

SQL> drop table test purge ;   --删除对象之后， 别的对象才能用

Table dropped.

SQL> INSERT INTO T2 SELECT * FROM DBA_OBJECTS WHERE ROWNUM<500;

499 rows created.

SQL> commit;

Commit complete.

SQL> set line 200

SQL>  SELECT   A.TABLESPACE_NAME,A.BYTES/1024/1024   TOTAL,B.BYTES/1024/1024   USED,   C.BYTES/1024/1024   FREE, 

  2  (B.BYTES*100)/A.BYTES   "%   USED ",(C.BYTES*100)/A.BYTES   "%   FREE " 

  3  FROM   SYS.SM$TS_AVAIL   A,SYS.SM$TS_USED   B,SYS.SM$TS_FREE   C 

  4  WHERE   A.TABLESPACE_NAME=B.TABLESPACE_NAME   AND   A.TABLESPACE_NAME=C.TABLESPACE_NAME AND a.tablespace_name='SONG_TS';

TABLESPACE_NAME                     TOTAL       USED       FREE  %   USED   %   FREE

------------------------------ ---------- ---------- ---------- ---------- ----------

SONG_TS                                        1500       9.25    1488.75 .616666667      99.25

SQL> create table test tablespace song_ts  as select * from dba_objects;

Table created.

SQL> insert into test select * from test;

74963 rows created.

SQL> /

149926 rows created.

SQL> commit;

Commit complete.

SQL>  SELECT   A.TABLESPACE_NAME,A.BYTES/1024/1024   TOTAL,B.BYTES/1024/1024   USED,   C.BYTES/1024/1024   FREE, 

  2  (B.BYTES*100)/A.BYTES   "%   USED ",(C.BYTES*100)/A.BYTES   "%   FREE " 

  3  FROM   SYS.SM$TS_AVAIL   A,SYS.SM$TS_USED   B,SYS.SM$TS_FREE   C 

  4  WHERE   A.TABLESPACE_NAME=B.TABLESPACE_NAME   AND   A.TABLESPACE_NAME=C.TABLESPACE_NAME AND a.tablespace_name='SONG_TS';

TABLESPACE_NAME                     TOTAL       USED       FREE  %   USED   %   FREE

------------------------------ ---------- ---------- ---------- ---------- ----------

SONG_TS                                       1500      43.25    1454.75 2.88333333 96.9833333    --表空间用了 43M

SQL> delete from test;

299852 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SONG','TEST');

PL/SQL procedure successfully completed.

SQL> select bytes/1024/1024 from dba_segments where segment_name='TEST';          --删除所有行之后，对象依然占 34M

BYTES/1024/1024

---------------

             34

SQL>  SELECT   A.TABLESPACE_NAME,A.BYTES/1024/1024   TOTAL,B.BYTES/1024/1024   USED,   C.BYTES/1024/1024   FREE, 

  2  (B.BYTES*100)/A.BYTES   "%   USED ",(C.BYTES*100)/A.BYTES   "%   FREE " 

  3  FROM   SYS.SM$TS_AVAIL   A,SYS.SM$TS_USED   B,SYS.SM$TS_FREE   C 

  4  WHERE   A.TABLESPACE_NAME=B.TABLESPACE_NAME   AND   A.TABLESPACE_NAME=C.TABLESPACE_NAME AND a.tablespace_name='SONG_TS';

TABLESPACE_NAME                     TOTAL       USED       FREE  %   USED   %   FREE

------------------------------ ---------- ---------- ---------- ---------- ----------

SONG_TS                              1500      43.25    1454.75 2.88333333 96.9833333       --表空间使用也没有变

SQL> drop table test purge;

Table dropped.

SQL>  SELECT   A.TABLESPACE_NAME,A.BYTES/1024/1024   TOTAL,B.BYTES/1024/1024   USED,   C.BYTES/1024/1024   FREE, 

  2  (B.BYTES*100)/A.BYTES   "%   USED ",(C.BYTES*100)/A.BYTES   "%   FREE " 

  3  FROM   SYS.SM$TS_AVAIL   A,SYS.SM$TS_USED   B,SYS.SM$TS_FREE   C 

  4  WHERE   A.TABLESPACE_NAME=B.TABLESPACE_NAME   AND   A.TABLESPACE_NAME=C.TABLESPACE_NAME AND a.tablespace_name='SONG_TS';

TABLESPACE_NAME                     TOTAL       USED       FREE  %   USED   %   FREE

------------------------------ ---------- ---------- ---------- ---------- ----------

SONG_TS                              1500       9.25    1488.75 .616666667      99.25          --drop之后，空间使用率变了

by song

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25099483/viewspace-772801/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25099483/viewspace-772801/