delete不釋放表空間

psufnxk2000發表於2013-09-16
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章