Undo 세미나
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 언두 테스트[편집]
SELECT SUBSTR(SEGMENT_NAME, 1, 20) TB , BYTES/1024/1024 "MB", BLOCKS
FROM USER_SEGMENTS
WHERE SEGMENT_NAME IN ( 'TB_BIG','TB_BIG2');
-- SHARED_POOL/BUFFER_POOL/FLASH_CACHE/BUFFER_CACHE/GLOBAL CONTEXT/PASSWORDFILE_METADATA_CACHE 키워드
alter system flush FLASH_CACHE;
alter system flush BUFFER_CACHE;
alter table tb_big rename to tb_big2;
drop table tb_big2 purge;
alter table tb_big rename to tb_big2;
SELECT A.TABLESPACE_NAME,A.STATUS, B.TOTAL_MB, A.USE_MB,
ROUND(RATIO_TO_REPORT(A.USE_MB) OVER(PARTITION BY A.TABLESPACE_NAME) * 100)||'%' AS PCT
FROM (SELECT TABLESPACE_NAME, STATUS,
ROUND(SUM(BYTES/1024/1024)) USE_MB
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME,STATUS
)A,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024) TOTAL_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'UNDO%'
GROUP BY TABLESPACE_NAME
)B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 1,2
;
SELECT SUBSTRB(A.SEGMENT_NAME, 1, 10) AS SEGMENT_NAME
, SUBSTRB(A.TABLESPACE_NAME, 1, 10) AS TABLESPACE_NAME
, TO_CHAR(A.SEGMENT_ID, '99,999') AS SEG_ID
, TO_CHAR(A.MAX_EXTENTS, '999,999') AS MAX_EXT
, TO_CHAR(B.EXTENTS, '999,999') AS EXTENTS
, TO_CHAR(B.EXTENDS, '999,999') AS EXTENDS
, TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS - 1) * A.NEXT_EXTENT) / 1000000, '9,999.999') AS "ALLOC(MB)"
, TO_CHAR(XACTS, '9,999') AS XACTS
FROM DBA_ROLLBACK_SEGS A
, V$ROLLSTAT B
WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1;
SELECT group#
, status
, member
FROM v$logfile;
select * from V$ARCHIVED_LOG;
select * from v$parameter where name like '%log%';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
drop table tb_big3 purge;
create table tb_big3
as
select cola,colb,22 as colc,cold,cole,colf,colg,colh,coli
from tb_big;
select count(*) from tb_big;
alter system switch logfile;
-- 테이블 생성
CREATE TABLE TB_BIG(
COLA VARCHAR2(20), COLB NUMBER , COLC NUMBER,
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30),
COLG NUMBER , COLH VARCHAR2(30), COLI VARCHAR2(30)
);
-- DATA 입력
DECLARE
TYPE tbl_ins IS TABLE OF TB_BIG%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..8960000 LOOP
w_ins(i).COLA :=i;
w_ins(i).COLB :=300000;
w_ins(i).COLC :=99; -- <== 변경 테스트 대상 컬럼 99를 11로 변경
w_ins(i).COLD :='ABC'||dbms_random.string('x',10);
w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
w_ins(i).COLG :=9999999;
w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
FORALL i in 1..8960000 INSERT INTO TB_BIG VALUES w_ins(i);
COMMIT;
END;
/
-- 8,960,000 건
TB MB BLOCKS
-------------------- ---------- ----------
TB_BIG 1216 155648
1.1 UPDATE 테스트[편집]
sh-4.2$ cat update_tb_big.sql
update tb_big set colc=11;
commit;
====== 시작 ======
15:46:35 SQL> @update_tb_big.sql
8960000 rows updated.
Commit complete.
15:54:14 SQL>
====== 종료 ====== 총 7분 40초 , 460초
1.2 CTAS 테스트[편집]
sh-4.2$ cat ctas_tb_big.sql
-- rename old
alter table tb_big rename to tb_big_old;
-- ctas
create table tb_big
as
select cola,colb,22 as colc
, cold,cole,colf,colg,colh,coli
from tb_big_old;
====== 시작 ======
15:58:56 SQL> @ctas_tb_big.sql
Table created.
15:59:22 SQL>
====== 종료 ======= 총 26초
2 archive log mode 인경우 테스트[편집]
16:28:03 SQL> @update_tb_big.sql
8960000 rows updated.
Commit complete.
16:35:46 SQL>
====== 종료 ====== 총 7분 43초 , 463초
2.1 UPDATE 테스트[편집]
- 아카이빙 로그 사이즈
- 로그 테스트 전
bash-4.2# du -h
16K .
- 로그 테스트 후
-rw-r----- 1 oracle oinstall 196845056 Jun 16 16:28 arch1_37_1171284405.dbf
-rw-r----- 1 oracle oinstall 195198976 Jun 16 16:29 arch1_38_1171284405.dbf
-rw-r----- 1 oracle oinstall 194925056 Jun 16 16:29 arch1_39_1171284405.dbf
-rw-r----- 1 oracle oinstall 195596288 Jun 16 16:30 arch1_40_1171284405.dbf
-rw-r----- 1 oracle oinstall 195552256 Jun 16 16:30 arch1_41_1171284405.dbf
-rw-r----- 1 oracle oinstall 195438592 Jun 16 16:31 arch1_42_1171284405.dbf
-rw-r----- 1 oracle oinstall 189432320 Jun 16 16:31 arch1_43_1171284405.dbf
-rw-r----- 1 oracle oinstall 183622656 Jun 16 16:32 arch1_44_1171284405.dbf
-rw-r----- 1 oracle oinstall 190867456 Jun 16 16:32 arch1_45_1171284405.dbf
-rw-r----- 1 oracle oinstall 190482432 Jun 16 16:33 arch1_46_1171284405.dbf
-rw-r----- 1 oracle oinstall 190621696 Jun 16 16:33 arch1_47_1171284405.dbf
-rw-r----- 1 oracle oinstall 191492608 Jun 16 16:34 arch1_48_1171284405.dbf
-rw-r----- 1 oracle oinstall 185030144 Jun 16 16:34 arch1_49_1171284405.dbf
-rw-r----- 1 oracle oinstall 188132864 Jun 16 16:35 arch1_50_1171284405.dbf
drwxr-xr-x 1 oracle dba 4096 Jun 16 16:35 .
-rw-r----- 1 oracle oinstall 179443200 Jun 16 16:35 arch1_51_1171284405.dbf
bash-4.2# date
Sun Jun 16 16:41:50 UTC 2024
bash-4.2# du -k
2795632 .
bash-4.2# du -h
2.7G .
2.2 CTAS 테스트[편집]
bash-4.2# ls -altr
total 1358964
-rw-r--r-- 1 oracle dba 3079 May 14 2015 init.ora
drwxr-xr-x 1 oracle dba 4096 Jun 10 12:44 ..
-rw-r----- 1 oracle oinstall 24 Jun 10 12:44 lkMONGO
lrwxrwxrwx 1 oracle oinstall 54 Jun 10 13:07 spfileMONGO.ora -> /u01/app/oracle/oradata/dbconfig/MONGO/spfileMONGO.ora
lrwxrwxrwx 1 oracle oinstall 49 Jun 10 13:07 orapwMONGO -> /u01/app/oracle/oradata/dbconfig/MONGO/orapwMONGO
-rw-rw---- 1 oracle oinstall 1544 Jun 16 16:27 hc_MONGO.dat
-rw-r----- 1 oracle oinstall 198791168 Jun 16 16:49 arch1_52_1171284405.dbf
-rw-r----- 1 oracle oinstall 198788096 Jun 16 16:49 arch1_53_1171284405.dbf
-rw-r----- 1 oracle oinstall 198794240 Jun 16 16:49 arch1_54_1171284405.dbf
-rw-r----- 1 oracle oinstall 198791680 Jun 16 16:49 arch1_55_1171284405.dbf
-rw-r----- 1 oracle oinstall 198794240 Jun 16 16:49 arch1_56_1171284405.dbf
-rw-r----- 1 oracle oinstall 198794240 Jun 16 16:49 arch1_57_1171284405.dbf
drwxr-xr-x 1 oracle dba 4096 Jun 16 16:49 .
-rw-r----- 1 oracle oinstall 198789120 Jun 16 16:49 arch1_58_1171284405.dbf
bash-4.2# du -k ./
1358956 ./
bash-4.2# du -h ./
1.3G ./
3 CTAS NOLOGGING MODE[편집]
3.1 ctas nologging mode[편집]
bash-4.2# du -h
16K .
- 00:19.21 소요
-- UNRECOVERABLE 옵션 사용
SQL> @ctas_tb_big_nolog.sql
Table altered.
Elapsed: 00:00:00.03
Table created.
Elapsed: 00:00:19.21
3.2 ctas nolooging 처리후[편집]
bash-4.2# du -h
1.2G .
==> nolooging 인데도 사이즈가 크게 줄지 않음......
--- 테이블 drop 시
drop table tb_big2 purge;
bash-4.2# du -h
1.3G .
3.3 insert ~ select nologging[편집]
- 01:19 초 소요
SQL> @ins_sel_nologging.sql
Table altered.
Elapsed: 00:00:00.07
Table created.
Elapsed: 00:00:00.10
Table altered.
Elapsed: 00:00:00.04
8960000 rows created.
Elapsed: 00:01:19.94
bash-4.2# du -h
1.2G .
bash-4.2#
4 undo 테이블스페이스 사이즈 확인[편집]
4.1 undo 블럭 디버깅[편집]
4.1.1 Undo Segment Header[편집]
===================
Undo Segment Header
===================
Start dump data blocks tsn: 2 file#:4 minblk 160 maxblk 160
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=2 rdba=16777376
BH (0xbaf9f378) file#: 4 rdba: 0x010000a0 (4/160) class: 21 ba: 0xba73a000
set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: -1 objn: 0 tsn: [1/2] afn: 4 hint: f
hash: [0x76ec93d0,0x76ec93d0] lru: [0xbafa1a58,0xbaf9f2f8]
lru-flags: hot_buffer
obj-flags: object_ckpt_list
ckptq: [0x89f76428,0x8ef9c3a8] fileq: [0x89f76438,0x8ef9c3b8]
objq: [0x8ef9c4b8,0x89f76538] objaq: [0x6c4d6578,0xbaf9f1c8]
use: [NULL] wait: [NULL]
st: XCURRENT md: NULL fpin: 'ktuwh72: ktugus:ktuswr1' fscn: 0x23d008 tch: 42 txn: 0x747dabf0
flags: buffer_dirty private block_written_once
LRBA: [0x6f.1e8e.0] LSCN: [0x28c8e7] HSCN: [0x28c914] HSUB: [1]
Printing buffer operation history (latest change first):
cnt: 10
01. sid:11 L786:pre_apl:bis:FMS 02. sid:11 L803:pre_apl:bis:FPB
03. sid:11 L353:gcur:set:MEXCL 04. sid:11 L464:chg1_mn:bic:FMS
05. sid:11 L778:chg1_mn:bis:FMS 06. sid:11 L353:gcur:set:MEXCL
07. sid:09 L464:chg1_mn:bic:FMS 08. sid:09 L778:chg1_mn:bis:FMS
09. sid:09 L353:gcur:set:MEXCL 10. sid:09 L464:chg1_mn:bic:FMS
11. sid:09 L778:chg1_mn:bis:FMS 12. sid:09 L353:gcur:set:MEXCL
13. sid:11 L464:chg1_mn:bic:FMS 14. sid:11 L778:chg1_mn:bis:FMS
15. sid:11 L353:gcur:set:MEXCL 16. sid:09 L464:chg1_mn:bic:FMS
buffer tsn: 2 rdba: 0x010000a0 (4/160)
scn: 0x28c914 seq: 0x01 flg: 0x00 tail: 0xc9142601
frmt: 0x02 chkval: 0x0000 type: 0x26=KTU SMU HEADER BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000BA73A000 to 0x00000000BA73C000
4.1.2 Undo Start block[편집]
================
Undo Start block
================
Start dump data blocks tsn: 2 file#:4 minblk 542 maxblk 542
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=2 rdba=16777758
BH (0x9cfc4d58) file#: 4 rdba: 0x0100021e (4/542) class: 22 ba: 0x9ca92000
set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: -1 objn: 0 tsn: [1/2] afn: 4 hint: f
hash: [0x8efadd98,0x76f2cf30] lru: [0x88fcc460,0x9af633e8]
obj-flags: object_ckpt_list
ckptq: [0xb1f8e140,0xbafca320] fileq: [0x75484058,0xbafca330]
objq: [0xbafca430,0x6c4c42f8] objaq: [0xa6fb41e0,0xb0fb63a0]
use: [NULL] wait: [NULL]
st: XCURRENT md: NULL fpin: 'ktuwh03: ktugnb' fscn: 0x2852b3 tch: 2 txn: 0x747dabf0
flags: buffer_dirty private block_written_once
LRBA: [0x6f.1ee9.0] LSCN: [0x28c8fa] HSCN: [0x28c913] HSUB: [5]
Printing buffer operation history (latest change first):
cnt: 8
01. sid:11 L802:gcur:bis:FPB 02. sid:11 L353:gcur:set:MEXCL
03. sid:09 L464:chg1_mn:bic:FMS 04. sid:09 L778:chg1_mn:bis:FMS
05. sid:09 L362:chg1:set:MEXCL 06. sid:09 L464:chg1_mn:bic:FMS
07. sid:09 L778:chg1_mn:bis:FMS 08. sid:09 L362:chg1:set:MEXCL
09. sid:09 L464:chg1_mn:bic:FMS 10. sid:09 L778:chg1_mn:bis:FMS
11. sid:09 L362:chg1:set:MEXCL 12. sid:09 L464:chg1_mn:bic:FMS
13. sid:09 L778:chg1_mn:bis:FMS 14. sid:09 L362:chg1:set:MEXCL
15. sid:09 L464:chg1_mn:bic:FMS 16. sid:09 L778:chg1_mn:bis:FMS
buffer tsn: 2 rdba: 0x0100021e (4/542)
scn: 0x28c913 seq: 0x05 flg: 0x00 tail: 0xc9130205
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000009CA92000 to 0x000000009CA94000
4.1.3 UNDO BLK[편집]
********************************************************************************
UNDO BLK:
xid: 0x0003.019.00000475 seq: 0x104 cnt: 0x8 irb: 0x8 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f80 0x02 0x1f2c 0x03 0x1ea4 0x04 0x1e1c 0x05 0x1db0
0x06 0x1d44 0x07 0x1cdc 0x08 0x1c98
*-----------------------------
* Rec #0x1 slt: 0x1d objn: 1(0x00000001) objd: 4294967295 tblspc: 0(0x00000000)
* Layer: 22 (Tablespace Bitmapped file) opc: 3 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: Yes
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x0100021d.0104.38 ctl max scn: 0x000000000028c27a prv tx scn: 0x000000000028c295
txn start scn: scn: 0x000000000028c8fa logon user: 0
prev brb: 16777751 prev bcl: 0
ktfbhundo - File Space Header Undo:
Space Header DBA:0x400002, File:0x1
Header Opcode:
Save: Free Extent:
Begin: 443680, Length: 8, Instance: 0
..... 생략 .....
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.07
KDO Op code: LKR row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00406991 hdba: 0x00400090
itli: 2 ispac: 0 maxfr: 4863
tabn: 3 slot: 0 to: 0
Block dump from disk:
buffer tsn: 2 rdba: 0x0100021e (4/542)
scn: 0x2852b3 seq: 0x1b flg: 0x04 tail: 0x52b3021b
frmt: 0x02 chkval: 0x4b75 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FF0E0927000 to 0x00007FF0E0929000
... 생략 ...
********************************************************************************
UNDO BLK:
xid: 0x0003.008.00000462 seq: 0x100 cnt: 0x1a irb: 0x1a icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f10 0x02 0x1e38 0x03 0x1d90 0x04 0x1d1c 0x05 0x1cac
0x06 0x1c3c 0x07 0x1bc8 0x08 0x1af0 0x09 0x1a48 0x0a 0x1970
0x0b 0x18c8 0x0c 0x17f0 0x0d 0x1718 0x0e 0x1640 0x0f 0x1598
0x10 0x14f0 0x11 0x1418 0x12 0x1370 0x13 0x1298 0x14 0x11f0
0x15 0x1118 0x16 0x1040 0x17 0x0f68 0x18 0x0ec0 0x19 0x0dcc
0x1a 0x0d84
*-----------------------------
* Rec #0x1 slt: 0x08 objn: 73609(0x00011f89) objd: 73609 tblspc: 1(0x00000001)
* Layer: 10 (Index) opc: 22 rci 0x00
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x0100021d
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021d.0100.21
Dump kdilk : itl=2, kdxlkflg=0x21 sdc=32767 indexid=0xc14832 block=0x00c159f0
(kdxlpu): purge leaf row
number of keys: 3
key sizes:
42 42 42
key :(126):
06 c5 1c 2b 06 19 43 02 c1 5e 02 c1 02 02 c1 03 03 c2 16 0a 07 78 7c 06 17
11 03 30 06 c5 1c 2b 06 19 43 06 00 c1 59 ff 00 65 06 c5 1c 2b 06 19 43 02
c1 5e 02 c1 02 02 c1 03 03 c2 16 16 07 78 7c 06 17 11 03 30 06 c5 1c 2b 06
19 43 06 00 c1 59 ff 00 66 06 c5 1c 2b 06 19 43 02 c1 5e 02 c1 02 02 c1 03
03 c2 16 2d 07 78 7c 06 17 11 03 30 06 c5 1c 2b 06 19 43 06 00 c1 59 ff 00
67
keydata/bitmap: (1): ff
5 redo 테이블스페이스 사이즈 확인[편집]
5.1 redo 블럭 디버깅[편집]
DUMP OF REDO FROM FILE '/u01/app/oracle/oradata/MONGO/redo01.log'
Container ID: 0
Container UID: 0
Opcodes *.*
Container ID: 0
Container UID: 0
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000000000000000 thru scn: 0xffffffffffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 318767104=0x13000000
Db ID=2742052466=0xa3706672, Db Name='MONGO'
Activation ID=2742030962=0xa3701272
Control Seq=8990=0x231e, File size=409600=0x64000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"T 0001, S 0000000112, SCN 0x000000000028c925-0xffffffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x00000070 hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x45d061b5 scn: 0x00000000001d4fd1
prev resetlogs count: 0x3bf3129f scn: 0x0000000000000001
Low scn: 0x000000000028c925 06/24/2024 13:04:33
Next scn: 0xffffffffffffffff 01/01/1988 00:00:00
Enabled scn: 0x00000000001d4fd1 06/10/2024 12:46:45
Thread closed scn: 0x000000000028c925 06/24/2024 13:04:33
Real next scn: 0xffffffffffffffff
Disk cksum: 0x5e1b Calc cksum: 0x5e1b
Terminal recovery stop scn: 0x0000000000000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000000000000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Miscellaneous second flags: 0x0
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000000000000000
Zero blocks: 0
Format ID is 18
redo log key is 675ff3d7a77b9fb48595ec24a9804d3f
redo log key flag is 15
High watermark block number: 0
Enabled redo threads: 1
REDO RECORD - Thread:1 RBA: 0x000070.00000002.0010 LEN: 0x05d8 VLD: 0x0d CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 1 06/24/2024 13:04:45
(LWN RBA: 0x000070.00000002.0010 LEN: 0x00000006 NST: 0x0001 SCN: 0x000000000028c928)
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c8f2 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0003.021.00000474 uba: 0x0100021e.0104.09
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 18
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #2 CON_ID:1 TYP:0 CLS:21 AFN:4 DBA:0x010000a0 OBJ:4294967295 SCN:0x000000000028c914 SEQ:1 OP:5.2 ENC:0 RBL:0 FLG:0x0000
ktudh redo: slt: 0x0021 sqn: 0x00000474 flg: 0x0052 siz: 140 fbi: 0
uba: 0x0100021e.0104.09 pxid: 0x0000.000.00000000 pdbid:1
CHANGE #3 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c8f2 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0003.021.00000474 uba: 0x0100021e.0104.0a
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 18
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #4 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:1 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0b
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 2 ckix: 18
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #5 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c928 SEQ:1 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0c
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 2 ckix: 18
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #6 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0d
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 2 ckix: 18
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #7 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c913 SEQ:5 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 140 spc: 7286 flg: 0x0012 seq: 0x0104 rec: 0x09
xid: 0x0003.021.00000474
ktubl redo: slt: 33 wrp: 1 flg: 0x0c08 prev dba: 0x00000000 rci: 0 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
Begin trans
prev ctl uba: 0x0100021e.0104.04 prev ctl max cmt scn: 0x000000000028c2b9
prev tx cmt scn: 0x000000000028c2cf
txn start scn: 0x000000000028c920 logon user: 0
prev brb: 0x01000217 prev bcl: 0x00000000
BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #8 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 88 spc: 7144 flg: 0x0022 seq: 0x0104 rec: 0x0a
xid: 0x0003.021.00000474
ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 9 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #9 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:2 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 96 spc: 7054 flg: 0x0022 seq: 0x0104 rec: 0x0b
xid: 0x0003.021.00000474
ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 10 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.09
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #10 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:3 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 96 spc: 6956 flg: 0x0022 seq: 0x0104 rec: 0x0c
xid: 0x0003.021.00000474
ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 11 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0a
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #11 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:4 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 96 spc: 6858 flg: 0x0022 seq: 0x0104 rec: 0x0d
xid: 0x0003.021.00000474
ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 12 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0b
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
REDO RECORD - Thread:1 RBA: 0x000070.00000005.0018 LEN: 0x00cc VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 2 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:3 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0b
KDO Op code: URP row dependencies Disabled
xtype: XR flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:5 OP:5.6 ENC:0 RBL:0 FLG:0x0000
ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 12 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
ktuxvoff: 0x1a94 ktuxvflg: 0x0002
REDO RECORD - Thread:1 RBA: 0x000070.00000005.00e4 LEN: 0x00cc VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 3 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c928 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0a
KDO Op code: URP row dependencies Disabled
xtype: XR flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:6 OP:5.6 ENC:0 RBL:0 FLG:0x0000
ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 11 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
ktuxvoff: 0x1af4 ktuxvflg: 0x0002
REDO RECORD - Thread:1 RBA: 0x000070.00000005.01b0 LEN: 0x00cc VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 4 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:4 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.09
KDO Op code: URP row dependencies Disabled
xtype: XR flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:7 OP:5.6 ENC:0 RBL:0 FLG:0x0000
ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 10 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
ktuxvoff: 0x1b54 ktuxvflg: 0x0002
REDO RECORD - Thread:1 RBA: 0x000070.00000006.008c LEN: 0x00c4 VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 5 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c928 SEQ:3 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XR flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:8 OP:5.6 ENC:0 RBL:0 FLG:0x0000
ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 9 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
ktuxvoff: 0x1bb4 ktuxvflg: 0x0002
REDO RECORD - Thread:1 RBA: 0x000070.00000006.0150 LEN: 0x00c4 VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 5 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:5 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XR flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 CON_ID:1 TYP:0 CLS:21 AFN:4 DBA:0x010000a0 OBJ:4294967295 SCN:0x000000000028c928 SEQ:1 OP:5.11 ENC:0 RBL:0 FLG:0x0000
ktubl redo: slt: 33 wrp: 1 flg: 0x0c18 prev dba: 0x00000000 rci: 0 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
Begin trans
BuExt idx: 0 flg2: 0
REDO RECORD - Thread:1 RBA: 0x000070.00000007.0024 LEN: 0x0058 VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c929 SUBSCN: 1 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:21 AFN:4 DBA:0x010000a0 OBJ:4294967295 SCN:0x000000000028c928 SEQ:2 OP:5.4 ENC:0 RBL:0 FLG:0x0000
ktucm redo: slt: 0x0021 sqn: 0x00000474 srt: 0 sta: 9 flg: 0x4
rolled back transaction
END OF REDO DUMP
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 1023Kb
-- Redo read_disk statistics --
Read rate (SYNC): 3Kb in 0.01s => 0.29 Mb/sec
Total redo bytes: 1023Kb Longest record: 1Kb, moves: 0/7 moved: 0Mb (0%)
Longest LWN: 3Kb, reads: 1
Last redo scn: 0x000000000028c929 (2672937)
Change vector header moves = 1/22 (4%)
6 테이블스페이스 사이즈 확인[편집]
select *
from dba_tablespaces
order by 1,2;
select owner,count(*)
from dba_segments
where 1=1 -- owner='SYS'
group by owner
order by 1,2
;
-- 테이블 생성
create table dbcafe.TB_T1(A number, B char(1),C varchar2(10))
tablespace TS_DBCAFE;
select * from dbcafe.TB_T1;
-- 세그먼트에는 테이블에 데이터가 입력된적이 없으므로 조회되지 않음. 데이터 입력시 적용됨.
select *
from dba_segments
where segment_name like 'TB_T%'
;
-- 데이터 1건 입력
INSERT INTO dbcafe.TB_T1 VALUES (1,'1','11111');
-- commit 하지 말고 세그먼트 조회
select *
from dba_segments
where segment_name like 'TB_T%'
;
-- header_file 13
-- header_block 130
-- bytes 65536
-- blocks 8
-- extents 1
-- initial_extent 65536
-- next_extent 1048576
-- relative_fno 13
select * from dba_data_files where file_id = 13; -- 테이블스페이 데이터파일 번호 // relative_fno 13
-- [데이터 입력후 아직 커밋하지 않음]
-- 13 130 65536 8 1 65536 1048576
select * from v$session
where status ='ACTIVE'
;
select rowid
, dbms_rowid.rowid_relative_fno(rowid) as file_no -- data file number
, dbms_rowid.rowid_block_number(rowid) as block_no
from dbcafe.TB_T1 where rownum = 1;
-- block info
-- rowid file_no block_no
AAAR/wAANAAAACEAAA 13 132
;
-- 트레이스 파일 식별자 설정 (트레이스파일을 쉽게 찾기 위해서 설정 )
alter session set tracefile_identifier ='DBCAFE_Block_Dump';
alter system dump datafile 13 block 132;
-- 트레이스 폴더로 이동
cd $ORACE_BASE/diag/rdbms/{mongo/MONGO}/trace -- DB마다 {---} 위치가 다름
-- 트레이스 파일 찾기
ls |grep DBCAFE_Block_Dump;
MONGO_ora_43565_DBCAFE_Block_Dump.trc <== 트레이스 파일
MONGO_ora_43565_DBCAFE_Block_Dump.trm <== trace meta file,트레이스파일 메타정보를 보관하는 파일 , adrci같은 툴에서 활용함
-- 트레이스 파일 열기
vi MONGO_ora_43565_DBCAFE_Block_Dump.trc
-- 12c 이상 부터는 sql로 조회 가능
SELECT *
FROM V$DIAG_TRACE_FILE
where trace_filename like '%DBCAFE%'
ORDER BY CHANGE_TIME DESC
;
SELECT replace(replace(payload,chr(13)),chr(10)) as payload
FROM V$DIAG_TRACE_FILE_CONTENTS
WHERE TRACE_FILENAME = 'MONGO_ora_23904_DBCAFE_Block_Dump.trc'
ORDER BY LINE_NUMBER
;
Trace file /u01/app/oracle/diag/rdbms/mongo/MONGO/trace/MONGO_ora_23904_DBCAFE_Block_Dump.trc
... 생략 ....
*** 2024-06-23T03:22:44.434510+00:00 (CDB$ROOT(1))
*** SESSION ID:(21.6605) 2024-06-23T03:22:44.434570+00:00
-- 21은 sid
-- 6605는 serial#
..... 생략 ....
-----------------
Start dump data blocks tsn: 6 file#:13 minblk 132 maxblk 132
-- 1. 버퍼캐시 덤프
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=6 rdba=54526084
BH (0x7cf64db8) file#: 13 rdba: 0x03400084 (13/132) class: 1 ba: 0x7c20a000
set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 73712 objn: 73712 tsn: [1/6] afn: 13 hint: f
hash: [0x75181db8,0x75181db8] lru: [0x9afaa2c0,0x8cf53998]
ckptq: [NULL] fileq: [NULL]
objq: [0x6667add0,0x6667add0] objaq: [0x6667adc0,0x6667adc0]
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x273bb5 tch: 2
flags: block_written_once
LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x273bb5] HSUB: [3]
Printing buffer operation history (latest change first):
cnt: 3
01. sid:00 L192:kcbbic2:bic:FBD 02. sid:00 L191:kcbbic2:bic:FBW
03. sid:00 L602:bic1_int:bis:FWC 04. sid:00 L822:bic1_int:ent:rtn
05. sid:00 L832:oswmqbg1:clr:WRT 06. sid:00 L930:kubc:sw:mq
07. sid:00 L913:bxsv:sw:objq 08. sid:00 L608:bxsv:bis:FBW
09. sid:00 L607:bxsv:bis:FFW 10. sid:05 L464:chg1_mn:bic:FMS
11. sid:05 L778:chg1_mn:bis:FMS 12. sid:05 L353:gcur:set:MEXCL
13. sid:05 L464:chg1_mn:bic:FMS 14. sid:05 L614:chg1_mn:bis:FBD
15. sid:05 L922:klbc:sw:cq 16. sid:05 L778:chg1_mn:bis:FMS
buffer tsn: 6 rdba: 0x03400084 (13/132)
scn: 0x273bb5 seq: 0x03 flg: 0x04 tail: 0x3bb50603
frmt: 0x02 chkval: 0x0e55 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007C20A000 to 0x000000007C20C000
........... 생략 ..............
-- 블럭 헤더 덤프
-- Itl 로우 입력한 트랜젝션 이 itl 0x01 슬롯에 저장됨 , 0x01은 1번째
select xidusn,xidslot,xidsqn from v$transaction
where addr = (select taddr from v$session where sid = 21);
-- 세션 정보 조회는 TM 락
SELECT A.SID
, A.SERIAL#
, C.object_name
, A.SID || ', ' || A.SERIAL# AS KILL_TASK
FROM V$SESSION A
INNER JOIN V$LOCK B
ON A.SID = B.SID
INNER JOIN DBA_OBJECTS C
ON B.ID1 = C.OBJECT_ID
WHERE B.TYPE = 'TM'
;
-- xidusn xidslot xidsqn
4 8 1059
-- 16진수 -> 10진수로 변환
4 8 4185
;
-- 16진수 -> 10진수로 변환
select to_number('1059', 'xxxx') from dual;
;
-- 10진수 -> 16진수로 변환
select to_char(1059, 'xxxx') from dual;
;
Block header dump: 0x03400084
Object id on Block? Y
seg/obj: 0x11ff0 csc: 0x0000000000273bb5 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3400080 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.008.00000423 0x01000614.017b.1f ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x03400084
data_block_dump,data header at 0x7c20a064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7c20a064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8a
avsp=0x1f76
tosp=0x1f76
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8a
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 1] 31
col 2: [ 5] 31 31 31 31 31
end_of_block_dump
Block dump from disk:
buffer tsn: 6 rdba: 0x03400084 (13/132)
scn: 0x273bb5 seq: 0x03 flg: 0x04 tail: 0x3bb50603
frmt: 0x02 chkval: 0x0e55 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F628C33A000 to 0x00007F628C33C000
........... 생략 ............
-- commit;
select rowid
, dbms_rowid.rowid_relative_fno(rowid) as file_no
, dbms_rowid.rowid_block_number(rowid) as block_no
from dbcafe.TB_T1 where rownum = 1;
6.1 블럭 디버깅[편집]
-- trace file 구분자 입력
alter session set tracefile_identifier = 'DBCAFE';
-- sql 실행
SQL> @core_demo_02.sql
6.1.1 trace file 분석[편집]
---------------------------
-- session info
*** SESSION ID:(395.49059) 2024-06-24T13:04:35.468088+00:00
6.1.2 Block dump from cache[편집]
Start dump data blocks tsn: 0 file#:1 minblk 443673 maxblk 443673
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=0 rdba=4637977
BH (0xb1f596e8) file#: 1 rdba: 0x0046c519 (1/443673) class: 1 ba: 0xb1106000
set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 73789 objn: 73789 tsn: [1/0] afn: 1 hint: f
hash: [0x9ff8d278,0x752a2038] lru: [0xb9fbc740,0xaefe2990]
ckptq: [NULL] fileq: [NULL]
objq: [0x6ccce6b8,0x6ccce6b8] objaq: [0x6ccce6a8,0x6ccce6a8]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x28c8fa tch: 1
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]
Printing buffer operation history (latest change first):
cnt: 3
01. sid:11 L338:zibmlt:set:MSHR 02. sid:11 L144:zibmlt:mk:EXCL
03. sid:11 L122:zgb:set:st 04. sid:11 L830:olq1:clr:WRT+CKT
05. sid:11 L951:zgb:lnk:objq 06. sid:11 L372:zgb:set:MEXCL
07. sid:11 L123:zgb:no:FEN 08. sid:11 L083:zgb:ent:fn
09. sid:11 L154:z_sw_cur:bic:FPB 10. sid:11 L940:z_sw_cur:sw:cq
11. sid:11 L070:zswcu:ent:ob 12. sid:11 L471:bpostapl:bic:FMS
13. sid:11 L786:pre_apl:bis:FMS 14. sid:11 L353:gcur:set:MEXCL
15. sid:11 L471:bpostapl:bic:FMS 16. sid:11 L786:pre_apl:bis:FMS
buffer tsn: 0 rdba: 0x0046c519 (1/443673)
scn: 0x28c8f2 seq: 0x02 flg: 0x04 tail: 0xc8f20602
frmt: 0x02 chkval: 0x9c7f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000B1106000 to 0x00000000B1108000
..... 생략 ......
6.1.3 Block header dump[편집]
Block header dump: 0x0046c519
Object id on Block? Y
seg/obj: 0x1203d csc: 0x000000000028c8f1 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 0x000000000028c8f1
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0046c519
data_block_dump,data header at 0xb1106074
===============
tsiz: 0x1f88
hsiz: 0x8a
pbl: 0xb1106074
76543210
flag=--------
ntab=1
nrow=60
frre=-1
fsbo=0x8a
fseo=0x412
avsp=0x388
tosp=0x388
0xe:pti[0] nrow=60 offs=0
.... 생략 ....
block_row_dump:
tab 0, row 0, @0x1f13
tl: 117 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 02
col 1: [ 2] c1 02
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
tab 0, row 1, @0x1e9e
tl: 117 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 04
col 1: [ 2] c1 03
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
... 생략 ...
end_of_block_dump
Block dump from disk:
buffer tsn: 0 rdba: 0x0046c519 (1/443673)
scn: 0x28c8f2 seq: 0x02 flg: 0x04 tail: 0xc8f20602
frmt: 0x02 chkval: 0x9c7f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
... 생략 ...
Block header dump: 0x0046c519
Object id on Block? Y
seg/obj: 0x1203d csc: 0x000000000028c8f1 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 0x000000000028c8f1
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0046c519
data_block_dump,data header at 0x7ff0e0927074
===============
tsiz: 0x1f88
hsiz: 0x8a
pbl: 0x7ff0e0927074
76543210
flag=--------
ntab=1
nrow=60
frre=-1
fsbo=0x8a
fseo=0x412
avsp=0x388
tosp=0x388