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:
Post a Comment