다른 명령
언두 테스트
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
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초
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초
archive log mode 인경우 테스트
16:28:03 SQL> @update_tb_big.sql 8960000 rows updated. Commit complete. 16:35:46 SQL> ====== 종료 ====== 총 7분 43초 , 463초
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 .
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 ./
CTAS NOLOGGING MODE
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
ctas nolooging 처리후
bash-4.2# du -h 1.2G . ==> nolooging 인데도 사이즈가 크게 줄지 않음...... --- 테이블 drop 시 drop table tb_big2 purge; bash-4.2# du -h 1.3G .
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#
undo 테이블스페이스 사이즈 확인
데이터 UPDATE 처리 과정
테이블 UPDATE 시나리오 SQL
- 진행 순서
- create table t1 (테이블 생성 후 2개 블럭에 데이터 입력.)
- 인덱스 생성
- 통계 정보 생성
- 블럭 번호 조회
- 로그 스위치
- 변경전 블록 덤프조회
- 테이블 업데이트 id가 5~9 인건
- 데이터 변경 후 블럭 덤프
- 언두 블럭 덤프
- 롤백 / 커밋 수행
- 리두 로그 덤프 수행
-- 1.create table t1 (2개 블럭에 생성 함.) 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 ; -- 1.1 인덱스 생성 create index t1_i1 on t1(id); -- 1.2 통계 정보 생성 begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T1', method_opt => 'for all columns size 1' ); end; / -- 2. 블럭 번호 조회 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 ; BLOCK_NUMBER ROWS_PER_BLOCK ------------ -------------- 443673 60 443674 60 -- 3. 로그 스위치 alter system switch logfile; execute dbms_lock.sleep(2) -- 4. 블럭 덤프 실행 (변경전 블록 덤프조회) execute dump_seg('t1') Dumped 1 blocks from TABLE t1 starting from block 1 Trace file name includes: 10609 -- 5. 테이블 업데이트 id가 5~9 인건 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) -- 5.1 데이터 변경 후 블럭 덤프 execute dump_seg('t1') -- 5.2 언두 블럭 덤프 execute dump_undo_block -- 5.3 롤백 / 커밋 수행 rollback; commit; -- 6.리두 로그 덤프 수행 execute dump_log
블럭 디버깅
-- trace file 구분자 입력 alter session set tracefile_identifier = 'DBCAFE'; -- sql 실행 SQL> @core_demo_02.sql
trace file 분석
--------------------------- -- session info *** SESSION ID:(277.62324) 2024-06-26T09:13:49.151026+00:00
컬럼 rowid 로 데이터 파일번호,데이터 블럭 위치 찾기
- 예제에서 id컬럼 5~9까지 값을 변경 함.
select rowid -- extended_format , id , dbms_rowid.rowid_to_restricted(rowid, 0) r_f , dbms_rowid.rowid_object(rowid) object , dbms_rowid.rowid_relative_fno(rowid) file_no , dbms_rowid.rowid_block_number(rowid) block_no , dbms_rowid.rowid_row_number(rowid) row_nillruJer from sys.t1 WHERE id BETWEEN 5 AND 9 ;
Block dump from cache
Start dump data blocks tsn: 0 file#:1 minblk 117153 maxblk 117153 Block dump from cache: Dump of buffer cache at level 3 for pdb=1 tsn=0 rdba=4311457 BH (0xb1f51150) file#: 1 rdba: 0x0041c9a1 (1/117153) class: 1 ba: 0xb1048000 set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0 dbwrid: 0 obj: 86126 objn: 86126 tsn: [1/0] afn: 1 hint: f hash: [0xacff8348,0x96f61f90] lru: [0xb8ff2db8,0xaafa3ab0] ckptq: [NULL] fileq: [NULL] objq: [0x7d16ecb8,0x7d16ecb8] objaq: [0x7d16eca8,0x7d16eca8] st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x8577c9 tch: 1 flags: only_sequential_access LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535] Printing buffer operation history (latest change first): cnt: 12 01. sid:05 L338:zibmlt:set:MSHR 02. sid:05 L144:zibmlt:mk:EXCL 03. sid:05 L122:zgb:set:st 04. sid:05 L830:olq1:clr:WRT+CKT 05. sid:05 L951:zgb:lnk:objq 06. sid:05 L372:zgb:set:MEXCL 07. sid:05 L123:zgb:no:FEN 08. sid:05 L083:zgb:ent:fn 09. sid:01 L940:z_sw_cur:sw:cq 10. sid:01 L070:zswcu:ent:ob 11. sid:01 L082:zcr:ret:TRU 12. sid:00 L192:kcbbic2:bic:FBD 13. sid:00 L191:kcbbic2:bic:FBW 14. sid:00 L602:bic1_int:bis:FWC 15. sid:00 L822:bic1_int:ent:rtn 16. sid:00 L832:oswmqbg1:clr:WRT buffer tsn: 0 rdba: 0x0041c9a1 (1/117153) scn: 0x8577c1 seq: 0x02 flg: 0x04 tail: 0x77c10602 frmt: 0x02 chkval: 0xe03e type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00000000B1048000 to 0x00000000B104A000 ..... 생략 ......
Block header dump
1.UPDATE 전 t1 테이블 블럭 덤프
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
- 78 78 78 78 78 78 은 'xxxxxx'
SELECT HEXTORAW(78) FROM dual; -- 소문자 x
2.UPDATE 후 t1 테이블 블럭 덤프
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
- 59 59 59 59 59 59 59 59 59 59
SELECT HEXTORAW(59) FROM dual;-- 대문자 Y
3.변경 사항
undo 블럭 덤프
UNDO BLK
******************************************************************************** UNDO BLK: xid: 0x0009.015.000013cb seq: 0x6e9 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x0014 *----------------------------- * Rec #0x1 slt: 0x15 objn: 11582(0x00002d3e) objd: 11582 tblspc: 1(0x00000001) * Layer: 10 (Index) opc: 21 rci 0x00 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x0100104b *----------------------------- index general undo (branch) operations KTB Redo op: 0x05 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: R itc: 2 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.015.000013cb 0x0100104b.06e9.08 ---- 1 fsc 0x0000.00000000 0x02 0x0009.014.000013c9 0x0100104c.06e9.14 ---- 6 fsc 0x0000.00000000 Dump kdige : block dba :0x00c130e0, seghdr dba: 0x00c02cba
온라인 리두 파일 덤프
redo 블럭 체인지 벡터
REDO RECORD - Thread:1 RBA: 0x000086.0000000b.0010 LEN: 0x05d8 VLD: 0x0d CON_UID: 1 SCN: 0x0000000000857805 SUBSCN: 1 06/26/2024 09:14:14 (LWN RBA: 0x000086.0000000b.0010 LEN: 0x00000006 NST: 0x0001 SCN: 0x0000000000857805) CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0041c9a1 OBJ:86126 SCN:0x00000000008577c1 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: 0x0009.007.000013de uba: 0x0100104d.06ee.3a KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x0041c9a1 hdba: 0x0041c9a0 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
undo 블럭 체인지 벡터
CHANGE #10 CON_ID:1 TYP:0 CLS:34 AFN:4 DBA:0x0100104d OBJ:4294967295 SCN:0x0000000000857805 SEQ:3 OP:5.1 ENC:0 RBL:0 FLG:0x0000 ktudb redo: siz: 96 spc: 1086 flg: 0x0022 seq: 0x06ee rec: 0x3d xid: 0x0009.007.000013de ktubu redo: slt: 7 wrp: 5086 flg: 0x0000 prev dba: 0x00000000 rci: 60 opc: 11.1 [objn: 86126 objd: 86126 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: 0x0100104d.06ee.3b KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x0041c9a2 hdba: 0x0041c9a0 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
redo 테이블스페이스 사이즈 확인
테이블스페이스 사이즈 확인
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;
구분 | 변경 전 | 변경 후 | 비 고 |
---|---|---|---|
dba(데이터 블럭 주소) | @0x1d3f | @0x2a7 | xxxxxx => YYYYYYYYYY 로우 길이가 길어져 주소이동 |
??????? | tl:117 | tl:121 | |
Lock Byte | lb: 0x0 | lb: 0x2 | 해당 로우는 ITL 2번째 슬롯을 사용하는 트랜잭션에 의해 락이 설정됨을 의미. |