oracle11g delete表空间没释放,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

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

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

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

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