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_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.Shrink收缩高水位实操：
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 表
3.MOVE收缩高水位
一、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 闲时：其余全部动作
相关阅读：
Oracle Undo 镜像数据探究
Oracle 回滚(ROLLBACK)和撤销(Undo)
Undo 表空间损坏导致无法open
Undo表空间失败的处理方法
Oracle Undo表空间重建与恢复