delete 删掉行之后,highmark不会下降,表空间使用率显示没有变化。但是再插入新的数据时,
会使用highmark之前的空间,只有highmark前的空间不足时,highmark才会继续上涨。
truncate之后,highmark下降,表空间使用率显示下降。
实验表明,表空间使用率变化与否,与highmark变化与否有关。
SQL> SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES 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.TABLE
SPACE_NAME
5 /
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.TABLE
SPACE_NAME
5 /
TABLESPACE_NAME TOTAL USED FREE % USED % FREE
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264241152 18808832 93.3333333 6.64351852
UNDOTBS1 183500800 71892992 111542272 39.1785714 60.7857143
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 545259520 510263296 34930688 93.5817308 6.40625
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264241152 18808832 93.3333333 6.64351852
UNDOTBS1 183500800 71892992 111542272 39.1785714 60.7857143
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 545259520 510263296 34930688 93.5817308 6.40625
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
已选择7行。
SQL> insert into test1 select * from test1;
已创建13824行。
SQL> /
已创建221184行。
SQL> SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES 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.TABLE
SPACE_NAME
5 /
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.TABLE
SPACE_NAME
5 /
TABLESPACE_NAME TOTAL USED FREE % USED % FREE
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264241152 18808832 93.3333333 6.64351852
UNDOTBS1 183500800 72941568 110493696 39.75 60.2142857
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 566231040 563740672 2424832 99.5601852 .428240741
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264241152 18808832 93.3333333 6.64351852
UNDOTBS1 183500800 72941568 110493696 39.75 60.2142857
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 566231040 563740672 2424832 99.5601852 .428240741
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
已选择7行。
^ 插入数据之后,空间使用率上涨。
——————————————————————————————————
SQL> delete test1 where rownum<2000000;
已删除242369行。
SQL> commit;
提交完成。
SQL> select count(*) from test1;
COUNT(*)
----------
0
----------
0
SQL> SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES 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.TABLE
SPACE_NAME
5 /
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.TABLE
SPACE_NAME
5 /
TABLESPACE_NAME TOTAL USED FREE % USED % FREE
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264241152 18808832 93.3333333 6.64351852
UNDOTBS1 183500800 177799168 5636096 96.8928571 3.07142857
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 566231040 563740672 2424832 99.5601852 .428240741
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264241152 18808832 93.3333333 6.64351852
UNDOTBS1 183500800 177799168 5636096 96.8928571 3.07142857
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 566231040 563740672 2424832 99.5601852 .428240741
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
已选择7行。
^ delete之后,空间使用率无变化
——————————————————————————————————
SQL> insert into test1 select * from dba_users;
已创建27行。
SQL> insert into test1 select * from test1;
已创建27行。
SQL> /
已创建221184行。
SQL> SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES 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.TABLE
SPACE_NAME
5 /
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.TABLE
SPACE_NAME
5 /
TABLESPACE_NAME TOTAL USED FREE % USED % FREE
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264241152 18808832 93.3333333 6.64351852
UNDOTBS1 183500800 179896320 3538944 98.0357143 1.92857143
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 566231040 563740672 2424832 99.5601852 .428240741
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264241152 18808832 93.3333333 6.64351852
UNDOTBS1 183500800 179896320 3538944 98.0357143 1.92857143
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 566231040 563740672 2424832 99.5601852 .428240741
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
已选择7行。
^重新插入数据之后,空间使用率仍无变化。
——————————————————————————————————
SQL> insert into test1 select * from test1;
已创建442368行。
SQL> SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES 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.TABLE
SPACE_NAME
5 /
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.TABLE
SPACE_NAME
5 /
TABLESPACE_NAME TOTAL USED FREE % USED % FREE
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264372224 18677760 93.3796296 6.59722222
UNDOTBS1 183500800 182059008 1376256 99.2142857 .75
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 629145600 625606656 3473408 99.4375 .552083333
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264372224 18677760 93.3796296 6.59722222
UNDOTBS1 183500800 182059008 1376256 99.2142857 .75
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 629145600 625606656 3473408 99.4375 .552083333
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
已选择7行。
^插入更多数据,空间使用率上涨。
————————————————————————————————————
SQL> truncate table test1;
表被截断。
SQL> SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES 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.TABLE
SPACE_NAME
5 /
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.TABLE
SPACE_NAME
5 /
TABLESPACE_NAME TOTAL USED FREE % USED % FREE
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264372224 18677760 93.3796296 6.59722222
UNDOTBS1 183500800 182059008 1376256 99.2142857 .75
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 629145600 508231680 120848384 80.78125 19.2083333
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
--------------- ---------- ---------- ---------- ---------- ----------
AA 1048576 65536 917504 6.25 87.5
SYSAUX 283115520 264372224 18677760 93.3796296 6.59722222
UNDOTBS1 183500800 182059008 1376256 99.2142857 .75
USERS 5242880 3342336 1835008 63.75 35
SYSTEM 629145600 508231680 120848384 80.78125 19.2083333
EXAMPLE 104857600 81395712 23396352 77.625 22.3125
PERFSTAT 524288000 150732800 373489664 28.75 71.2375
已选择7行。
^truncate之后,空间使用率下降。
——————————————————————————————————
——————————————————————————————————
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16426127/viewspace-606383/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16426127/viewspace-606383/