演示PCTUSED和PCTFREE对数据操作的影响

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