행위

"Undo 세미나"의 두 판 사이의 차이

DB CAFE

(→‎UNDO BLK)
(→‎UNDO BLK)
410번째 줄: 410번째 줄:
 
Hex dump of block: st=0, typ_found=1
 
Hex dump of block: st=0, typ_found=1
 
Dump of memory from 0x00007FF0E0927000 to 0x00007FF0E0929000
 
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
  
 
</source>
 
</source>

2024년 6월 24일 (월) 21:57 판

thumb_up 추천메뉴 바로가기

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 블럭 디버깅[편집]

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
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=Undo_세미나&oldid=11997"