实例讲解Oracle表碎片的具体起因及解决办法(2)

SQL> ANALYZE TABLE scott.emp LIST CHAINED ROWS; 
SQL> SELECT * FROM chained_rows;

SQL> SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row'; 
NAME VALUE 
---------------------------------------------------------------- --------- 
table fetch continued row 308

-- Get the name of the table with migrated rows: 
ACCEPT table_name PROMPT 'Enter the name of the table with migrated rows: ' 
-- Clean up from last execution 
set echo off 
DROP TABLE migrated_rows; 
DROP TABLE chained_rows; 
-- Create the CHAINED_ROWS table 
@.../rdbms/admin/utlchain.sql 
set echo on 
spool fix_mig 
-- List the chained and migrated rows 
ANALYZE TABLE &table_name LIST CHAINED ROWS; 
-- Copy the chained/migrated rows to another table 
create table migrated_rows as 
SELECT orig.* 
FROM &table_name orig, chained_rows cr 
WHERE orig.rowid = cr.head_rowid 
AND cr.table_name = upper('&table_name'); 
-- Delete the chained/migrated rows from the original table 
DELETE FROM &table_name WHERE rowid IN (SELECT head_rowid FROM chained_rows); 
-- Copy the chained/migrated rows back into the original table 
INSERT INTO &table_name SELECT * FROM migrated_rows; 
spool off

Alter table emp compute statistics.

For ORACLE 7: 
SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)|| 
SUBSTR(rowid,1,8)) "Used" 
FROM schema.table; 
For ORACLE 8+: 
SELECT COUNT (DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)|| 
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used" 
FROM schema.table; 
or 
SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) "Used" 
FROM schema.table;