如何確定oracle 文件碎片化情況

1.表空間碎片

目前創(chuàng)新互聯(lián)已為上千家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)站空間、網(wǎng)站托管、服務(wù)器托管、企業(yè)網(wǎng)站設(shè)計(jì)、香河網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

----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（一個(gè)理想的單文件表空間）。隨著范圍的增加，fsfi值緩慢下降，而隨著最大范圍尺寸的減少，fsfi值會(huì)迅速下降。

---2.查看dba_free_space

dba_free_space 顯示的是有free 空間的tablespace ，如果一個(gè)tablespace 的free 空間不連續(xù)，那每段free空間都會(huì)在dba_free_space中存在一條記錄。如果一個(gè)tablespace 有好幾條記錄，說明表空間存在碎片，當(dāng)采用字典管理的表空間碎片超過500就需要對(duì)表空間進(jìn)行碎片整理。

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.按照表空間顯示連續(xù)的空閑空間

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個(gè)表(基于統(tǒng)計(jì)信息是否準(zhǔn)確)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：

-- 收集表統(tǒng)計(jì)信息

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方法(會(huì)鎖表)

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%說明索引碎片嚴(yán)重.

********************************************************************************4.automatic segment advisor********************************************************************************數(shù)據(jù)表上頻繁的進(jìn)行插入、更新和刪除動(dòng)作會(huì)產(chǎn)生表空間碎片。Oracle可在表或索引上執(zhí)行Segment shrink。

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

調(diào)用Segment Advisor對(duì)指定segment執(zhí)行增長趨勢分析以確定哪些Segment受益于Segment shrink。

執(zhí)行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;

/

---刪除執(zhí)行計(jì)劃

declare name varchar2(100);

begin

name:='Manual_tblorders';

DBMS_ADVISOR.DELETE_TASK (name);

end;

/

---手動(dòng)執(zhí)行計(jì)劃

declare name varchar2(100);

begin

name:='Manual_tblorders';

dbms_advisor.execute_task(name);

end;

/

NOTE:如果執(zhí)行計(jì)劃結(jié)果中已經(jīng)有數(shù)據(jù)則不能直接手動(dòng)執(zhí)行需要?jiǎng)h除再執(zhí)行---查看手動(dòng)新建的計(jì)劃是否已經(jīng)執(zhí)行完成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;

----只查詢可以進(jìn)行shrink操作的對(duì)象

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結(jié)果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數(shù)據(jù)泵技術(shù)

---方法2:CTAS

create table newtable as select * from oldtable;drop table oldtable;rename table newtable to oldtable;----方法3:move tablespace技術(shù)

alter table table_name move tablespace newtablespace_name;----方法4:shrinkalter table table_name enable row movement;alter table table_name shrink space cascade; --壓縮表以及相關(guān)數(shù)據(jù)段并下調(diào)HWMalter table table_name shrink space compact; --只壓縮數(shù)據(jù)不下調(diào)HWM,不影響DML操作alter table table_name shrink space; --下調(diào)HWM,影響DML操作----方法5:online redefinition--online redefinition具有的應(yīng)用場景:

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.

怎么查看數(shù)據(jù)庫表空間足不足？oracle

這個(gè)方法很多吧，可以使用語句查詢，也可以使用oracle數(shù)據(jù)庫工具查看，還可以用EM。

我這有個(gè)自己寫的sql腳本，是平時(shí)自己看公司數(shù)據(jù)庫表空間狀態(tài)時(shí)用的。你可以參考下：

.查看所有表空間的碎片程度（值在30以下表示碎片很多）

select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes) from (select

b.file_id file_ID,

b.tablespace_name tablespace_name,

b.bytes Bytes,

(b.bytes-sum(nvl(a.bytes,0))) used,

sum(nvl(a.bytes,0)) free,

sum(nvl(a.bytes,0))/(b.bytes)*100 Percent

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id

group by b.tablespace_name,b.file_id,b.bytes

order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes)；

查看表空間是否自動(dòng)擴(kuò)展

select file_name,autoextensible,increment_by from dba_data_files;

表空間自動(dòng)擴(kuò)展

alter database datafile 'path:\datafile name' autoextend on next 1M maxsize 100M;

表空間大小

select tablespace_name,count(*),sum(blocks),sum(bytes)/1024/1024

from dba_data_files

group by tablespace_name;

使用情況

select df.tablespace_name "表空間名",totalspace "總空間M",freespace "剩余空間M",round((1-freespace/totalspace)*100,2) "使用率%"

from

(select tablespace_name,round(sum(bytes)/1024/1024) totalspace

from dba_data_files

group by tablespace_name) df,

(select tablespace_name,round(sum(bytes)/1024/1024) freespace

from dba_free_space

group by tablespace_name) fs

where df.tablespace_name=fs.tablespace_name;

oracle查詢碎片，請問大于多少M(fèi)B，需要整理碎片？ 衡量標(biāo)準(zhǔn)是什么？ 謝謝

select tablespace_name,sqrt(max(blocks)/sum(blocks))*

(100/sqrt(sqrt(count(blocks)))) FSFI

from dba_free_space

group by tablespace_name order by 1;

查詢這個(gè),當(dāng)FSFI=30的時(shí)候建議整理碎片,

另外這種問題分類選數(shù)據(jù)庫分類比較好

在指定的oracle數(shù)據(jù)庫中，查找碎片化程度排名前10的表名稱

4、檢查碎片化嚴(yán)重的表 8K數(shù)據(jù)塊

col owner format a20

col table_name format a40

col "act/block %" format 9999999

select owner,table_name,act_size,block_size,round(act_size/block_size*100,3) "act/block %"

from (

select owner,table_name,round((blocks*8/1024),3) block_size,round((avg_row_len*num_rows/1024/1024),3) act_size

from dba_tables

where owner not in ('SYS','SYSTEM') AND blocks0

)

where block_size20 and round(act_size/block_size,3)*10030;

網(wǎng)頁名稱：oracle怎么查碎片率,oracle表碎片詳解
網(wǎng)頁路徑：http://chinadenli.net/article8/dsgjdop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信公眾號(hào)、Google、全網(wǎng)營銷推廣、動(dòng)態(tài)網(wǎng)站、用戶體驗(yàn)、云服務(wù)器

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)