April 9, 2008
数据字典表之：DBA_HIGH_WATER_MARK_STATISTICS
作者:eygle
出处:http://blog.eygle.com
DBA_HIGH_WATER_MARK_STATISTICS是在Oracle10g中引入的。其中记录了数据库相关的一些HWM数据，包括数据库的空间使用，会话数量，表数量等
以下是这个视图的创建过程，数据来自Oracle10g 10.2.0.3
CREATE OR REPLACE FORCE VIEW SYS.dba_high_water_mark_statistics (dbid,
NAME,
VERSION,
highwater,
LAST_VALUE,
description
)
AS
SELECT dbid, hwm.NAME, VERSION, highwater, LAST_VALUE, description
FROM wri$_dbu_high_water_mark hwm, wri$_dbu_hwm_metadata mt
WHERE hwm.NAME = mt.NAME
AND hwm.NAME NOT LIKE '_HWM_TEST%'
AND /* filter out test hwm */
BITAND (mt.method, 4) != 4 /* filter out disabled hwm */
;
COMMENT ON TABLE SYS.DBA_HIGH_WATER_MARK_STATISTICS IS 'Database High Water Mark Statistics';
COMMENT ON COLUMN SYS.DBA_HIGH_WATER_MARK_STATISTICS.DBID IS 'database ID';
COMMENT ON COLUMN SYS.DBA_HIGH_WATER_MARK_STATISTICS.NAME IS 'name of high water mark statistics';
COMMENT ON COLUMN SYS.DBA_HIGH_WATER_MARK_STATISTICS.VERSION IS 'the database version the highwater marks are tracked in';
COMMENT ON COLUMN SYS.DBA_HIGH_WATER_MARK_STATISTICS.HIGHWATER IS 'highest value for statistic seen at sampling time';
COMMENT ON COLUMN SYS.DBA_HIGH_WATER_MARK_STATISTICS.LAST_VALUE IS 'value of statistic at last sample time';
COMMENT ON COLUMN SYS.DBA_HIGH_WATER_MARK_STATISTICS.DESCRIPTION IS 'description of high water mark';
DROP PUBLIC SYNONYM DBA_HIGH_WATER_MARK_STATISTICS;
CREATE PUBLIC SYNONYM DBA_HIGH_WATER_MARK_STATISTICS FOR SYS.DBA_HIGH_WATER_MARK_STATISTICS;
GRANT SELECT ON SYS.DBA_HIGH_WATER_MARK_STATISTICS TO SELECT_CATALOG_ROLE;
Posted by eygle at 11:47 PM | Comments (0)
数据字典表之：DBA_TABLES
作者:eygle
出处:http://blog.eygle.com
数据字典表 DBA_TABLES 与 ALL_TABLES 字段结构相同，其创建语句如下（以下脚本来自Oracle10g 10.2.0.3）：CREATE OR REPLACE FORCE VIEW SYS.dba_tables (owner,
table_name,
tablespace_name,
cluster_name,
iot_name,
status,
pct_free,
pct_used,
ini_trans,
max_trans,
initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
FREELISTS,
freelist_groups,
LOGGING,
backed_up,
num_rows,
blocks,
empty_blocks,
avg_space,
chain_cnt,
avg_row_len,
avg_space_freelist_blocks,
num_freelist_blocks,
DEGREE,
INSTANCES,
CACHE,
table_lock,
sample_size,
last_analyzed,
partitioned,
iot_type,
TEMPORARY,
secondary,
NESTED,
BUFFER_POOL,
row_movement,
global_stats,
user_stats,
DURATION,
skip_corrupt,
MONITORING,
cluster_owner,
dependencies,
compression,
dropped
)
AS
SELECT u.NAME, o.NAME,
DECODE (BITAND (t.property, 2151678048), 0, ts.NAME, NULL),
DECODE (BITAND (t.property, 1024), 0, NULL, co.NAME),
DECODE ((BITAND (t.property, 512) + BITAND (t.flags, 536870912)),
0, NULL,
co.NAME
),
DECODE (BITAND (t.trigflag, 1073741824),
1073741824, 'UNUSABLE',
'VALID'
),
DECODE (BITAND (t.property, 32 + 64),
0, MOD (t.pctfree$, 100),
64, 0,
NULL
),
DECODE (BITAND (ts.flags, 32),
32, TO_NUMBER (NULL),
DECODE (BITAND (t.property, 32 + 64),
0, t.pctused$,
64, 0,
NULL
)
),
DECODE (BITAND (t.property, 32), 0, t.INITRANS, NULL),
DECODE (BITAND (t.property, 32), 0, t.MAXTRANS, NULL),
s.iniexts * ts.BLOCKSIZE,
DECODE (BITAND (ts.flags, 3),
1, TO_NUMBER (NULL),
s.extsize * ts.BLOCKSIZE
),
s.minexts, s.maxexts,
DECODE (BITAND (ts.flags, 3), 1, TO_NUMBER (NULL), s.extpct),
DECODE (BITAND (ts.flags, 32),
32, TO_NUMBER (NULL),
DECODE (BITAND (o.flags, 2),
2, 1,
DECODE (s.lists, 0, 1, s.lists)
)
),
DECODE (BITAND (ts.flags, 32),
32, TO_NUMBER (NULL),
DECODE (BITAND (o.flags, 2),
2, 1,
DECODE (s.GROUPS, 0, 1, s.GROUPS)
)
),
DECODE (BITAND (t.property, 32 + 64),
0, DECODE (BITAND (t.flags, 32), 0, 'YES', 'NO'),
NULL
),
DECODE (BITAND (t.flags, 1), 0, 'Y', 1, 'N', '?'), t.rowcnt,
DECODE (BITAND (t.property, 64), 0, t.blkcnt, NULL),
DECODE (BITAND (t.property, 64), 0, t.empcnt, NULL), t.avgspc,
t.chncnt, t.avgrln, t.avgspc_flb,
DECODE (BITAND (t.property, 64), 0, t.flbcnt, NULL),
LPAD (DECODE (t.DEGREE, 32767, 'DEFAULT', NVL (t.DEGREE, 1)), 10),
LPAD (DECODE (t.INSTANCES, 32767, 'DEFAULT', NVL (t.INSTANCES, 1)),
10
),
LPAD (DECODE (BITAND (t.flags, 8), 8, 'Y', 'N'), 5),
DECODE (BITAND (t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
DECODE (BITAND (t.property, 32), 32, 'YES', 'NO'),
DECODE (BITAND (t.property, 64),
64, 'IOT',
DECODE (BITAND (t.property, 512),
512, 'IOT_OVERFLOW',
DECODE (BITAND (t.flags, 536870912),
536870912, 'IOT_MAPPING',
NULL
)
)
),
DECODE (BITAND (o.flags, 2), 0, 'N', 2, 'Y', 'N'),
DECODE (BITAND (o.flags, 16), 0, 'N', 16, 'Y', 'N'),
DECODE (BITAND (t.property, 8192),
8192, 'YES',
DECODE (BITAND (t.property, 1), 0, 'NO', 'YES')
),
DECODE (BITAND (o.flags, 2),
2, 'DEFAULT',
DECODE (s.cachehint,
0, 'DEFAULT',
1, 'KEEP',
2, 'RECYCLE',
NULL
)
),
DECODE (BITAND (t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
DECODE (BITAND (t.flags, 512), 0, 'NO', 'YES'),
DECODE (BITAND (t.flags, 256), 0, 'NO', 'YES'),
DECODE (BITAND (o.flags, 2),
0, NULL,
DECODE (BITAND (t.property, 8388608),
8388608, 'SYS$SESSION',
'SYS$TRANSACTION'
)
),
DECODE (BITAND (t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
DECODE (BITAND (o.flags, 2),
2, 'NO',
DECODE (BITAND (t.property, 2147483648),
2147483648, 'NO',
DECODE (ksppcv.ksppstvl, 'TRUE', 'YES', 'NO')
)
),
DECODE (BITAND (t.property, 1024), 0, NULL, cu.NAME),
DECODE (BITAND (t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
DECODE (BITAND (t.property, 32),
32, NULL,
DECODE (BITAND (s.spare1, 2048),
2048, 'ENABLED',
'DISABLED'
)
),
DECODE (BITAND (o.flags, 128), 128, 'YES', 'NO')
FROM SYS.user$ u,
SYS.ts$ ts,
SYS.seg$ s,
SYS.obj$ co,
SYS.tab$ t,
SYS.obj$ o,
SYS.obj$ cx,
SYS.user$ cu,
x$ksppcv ksppcv,
x$ksppi ksppi
WHERE o.owner# = u.user#
AND o.obj# = t.obj#
AND BITAND (t.property, 1) = 0
AND BITAND (o.flags, 128) = 0
AND t.bobj# = co.obj#(+)
AND t.ts# = ts.ts#
AND t.file# = s.file#(+)
AND t.block# = s.block#(+)
AND t.ts# = s.ts#(+)
AND t.dataobj# = cx.obj#(+)
AND cx.owner# = cu.user#(+)
AND ksppi.indx = ksppcv.indx
AND ksppi.ksppinm = '_dml_monitoring_enabled';
Posted by eygle at 2:55 PM | Comments (0)
并行查询并行度Degree与instances 设置
作者:eygle
出处:http://blog.eygle.com
在DBA_TABLES字典表中有一个degree字段，这个字段代表并行查询在数据表上的并行度，在RAC环境中，这个参数还和实例有关。以下生活文档中对于 DEGREE 和 INSTANCES 参数的说明：
DEGREE VARCHAR2(10) Number of threads per instance for scanning the table
INSTANCES VARCHAR2(10) Number of instances across which the table is to be scanned
但是注意，当你使用类似如下查询时，你可能无法获得返回值：
SQL> select table_name from dba_tables where degree='1' or degree='DEFAULT';
no rows selected
我们看一下Degree以及instances的记录方式：
SQL> select degree,length(degree) from dba_tablesDegree和Instances实际上记录了10个字符，左端用空格补齐。
2 group by degree;
DEGREE LENGTH(DEGREE)
-------------------- --------------
DEFAULT 10
1 10
SQL>select instances,length(instances) from dba_tables
2 group by instances;
INSTANCES LENGTH(INSTANCES)
-------------------- -----------------
DEFAULT 10
1 10
0 10
在 dba_tables 的创建语句中，我们可以找到根本原因，以下是这两个字段的定义来源：
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
以上信息来自Oracle10gR2数据库：
SQL> select table_name,owner from dba_tables where degree=' DEFAULT' or instances=' DEFAULT';
TABLE_NAME OWNER
------------------------------ ------------------------------
TEST_EXT2 SYS
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
以下收录dba_tables视图的创建语句：
CREATE OR REPLACE VIEW dba_tables (owner,
table_name,
tablespace_name,
cluster_name,
iot_name,
status,
pct_free,
pct_used,
ini_trans,
max_trans,
initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
FREELISTS,
freelist_groups,
LOGGING,
backed_up,
num_rows,
blocks,
empty_blocks,
avg_space,
chain_cnt,
avg_row_len,
avg_space_freelist_blocks,
num_freelist_blocks,
DEGREE,
INSTANCES,
CACHE,
table_lock,
sample_size,
last_analyzed,
partitioned,
iot_type,
TEMPORARY,
secondary,
NESTED,
BUFFER_POOL,
row_movement,
global_stats,
user_stats,
DURATION,
skip_corrupt,
MONITORING,
cluster_owner,
dependencies,
compression,
dropped
)
AS
SELECT u.NAME, o.NAME,
DECODE (BITAND (t.property, 2151678048), 0, ts.NAME, NULL),
DECODE (BITAND (t.property, 1024), 0, NULL, co.NAME),
DECODE ((BITAND (t.property, 512) + BITAND (t.flags, 536870912)),
0, NULL,
co.NAME
),
DECODE (BITAND (t.trigflag, 1073741824),
1073741824, 'UNUSABLE',
'VALID'
),
DECODE (BITAND (t.property, 32 + 64),
0, MOD (t.pctfree$, 100),
64, 0,
NULL
),
DECODE (BITAND (ts.flags, 32),
32, TO_NUMBER (NULL),
DECODE (BITAND (t.property, 32 + 64),
0, t.pctused$,
64, 0,
NULL
)
),
DECODE (BITAND (t.property, 32), 0, t.INITRANS, NULL),
DECODE (BITAND (t.property, 32), 0, t.MAXTRANS, NULL),
s.iniexts * ts.BLOCKSIZE,
DECODE (BITAND (ts.flags, 3),
1, TO_NUMBER (NULL),
s.extsize * ts.BLOCKSIZE
),
s.minexts, s.maxexts,
DECODE (BITAND (ts.flags, 3), 1, TO_NUMBER (NULL), s.extpct),
DECODE (BITAND (ts.flags, 32),
32, TO_NUMBER (NULL),
DECODE (BITAND (o.flags, 2),
2, 1,
DECODE (s.lists, 0, 1, s.lists)
)
),
DECODE (BITAND (ts.flags, 32),
32, TO_NUMBER (NULL),
DECODE (BITAND (o.flags, 2),
2, 1,
DECODE (s.GROUPS, 0, 1, s.GROUPS)
)
),
DECODE (BITAND (t.property, 32 + 64),
0, DECODE (BITAND (t.flags, 32), 0, 'YES', 'NO'),
NULL
),
DECODE (BITAND (t.flags, 1), 0, 'Y', 1, 'N', '?'), t.rowcnt,
DECODE (BITAND (t.property, 64), 0, t.blkcnt, NULL),
DECODE (BITAND (t.property, 64), 0, t.empcnt, NULL), t.avgspc,
t.chncnt, t.avgrln, t.avgspc_flb,
DECODE (BITAND (t.property, 64), 0, t.flbcnt, NULL),
LPAD (DECODE (t.DEGREE, 32767, 'DEFAULT', NVL (t.DEGREE, 1)), 10),
LPAD (DECODE (t.INSTANCES, 32767, 'DEFAULT', NVL (t.INSTANCES, 1)),
10
),
LPAD (DECODE (BITAND (t.flags, 8), 8, 'Y', 'N'), 5),
DECODE (BITAND (t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
DECODE (BITAND (t.property, 32), 32, 'YES', 'NO'),
DECODE (BITAND (t.property, 64),
64, 'IOT',
DECODE (BITAND (t.property, 512),
512, 'IOT_OVERFLOW',
DECODE (BITAND (t.flags, 536870912),
536870912, 'IOT_MAPPING',
NULL
)
)
),
DECODE (BITAND (o.flags, 2), 0, 'N', 2, 'Y', 'N'),
DECODE (BITAND (o.flags, 16), 0, 'N', 16, 'Y', 'N'),
DECODE (BITAND (t.property, 8192),
8192, 'YES',
DECODE (BITAND (t.property, 1), 0, 'NO', 'YES')
),
DECODE (BITAND (o.flags, 2),
2, 'DEFAULT',
DECODE (s.cachehint,
0, 'DEFAULT',
1, 'KEEP',
2, 'RECYCLE',
NULL
)
),
DECODE (BITAND (t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
DECODE (BITAND (t.flags, 512), 0, 'NO', 'YES'),
DECODE (BITAND (t.flags, 256), 0, 'NO', 'YES'),
DECODE (BITAND (o.flags, 2),
0, NULL,
DECODE (BITAND (t.property, 8388608),
8388608, 'SYS$SESSION',
'SYS$TRANSACTION'
)
),
DECODE (BITAND (t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
DECODE (BITAND (o.flags, 2),
2, 'NO',
DECODE (BITAND (t.property, 2147483648),
2147483648, 'NO',
DECODE (ksppcv.ksppstvl, 'TRUE', 'YES', 'NO')
)
),
DECODE (BITAND (t.property, 1024), 0, NULL, cu.NAME),
DECODE (BITAND (t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
DECODE (BITAND (t.property, 32),
32, NULL,
DECODE (BITAND (s.spare1, 2048),
2048, 'ENABLED',
'DISABLED'
)
),
DECODE (BITAND (o.flags, 128), 128, 'YES', 'NO')
FROM SYS.user$ u,
SYS.ts$ ts,
SYS.seg$ s,
SYS.obj$ co,
SYS.tab$ t,
SYS.obj$ o,
SYS.obj$ cx,
SYS.user$ cu,
x$ksppcv ksppcv,
x$ksppi ksppi
WHERE o.owner# = u.user#
AND o.obj# = t.obj#
AND BITAND (t.property, 1) = 0
AND BITAND (o.flags, 128) = 0
AND t.bobj# = co.obj#(+)
AND t.ts# = ts.ts#
AND t.file# = s.file#(+)
AND t.block# = s.block#(+)
AND t.ts# = s.ts#(+)
AND t.dataobj# = cx.obj#(+)
AND cx.owner# = cu.user#(+)
AND ksppi.indx = ksppcv.indx
AND ksppi.ksppinm = '_dml_monitoring_enabled'
-The End-
Posted by eygle at 10:20 AM | Comments (0)
百度 城管 与 GFW
作者:eygle
出处:http://blog.eygle.com
在几个朋友的Blog上看到百度百科关于城管的词条介绍，感觉网友们实在上太有才了：
城管 发音：cheng guan 释义：名词。
虽然经常会爆出有城管打人等事件，但是，却不能以偏概全，相信绝大多数城管是比较文明的，比较有道德，有修养，有素质，为维护城市健康文明地发展不可或缺的相关服务人员。
绝对临时工词目：城管
发音：cheng guan
释义：
① 名词：以暴力手段维持××形象，专门欺压租不起商铺、办不起执照商贩以及其他弱势群体的黑社会组织。例句：城管上道，鸡飞狗跳。
② 形容词：形容残暴、血腥、恐怖。例句：你也太城管了！
③ 动词：等同于打、砸、抢。例句：他不老实就城管他一下！
④ 叹词：无奈之意。例句：这世道，城管！
⑤ 代名词。例句： 关门，放城管
网易上关于此事件的报道有将近7000个回复，可见网友对这一词条的关注，当然这一词条目前已经被城管了。
早晨发现的另外一个变化是，GFW 似乎有所松动，BlogSpot可以直接访问了，测试站点是Thomas Kyte 的Blog。
是否有人不知道GFW是什么含义呢？顺便看看百度百科的解释：
防火长城，也称中国防火墙或中国国家防火墙，是对中华人民共和国政府在其管辖互联网内部建立的多套网络审查系统（包括相关行政审查系统）的俗称。
其名称得自于2002年5月17日Charles R. Smith所写的一篇关于中国网络审查的文章《The Great Firewall of China》，取与Great Wall（长城）相谐的效果，简写为Great Firewall，缩写GFW，戏称功夫网。
随着使用的广泛，GFW已被用于动词，GFWed是指被防火长城所屏蔽。
关于GFW，还有三条著名的定律：
GFW 第一定律：只要是 "用户产生内容"(User-generated content, UGC) 的国外网站都会被和谐。
GFW 第二定律：只要是被和谐的网站，国内一定会有个克隆版。
GFW 第三定律：没有被和谐的网站一定不是同类竞争者中最出色的。
-The End-
Posted by eygle at 8:01 AM | Comments (12)