이슈를 공유하자

Table 단위 DML 현황 조회

SELECT M.TABLE_OWNER,
   M.TABLE_NAME,T.NUM_ROWS,ROUND(NUM_ROWS/10,0) "NUM_ROWS/10", 
   INSERTS+UPDATES+DELETES "tot_changed",
   INSERTS, UPDATES, DELETES , 
   m.TIMESTAMP,
   LAST_ANALYZED,ROUND(sysdate - m.TIMESTAMP,0) GAP
FROM DBA_TAB_MODIFICATIONS M,DBA_TABLES T
WHERE TABLE_OWNER NOT IN ('SYS','SYSTEM')
AND T.TABLE_NAME = M.TABLE_NAME
AND T.OWNER = M.TABLE_OWNER
AND M.TABLE_OWNER = 'SCOTT'
AND TO_CHAR(TIMESTAMP,'YYYY') = '2023'
ORDER BY LAST_ANALYZED - TIMESTAMP

% number of rows DML since last analyze time (마지막 통계정보 생성 이 후 DML Count(10%변경분))

개별 Table로 조회 시

select o.object_name,m.* 
from SYS.MON_MODS_ALL$ m, dba_objects o
where m.obj# = o.object_id
and object_name = 'TB_TEST01'

shared_pool에 모았다가 3시간주기로 Dictionary Veiw에 반영, 즉시 반영하려면
exec dbms_stats.flush_database_monitoring_info 실행

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2668605_1.html

• In customer environment, SQLNET.ALLOWED_LOGON_VERSION was set to 8 in Oracle Home of DB server.

sqlnet.ora:
SQLNET.ALLOWED_LOGON_VERSION=8

• DB user password version was "10G 11G 12C".
• After changed password By Alter User Identified by <Password> command, the password version of target db user was changed to "11G 12C".
• This problem can be reproduce as following:

/<ORACLE_HOME>/network/admin/
sqlnet.ora:
SQLNET.ALLOWED_LOGON_VERSION=8

/<PATH1>/
sqlnet.ora:
SQLNET.ALLOWED_LOGON_VERSION=12

export TNS_ADMIN=<PATH1>

$ sqlplus <DBA USER>/<PASSWORD>

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 21 03:37:23 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Apr 21 2020 03:35:27 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select username,password_versions from dba_users where username='<USER_NAME>';

USERNAME   PASSWORD_VERSIONS
---------- -----------------
<USER_NAME>       10G 11G 12C

SQL> alter user <USER_NAME> identified by <PASSWORD>;

User altered.

SQL> select username,password_versions from dba_users where username='<USER_NAME>';

USERNAME   PASSWORD_VERSIONS
---------- -----------------
<USER_NAME>       11G 12C

SQL> quit

Which sqlnet.ora or password version is fetched can be found by strace as following:

strace -ftttT -o /tmp/strace1.log sqlplus <USER_NAME>/<PASSWORD>
The abstract of strace1.log:
7005  1587440243.885264 access("/<PATH>/network/admin/sqlnet.ora", F_OK) = 0 <0.000008>
7005  1587440243.885296 open("/<PATH>/network/admin/sqlnet.ora", O_RDONLY) = 8 <0.000007>
7005  1587440243.885320 fcntl(8, F_SETFD, FD_CLOEXEC) = 0 <0.000004>
7005  1587440243.885338 fstat(8, {st_mode=S_IFREG|0644, st_size=32, ...}) = 0 <0.000004>
7005  1587440243.885361 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fb08d2c9000 <0.000005>
7005  1587440243.885377 read(8, "SQLNET.ALLOWED_LOGON_VERSION=12\n", 4096) = 32 <0.000006> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

● REGEXP_LIKE 함수 사용

REGEXP_LIKE(srcstr,pattern,[math_option])
[[:alpha:]]
[[:digit:]] 숫자 인 것
[^[:digit:]] 숫자가 아닌 것
[[:punct:]] 특수문자
[^expression]
REGEXP_LIKE(product_nam,'SS[^P]') SS 다음에 P를 포함하지 않는
REGEXP_LIKE(product_nam,'SS[PS]') SS 다음에 P나 S를 포함하는
REGEXP_LIKE(text,'[a-z][0-9]') 영어 소문자전체와 0부터9까지의 숫자
REGEXP_LIKE(text,'[a-z] [0-9]') 영어 소문자전체와 공백 그리고 0부터9까지의 숫자
REGEXP_LIKE(text,'[a-z]?[0-9]') 영어 소문자전체와 여러 공백 그리고 0부터9까지의 숫자
REGEXP_LIKE(text,'[a-z]*[0-9]') 영어 소문자전체와 여러 공백 그리고 0부터9까지의 숫자
REGEXP_LIKE(text,'*[a-z]') 영어 소문자를 포함한 모든 문자
REGEXP_LIKE(text,'[A-Z]{3}') 대문자 영어가 연속으로 3자리 있는
REGEXP_LIKE(text,'[0-9]{3}') 숫자가 연속으로 3자리 있는
REGEXP_LIKE(text,'[A-Z][0-9]{3}') 영어대문자, 숫자 모두 3자리이상 있는
REGEXP_LIKE(text,'^[0-9]') 숫자로 시작하는
REGEXP_LIKE(text,'^[^0-9]') 숫자로 시작하지 않는
REGEXP_LIKE(text,'^[a-z]|^[0-9]') 영어 소문자 또는 숫자로 시작하는
REGEXP_LIKE(text,'[a-z]$') 영어 소문자 끝나는
REGEXP_LIKE(text,'A|1') 'A' 나 '1'을 포함하고 있는
NOT REGEXP_LIKE(text,'A|1') 'A' 나 '1'을 포함하지 않는
REGEXP_LIKE(text,'\?') '?' 가 포함되어 있는
REGEXP_LIKE(text,'A+') 'A'가 1개 이상인 Row
REGEXP_LIKE(text,'A?') 'A'가 0개 또는 1개이므로 'A'가 없거나 1개라도 있는(모든레코드)
REGEXP_LIKE(text,'A.') 'A' 뒤에 문자가 1개있는 레코드
REGEXP_LIKE(fnm,'\<')  특수문자 조회

● 문자교환 및 형식 변경

"\N" 백리퍼런스(Backreference) 서브표현식들 중에서 원하는 서브표현식을 지정할때 사용한다. \1~\9까지 사용가능하다.
REGEXP_REPLACE('ABC','(A)(B)(C)', '\3\2\1')
-> CBA

REGEXP_REPLACE('ABCDEFGHIJ','(A)(B)(C)(D)(E)(F)(G)(H)(I)', '\9\8\7')
-> IHGJ

● sqlplus 툴로 DB 접속 할 때 특수문자가 들어가 있는 패스워드로 접속이 되지 않을 때 

]$ sqlplus etawas/\"xxx@#\"

※ 역슬러시,더블쿼테이션(\")를 앞과 뒤에 삽입

• ASM 파일을 Local filesystem으로 복사하기 (archive log file backup)

for j in `cat dlist`
do
for i in $(asmcmd ls +RECO/TESTDB/ARCHIVELOG/$j)
do
asmcmd cp +RECO/TESTDB/ARCHIVELOG/$j/$i /oradump/arch_bak/$j
done
done

※ dlist 에는 복사할 파일 목록이 들어 있음.

• ASM 파일을 삭제하기 (archive log file backup)

for j in `cat dlist`
do
for i in $(asmcmd ls +RECO/TESTDB/ARCHIVELOG/$j)
do
asmcmd rm +RECO/TESTDB/ARCHIVELOG/$j/$i
done
done

# 시퀀스 네임을 받아 초기 값으로 설정하는 프로시저

CREATE OR REPLACE PROCEDURE RESET_SEQ(P_SEQ IN VARCHAR2)
IS 
L_VAL NUMBER;

BEGIN
    EXECUTE IMMEDIATE
    'SELECT '||P_SEQ||'.NEXTVAL FROM DUAL' INTO L_VAL;
    EXECUTE IMMEDIATE
    'ALTER SEQUENCE '||P_SEQ||' INCREMENT BY -'||L_VAL||' MINVALUE 0';
    
    EXECUTE IMMEDIATE
    'SELECT '||P_SEQ||'.NEXTVAL FROM DUAL' INTO L_VAL;
    EXECUTE IMMEDIATE
    'ALTER SEQUENCE '||P_SEQ||' INCREMENT BY 1 MINVALUE 0;
 END;
 /

※ 초기화 시 반드시 "MINVALUE 0" 로 설정해야 함.

- Decode, Max를 사용하여 결과를 가로로 표시되게 함.

SELECT
  part,
  MAX(decode(substr(d_seq,length(d_seq)-1,2),'01',c_cd)) A1,
  MAX(decode(substr(d_seq,length(d_seq)-1,2),'02',c_cd)) A2,
  MAX(decode(substr(d_seq,length(d_seq)-1,2),'03',c_cd)) A3,
  MAX(decode(substr(d_seq,length(d_seq)-1,2),'04',c_cd)) A4,
  MAX(decode(substr(d_seq,length(d_seq)-1,2),'05',c_cd)) A5,
  MAX(decode(substr(d_seq,length(d_seq)-1,2),'06',c_cd)) A6
FROM (
	SELECT 
        'SUAA'||(level+20000000) D_SEQ,
        'SUCH'||level P_CD,
        'SBAA'||level C_CD,
         to_char(sysdate,'YYYY/MM/DD') DT,
        '2' INST,
        'SU' PART,
        '100001' SAL,
        '3' INST1,
        '' ETC,
        '100001' ETC1,
         sysdate T_DT,
        '100001' ETC_CD,
         sysdate U_DT
	FROM dual
	CONNECT BY  level <=6
)
GROUP BY part

- 정상적이지 않은 DML 실행시 다음 쿼리 조회후 확인

SELECT B.NAME USEG,
       B.INST# INSTID, 
       B.STATUS$ STATUS, 
       A.KTUXEUSN XID_USN,
       A.KTUXESLT XID_SLOT,
       A.KTUXESQN XID_SEQ,
       A.KTUXESIZ UNDOBLOCKS,
       A.KTUXESTA TXSTATUS
  FROM X$KTUXE A,UNDO$ B
 WHERE A.KTUXECFL LIKE '%DEAD%'
   AND A.KTUXEUSN = B.US#;

alter system dump undo header '_SYSSMU289_1564695500$';

Transaction recovery: lock conflict caught and ignored

-- Alert Log에 기록 남기기

SYS.DBMS_SYSTEM.KSDWRT(2,l_message);

1-write to trace file (Trace 파일에만 Message 기록)

2-write to alert log (Alert Log 파일에만 Message 기록)

3-write to both (Trace / Alert Log 파일 모두에 Message 기록)

-- 활용예(로그인 실패 로그)

create or replace trigger logon_fail_write_alertlog

AFTER SERVERERROR on database

declare

l_message varchar2(2000); -- 메시지 내용 저장

BEGIN

-- ORA-1017 : invalid username/password; logon denied

IF(IS_SERVERERROR(1017)) THEN

SELECT 'ORA-01017 Failed login attempt to the "'|| sys_context('USERENV','AUTHENTICATED_IDENTITY') ||'" schema'||' using ' ||

sys_context('USERENV','AUTHENTICATION_TYPE') ||' Authentication'||' at '||to_char(logon_time,'YYYY/MM/DD HH24:MI:SS') ||

' from ' ||osuser||'@'||machine||' ['||nvl(sys_context('USERENV','IP_ADDRESS'),'Unknown IP')||']' ||' via the "' ||program||'" program.'

INTO l_message

FROM SYS.V_$SESSION

WHERE SID = TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,1,4),'XXXX') AND SERIAL# = TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,5,4),'XXXX');

--write to alert log

SYS.DBMS_SYSTEM.KSDWRT(2,l_message);

-- ORA-28000 : the account is locked

elsif (IS_SERVERERROR(28000)) THEN

select 'ORA-28000 the account is locked "'|| sys_context('USERENV','AUTHENTICATED_IDENTITY') ||'" Schema '||' using ' ||

sys_context('USERENV','AUTHENTICATION_TYPE') ||' Authentication'||' at '||to_char(logon_time,'YYYY/MM/DD HH24:MI:SS') ||

' from ' ||osuser||'@'||machine||' ['||nvl(sys_context('USERENV','IP_ADDRESS'),'Unknown IP')||']' ||' via the "' ||program||'" program.'

INTO l_message

FROM SYS.V_$SESSION

WHERE SID = TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,1,4),'XXXX') AND SERIAL# = TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,5,4),'XXXX');

--write to alert log

SYS.DBMS_SYSTEM.KSDWRT(2,l_message);

END IF;

END;

/