Oracle 表空间管理和优化

最新推荐文章于 2024-12-26 08:22:06 发布

原创最新推荐文章于 2024-12-26 08:22:06 发布 · 872 阅读

0 ·

CC 4.0 BY-SA版权

Oracle Basic 专栏收录该内容

64 篇文章

订阅专栏

本文详细介绍Oracle数据库中表信息查看方法、表空间使用情况监控、表和索引的迁移策略，以及表收缩操作等内容。通过具体示例，展示了如何使用SYS.SHOW_SPACE过程查询表空间使用详情，如何进行表和分区表的收缩，并提供了表空间使用情况的综合监控方法。

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_MANAGEMENT
FROM DBA_SEGMENTS SEG, DBA_TABLESPACES TS
WHERE SEG.SEGMENT_NAME = :P_SEGNAME
AND (:P_PARTITION IS NULL OR
SEG.PARTITION_NAME = :P_PARTITION)
AND SEG.OWNER = :P_OWNER
AND 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_SPACE
SYS@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/zcs
drop 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=2
ZCS@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 =2
ZCS@11g>  set serverout on;
exec sys.show_space('T3');
/* FREE BLOCKS.............................               0
TOTAL BLOCKS............................               8
UNUSED BLOCKS...........................               7 */

3.Shrink收缩高水位

一、shrink操作
1.行的rowid会改变所以表必须启用row movement
SYS@zcs11G>  alter table t4 enable row movement;
2.shrink space cascade（cascade可省略）
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  ----检查表中是否有LOB
2. 如果表没有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_indexes
a 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.625
02.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)
from
sm$ts_avail disk,sm$ts_used used,sm$ts_free free
where disk.tablespace_name=used.tablespace_name
and disk.tablespace_name=free.tablespace_name
and 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)
FROM
SYS.sm$ts_avail A,SYS.sm$ts_used B,SYS.sm$ts_free  C
WHERE A.tablespace_name=B.tablespace_name
AND A.tablespace_name=C.tablespace_name
and 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