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
　　********************************************************************************
　　数据表上频繁的进行插入、更新和删除动作会产生表空间碎片。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'));

　　********************************************************************************
　　5. 碎片整理方法
　　********************************************************************************
　　------------------------------------------------*
　　5.1表空间碎片整理
　　------------------------------------------------*
　　alter tablespace users coalesce;
　　------------------------------------------------*
　　5.2表碎片整理
　　------------------------------------------------*
　　---方法1:exo/imp或data pump数据泵技术
　　---方法2:CTAS
　　create table newtable as select * from oldtable;
　　drop table oldtable;
　　rename table newtable to oldtable;
　　----方法3:move tablespace技术
　　alter table <table_name> move tablespace <newtablespace_name>;
　　----方法4:shrink
　　alter table <table_name> enable row movement; 
　　alter table <table_name> shrink space cascade;  --压缩表以及相关数据段并下调HWM
　　alter table <table_name> shrink space compact;  --只压缩数据不下调HWM，不影响DML操作
　　alter table <table_name> shrink   space;  --下调HWM，影响DML操作
　　----方法5:online redefinition
　　--online redefinition具有的应用场景:
　　1).Online table redefinition enables you to:
　　2).Modify the storage parameters of a table or cluster
　　3).Move a table or cluster to a different tablespace
　　4).Add or drop partitioning support (non-clustered tables only)
　　5).Change partition structure
　　6).Change physical properties of a single table partition， including moving it to a different tablespace in the same schema
　　7).Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
　　8).Add support for parallel queries
　　9).Re-create a table or cluster to reduce fragmentation
　　10).Convert a relational table into a table with object columns， or do the reverse.
　　11).Convert an object table into a relational table or a table with object columns， or do the reverse.
　　---整理步骤
　　--步骤1：检测表是否具有按主键进行ONLINE REDIFINITION能力
　　BEGIN
　　DBMS_REDEFINITION.CAN_REDEF_TABLE('scott'，'t1'，
　　DBMS_REDEFINITION.CONS_USE_PK);
　　END;
　　/
　　--步骤2：新建一张同结构的临时表
　　create table scott.tp1 tablespace ocpyang
　　as
　　select * from scott.t1 where 1=2;
　　--步骤3:启动ONLINE REDIFINITION
　　BEGIN
　　DBMS_REDEFINITION.START_REDEF_TABLE('scott'， 't1'，'tp1'，
　　''，
　　dbms_redefinition.cons_use_pk);
　　END;
　　/
　　--步骤4:Copy dependent objects. (Automatically create any triggers， indexes， materialized view logs，
　　grants， and constraints on scott.tblorders.)
　　DECLARE
　　num_errors PLS_INTEGER;
　　BEGIN
　　DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('scott'， 't1'，'tp1'，
　　DBMS_REDEFINITION.CONS_ORIG_PARAMS， TRUE， TRUE， TRUE， TRUE， num_errors);
　　END;
　　/
　　--步骤5:检查是否除primary、constraint之外的错误
　　select object_name， base_table_name， ddl_txt from
　　DBA_REDEFINITION_ERRORS;
　　--步骤6：Optionally， synchronize the interim table hr.int_admin_emp.
　　BEGIN
　　DBMS_REDEFINITION.SYNC_INTERIM_TABLE('scott'， 't1'， 'tp1');
　　END;
　　/
　　--步骤7：Complete the redefinition.
　　BEGIN
　　DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott'， 't1'， 'tp1');
　　END;
　　/
　　NOTE:
　　The table scott.tblorders is locked in the exclusive mode only for a small window toward the end of this step.
　　After this call the table scott.tblorders is redefined such that it has all the attributes of the scott.tptblorders table.
　　------------------------------------------------*
　　5.3 索引碎片整理
　　------------------------------------------------*
　　alter index <index_name> rebuild online parallel 4 nologging;
　　alter table <index_name> coalesce;
　　由于rebuild index可以在线、并行、不产生日志方式进行.推荐使用rebuild index.
　　********************************************************************************
　　6.最佳实践
　　********************************************************************************
　　1.针对表的碎片化优先考虑shrink技术；针对索引的碎片优先考虑rebuild index技术；
　　2.如果shrink不理想则采用online redefinition技术
　　3.如果空间不够导致rebuild index无法实施则考虑coalesce技术
　　4.虽然shrink和rebuild index都不影响在线应用但保险起见尽量避免在业务高峰执行
　　5.shrink技术考虑先压缩数据不下调HWM，然后找业务低谷时间再下调HWM并释放空间
　　6.建议rebuild index以非ONLINE方式执行虽然支持online.