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

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

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초

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초

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	.

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[편집]

bash-4.2# du -h
16K	.

-- UNRECOVERABLE 옵션 사용 
SQL> @ctas_tb_big_nolog.sql

Table altered.

Elapsed: 00:00:00.03

Table created.

Elapsed: 00:00:19.21

bash-4.2# du -h
1.2G	.
==> nolooging 인데도 사이즈가 크게 줄지 않음......


--- 테이블 drop 시 
drop table tb_big2 purge; 

bash-4.2# du -h
1.3G	.

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 테이블스페이스 사이즈 확인[편집]

5 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
        , 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

-- 트레이스 파일 열기 vi MONGO_ora_43565_DBCAFE_Block_dump.trc

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

-- 버퍼캐시 덤프 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

0x0004.008.00000423

-- 16진수 -> 10진수로 변환 select to_number('1059', 'xxxx') from dual;

-- 10진수 -> 16진수로 변환 select to_char(1059, 'xxxx') from dual;

0x0016.026.00003222

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

    76543210

drop table dbcafe.TB_TEST purge;
select *
  from dba_tables 
  where 
owner='DBCAFE' ;





select rowid
        , dbms_rowid.rowid_relative_fno(rowid) as file_no
        , dbms_rowid.rowid_block_number(rowid) as block_no