用聯(lián)機重定義，先聯(lián)機拷貝，找個表訪問量小的窗口分分鐘完成新舊表切換，基本能滿足24小時運轉的要求。
創(chuàng)新互聯(lián)公司為您提適合企業(yè)的網(wǎng)站設計?讓您的網(wǎng)站在搜索引擎具有高度排名，讓您的網(wǎng)站具備超強的網(wǎng)絡競爭力！結合企業(yè)自身，進行網(wǎng)站設計及把握，最后結合企業(yè)文化和具體宗旨等，才能創(chuàng)作出一份性化解決方案。從網(wǎng)站策劃到成都網(wǎng)站建設、網(wǎng)站建設， 我們的網(wǎng)頁設計師為您提供的解決方案。
這是關于oracle碎片的文章，你看下，另外在oracle中刪除表中的數(shù)據(jù)有兩種方法，一種是delete，delete在刪除表數(shù)據(jù)后空間不會自動被oracle回收，另一種是truncate刪除表數(shù)據(jù)后空間會自動被oracle回收，truncate的速度快點。
性能調(diào)整：調(diào)整需求一般根據(jù)數(shù)據(jù)庫服務器的響應時間來確定是否要調(diào)整，發(fā)現(xiàn)瓶頸，調(diào)整性能，解決問題。需要有具體的問題描述，方可意義。
回收表空間碎片：簡單粗暴管用的方法，導出后重建，再導入。
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 空間不連續(xù)，那每段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.按照表空間顯示連續(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個表(基于統(tǒng)計信息是否準確)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)計信息
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********************************************************************************數(shù)據(jù)表上頻繁的進行插入、更新和刪除動作會產(chǎn)生表空間碎片。Oracle可在表或索引上執(zhí)行Segment shrink。
使得segment的空閑空間可用于表空間中的其它segment，可改善DML性能。
調(diào)用Segment Advisor對指定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í)行計劃
declare name varchar2(100);
begin
name:='Manual_tblorders';
DBMS_ADVISOR.DELETE_TASK (name);
end;
/
---手動執(zhí)行計劃
declare name varchar2(100);
begin
name:='Manual_tblorders';
dbms_advisor.execute_task(name);
end;
/
NOTE:如果執(zhí)行計劃結果中已經(jīng)有數(shù)據(jù)則不能直接手動執(zhí)行需要刪除再執(zhí)行---查看手動新建的計劃是否已經(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;
----只查詢可以進行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數(shù)據(jù)泵技術
---方法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; --壓縮表以及相關數(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具有的應用場景:
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.
重慶分公司
重慶分公司
