oracle碎片如何整理,oracle表碎片情况分析

oracle数据库碎片，有什么好办法整理

用联机重定义，先联机拷贝，找个表访问量小的窗口分分钟完成新旧表切换，基本能满足24小时运转的要求。

创新互联公司为您提适合企业的网站设计 让您的网站在搜索引擎具有高度排名，让您的网站具备超强的网络竞争力！结合企业自身，进行网站设计及把握，最后结合企业文化和具体宗旨等，才能创作出一份性化解决方案。从网站策划到成都网站建设、网站建设， 我们的网页设计师为您提供的解决方案。

oracle 如何清理表空间碎片

这是关于oracle碎片的文章，你看下，另外在oracle中删除表中的数据有两种方法，一种是delete，delete在删除表数据后空间不会自动被oracle回收，另一种是truncate删除表数据后空间会自动被oracle回收，truncate的速度快点。

oracle性能如何调整，如何回收表空间碎片

性能调整：调整需求一般根据数据库服务器的响应时间来确定是否要调整，发现瓶颈，调整性能，解决问题。需要有具体的问题描述，方可意义。

回收表空间碎片：简单粗暴管用的方法，导出后重建，再导入。

如何确定oracle 文件碎片化情况

1.表空间碎片

----1.查看fsfi值

select a.tablespace_name,

trunc(sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))),2) fsfifrom dba_free_space a,dba_tablespaces bwhere a.tablespace_name=b.tablespace_nameand b.contents not in('TEMPORARY','UNDO','SYSAUX')group by A.tablespace_nameorder 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) 碎片量 fromdba_free_space a, dba_tablespaces bwhere a.tablespace_name =b.tablespace_nameand b.contents not in('TEMPORARY','UNDO','SYSAUX')group by a.tablespace_namehaving count(1) 20order 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 extentsREM of contiguous free space you have in Oracle as well as theREM total amount of free space you have in each tablespace. FromREM these results you can detect how fragmented your tablespace is.

REM

REM The ideal situation is to have one large free extent in yourREM tablespace. The more extents of free space there are in theREM tablespace, the more likely you will run into fragmentationREM problems. The size of the free extents is also very important.

REM If you have a lot of small extents (too small for any nextREM extent size) but the total bytes of free space is large, thenREM you may want to consider defragmentation options.

REM ------------------------------------------------------------------------REM DISCLAIMER:

REM This script. is provided for educational purposes only. It is NOTREM 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 thentotal := 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 3break on "TABLESPACE NAME" skip page duplicatespool contig_free_space.lisremcolumn "CONTIGUOUS BYTES" format 999,999,999column "COUNT" format 999column "TOTAL BYTES" format 999,999,999column "TODAY" noprint new_value new_today format a1remselect TABLESPACE_NAME "TABLESPACE NAME",CONTIGUOUS_BYTES "CONTIGUOUS BYTES"from SPACE_TEMPwhere 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_tempgroup by tablespace_name;

spool off

drop table SPACE_TEMP

/

********************************************************************************2.表碎片********************************************************************************----方法1：显示碎片率最高的200个表(基于统计信息是否准确)col frag format 999999.99col 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||'%' fragfrom dba_tables a,dba_segments bwhere a.table_name=b.segment_nameand 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_rowshaving a.avg_row_len*a.num_rows/sum(b.bytes)0.7order 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_nameand id.owner not in('SYS','SYSTEM','USER','DBSNMP','ORDSYS','OUTLN')and sg.extents100and id.blevel=2group by id.tablespace_name,

id.owner,

id.index_name,

id.blevel,

sg.blocks,

sg.extents

having sum(sg.bytes)/1024/102420;

---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_deleted20%说明索引碎片严重.

********************************************************************************4.automatic segment advisor********************************************************************************数据表上频繁的进行插入、更新和删除动作会产生表空间碎片。Oracle可在表或索引上执行Segment shrink。

使得segment的空闲空间可用于表空间中的其它segment，可改善DML性能。

调用Segment Advisor对指定segment执行增长趋势分析以确定哪些Segment受益于Segment shrink。

执行shrink操作，Segment Advisor推荐启用表的ROW MOVEMENTSQL alter table scott.tblorders enable row movement;variable id number;begindeclare

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_taskswhere 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.messagefrom dba_advisor_findings af, dba_advisor_objects aowhere ao.task_id = af.task_idand ao.object_id = af.object_idand af.task_id=task_id;

----只查询可以进行shrink操作的对象

select f.task_name, o.attr2 segname, o.attr3 partition, o.type, f.messagefrom dba_advisor_findings f, dba_advisor_objects owhere o.object_id = f.object_idand 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 fromtable(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE5. 碎片整理方法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:shrinkalter table table_name enable row movement;alter table table_name shrink space cascade; --压缩表以及相关数据段并下调HWMalter 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 cluster3).Move a table or cluster to a different tablespace4).Add or drop partitioning support (non-clustered tables only)5).Change partition structure6).Change physical properties of a single table partition, including moving it to a different tablespace in the same schema7).Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table8).Add support for parallel queries9).Re-create a table or cluster to reduce fragmentation10).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.