????1.???????
????********************************************************************************
????----1.??fsfi?
????select a.tablespace_name??
????trunc(sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks))))??2) fsfi
????from dba_free_space  a??dba_tablespaces b
????where a.tablespace_name=b.tablespace_name
????and b.contents not in('TEMPORARY'??'UNDO'??'SYSAUX')
????group by A.tablespace_name
????order by fsfi;
???????FSFIС??<30%???????????.
????fsfi????????100?????????????????????????Χ???????fsfi?????????????????Χ????????
????fsfi???????????
????---2.??dba_free_space
????dba_free_space ?????????free ????tablespace ????????tablespace ??free ?????????
?????????free???????dba_free_space?д?????????????????tablespace ?кü????????
??????????????????????????????????????????500???????????????????
????select a.tablespace_name ??count(1) ????? from
????dba_free_space a?? dba_tablespaces b
????where a.tablespace_name =b.tablespace_name
????and b.contents not in('TEMPORARY'??'UNDO'??'SYSAUX')
????group by a.tablespace_name
????having count(1) >20
????order by 2;
????-----3.??????????????????п??
????========
????Script. tfstsfgm
????========
????SET ECHO off
????REM NAME:TFSTSFRM.SQL
????REM USAGE:"@path/tfstsfgm"
????REM ------------------------------------------------------------------------
????REM REQUIREMENTS:
????REM    SELECT ON DBA_FREE_SPACE
????REM ------------------------------------------------------------------------
????REM PURPOSE:
????REM    The following is a script. that will determine how many extents
????REM    of contiguous free space you have in Oracle as well as the 
????REM total amount of free space you have in each tablespace. From 
????REM    these results you can detect how fragmented your tablespace is. 
????REM  
????REM    The ideal situation is to have one large free extent in your 
????REM    tablespace. The more extents of free space there are in the 
????REM    tablespace?? the more likely you  will run into fragmentation 
????REM    problems. The size of the free extents is also  very important. 
????REM    If you have a lot of small extents (too small for any next  
????REM    extent size) but the total bytes of free space is large?? then 
????REM    you may want to consider defragmentation options. 
????REM ------------------------------------------------------------------------
????REM DISCLAIMER:
????REM    This script. is provided for educational purposes only. It is NOT 
????REM    supported by Oracle World Wide Technical Support.
????REM    The script. has been tested and appears to work as intended.
????REM    You should always run new scripts on a test instance initially.
????REM ------------------------------------------------------------------------
????REM Main text of script. follows:
????create table SPACE_TEMP (  
????TABLESPACE_NAME        CHAR(30)??  
????CONTIGUOUS_BYTES       NUMBER)  
????/  
????declare  
????cursor query is select *  
????from dba_free_space  
????order by tablespace_name?? block_id;  
????this_row        query%rowtype;  
????previous_row    query%rowtype;  
????total           number;  
????begin  
????open query;  
????fetch query into this_row;  
????previous_row := this_row;  
????total := previous_row.bytes;  
????loop  
????fetch query into this_row;  
????exit when query%notfound;  
????if this_row.block_id = previous_row.block_id + previous_row.blocks then  
????total := total + this_row.bytes;  
????insert into SPACE_TEMP (tablespace_name)  
????values (previous_row.tablespace_name);  
????else  
????insert into SPACE_TEMP values (previous_row.tablespace_name??  
????total);  
????total := this_row.bytes;  
????end if;  
????previous_row := this_row;  
????end loop;  
????insert into SPACE_TEMP values (previous_row.tablespace_name??  
????total);  
????end;  
????.  
????/  
????set pagesize 60  
????set newpage 0  
????set echo off  
????ttitle center 'Contiguous Extents Report'  skip 3  
????break on "TABLESPACE NAME" skip page duplicate  
????spool contig_free_space.lis  
????rem  
????column "CONTIGUOUS BYTES"       format 999??999??999  
????column "COUNT"                  format 999  
????column "TOTAL BYTES"            format 999??999??999  
????column "TODAY"   noprint new_value new_today format a1  
????rem  
????select TABLESPACE_NAME  "TABLESPACE NAME"??  
????CONTIGUOUS_BYTES "CONTIGUOUS BYTES"  
????from SPACE_TEMP  
????where CONTIGUOUS_BYTES is not null  
????order by TABLESPACE_NAME?? CONTIGUOUS_BYTES desc;  
????select tablespace_name?? count(*) "# OF EXTENTS"??  
????sum(contiguous_bytes) "TOTAL BYTES"   
????from space_temp  
????group by tablespace_name;  
????spool off  
????drop table SPACE_TEMP  
????/ 
????********************************************************************************
????2.?????
????********************************************************************************
????----????1????????????200????(???????????????)
????col frag format 999999.99
????col owner format a30;
????col table_name format a30;
????select * from (
????select a.owner??
????a.table_name??
????a.num_rows??
????a.avg_row_len * a.num_rows total_bytes??
????sum(b.bytes)??
????trunc((a.avg_row_len*a.num_rows)/sum(b.bytes)??2)*100||'%'  frag
????from dba_tables a??dba_segments b
????where a.table_name=b.segment_name
????and a.owner=b.owner
????and a.owner not in
????('SYS'??'SYSTEM'??'OUTLN'??'DMSYS'??'TSMSYS'??'DBSNMP'??'WMSYS'??
????'EXFSYS'??'CTXSYS'??'XDB'??'OLAPSYS'??'ORDSYS'??'MDSYS'??'SYSMAN')
????group by a.owner??a.table_name??a.avg_row_len??a.num_rows
????having a.avg_row_len*a.num_rows/sum(b.bytes)<0.7
????order by sum(b.bytes) desc)
????where rownum<=200;
????---????2??
????-- ???????????
????exec dbms_stats.gather_table_stats(ownname=>'SCOTT'??tabname=> 'TBLORDERS');
????-- ?????????
????SELECT table_name?? trunc(ROUND ((blocks * 8)?? 2)/1024??2) "High water levelM"??
????trunc(ROUND ((num_rows * avg_row_len / 1024)?? 2)/1024??2) "Real  used spaceM"??
????trunc(ROUND ((blocks * 10 / 100) * 8?? 2)/1024??2) "Reserve space(pctfree) M"??
????trunc( ROUND ((  blocks * 8
????- (num_rows * avg_row_len / 1024)
????- blocks * 8 * 10 / 100
????)??
????2
????) /1024??2) "Waste spaceM"
????FROM dba_tables
????WHERE table_name = 'TBLORDERS';
????********************************************************************************
????3.???????
????********************************************************************************
????---1..??????????2??????????С????20M??????
????select id.tablespace_name??
????id.owner??
????id.index_name??
????id.blevel??
????sum(sg.bytes)/1024/1024??
????sg.blocks??
????sg.extents
????from dba_indexes id??dba_segments sg
????where id.owner=sg.owner
????and id.index_name=sg.segment_name
????and id.tablespace_name=sg.tablespace_name
????and id.owner not in
????('SYS'??'SYSTEM'??'USER'??'DBSNMP'??'ORDSYS'??'OUTLN')
????and sg.extents>100
????and id.blevel>=2
????group by id.tablespace_name??
????id.owner??
????id.index_name??
????id.blevel??
????sg.blocks??
????sg.extents
????having sum(sg.bytes)/1024/1024>20;
????---2.analyze index????(??????)
????analyze index index_name validate structure;
????select del_lf_rows*100/decode(lf_rows??0??1??lf_rows) pct_deleted from index_stats;
???????pct_deleted>20%??????????????.
????********************************************************************************
????4.automatic segment advisor
????********************************************************************************
??????????????????в??????o??????????????????????Oracle???????????????Segment shrink??
???????segment????п???????????е?????segment???????DML?????
????????Segment Advisor?????segment?????????????????????ЩSegment??????Segment shrink??
???????shrink??????Segment Advisor?????????ROW MOVEMENT
????SQL> alter table scott.tblorders enable row movement;
????variable id number;
????begin
????declare
????name varchar2(100);
????descr varchar2(500);
????obj_id number;
????begin
????name:='Manual_tblorders';
????descr:='Segment Advisor Example';
????dbms_advisor.create_task (
????advisor_name     => 'Segment Advisor'??
????task_id          => :id??
????task_name        => name??
????task_desc        => descr);
????dbms_advisor.create_object (
????task_name        => name??
????object_type      => 'TABLE'??
????attr1            => 'SCOTT'??
????attr2            => 'TBLORDERS'??
????attr3            => NULL??
????attr4            => NULL??
????attr5            => NULL??
????object_id        => obj_id);
????dbms_advisor.set_task_parameter(
????task_name        => name??
????parameter        => 'recommend_all'??
????value            => 'TRUE');
????dbms_advisor.execute_task(name);
????end;
????end;
????/
????---?????м??
????declare name varchar2(100);
????begin
????name:='Manual_tblorders';
????DBMS_ADVISOR.DELETE_TASK (name);
????end;
????/
????---?????м??
????declare name varchar2(100);
????begin
????name:='Manual_tblorders';
????dbms_advisor.execute_task(name);
????end;
????/
????NOTE:?????м????????????????????????????????????????
????---????????????????????????
????select task_id?? task_name?? status??advisor_name??created from dba_advisor_tasks
????where owner = 'SYS'   and task_name='Manual_tblorders' and advisor_name = 'Segment Advisor' ;
????select af.task_name?? ao.attr2 segname?? ao.attr3 partition?? ao.type?? af.message
????from dba_advisor_findings af?? dba_advisor_objects ao
????where ao.task_id = af.task_id
????and ao.object_id = af.object_id
????and af.task_id=&task_id;
????----???????????shrink?????????
????select  f.task_name?? o.attr2 segname?? o.attr3 partition?? o.type?? f.message
????from dba_advisor_findings f?? dba_advisor_objects o
????where o.object_id = f.object_id
????and o.task_name=f.task_name
????--and f.message like '%shrink%'
????and f.message like '%????%'
????and f.task_id=&task_id
????order by f.impact desc;
????---??automatic segment advisor??recommendations???
????select tablespace_name?? segment_name?? segment_type?? partition_name??
????recommendations?? c1 from
????table(dbms_space.asa_recommendations('FALSE'?? 'FALSE'?? 'FALSE'));