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
SQL> create tablespace USERS02 datafile &#39;D:\oracle\product\10.2.0\oradata\ordb10\USER02.DBF&#39; 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,&#39;test&#39;); PL/SQL 过程已成功完成。 SQL> exec dbms_stats.gather_table_stats(user,&#39;test1&#39;); PL/SQL 过程已成功完成。 SQL> select table_name,blocks from user_tables s where s.table_name in(&#39;TEST&#39;,&#39;TEST1&#39;); 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,&#39;test&#39;); PL/SQL 过程已成功完成。 SQL> exec dbms_stats.gather_table_stats(user,&#39;test1&#39;); PL/SQL 过程已成功完成。 SQL> select table_name,blocks from user_tables s where s.table_name in(&#39;TEST&#39;,&#39;TEST1&#39;); TABLE_NAME BLOCKS ------------------------------ ---------- TEST 1384 TEST1 1196