ITPub博客

首页 > Linux操作系统 > Linux操作系统 > delete不释放表空间

delete不释放表空间

原创 Linux操作系统 作者:psufnxk2000 时间:2013-09-16 23:35:13 0 删除 编辑
只是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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-31

  • 博文量
    215
  • 访问量
    625942