测试表空间demo_01(20M)上只建有一个表logon_table,
logon_table里有20多万笔记录。
SQL> select count(*) from logon_table;
COUNT(*)
----------
260007
此时查询表空间的占用率:空余空间是1.9M
SQL> SELECT D.TABLESPACE_NAME "Name", D.STATUS "Status",TO_CHAR((A.BYTES / 1024 / 1024), '99,990.9') "Size (M)",TO_CHAR(((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024),'99,990.9') "Used (M)", TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'99,990.9') "Free (M)" FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME;
Name
Status
Size (M)
------------------------------------------------------------------------------------------ --------------------------- ---------------------------
Used (M)
Free (M)
--------------------------- ---------------------------
DEMO_01
ONLINE
20.0
18.1
1.9
SQL> delete from logon_table;
260007 rows deleted.
SQL> commit;
Commit complete.
查询表空间的占用率:空余空间还是1.9M
SQL> SELECT D.TABLESPACE_NAME "Name", D.STATUS "Status",TO_CHAR((A.BYTES / 1024 / 1024), '99,990.9') "Size (M)",TO_CHAR(((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024),'99,990.9') "Used (M)", TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'99,990.9') "Free (M)" FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME;
Name
Status
Size (M)
------------------------------------------------------------------------------------------ --------------------------- ---------------------------
Used (M)
Free (M)
--------------------------- ---------------------------
DEMO_01
ONLINE
20.0
18.1
1.9
这样的话,好象没办法释放表空间,请解释下原因么,
那么是不是重启数据库后,表占用的空间能释放,
还是一直是这样?
|