Tuesday, 24 July 2012

Row Spaning multiple blocks (Row chaning & Migration)


SELECT 'TASK_NAME     :'||f.TASK_NAME||chr(10)||
'start run time       :'||to_char(execution_start,'dd-mon-yy hh12:mi')||chr(10)||
'SEGMENT NAME         :'||o.ATTR2 ||chr(10)||
'SEGMENT TYPE         :'||o.TYPE ||chr(10)||
'PARTITION NAME       :'||o.ATTR3 ||chr(10)||
'MESSAGE              :'||f.MESSAGE||chr(10)||
'MORE INFO            :'||f.More_INFO||chr(10)||
'----------------------------------------------'Advice
FROM dba_advisor_findings f,dba_advisor_objects o,
DBA_ADVISOR_LOG l
WHERE o.TASK_ID=f.TASK_ID
AND o.OBJECT_ID=f.OBJECT_ID
and f.task_id=l.task_id
and l.execution_start > sysdate - 1
and o.type in ('TABLE','INDEX')
order by f.task_name
/



## Move the table
alter table clientscannedimage move; //move on segemnt
select owner,index_name,status from dba_indexes where table_name='CLIENTSCANNEDIMAGE'
alter index <index_name> rebuild>;

## If the move setting is low space then use it
select table_name,pct_free from user_tables order by 1
alter table clientscannedimage move pctfree 40;

## Detect & Analyze row chaining
-utlchain.sql //create table this script
--analyze table clientscannedimage list chained rows;
---select count(*) from chained_rows where table_name='CLIENTSCANNEDIMAGE'

----analyze table ldbo.clientscannedimage compute statistics;
-----select CHAIN_CNT from user_tables where table_name='CLIENTSCANNEDIMAGE'

Solution--------
create table temp_scannedimage
as select * from clientscannedimage
where rowid(select head_rowid from chained_rows where table_name='CLIENTSCANNEDIMAGE');

delete from emp
where rowid in (select head_rowid from chained_rows where table_name='CLIENTSCANNEDIMAGE');

insert into clientscannedimage select * from temp_scannedimage

No comments: