PCTFREE当数据块中的数据量达到这个值时,将不允许继续插入数据; PCTUSED当数据块中数据占用空间小于这个比例时,数据块会被再次使用。
PCTFREE实验,随着pctfree越来越大,block装的记录越少,占用的数据块越多。
SQL> create table test as select rownum as id from dual connect by level<=1960;
表已创建。
SQL> create table test1 pctfree 20 as select rownum as id from dual connect by level<=1960;
表已创建。
SQL> create table test2 pctfree 40 as select rownum as id from dual connect by level<=1960;
表已创建。
SQL> create table test3 pctfree 60 as select rownum as id from dual connect by level<=1960;
表已创建。
SQL> select block_id,count(*) from
(select dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
from test) group by block_id;
BLOCK_ID COUNT(*)
---------- ----------
538805 657
538804 657
538806 646
SQL> select block_id,count(*) from
(select dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
from test1) group by block_id;
BLOCK_ID COUNT(*)
---------- ----------
545348 584
545349 584
545351 208
545350 584
SQL> select block_id,count(*) from
(select dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
from test2) group by block_id;
BLOCK_ID COUNT(*)
---------- ----------
833380 437
833383 437
833384 212
833381 437
833382 437
SQL> select block_id,count(*) from
(select dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
from test3) group by block_id;
BLOCK_ID COUNT(*)
---------- ----------
833389 291
833390 291
833391 291
833392 291
833394 214
833388 291
833393 291
PCTUSED实验,需要建一个手动管理的表空间才行,当删除一个块中的一些数据后再插入一些数据,可以看到PCTUSED值越小的数据块越多。
SQL> create tablespace USERS02 datafile 'D:\oracle\product\10.2.0\oradata\ordb10\USER02.DBF'
size 100m autoextend on next 10m segment space management manual;
SQL> create table test pctused 40 tablespace USERS02 as select * from dba_objects;
表已创建。
SQL> create table test1 pctused 80 tablespace USERS02 as select * from dba_objects;
表已创建。
SQL> exec dbms_stats.gather_table_stats(user,'test');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats(user,'test1');
PL/SQL 过程已成功完成。
SQL> select table_name,blocks from user_tables s where s.table_name in('TEST','TEST1');
TABLE_NAME BLOCKS
------------------------------ ----------
TEST 693
TEST1 693
SQL> delete from test
where rowid in (select rowid
from (select rowid,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
from test)
where num < 20);
已删除13860行。
SQL> delete from test1
where rowid in (select rowid
from (select rowid,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
from test1)
where num < 20);
已删除13860行。
SQL> commit;
提交完成。
SQL> insert into test select * from dba_objects;
已创建50479行。
SQL> insert into test1 select * from dba_objects;
已创建50479行。
SQL> commit;
提交完成。
SQL> exec dbms_stats.gather_table_stats(user,'test');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats(user,'test1');
PL/SQL 过程已成功完成。
SQL> select table_name,blocks from user_tables s where s.table_name in('TEST','TEST1');
TABLE_NAME BLOCKS
------------------------------ ----------
TEST 1384
TEST1 1196