오라클 코어 - 조나단 루이스
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 Oracle Core Essential Internals for DBAs and Developers (Apress)[편집]
1.1 관련 스크립트[편집]
1.1.1 setenv.sql[편집]
- Set up various SQL*Plus formatting commands.
rem
rem Script: setenv.sql
rem Author: Jonathan Lewis
rem Dated: June 2002
rem Purpose: Set up various SQL*Plus formatting commands.
rem
rem Notes:
rem
set pause off
rem
rem If you want to call dbms_xplan.display_cursor() to get the
rem place for the last statement you executed you will have to
rem set serveroutput off
rem
set serveroutput on size 1000000 format wrapped
rem
rem I'd like to enable java output, but it seems
rem to push the UGA up by about 4MB when I do it
rem
rem execute dbms_java.set_output(1000000)
rem
rem Reminder about DOC, and using the # to end DOC
rem the SET command stops doc material from appearing
rem
execute dbms_random.seed(0)
set doc off
doc
end doc is marked with #
#
set linesize 120
set trimspool on
set pagesize 24
set arraysize 25
-- set longchunksize 32768
-- set long 32768
set autotrace off
clear breaks
ttitle off
btitle off
column owner format a15
column segment_name format a20
column table_name format a20
column index_name format a20
column object_name format a20
column subobject_name format a20
column partition_name format a20
column subpartition_name format a20
column column_name format a20
column column_expression format a40 word wrap
column constraint_name format a20
column referenced_name format a30
column file_name format a60
column low_value format a24
column high_value format a24
column parent_id_plus_exp format 999
column id_plus_exp format 990
column plan_plus_exp format a90
column object_node_plus_exp format a14
column other_plus_exp format a90
column other_tag_plus_exp format a29
column access_predicates format a80
column filter_predicates format a80
column projection format a80
column remarks format a80
column partition_start format a12
column partition_stop format a12
column partition_id format 999
column other_tag format a32
column object_alias format a24
column object_node format a13
column other format a150
column os_username format a30
column terminal format a24
column userhost format a24
column client_id format a24
column statistic_name format a35
column namespace format a20
column attribute format a20
column hint format a40
column start_time format a25
column end_time format a25
column time_now noprint new_value m_timestamp
set feedback off
select to_char(sysdate,'hh24miss') time_now from dual;
commit;
set feedback on
set timing off
set verify off
alter session set optimizer_mode = all_rows;
spool log1.1.2 c_dump_seg.sql[편집]
- Create a procedure to dump blocks from a segment
rem
rem Script: c_dump_seg.sql
rem Author: Jonathan Lewis
rem Dated: December 2002
rem Purpose: Create a procedure to dump blocks from a segment
rem
rem Last tested
rem 11.2.0.2
rem 11.1.0.7
rem 10.2.0.3
rem 10.1.0.4
rem 9.2.0.6
rem 8.1.7.4
rem
rem Notes:
rem The code is very simple minded with no error trapping.
rem It only covers the first extent (extent zero) of a segment
rem Could be enhanced to use get_ev to save and restore the state
rem of event 10289 (the one that controls raw/cooked dumps).
rem
rem Change in 10.2: the raw block dump always appears in
rem a block dump, you cannot stop it. Event 10289 blocks
rem the appearance of the formatted dump
rem
rem Script has to be run by a DBA who has the privileges to
rem view v$process, v$session, v$mystat
rem
rem Usage
rem -- the notes assume the tablespace is not ASSM.
rem execute dump_seg('tablex'); -- dump first data block
rem execute dump_seg('tablex',5) -- dump first five data blocks
rem execute dump_seg('indexy',1,'INDEX') -- dump root block of index
rem execute dump_seg('tableX',i_start_block=>0 ) -- dump seg header block
rem
rem Various "optimizer" issues with 10g:
rem select * from dba_extents
rem where segment_name = 'T1'
rem and extent_id = 0;
rem vs.
rem select * from dba_extents
rem where segment_name = 'T1'
rem order by extent_id;
rem
rem On one system, the first query crashed with error:
rem ORA-00379: no free buffers available in buffer pool DEFAULT for block size 2K
rem
rem There had been an object in the 2K tablespace,
rem which had been dropped but not purged. There
rem were no buffers allocated to the 2K cache,
rem hence the failure. And it was not possible
rem to purge the recyclebin without creating the
rem cache.
rem
rem Clearly, the join order had changed because of
rem the extent_id predicate - and this led to the
rem crash
rem
rem For this reason, I changed the code to query by
rem segment and order by extent_id - stopping at the
rem zero extent
rem
rem Performance can also be affected by how many extents
rem you have, and whether you have collected statistics
rem (in 10g) on the fixed tables - because of the call to
rem check the extents in the segment headers.
rem
rem Internal enhancements in 11g
rem You get a dump of all the copies in the buffer cache,
rem and a copy of the version of the block on disc.
rem
start setenv
create or replace procedure dump_seg(
i_seg_name in varchar2,
i_block_count in number default 1,
i_seg_type in varchar2 default 'TABLE',
i_start_block in number default 1,
i_owner in varchar2 default sys_context('userenv','session_user'),
i_partition_name in varchar2 default null,
i_dump_formatted in boolean default true,
i_dump_raw in boolean default false
)
as
m_file_id number;
m_block_min number;
m_block_max number;
m_process varchar2(32);
begin
for r in (
select
file_id,
block_id + i_start_block block_min,
block_id + i_start_block + i_block_count - 1 block_max
from
dba_extents
where
segment_name = upper(i_seg_name)
and segment_type = upper(i_seg_type)
and owner = upper(i_owner)
and nvl(partition_name,'N/A') = upper(nvl(i_partition_name,'N/A'))
order by
extent_id
) loop
m_file_id := r.file_id;
m_block_min := r.block_min;
m_block_max := r.block_max;
exit;
end loop;
if (i_dump_formatted) then
execute immediate
'alter session set events ''10289 trace name context off''';
execute immediate
'alter system dump datafile ' || m_file_id ||
' block min ' || m_block_min ||
' block max ' || m_block_max
;
end if;
if (i_dump_raw) then
execute immediate
'alter session set events ''10289 trace name context forever''';
execute immediate
'alter system dump datafile ' || m_file_id ||
' block min ' || m_block_min ||
' block max ' || m_block_max
;
end if;
execute immediate
'alter session set events ''10289 trace name context off''';
--
-- For non-MTS, work out the trace file name
--
select
spid
into
m_process
from
v$session se,
v$process pr
where
se.sid = (select sid from v$mystat where rownum = 1)
and pr.addr = se.paddr
;
dbms_output.new_line;
dbms_output.put_line(
'Dumped ' || i_block_count || ' blocks from ' ||
i_seg_type || ' ' || i_seg_name ||
' starting from block ' || i_start_block
);
dbms_output.new_line;
dbms_output.put_line('Trace file name includes: ' || m_process);
dbms_output.new_line;
exception
when others then
dbms_output.new_line;
dbms_output.put_line('Unspecified error.');
dbms_output.put_line('Check syntax.');
dbms_output.put_line('dumpseg({segment_name},[{block count}],[{segment_type}]');
dbms_output.put_line(' [{start block (1)}],[{owner}],[{partition name}]');
dbms_output.put_line(' [{dump formatted YES/n}],[{dump raw y/NO}]');
dbms_output.new_line;
raise;
end;
.
/
show errors
drop public synonym dump_seg;
create public synonym dump_seg for dump_seg;
grant execute on dump_seg to public;1.1.3 c_dump_log.sql[편집]
- Dump the current online redo log file.
rem
rem Script: c_dump_log.sql
rem Author: Jonathan Lewis
rem Dated: December 2002
rem Purpose: Dump the current online redo log file.
rem
rem
rem Last tested
rem 11.2.0.2
rem 10.2.0.3
rem 10.1.0.4
rem 9.2.0.8
rem 8.1.7.4
rem
rem Notes:
rem Must be run as a DBA
rem Very simple minded - no error trapping
rem
start setenv
create or replace procedure dump_log
as
m_log_name varchar2(255);
m_process varchar2(32);
begin
select
lf.member
into
m_log_name
from
V$log lo,
v$logfile lf
where
lo.status = 'CURRENT'
and lf.group# = lo.group#
and rownum = 1
;
execute immediate
'alter system dump logfile ''' || m_log_name || '''';
select
spid
into
m_process
from
v$session se,
v$process pr
where
se.sid = --dbms_support.mysid
(select sid from v$mystat where rownum = 1)
and pr.addr = se.paddr
;
dbms_output.put_line('Trace file name includes: ' || m_process);
end;
.
/
show errors
create public synonym dump_log for dump_log;
grant execute on dump_log to public;
spool off
set doc off
doc
----------------------------------------------
Skipping IMU Redo Record: cannot be filtered by XID/OBJNO
-------------------------------------------------
----------------------------------------------
Skipping IMU Redo Record: cannot be filtered by XID/OBJNO
-------------------------------------------------
----------------------------------------------
Skipping IMU Redo Record: cannot be filtered by XID/OBJNO
-------------------------------------------------
----------------------------------------------
Skipping IMU Redo Record: cannot be filtered by XID/OBJNO
-------------------------------------------------
#1.1.4 c_dump_undo_block.sql[편집]
rem
rem Script: c_dump_undo_block.sql
rem Author: Jonathan Lewis
rem Dated: December 2002
rem Purpose: Create a procedure to dump your CURRENT undo block
rem
rem Last tested
rem 11.2.0.2
rem 11.1.0.7
rem 10.2.0.3
rem 9.2.0.8
rem Not tested
rem 10.1.0.4
rem 8.1.7.4
rem
rem Notes:
rem The code is very simple minded with no error trapping.
rem Has to be run by SYS to create the procedure.
rem
start setenv
create or replace procedure dump_undo_block
as
m_xidusn number;
m_header_file_id number;
m_header_block_id number;
m_start_file_id number;
m_start_block_id number;
m_file_id number;
m_block_id number;
m_process number;
begin
select
xidusn,
start_ubafil,
start_ubablk,
ubafil,
ubablk
into
m_xidusn,
m_start_file_id,
m_start_block_id,
m_file_id,
m_block_id
from
v$session ses,
v$transaction trx
where
ses.sid = (select mys.sid from V$mystat mys where rownum = 1)
and trx.ses_addr = ses.saddr
;
select
file_id, block_id
into
m_header_file_id,
m_header_block_id
from
dba_rollback_segs
where
segment_id = m_xidusn
;
dbms_output.put_line('Header File: ' || m_header_file_id || ' Header block: ' || m_header_block_id);
dbms_output.put_line('Start File: ' || m_start_file_id || ' Start block: ' || m_start_block_id);
dbms_output.put_line('Current File: ' || m_file_id || ' Current block: ' || m_block_id);
dbms_system.ksdwrt(1,'===================');
dbms_system.ksdwrt(1,'Undo Segment Header');
dbms_system.ksdwrt(1,'===================');
execute immediate
'alter system dump datafile ' || m_header_file_id ||' block ' || m_header_block_id;
dbms_system.ksdwrt(1,'================');
dbms_system.ksdwrt(1,'Undo Start block');
dbms_system.ksdwrt(1,'================');
execute immediate
'alter system dump datafile ' || m_start_file_id ||' block ' || m_start_block_id;
if m_start_block_id != m_block_id then
dbms_system.ksdwrt(1,'==================');
dbms_system.ksdwrt(1,'Current Undo block');
dbms_system.ksdwrt(1,'==================');
execute immediate
'alter system dump datafile ' || m_file_id ||' block ' || m_block_id;
end if;
select
spid
into
m_process
from
v$session se,
v$process pr
where se.sid = (select sid from v$mystat where rownum = 1)
and
pr.addr = se.paddr
;
dbms_output.put_line('Trace file name includes: ' || m_process);
end;
/
grant execute on dump_undo_block to public;
drop public synonym dump_undo_block;
create public synonym dump_undo_block for dump_undo_block;
set doc off
doc
#1.1.5 core_imu_01.sql (19c 버전에서 sql에러 발생으로 sql 수정함.)[편집]
- Diagnostics for core undo/redo concepts
rem
rem Script: core_imu_01.sql
rem Author: Jonathan Lewis
rem Dated: Feb 2011
rem Purpose:
rem
rem Last tested
rem 11.2.0.2
rem 10.2.0.3
rem Not relevant
rem 9.2.0.8
rem 8.1.7.4
rem
rem Notes:
rem Can only be run as SYS in this form
rem
rem Diagnostics for core undo/redo concepts
rem
column indx format 9999
-- (변경전 원본)
-- 19c 버전에서 에러 발생
-- select
-- indx,
-- to_number(ktifpupe,'XXXXXXXX') -
-- to_number(ktifpupb,'XXXXXXXX') undo_size,
-- to_number(ktifpupc,'XXXXXXXX') -
-- to_number(ktifpupb,'XXXXXXXX') undo_usage,
-- to_number(ktifprpe,'XXXXXXXX') -
-- to_number(ktifprpb,'XXXXXXXX') redo_size,
-- to_number(ktifprpc,'XXXXXXXX') -
-- to_number(ktifprpb,'XXXXXXXX') redo_usage
-- from
-- x$ktifp
-- ;
-- Size of in-memory undo and private redo after a small change
-- (변경후)
-- 19c 버전에서 실행
select indx,
-- KTIFPTXFLG,ADDR,KTIFPNO, KTIFPSTA, KTIFPXCB xctaddr,
to_number(KTIFPUPE, 'XXXXXXXXXXXXXXXX') -
to_number(KTIFPUPB, 'XXXXXXXXXXXXXXXX') undo_size,
(to_number(KTIFPUPB, 'XXXXXXXXXXXXXXXX') - to_number(KTIFPUPC, 'XXXXXXXXXXXXXXXX'))*-1 undo_usage,
to_number(KTIFPRPE, 'XXXXXXXXXXXXXXXX') - to_number(KTIFPRPB, 'XXXXXXXXXXXXXXXX') redo_size,
(to_number(KTIFPRPB, 'XXXXXXXXXXXXXXXX') -to_number(KTIFPRPC, 'XXXXXXXXXXXXXXXX'))*-1 redo_usage
-- , KTIFPPSI,KTIFPRBS,KTIFPTCN
from x$ktifp
;
-- Sizes of private redo
select
indx,
PNEXT_BUF_KCRFA_CLN,
PTR_KCRF_PVT_STRAND,
FIRST_BUF_KCRFA,
LAST_BUF_KCRFA,
-- LASTCHANGE_KCRFA, not available in 11.2
STRAND_SIZE_KCRFA strand_size,
SPACE_KCRF_PVT_STRAND strand_space
from
x$kcrfstrand
;1.1.6 c_mystats.sql[편집]
- Put names to v$mystat
rem
rem Script: c_mystats.sql
rem Author: Jonathan Lewis
rem Dated: March 2001
rem Purpose: Put names to v$mystat
rem
rem Last tested
rem 11.2.0.2
rem 10.2.0.3
rem 10.1.0.4
rem 9.2.0.8
rem 8.1.7.4
rem
rem Notes:
rem Should be run by SYS - which means it has to be re-run
rem on a full database export/import
rem
rem Option 1 - using v$
rem Use first_rows and ordered to avoid a sort/merge join, and
rem to allow faster elimination of the 'value = 0' rows.
rem
create or replace view v$my_stats
as
select
/*+
first_rows
ordered
*/
ms.sid,
sn.statistic#,
sn.name,
sn.class,
ms.value
from
v$mystat ms,
v$statname sn
where
sn.statistic# = ms.statistic#
;
rem
rem Option 2 - using x$
rem Avoids the filter subquery for count(*) from x$ksusd
rem (See v$fixed_view_definition)
rem
create or replace view v$my_stats
as
select
/*+
first_rows
ordered
*/
ms.ksusenum sid,
sn.indx statistic#,
sn.ksusdnam name,
sn.ksusdcls class,
ms.ksusestv value
from
x$ksumysta ms,
x$ksusd sn
where
ms.inst_id = sys_context('userenv','instance')
and bitand(ms.ksspaflg,1)!=0
and bitand(ms.ksuseflg,1)!=0
and sn.inst_id = sys_context('userenv','instance')
and sn.indx = ms.ksusestn
;
drop public synonym v$my_stats;
create public synonym v$my_stats for v$my_stats;
grant select on v$my_stats to public;1.1.7 core_demo_02.sql[편집]
- Construct example to dump consistent undo / redo for a single row change that does not involve any index maintenance.
rem
rem Script: core_demo_02.sql
rem Author: Jonathan Lewis
rem Dated: Feb 2011
rem Purpose:
rem
rem Last tested
rem 10.2.0.3
rem 9.2.0.8
rem Not tested
rem 11.2.0.2
rem 11.1.0.7
rem Not considered
rem 8.1.7.4
rem
rem Depends on
rem c_dump_seg.sql
rem c_dump_undo_block.sql
rem c_dump_log.sql
rem
rem Priveleges needed
rem Execute on
rem dbms_random
rem dbms_stats
rem dbms_rowid
rem dbms_lock
rem alter session
rem alter system
rem
rem Notes:
rem Construct example to dump consistent undo / redo
rem for a single row change that does not involve any
rem index maintenance.
rem
rem Uses 1MB uniform extents, 8KB blocks,
rem locally managed tablespace
rem freelist management.
rem
rem We need to update three rows in a single block, and
rem we should not visit those three rows in order, as
rem we want to pick the third update and show the undo
rem pointers in various ways:
rem
rem a) undo record pointing to previous undo record for
rem rollback which we want to be for a different block
rem b) undo record holding the contents of the previous
rem version of the ITL record for this transaction
rem
rem This code produces 60 rows per block, and updates
rem five rows going from block 1 to 2 to 1 to 2 to 1
rem
start setenv
set timing off
execute dbms_random.seed(0)
drop table t1;
begin
begin execute immediate 'purge recyclebin';
exception when others then null;
end;
begin
dbms_stats.set_system_stats('MBRC',8);
dbms_stats.set_system_stats('MREADTIM',26);
dbms_stats.set_system_stats('SREADTIM',12);
dbms_stats.set_system_stats('CPUSPEED',800);
exception
when others then null;
end;
begin execute immediate 'begin dbms_stats.delete_system_stats; end;';
exception when others then null;
end;
begin execute immediate 'alter session set "_optimizer_cost_model"=io';
exception when others then null;
end;
end;
/
create table t1
as
select
2 * rownum - 1 id,
rownum n1,
cast('xxxxxx' as varchar2(10)) v1,
rpad('0',100,'0') padding
from
all_objects
where
rownum <= 60
union all
select
2 * rownum id,
rownum n1,
cast('xxxxxx' as varchar2(10)) v1,
rpad('0',100,'0') padding
from
all_objects
where
rownum <= 60
;
create index t1_i1 on t1(id);
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T1',
method_opt => 'for all columns size 1'
);
end;
/
select
dbms_rowid.rowid_block_number(rowid) block_number,
count(*) rows_per_block
from
t1
group by
dbms_rowid.rowid_block_number(rowid)
order by
block_number
;
alter system switch logfile;
execute dbms_lock.sleep(2)
spool core_demo_02.lst
execute dump_seg('t1')
update
/*+ index(t1 t1_i1) */
t1
set
v1 = 'YYYYYYYYYY'
where
id between 5 and 9
;
pause Query the IMU structures now (@core_imu_01.sql)
execute dump_seg('t1')
execute dump_undo_block
rollback;
commit;
execute dump_log
spool off
set doc off
doc
Table block before update - row [4]
-----------------------------------
scn: 0x0000.03ee4843 seq: 0x02 flg: 0x04 tail: 0x48430602
frmt: 0x02 chkval: 0x6b62 type: 0x06=trans data
Block header dump: 0x02c0018a
Object id on Block? Y
seg/obj: 0xb2f2 csc: 0x00.3ee4842 itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.03ee4842
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x3397074
===============
tsiz: 0x1f88
hsiz: 0x8a
pbl: 0x03397074
bdba: 0x02c0018a
76543210
flag=--------
ntab=1
nrow=60
frre=-1
fsbo=0x8a
fseo=0x412
avsp=0x388
tosp=0x388
0xe:pti[0] nrow=60 offs=0
0x12:pri[0] offs=0x1f13
0x14:pri[1] offs=0x1e9e
0x16:pri[2] offs=0x1e29
0x18:pri[3] offs=0x1db4
0x1a:pri[4] offs=0x1d3f
tab 0, row 4, @0x1d3f
tl: 117 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 0a
col 1: [ 2] c1 06
col 2: [ 6] 78 78 78 78 78 78
col 3: [100]
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
Table block after update - row 4
--------------------------------
scn: 0x0000.03ee485a seq: 0x03 flg: 0x00 tail: 0x485a0603
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x02c0018a
Object id on Block? Y
seg/obj: 0xb2f2 csc: 0x00.3ee4842 itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.03ee4842
0x02 0x000a.01a.0000255b 0x0080009a.09d4.0f ---- 3 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x3397074
===============
tsiz: 0x1f88
hsiz: 0x8a
pbl: 0x03397074
bdba: 0x02c0018a
76543210
flag=--------
ntab=1
nrow=60
frre=-1
fsbo=0x8a
fseo=0x2a7
avsp=0x37c
tosp=0x37c
0xe:pti[0] nrow=60 offs=0
0x12:pri[0] offs=0x1f13
0x14:pri[1] offs=0x1e9e
0x16:pri[2] offs=0x399
0x18:pri[3] offs=0x320
0x1a:pri[4] offs=0x2a7
tab 0, row 4, @0x2a7
tl: 121 fb: --H-FL-- lb: 0x2 cc: 4
col 0: [ 2] c1 0a
col 1: [ 2] c1 06
col 2: [10] 59 59 59 59 59 59 59 59 59 59
col 3: [100]
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
Undo header:
------------
------------
scn: 0x0000.03ee485a seq: 0x01 flg: 0x00 tail: 0x485a2601
frmt: 0x02 chkval: 0x0000 type: 0x26=KTU SMU HEADER BLOCK
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 5 #blocks: 39
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x0080009a ext#: 0 blk#: 0 ext size: 7
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 5 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080009a length: 7
0x00800081 length: 8
0x008000c1 length: 8
0x008000f1 length: 8
0x008000e1 length: 8
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1300038179
Extent Number:1 Commit Time: 1299512180
Extent Number:2 Commit Time: 1299512190
Extent Number:3 Commit Time: 1299951292
Extent Number:4 Commit Time: 1300038179
TRN CTL:: seq: 0x09d4 chd: 0x0025 ctl: 0x0017 inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x0080009a.09d4.0b scn: 0x0000.03ee398c
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.09d4.0a ext: 0x0 spc: 0x1c02
uba: 0x00000000.09d2.03 ext: 0x3 spc: 0x1ea4
uba: 0x00000000.09cb.07 ext: 0x0 spc: 0x1dbc
uba: 0x00000000.0945.01 ext: 0x2 spc: 0x1fa0
uba: 0x00000000.0945.01 ext: 0x2 spc: 0x1fa0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x255a 0x0007 0x0000.03ee3fc0 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x01 9 0x00 0x256a 0x001e 0x0000.03ee4826 0x008000e8 0x0000.000.00000000 0x00000001 0x00000000
0x02 9 0x00 0x2575 0x0021 0x0000.03ee3aad 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x03 9 0x00 0x2560 0x0017 0x0000.03ee4846 0x0080009a 0x0000.000.00000000 0x00000001 0x00000000
0x04 9 0x00 0x2565 0x0024 0x0000.03ee464d 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x05 9 0x00 0x2569 0x0002 0x0000.03ee39c0 0x008000f5 0x0000.000.00000000 0x00000001 0x00000000
0x06 9 0x00 0x256f 0x0026 0x0000.03ee39aa 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x07 9 0x00 0x2559 0x0009 0x0000.03ee3fd5 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x08 9 0x00 0x256d 0x0013 0x0000.03ee404f 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x09 9 0x00 0x1d15 0x0014 0x0000.03ee3fe7 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x0a 9 0x00 0x256b 0x0000 0x0000.03ee3c79 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x0b 9 0x00 0x2563 0x000f 0x0000.03ee4418 0x00000000 0x0000.000.00000000 0x00000000 0x00000000
0x0c 9 0x00 0x2576 0x0027 0x0000.03ee399b 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x0d 9 0x00 0x256e 0x0012 0x0000.03ee39a1 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x0e 9 0x00 0x2566 0x002f 0x0000.03ee39bd 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x0f 9 0x00 0x2568 0x0016 0x0000.03ee45ca 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x10 9 0x00 0x2571 0x0006 0x0000.03ee39a7 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x11 9 0x00 0x2570 0x0019 0x0000.03ee3ad9 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x12 9 0x00 0x256e 0x0010 0x0000.03ee39a4 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x13 9 0x00 0x256a 0x000b 0x0000.03ee434b 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x14 9 0x00 0x2571 0x0008 0x0000.03ee3ffa 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x15 9 0x00 0x255d 0x002e 0x0000.03ee3ac4 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x16 9 0x00 0x256e 0x0004 0x0000.03ee460f 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x17 9 0x00 0x255f 0xffff 0x0000.03ee4848 0x008000e8 0x0000.000.00000000 0x00000001 0x00000000
0x18 9 0x00 0x2567 0x0020 0x0000.03ee4720 0x008000e8 0x0000.000.00000000 0x00000001 0x00000000
0x19 9 0x00 0x2546 0x000a 0x0000.03ee3c71 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
** 0x1a 10 0x80 0x255b 0x0000 0x0000.03ee485a 0x0080009a 0x0000.000.00000000 0x00000001 0x00000000
0x1b 9 0x00 0x256f 0x002a 0x0000.03ee39b6 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x1c 9 0x00 0x2571 0x0022 0x0000.03ee39b0 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x1d 9 0x00 0x2576 0x0011 0x0000.03ee3ad2 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x1e 9 0x00 0x255a 0x002b 0x0000.03ee4842 0x0080009a 0x0000.000.00000000 0x00000001 0x00000000
0x1f 9 0x00 0x2572 0x000c 0x0000.03ee3998 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x20 9 0x00 0x256d 0x0001 0x0000.03ee4746 0x008000e8 0x0000.000.00000000 0x00000001 0x00000000
0x21 9 0x00 0x2486 0x0029 0x0000.03ee3ab5 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x22 9 0x00 0x2569 0x001b 0x0000.03ee39b3 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x23 9 0x00 0x256a 0x000e 0x0000.03ee39bc 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x24 9 0x00 0x2575 0x0018 0x0000.03ee4716 0x008000e8 0x0000.000.00000000 0x00000002 0x00000000
0x25 9 0x00 0x2561 0x0028 0x0000.03ee398f 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x26 9 0x00 0x2566 0x001c 0x0000.03ee39ad 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x27 9 0x00 0x2558 0x000d 0x0000.03ee399e 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x28 9 0x00 0x2571 0x002c 0x0000.03ee3992 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x29 9 0x00 0x256e 0x0015 0x0000.03ee3abc 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x2a 9 0x00 0x2568 0x0023 0x0000.03ee39b9 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x2b 9 0x00 0x256f 0x002d 0x0000.03ee4844 0x0080009a 0x0000.000.00000000 0x00000001 0x00000000
0x2c 9 0x00 0x256f 0x001f 0x0000.03ee3995 0x008000e6 0x0000.000.00000000 0x00000001 0x00000000
0x2d 9 0x00 0x254a 0x0003 0x0000.03ee4845 0x0080009a 0x0000.000.00000000 0x00000001 0x00000000
0x2e 9 0x00 0x2567 0x001d 0x0000.03ee3acc 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
0x2f 9 0x00 0x2553 0x0005 0x0000.03ee39be 0x008000e7 0x0000.000.00000000 0x00000001 0x00000000
Undo block:
-----------
-----------
scn: 0x0000.03ee485a seq: 0x05 flg: 0x00 tail: 0x485a0205
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
********************************************************************************
UNDO BLK:
xid: 0x000a.01a.0000255b seq: 0x9d4 cnt: 0xf irb: 0xf icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f9c 0x02 0x1f4c 0x03 0x1ebc 0x04 0x1e88 0x05 0x1e2c
0x06 0x1db8 0x07 0x1d40 0x08 0x1cec 0x09 0x1c84 0x0a 0x1c28
0x0b 0x1bbc 0x0c 0x1b68 0x0d 0x1b0c 0x0e 0x1ab0 0x0f 0x1a54
*-----------------------------
* Rec #0xb slt: 0x1a objn: 45810(0x0000b2f2) objd: 45810 tblspc: 12(0x0000000c)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x0080009a.09d4.09 ctl max scn: 0x0000.03ee3989 prv tx scn: 0x0000.03ee398c
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
*-----------------------------
* Rec #0xc slt: 0x1a objn: 45810(0x0000b2f2) objd: 45810 tblspc: 12(0x0000000c)
* Layer: 11 (Row) opc: 1 rci 0x0b
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018b hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
*-----------------------------
* Rec #0xd slt: 0x1a objn: 45810(0x0000b2f2) objd: 45810 tblspc: 12(0x0000000c)
* Layer: 11 (Row) opc: 1 rci 0x0c
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0b
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
*-----------------------------
* Rec #0xe slt: 0x1a objn: 45810(0x0000b2f2) objd: 45810 tblspc: 12(0x0000000c)
* Layer: 11 (Row) opc: 1 rci 0x0d
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0c
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018b hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
*********************
This one
*********************
*-----------------------------
* Rec #0xf slt: 0x1a objn: 45810(0x0000b2f2) objd: 45810 tblspc: 12(0x0000000c)
* Layer: 11 (Row) opc: 1 rci 0x0e
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0d
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
Redo Record / Changes
*********************
REDO RECORD - Thread:1 RBA: 0x00036f.00000003.0010 LEN: 0x0174 VLD: 0x01
SCN: 0x0000.03ee485a SUBSCN: 1 03/13/2011 17:43:01
CHANGE #1 TYP:0 CLS:35 AFN:2 DBA:0x00800099 SCN:0x0000.03ee4848 SEQ: 1 OP:5.2
ktudh redo: slt: 0x001a sqn: 0x0000255b flg: 0x0012 siz: 108 fbi: 0
uba: 0x0080009a.09d4.0b pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:2 DBA:0x0080009a SCN:0x0000.03ee4845 SEQ: 2 OP:5.1
ktudb redo: siz: 108 spc: 7170 flg: 0x0012 seq: 0x09d4 rec: 0x0b
xid: 0x000a.01a.0000255b
ktubl redo: slt: 26 rci: 0 opc: 11.1 objn: 45810 objd: 45810 tsn: 12
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0080009a.09d4.09
prev ctl max cmt scn: 0x0000.03ee3989 prev tx cmt scn: 0x0000.03ee398c
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #3 TYP:0 CLS: 1 AFN:11 DBA:0x02c0018a SCN:0x0000.03ee4843 SEQ: 2 OP:11.5
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x000a.01a.0000255b uba: 0x0080009a.09d4.0b
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 16
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 9
serial number = 9
transaction name =
REDO RECORD - Thread:1 RBA: 0x00036f.00000003.0184 LEN: 0x00f8 VLD: 0x01
SCN: 0x0000.03ee485a SUBSCN: 1 03/13/2011 17:43:01
CHANGE #1 TYP:0 CLS:36 AFN:2 DBA:0x0080009a SCN:0x0000.03ee485a SEQ: 1 OP:5.1
ktudb redo: siz: 84 spc: 7060 flg: 0x0022 seq: 0x09d4 rec: 0x0c
xid: 0x000a.01a.0000255b
ktubu redo: slt: 26 rci: 11 opc: 11.1 objn: 45810 objd: 45810 tsn: 12
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018b hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 TYP:0 CLS: 1 AFN:11 DBA:0x02c0018b SCN:0x0000.03ee4843 SEQ: 2 OP:11.5
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x000a.01a.0000255b uba: 0x0080009a.09d4.0c
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018b hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 16
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
REDO RECORD - Thread:1 RBA: 0x00036f.00000004.008c LEN: 0x00f8 VLD: 0x01
SCN: 0x0000.03ee485a SUBSCN: 1 03/13/2011 17:43:01
CHANGE #1 TYP:0 CLS:36 AFN:2 DBA:0x0080009a SCN:0x0000.03ee485a SEQ: 2 OP:5.1
ktudb redo: siz: 92 spc: 6974 flg: 0x0022 seq: 0x09d4 rec: 0x0d
xid: 0x000a.01a.0000255b
ktubu redo: slt: 26 rci: 12 opc: 11.1 objn: 45810 objd: 45810 tsn: 12
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0b
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 TYP:0 CLS: 1 AFN:11 DBA:0x02c0018a SCN:0x0000.03ee485a SEQ: 1 OP:11.5
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0d
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 2 ckix: 16
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
REDO RECORD - Thread:1 RBA: 0x00036f.00000004.0184 LEN: 0x00f8 VLD: 0x01
SCN: 0x0000.03ee485a SUBSCN: 1 03/13/2011 17:43:01
CHANGE #1 TYP:0 CLS:36 AFN:2 DBA:0x0080009a SCN:0x0000.03ee485a SEQ: 3 OP:5.1
ktudb redo: siz: 92 spc: 6880 flg: 0x0022 seq: 0x09d4 rec: 0x0e
xid: 0x000a.01a.0000255b
ktubu redo: slt: 26 rci: 13 opc: 11.1 objn: 45810 objd: 45810 tsn: 12
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0c
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018b hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 TYP:0 CLS: 1 AFN:11 DBA:0x02c0018b SCN:0x0000.03ee485a SEQ: 1 OP:11.5
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0e
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018b hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 2 ckix: 16
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
*********************
This one
*********************
REDO RECORD - Thread:1 RBA: 0x00036f.00000005.008c LEN: 0x00f8 VLD: 0x01
SCN: 0x0000.03ee485a SUBSCN: 1 03/13/2011 17:43:01
CHANGE #1 TYP:0 CLS:36 AFN:2 DBA:0x0080009a SCN:0x0000.03ee485a SEQ: 4 OP:5.1
ktudb redo: siz: 92 spc: 6786 flg: 0x0022 seq: 0x09d4 rec: 0x0f
xid: 0x000a.01a.0000255b
ktubu redo: slt: 26 rci: 14 opc: 11.1 objn: 45810 objd: 45810 tsn: 12
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0d
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 TYP:0 CLS: 1 AFN:11 DBA:0x02c0018a SCN:0x0000.03ee485a SEQ: 2 OP:11.5
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0f
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 2 ckix: 16
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
*********************
COMMIT
*********************
REDO RECORD - Thread:1 RBA: 0x00036f.00000005.0184 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.03ee485b SUBSCN: 1 03/13/2011 17:43:01
CHANGE #1 TYP:0 CLS:35 AFN:2 DBA:0x00800099 SCN:0x0000.03ee485a SEQ: 1 OP:5.4
ktucm redo: slt: 0x001a sqn: 0x0000255b srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080009a.09d4.0f ext: 0 spc: 6692 fbi: 0
#