source 1: http://blog.go-faster.co.uk/2009/01/detecting-and-fixing-row-migration.html
source 2 : http://www.akadia.com/services/ora_chained_rows.html
source 3 : extrait du livre Oracle 12C admin
BEGIN FOR x IN ( SELECT owner, table_name, num_rows FROM all_tables t WHERE 1=1 /*------------------------------------------------------- AND NOT table_name IN(SELECT DISTINCT table_name FROM chained_rows) AND num_rows >= 1000000 AND num_rows BETWEEN 100000 AND 1000000 /*--------------------------------------------------------*/ AND temporary = 'N' ) LOOP DELETE FROM chained_rows WHERE owner_name = x.owner AND table_name = x.table_name; EXECUTE IMMEDIATE 'ANALYZE TABLE '||x.owner||'.'||x.table_name ||' LIST CHAINED ROWS INTO chained_rows'; END LOOP; END; /
Having identified the migrated rows, I produced a simple report
SELECT /*+LEADING(c)*/ c.*, t.num_rows , c.chained_rows/t.num_rows*100 pct_chained , t.pct_free, t.pct_used , 100-FLOOR((100-t.pct_free)*(1-c.chained_rows/t.num_rows)) new_pct_free FROM ( SELECT owner_name, table_name, COUNT(*) chained_rows FROM chained_rows c GROUP BY owner_name, table_name) c , all_tables t WHERE t.owner = c.owner_name AND t.table_name = c.table_name AND t.num_rows > 0 ORDER BY chained_rows desc, 1,2 /
The last column of the report is a calculated new value for PCTFREE. The amount of in a block that can be used to insert new rows (100-PCTFREE) space used is scaled back by the proportion of migrated rows.
NB: This approach doesn't take chained rows into account. Chained rows are too big to fit in a single block under any circumstances, as opposed to migrated rows that could have fitted in a block had there been space. However, Oracle counts both types and stores the result in the column CHAIN_CNT.
Chained Number % % New Owner Table Name Rows of Rows %Chained Free Used %Free -------- -------------------- ---------- -------- -------- ---- ---- ----- MY_APP MY_TABLE 239667 1193233 20.1 10 40 29 …
The script then generates certain commands for each table. First the table is rebuilt by moving it to the same tablespace.
I specify PCTFREE as 1. Usually, the whole table does not need to be rebuilt with the new higher value for PCTFREE. Most of the rows are fully populated and generally will not grow further because they have already migrated. Setting a high value for PCTFREE will simply result in leaving a lot of free space, and result in Oracle reading more blocks for the same data. Instead, I set a low value for PCTFREE in order to pack the data into the minimum number of blocks.
ALTER TABLE MY_APP.MY_TABLE MOVE TABLESPACE MYAPP_TABLE PCTFREE 1;
Fin de source 1.
__________________________________
ALTER TABLE MOVE
First count the number of Rows per Block before the ALTER TABLE MOVE
SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"
FROM row_mig_chain_demo
GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;
Block-Nr Rows
---------- ----------
2066 3
Now, de-chain the table, the ALTER TABLE MOVE rebuilds the row_mig_chain_demo table in a new segment, specifying new storage parameters:
ALTER TABLE row_mig_chain_demo MOVE
PCTFREE 20
PCTUSED 40
STORAGE (INITIAL 20K
NEXT 40K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 0);
Table altered.
Again count the number of Rows per Block after the ALTER TABLE MOVE
SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"
FROM row_mig_chain_demo
GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;
Block-Nr Rows
---------- ----------
2322 1
2324 1
2325 1
Fin source 2.
______________________________