Oracle 表空间管理和优化

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 镜像数据探究 http://www.linuxidc.com/Linux/2013-08/89074.htmOracle 回滚（ROLLBACK）和撤销（Undo） http://www.linuxidc.com/Linux/2013-08/88792.htmUndo 表空间损坏导致无法open http://www.linuxidc.com/Linux/2013-07/88122.htmUndo表空间失败的处理方法 http://www.linuxidc.com/Linux/2013-05/83974.htmOracle Undo表空间重建与恢复 http://www.linuxidc.com/Linux/2013-04/83128.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle常用脚本——Agent问题处理脚本Oracle XDB和XPT相关资讯 Oracle表空间 Oracle表空间管理

关于Oracle可传输表空间的总结（今 20:12）
Aix下删除Oracle表空间以及对应的（04月17日）
Oracle表空间过大时候的处理（10/27/2015 20:42:50）

Oracle删除表空间遇到的问题及解决（04月17日）
Oracle使用SQL传输表空间（03月01日）
Oracle 设置表空间自增长（10/09/2015 11:42:07）

本文评论查看全部评论（0）