Oracle 資料表空間管理和最佳化

1.TOM大神的表資訊查看過程和授權

create or replace procedure SHOW_SPACE(P_SEGNAME   IN VARCHAR2,                                       P_OWNER     IN VARCHAR2 DEFAULT USER,                                       P_TYPE      IN VARCHAR2 DEFAULT 'TABLE',                                       P_PARTITION IN VARCHAR2 DEFAULT NULL)-- THIS PROCEDURE USES AUTHID CURRENT USER SO IT CAN QUERY DBA_*  -- VIEWS USING PRIVILEGES FROM A ROLE AND SO IT CAN BE INSTALLED  -- ONCE PER DATABASE, INSTEAD OF ONCE PER USER WHO WANTED TO USE IT.AUTHID CURRENT_USER AS  L_FREE_BLKS          NUMBER;  L_TOTAL_BLOCKS       NUMBER;  L_TOTAL_BYTES        NUMBER;  L_UNUSED_BLOCKS      NUMBER;  L_UNUSED_BYTES       NUMBER;  L_LASTUSEDEXTFILEID  NUMBER;  L_LASTUSEDEXTBLOCKID NUMBER;  L_LAST_USED_BLOCK    NUMBER;  L_SEGMENT_SPACE_MGMT VARCHAR2(255);  L_UNFORMATTED_BLOCKS NUMBER;  L_UNFORMATTED_BYTES  NUMBER;  L_FS1_BLOCKS         NUMBER;  L_FS1_BYTES          NUMBER;  L_FS2_BLOCKS         NUMBER;  L_FS2_BYTES          NUMBER;  L_FS3_BLOCKS         NUMBER;  L_FS3_BYTES          NUMBER;  L_FS4_BLOCKS         NUMBER;  L_FS4_BYTES          NUMBER;  L_FULL_BLOCKS        NUMBER;  L_FULL_BYTES         NUMBER;  -- INLINE PROCEDURE TO PRINT OUT NUMBERS NICELY FORMATTED  -- WITH A SIMPLE LABEL.  PROCEDURE P(P_LABEL IN VARCHAR2, P_NUM IN NUMBER) IS  BEGIN    DBMS_OUTPUT.PUT_LINE(RPAD(P_LABEL, 40, '.') ||                         TO_CHAR(P_NUM, '999,999,999,999'));  END;BEGIN  -- THIS QUERY IS EXECUTED DYNAMICALLY IN ORDER TO ALLOW THIS PROCEDURE  -- TO BE CREATED BY A USER WHO HAS ACCESS TO DBA_SEGMENTS/TABLESPACES  -- VIA A ROLE AS IS CUSTOMARY.  -- NOTE: AT RUNTIME, THE INVOKER MUST HAVE ACCESS TO THESE TWO  -- VIEWS!  -- THIS QUERY DETERMINES IF THE OBJECT IS AN ASSM OBJECT OR NOT.  BEGIN    EXECUTE IMMEDIATE 'SELECT TS.SEGMENT_SPACE_MANAGEMENTFROM DBA_SEGMENTS SEG, DBA_TABLESPACES TSWHERE SEG.SEGMENT_NAME = :P_SEGNAMEAND (:P_PARTITION IS NULL ORSEG.PARTITION_NAME = :P_PARTITION)AND SEG.OWNER = :P_OWNERAND SEG.TABLESPACE_NAME = TS.TABLESPACE_NAME'      INTO L_SEGMENT_SPACE_MGMT      USING P_SEGNAME, P_PARTITION, P_PARTITION, P_OWNER;  EXCEPTION    WHEN TOO_MANY_ROWS THEN      DBMS_OUTPUT.PUT_LINE('THIS MUST BE A PARTITIONED TABLE, USE P_PARTITION => ');      RETURN;  END;  -- IF THE OBJECT IS IN AN ASSM TABLESPACE, WE MUST USE THIS API  -- CALL TO GET SPACE INFORMATION; ELSE WE USE THE FREE_BLOCKS  -- API FOR THE USER MANAGED SEGMENTS.  IF L_SEGMENT_SPACE_MGMT = 'AUTO' THEN    DBMS_SPACE.SPACE_USAGE(P_OWNER,                           P_SEGNAME,                           P_TYPE,                           L_UNFORMATTED_BLOCKS,                           L_UNFORMATTED_BYTES,                           L_FS1_BLOCKS,                           L_FS1_BYTES,                           L_FS2_BLOCKS,                           L_FS2_BYTES,                           L_FS3_BLOCKS,                           L_FS3_BYTES,                           L_FS4_BLOCKS,                           L_FS4_BYTES,                           L_FULL_BLOCKS,                           L_FULL_BYTES,                           P_PARTITION);    P('UNFORMATTED BLOCKS ', L_UNFORMATTED_BLOCKS);    P('FS1 BLOCKS (0-25) ', L_FS1_BLOCKS);    P('FS2 BLOCKS (25-50) ', L_FS2_BLOCKS);    P('FS3 BLOCKS (50-75) ', L_FS3_BLOCKS);    P('FS4 BLOCKS (75-100)', L_FS4_BLOCKS);    P('FULL BLOCKS ', L_FULL_BLOCKS);  ELSE    DBMS_SPACE.FREE_BLOCKS(SEGMENT_OWNER     => P_OWNER,                           SEGMENT_NAME      => P_SEGNAME,                           SEGMENT_TYPE      => P_TYPE,                           FREELIST_GROUP_ID => 0,                           FREE_BLKS         => L_FREE_BLKS);    P('FREE BLOCKS', L_FREE_BLKS);  END IF;  -- AND THEN THE UNUSED SPACE API CALL TO GET THE REST OF THE  -- INFORMATION.  DBMS_SPACE.UNUSED_SPACE(SEGMENT_OWNER             => P_OWNER,                          SEGMENT_NAME              => P_SEGNAME,                          SEGMENT_TYPE              => P_TYPE,                          PARTITION_NAME            => P_PARTITION,                          TOTAL_BLOCKS              => L_TOTAL_BLOCKS,                          TOTAL_BYTES               => L_TOTAL_BYTES,                          UNUSED_BLOCKS             => L_UNUSED_BLOCKS,                          UNUSED_BYTES              => L_UNUSED_BYTES,                          LAST_USED_EXTENT_FILE_ID  => L_LASTUSEDEXTFILEID,                          LAST_USED_EXTENT_BLOCK_ID => L_LASTUSEDEXTBLOCKID,                          LAST_USED_BLOCK           => L_LAST_USED_BLOCK);  P('TOTAL BLOCKS', L_TOTAL_BLOCKS);  P('TOTAL BYTES', L_TOTAL_BYTES);  P('TOTAL MBYTES', TRUNC(L_TOTAL_BYTES / 1024 / 1024));  P('UNUSED BLOCKS', L_UNUSED_BLOCKS);  P('UNUSED BYTES', L_UNUSED_BYTES);  P('LAST USED EXT FILEID', L_LASTUSEDEXTFILEID);  P('LAST USED EXT BLOCKID', L_LASTUSEDEXTBLOCKID);  P('LAST USED BLOCK', L_LAST_USED_BLOCK);END;讓普通使用者能執行SYS.SHOW_SPACESYS@zcs11G>  drop user zcs1 CASCADE;create user zcs identified by zcs;grant connect,resource,dba to zcs;grant execute on SYS.SHOW_SPACE TO zcs;connect zcs/zcsdrop table t1 purge;create table t1 (id int,name varchar2(19)) segment creation IMMEDIATE tablespace users;set serverout on;exec sys.show_space('T1');

2.HVW查看方法

.01-查看HWM  = 2一、使用user_tables查看——HWM=0+1段頭塊+1=2ZCS@11g>  analyze table t3 compute statistics;select '--',t.blocks,t.empty_blocks,s.blocks from user_tables t,dba_segments s where t.table_name=s.segment_name and t.table_name='T3' and owner='SYS';--0=hwm之下曾用過的塊   7=hwm之外已指派給表未用過的塊  8=段大小二、使用show_space查看——HWM=Total_Blocks8 – Unused_Blocks7 + 1 =2ZCS@11g>  set serverout on;exec sys.show_space('T3');/* FREE BLOCKS.............................               0TOTAL BLOCKS............................               8UNUSED BLOCKS...........................               7 */

3.Shrink收縮高水位

一、shrink操作1.行的rowid會改變所以表必須啟用row movementSYS@zcs11G>  alter table t4 enable row movement;2.shrink space cascadecascade可省略）SYS@zcs11G>  alter table t4 shrink space cascade;4.shrink space可分成兩步逐步執行1、shrink space  compact   忙時：僅重整表記錄行，HWM及索引不變2、shrink space  cascade   閑時：其餘全部動作

4.MOVE收縮HVW

ALTER TABLE MOVE 步驟:1. desc username.table_name  ----檢查表中是否有LOB2. 如果表沒有LOB欄位    直接 alter table move; 然後 rebuild index--如果表中包含了LOB欄位alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lobsegment       tablespace tablespace_name;                                       --也可以單獨move lob，但是表上面的index 同樣會失效，這是不推薦的alter table owner.table_name move lob(lob_column) store as lobsegment tablespace tablespace_name ;3. rebuild index 首先用下面的SQL查看錶上面有哪類索引:select a.owner,a.index_name,a.index_type,a.partitioned,a.status,b.status p_status,b.composite from dba_indexesa left join dba_ind_partitions b on a.owner=b.index_owner and a.index_name=b.index_name where a.owner='&owner'  and a.table_name='&table_name';                                     對於普通索引直接rebuild index index_name online nologging parallel,對於分區索引，必須單獨rebuild 每個分區,對於組合分區索引,必須單獨rebuild 每個子分區。4.對錶收集統計資訊限制：雖然在10g中可以用shrink ，但也有些限制：1). 對cluster，cluster table，或具有Long，lob類型列的對象 不起作用。2). 不支援具有function-based indexes 或 bitmap join indexes的表3). 不支援mapping 表或index-organized表。4). 不支援compressed 表

注意：

1、alter table move 省略了tablespace XXX, 表示使用者移到自己預設的資料表空間，因此當前資料表空間至少要是該表兩倍大。

2、alter table move過程中會導致索引失效，必須要考慮重新索引

3、alter table move過程中會產生鎖，應該避免在業務高峰期操作！

5.遷移表到某資料表空間

一、move普通表、索引1、基本文法：a、alter table table_name t move tablespace xxx;b、alter index index_name rebuild tablespace xxx;move過的普通表，在不用到失效的索引的動作陳述式中，語句執行正常，但如果操作的語句用到了索引主鍵當做唯一索引），則此時報告用到的索引失效，語句執行失敗，其他如外鍵，非空約束，預設值等不會失效。2、重新建立主鍵或索引基本文法為：a、alter index index_name rebuild;b、alter index pk_name rebuild;3、move索引用rebuild文法：a、alter index index_name rebuild tablespace tbs_name;b、alter index pk_name rebuild tablespace tbs_name;二、move分區表及索引和普通表一樣，分區表索引會失效，區別的僅僅是文法而已。1、分區基本文法註：如果是單級分區，則使用關鍵字PARTITION，如果是多級分區，則使用SUBPARTITION替代PARTITION。如果分區或分區索引比較大，可以使用並行move或rebuild，PARALLEL (DEGREE 2);如：ALTER TABLE PART_ALARM move SUBPARTITION p_01 TABLESPACE usersPARALLEL (DEGREE 2);--全域索引ALTER INDEX GX1_ PART_ALARM REBUILD tablespace usersPARALLEL (DEGREE 2);--分區索引ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION p_01 TABLESPACE users1PARALLEL (DEGREE 2);ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION p_02 TABLESPACE users2PARALLEL (DEGREE 2);………………ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION p_0n TABLESPACE usersnPARALLEL (DEGREE 2);2、移動表的某個分區ALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name;3、重建全域索引ALTER INDEX global_index REBUILD;或ALTER INDEX global_index REBUILD tablespace tbs_name;4、重建局部索引ALTER TABLE tab_name MODIFY PARTITION partition_name REBUILD UNUSABLE LOCAL INDEXES;或ALTER INDEX local_index_name REBUILD PARTITION partition_name TABLESPACE tbs_name;8.資料表空間使用方式極愛內控

8.資料表空間使用方式監控

6-綜合監控dba_data_files、dba_free_space）SYS@11g> select'--Disk_MB='||disk,'Free_MB='||free,'used_MB='|| (disk - free)from ( select tablespace_name,sum(bytes)/1024/1024 disk from dba_data_files  group by tablespace_name) a, ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space   group by tablespace_name) b where a.tablespace_name=b.tablespace_name and a.tablespace_name='SYSTEM';--Disk_MB=680    Free_MB=4.375      Used_MB=675.62502.7-綜合監控sm$ts_avail、sm$ts_used、sm$ts_free）SYS@11g>   select'--Disk_use_MB='||disk.BYTES/1024/1024,'Used_MB='||trunc(used.BYTES/1024/1024) ,'Free_MB='||trunc(free.BYTES/1024/1024)fromsm$ts_avail disk,sm$ts_used used,sm$ts_free freewhere disk.tablespace_name=used.tablespace_nameand disk.tablespace_name=free.tablespace_nameand disk.tablespace_name='SYSTEM';--Disk_use_MB=680    Used_MB=674    Free_MB=4三、查看百分比SYS@11g>   select'--Disk_use_MB='||A.bytes/1024/1024,'Used_MB='||trunc(B.bytes/1024/1024),'Free_MB='||trunc(C.bytes/1024/1024),'%USED='||trunc((B.bytes*100)/A.bytes),'%FREE='||trunc((C.bytes*100)/A.bytes)FROMSYS.sm$ts_avail A,SYS.sm$ts_used B,SYS.sm$ts_free  CWHERE A.tablespace_name=B.tablespace_nameAND A.tablespace_name=C.tablespace_nameand A.tablespace_name='SYSTEM';--Disk_use_MB=680    Used_MB=674      Free_MB=4     %USED=99       %FREE=0

本文出自 “無雙城” 部落格，請務必保留此出處http://929044991.blog.51cto.com/1758347/1303503