-
Notifications
You must be signed in to change notification settings - Fork 35
/
dba.sql
107 lines (99 loc) · 4.16 KB
/
dba.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
--------------------------------------------------------------------------------
-- ~~~ xtender: based on:
-- File name: dba.sql
-- Purpose: Convert Data Block Address (a 6 byte hex number) to file#, block#
-- and find to which segment it belongs
--
-- Author: Tanel Poder
-- Copyright: (c) http://www.tanelpoder.com
--
-- Usage: @dba <data_block_address>
-- @dba 40EB02
--
--
-- Other: This script also tries to identify the segment into which this
-- block belongs. It first queries X$BH, as if this block is really
-- hot, it should be in buffer cache. Note that you can change this
-- query to use V$BH if you dont have access to X$ tables.
-- If the block is not in buffer cache anymore, then this script
-- can query DBA_EXTENTS next, but this can be a IO intensive operation
-- on some systems, so if X$BH already answers your question, press
-- CTRL+C here.
--
--------------------------------------------------------------------------------
col rfile# new_value v_dba_rfile
col block# new_value v_dba_block
col dba_object head object for a40 truncate;
col dba_DBA head DBA for a20;
col dump_cmd for a50;
select
dbms_utility.data_block_address_file(to_number('&1','XXXXXXXXXX')) RFILE#,
dbms_utility.data_block_address_block(to_number('&1','XXXXXXXXXX')) BLOCK#,
'-- alter system dump datafile '||dbms_utility.data_block_address_file(to_number('&1','XXXXXXXXXX'))
||' block '||dbms_utility.data_block_address_block(to_number('&1','XXXXXXXXXX')) dump_cmd
from dual;
col dba_object clear;
col dba_DBA clear;
col dump_cmd clear;
prompt #########################################################################################
prompt &_C_RED Enter 'Y' to find the segment using V$BH (this may take CPU time), &_C_RESET;
accept _chk prompt "'N' to cancel[N]: " default "N"
col STATE for a20;
col BLOCK_CLASS for a30;
col OBJECT_TYPE for a30;
col object for a30;
select /*+ ORDERED */
decode(bh.state,0,'free',1,'xcur',2,'scur',3,'cr',
4,'read',5,'mrec',
6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',
11,'donated', 12,'protected',13,'securefile', 14,'siop',15,'recckpt'
) state,
decode(bh.class,1,'data block',2,'sort block',3,'save undo block',
4,'segment header',5,'save undo header',6,'free list',7,'extent map',
8,'1st level bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block',
12,'bitmap index block',13,'file header block',14,'unused',
15,'system undo header',16,'system undo block', 17,'undo header',
18,'undo block'
) block_class,
o.object_type,
o.owner||'.'||o.object_name dba_object,
bh.tch,
bh.mode_held,
decode(bitand(bh.flag,1),0, 'N', 'Y') dirty,
decode(bitand(bh.flag,16), 0, 'N', 'Y') temp,
decode(bitand(bh.flag,1536), 0, 'N', 'Y') ping,
decode(bitand(bh.flag,16384), 0, 'N', 'Y') stale,
decode(bitand(bh.flag,65536), 0, 'N', 'Y') direct,
trim(to_char(bh.flag, 'XXXXXXXX'))||':'||
trim(to_char(bh.lru_flag, 'XXXXXXXX')) flg_lruflg,
bh.dirty_queue DQ
from
vx$bh bh,
dba_objects o
where lower('&_chk')='y'
and bh.obj = o.data_object_id
and file# = &v_dba_rfile
and dbablk = &v_dba_block
order by
tch asc
/
col STATE clear;
col BLOCK_CLASS clear;
col OBJECT_TYPE clear;
col object clear;
prompt &_C_RED Enter 'Y' to query what segment resides there using DBA_EXTENTS (this can be IO intensive),&_C_RESET;
accept _chk prompt "'N' to cancel[N]: " default "N"
col OWNER format a30;
col SEGMENT_NAME format a30;
col PARTITION_NAME format a30;
col TABLESPACE_NAME format a30;
select owner, segment_name, partition_name, tablespace_name
from dba_extents
where lower('&_chk')='y'
and relative_fno = &v_dba_rfile
and &v_dba_block between block_id and block_id + blocks - 1
/
col OWNER clear;
col SEGMENT_NAME clear;
col PARTITION_NAME clear;
col TABLESPACE_NAME clear;