delete 后空间还可以继续使用测试：

create tablespace test datafile '/oracle/oradata/nindb/test01.dbf' size 10m;

创建用户：赋权限

create user cs identified by cs default tablespace test temporary tablespace temp profile default;

grant dba,resource ,connect to cs;

alter user cs quota 10m on test;

[@more@] delete 后空间可以继续使用 [折叠]

创建表空间：

create tablespace test datafile '/oracle/oradata/nindb/test01.dbf' size 10m;

创建用户：赋权限

create user cs identified by cs default tablespace test temporary tablespace temp profile default;

grant dba,resource ,connect to cs;

alter user cs quota 10m on test;

插入数据：

SQL> connect cs/cs ;
Connected.
SQL>
SQL> create table aa as select * from dba_objects;

Table created.

SQL> insert into aa select * from dba_objects;

31368 rows created.

SQL> /
insert into aa select * from dba_objects
*
ERROR at line 1:
ORA-01653: unable to extend table CS.AA by 128 in tablespace TEST

SQL> commit;

察看表空间的利用率：

SQL> SELECT A.TABLESPACE_NAME,A.BYTES/(1024*1024) TOTAL,B.BYTES/(1024*1024) USED, C.BYTES/(1024*1024) FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
2 FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

TABLESPACE_NAME TOTAL USED FREE % USED % FREE
------------------------------ ---------- ---------- ---------- ---------- ----------
DRSYS 20 4.75 15.1875 23.75 75.9375
NINDATA 20480 14279.875 6271.9375 69.7259521 30.6246948
NINIDX 2048 .25 2047.6875 .012207031 99.9847412
SYSTEM 2500 358.125 2141.8125 14.325 85.6725
TEST 10 9 .9375 90 9.375
UNDOTBS1 7085 6.359375 7078.5 .089758292 99.9082569
USERS 250 4.0625 245.875 1.625 98.35
XDB 200 44.6875 155.25 22.34375 77.625

8 rows selected.

SQL> delete from aa;

62736 rows deleted.

SQL> commit;

Commit complete.

查询test 表空间的得利用率没有改变：

SQL> SELECT A.TABLESPACE_NAME,A.BYTES/(1024*1024) TOTAL,B.BYTES/(1024*1024) USED, C.BYTES/(1024*1024) FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
2 FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

TABLESPACE_NAME TOTAL USED FREE % USED % FREE
------------------------------ ---------- ---------- ---------- ---------- ----------
DRSYS 20 4.75 15.1875 23.75 75.9375
NINDATA 20480 14279.875 6271.9375 69.7259521 30.6246948
NINIDX 2048 .25 2047.6875 .012207031 99.9847412
SYSTEM 2500 358.125 2141.8125 14.325 85.6725
TEST 10 9 .9375 90 9.375
UNDOTBS1 7085 21.234375 7063.625 .299708892 99.6983063
USERS 250 4.0625 245.875 1.625 98.35
XDB 200 44.6875 155.25 22.34375 77.625

继续插入数据，仍然可以插入数据

SQL> insert into aa select * from dba_objects;

31368 rows created.

SQL> commit;

Commit complete.

SQL> insert into aa select * from dba_objects;

31368 rows created.

SQL> commit;

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

转载于:http://blog.itpub.net/66233/viewspace-995004/