oracle 表空间管理再续
来源:互联网 发布:网络电视全球在线 编辑:程序博客网 时间:2024/06/11 20:14
今天是2013-12-17,继续学习。
知识点一:在昨天dump块 的时候经常看到dba:0x22222222222这样的16进制数值。那么怎么转换呢?
oracle 提供了一个软件包dbms_utility,其中包含两个过程data_block_address_file和data_block_address_block;
可以创建一个函数,进而通过这个函数进行转换。
eg:
SQL> create or replace function fno(p_dba in varchar2)
2 return varchar2
3 as
4 l_str varchar2(255) default null;
5 begin
6 l_str:='datafile# is '||dbms_utility.data_block_address_file(to_number(ltrim(p_dba,'0x'),'xxxxxxxxx'))||chr('10')||'datablock# is '||dbms_utility.data_block_address_block(to_number(ltrim(p_dba,'0x'),'xxxxxxxxx'));
7 return l_str;
8 end;
9 /
Function created.
SQL> select fno('0x00c0000b') from dual;
FNO('0X00C0000B')
--------------------------------------------------------------------------------
datafile# is 3
datablock# is 11
知识点二:
如何降低高水位?(对全表扫描存在性能关系)
1、truncate exp、imp 终端业务
2、rename insert 不适合dml语句频繁操作
3、dbms_redefinition 存在中间表
4、shrink redo过多 维持索引
5、move 索引重建
分区表 可以考虑因素
知识点三:
如何分析segment块的使用:
1、通过rowid xxxxxx-xxx-xxxxxx获的对象使用的block
select count(distinct(substr(rowid,1,15))) blocks from table_name;
2、通过脚本查看数据块的使用情况:
CREATE OR REPLACE PROCEDURE show_space ( p_segname_1 IN VARCHAR2, p_type_1 IN VARCHAR2 DEFAULT 'TABLE',p_space IN VARCHAR2 DEFAULT'MANUAL', p_analyzed IN VARCHAR2 DEFAULT 'N',p_partition_1 IN VARCHAR2 DEFAULT NULL, p_owner_1 IN VARCHAR2 DEFAULT USER) AUTHID CURRENT_USERAS p_segname VARCHAR2 (100); p_type VARCHAR2 (30); p_owner VARCHAR2 (30); p_partition VARCHAR2 (50); 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; 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; PROCEDURE p (p_label IN VARCHAR2,p_num IN NUMBER) IS BEGIN DBMS_OUTPUT.put_line (RPAD(p_label, 40, '.') || p_num); END;BEGIN p_segname := UPPER (p_segname_1); p_owner := UPPER (p_owner_1); p_type := p_type_1; p_partition := UPPER(p_partition_1); IF (p_type_1 = 'i' OR p_type_1 ='I') THEN p_type := 'INDEX'; END IF; IF (p_type_1 = 't' OR p_type_1 ='T') THEN p_type := 'TABLE'; END IF; IF (p_type_1 = 'tp' OR p_type_1 ='TP') THEN p_type := 'TABLE PARTITION'; END IF; IF (p_type_1 = 'ip' OR p_type_1 = 'IP') THEN p_type := 'INDEX PARTITION'; END IF; IF (p_type_1 = 'c' OR p_type_1 ='C') THEN p_type := 'CLUSTER'; END IF; 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); IF p_space = 'MANUAL' OR (p_space<> 'auto' AND p_space <> 'AUTO') THEN DBMS_SPACE.FREE_BLOCKS (segment_owner => p_owner, segment_name =>p_segname, segment_type => p_type, partition_name =>p_partition, freelist_group_id => 0, free_blks =>l_free_blks); p ('Free Blocks', l_free_blks); END IF; p ('Total Blocks',l_total_blocks); p ('Total Bytes', l_total_bytes); 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); /*IF the segment is analyzed */ IF p_analyzed = 'Y' THEN DBMS_SPACE.SPACE_USAGE(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name =>p_partition, unformatted_blocks => l_unformatted_blocks, unformatted_bytes =>l_unformatted_bytes, fs1_blocks =>l_fs1_blocks, fs1_bytes =>l_fs1_bytes, fs2_blocks =>l_fs2_blocks, fs2_bytes => l_fs2_bytes, fs3_blocks =>l_fs3_blocks, fs3_bytes =>l_fs3_bytes, fs4_blocks =>l_fs4_blocks, fs4_bytes => l_fs4_bytes, full_blocks =>l_full_blocks, full_bytes =>l_full_bytes); DBMS_OUTPUT.put_line (RPAD ('', 50, '*')); DBMS_OUTPUT.put_line ('Thesegment is analyzed'); p ('0% -- 25% free spaceblocks', l_fs1_blocks); p ('0% -- 25% free spacebytes', l_fs1_bytes); p ('25% -- 50% free spaceblocks', l_fs2_blocks); p ('25% -- 50% free spacebytes', l_fs2_bytes); p ('50% -- 75% free spaceblocks', l_fs3_blocks); p ('50% -- 75% free spacebytes', l_fs3_bytes); p ('75% -- 100% free spaceblocks', l_fs4_blocks); p ('75% -- 100% free spacebytes', l_fs4_bytes); p ('Unused Blocks', l_unformatted_blocks); p ('Unused Bytes',l_unformatted_bytes); p ('Total Blocks',l_full_blocks); p ('Total bytes',l_full_bytes); END IF;END;
知识点四:
四个函数:
substr,
ltrim,截取指定的字符串,
ceil,取靠近该值的最大值
round,四舍五入
eg:
SQL> select ltrim('xxxhellooracle','xxx') ltr from dual;
LTR
-----------
hellooracle
SQL> select ceil(123.3) from dual;
CEIL(123.3)
-----------
124
SQL> select ceil(123.5) from dual;
CEIL(123.5)
-----------
124
SQL> select ceil(123.6) from dual;
CEIL(123.6)
-----------
124
SQL> select round(123.123,2) from dual;
ROUND(123.123,2)
----------------
123.12
SQL> select round(-123.123,2) from dual;
ROUND(-123.123,2)
-----------------
-123.12
SQL> select round(123.123,-1) from dual;
ROUND(123.123,-1)
-----------------
120
SQL>
知识点5:
以点及面视图:
truncate table xxxx drop all storage;
dbms_space_admin.materialize_deferred_segments
dbms_space_admin.drop_empty_segments;
空间查询rowid,函数查看。
dba_tablespace
dba_data_files
dba_segments
v$segment_statistics
v$segstat_name,v$segstat,v$segment_statistics
dba_thresholds
dba_outstanding_alerts
dba_alert_history
v$alert_types;
- oracle 表空间管理再续
- ORACLE 表空间管理
- ORACLE表空间管理
- ORACLE表空间管理
- oracle表空间管理
- Oracle表空间管理
- Oracle 表空间管理
- oracle 表空间管理
- oracle 表空间管理
- Oracle表空间管理
- Oracle表空间管理
- Oracle表空间管理
- oracle表空间管理
- Oracle表空间管理
- oracle|表空间管理
- Oracle 管理表空间
- oracle表空间管理
- oracle 表空间管理
- oracle chr(ASCII)对照表
- DLNA简介
- Android WebView中Header与Cookie应用场景深入剖析
- GTK编程基础------对话框
- Documents打开文档时的参数需要“COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); ”
- oracle 表空间管理再续
- 去除JSP页面自动生成的空行
- Hibernate get和load区别
- 节点2主机关停之后,VIP并没有failover到节点一
- 中国电视覆盖及收视状况调查结果出炉
- spring MVC 之构造ModelAndView对象
- Spring IOC 原理
- ModelAndView详解
- bigfib java