오라클 주민번호 체크

select '주민번호',
  translate('주민번호', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') ,
  translate('주민번호', '0123456789'||'주민번호', '0123456789'),
 (case when
        MOD(11 -
            MOD(
                substr('주민번호',1,1) * 2 +
                substr('주민번호',2,1) * 3 +
                substr('주민번호',3,1) * 4 +
                substr('주민번호',4,1) * 5 +
                substr('주민번호',5,1) * 6 +
                substr('주민번호',6,1) * 7 +
                substr('주민번호',7,1) * 8 +
                substr('주민번호',8,1) * 9 +
                substr('주민번호',9,1) * 2 +
                substr('주민번호',10,1) * 3 +
                substr('주민번호',11,1) * 4 +
                substr('주민번호',12,1) * 5
            ,11)
          , 10)  = substr('주민번호',13,1)  
       then 'T'
       else 'F'
       end)
from duall

oracle 날짜 차이 계산

SELECT EMP_NO,
       FST_ENT_DT,
       MONTHS_BETWEEN(SYSDATE , TO_DATE(FST_ENT_DT,'yyyymmdd')) "개월차" ,
       TRUNC(MONTHS_BETWEEN(SYSDATE , TO_DATE(FST_ENT_DT,'yyyymmdd')) /12,0 ) "년" ,
       TRUNC(MOD(MONTHS_BETWEEN(SYSDATE , TO_DATE(FST_ENT_DT,'yyyymmdd')) /12,1 ) *12,0)  "월",
      ROUND(MOD(MOD(MONTHS_BETWEEN(SYSDATE , TO_DATE(FST_ENT_DT,'yyyymmdd')) /12,1 ) *12,1)*(365/12),0)  "일"
  FROM TBH_EMP
 WHERE EMP_NO = 'E20050107'

oracle 다중 Row를 한 컬럼으로 처리

 SELECT TASK_SEQ,
           SUBSTR(MAX(SYS_CONNECT_BY_PATH(COMP_NM,',')),2)
    FROM (
             SELECT TASK_SEQ, COMP_NM ,
                    ROW_NUMBER() OVER(PARTITION BY TASK_SEQ ORDER BY TASK_SEQ) AS RNUM
             FROM W_MINI_TASK_COMP
             WHERE  task_seq = 8235
         )
    START WITH RNUM =1
    CONNECT BY PRIOR RNUM = RNUM -1 AND PRIOR TASK_SEQ = TASK_SEQ
    GROUP BY TASK_SEQ
 
   - W_MINI_TASK_COMP 이 PK 는 TASK_SEQ , TASK_COMP_SEQ 이고 ',' 로 묶고자 하는 회사명의 GROUP KEY 는 TASK_SEQ

모든 테이블의 rowcount 조회

SELECT table_name,
       num_rows
  FROM all_tables
 WHERE owner = 'xxx' ;

오라클 컬럼 찾기

--해당컬럼 사용하는 곳 찾기

1. PROC, FUNC, TRIGGER
 1) 해당 user에 대하여
SELECT *
FROM USER_SOURCE
WHERE TEXT LIKE '%TECHR_CD%'

 2) 모든 uesr에 대하여
SELECT *
FROM DBA_SOURCE
WHERE TEXT LIKE '%TECHR_CD%'

2. view
 1) 해당 user에 대하여
SELECT *
FROM USER_VIEWS
WHERE dbms_xmlgen.getxml('select text from user_views where view_name = ''' || view_name || '''') like '%TECHR_CD%';

 2) 모든 uesr에 대하여
SELECT *
FROM DBA_VIEWS
WHERE dbms_xmlgen.getxml('select text from DBA_views where view_name = ''' || view_name || '''') like '%TECHR_CD%';

오라클 object 내 특정문자열 찾기

SELECT *
  FROM ALL_SOURCE
 WHERE OWNER = '계정'
   --AND TYPE = 'PROCEDURE' --object
   AND TEXT LIKE '%com_member%';  --문자열

Lock 확인 및 해제

1. 확인 방법
SELECT DISTINCT t1.session_id AS session_id
   ,t2.serial# AS serial_no
   ,t1.os_user_name AS os_user_name
   ,t1.oracle_username AS oracle_username
   ,t2.status AS status
   ,t3.object_name
   ,DECODE( locked_mode
           ,2, 'ROW SHARE'
           ,3, 'ROW EXCLUSIVE'
           ,4, 'SHARE'
           ,5, 'SHARE ROW EXCLUSIVE'
           ,6, 'EXCLUSIVE'
           ,'UNKNOWN'
          ) lock_mode
FROM v$locked_object t1, v$session t2, dba_objects t3
WHERE t1.session_id = t2.SID
AND t1.object_id = t3.object_id;

2. 확인 방법

select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'
;

3. kill 하기

ALTER SYSTEM KILL SESSION s.sid,s.serial#;
ALTER SYSTEM KILL SESSION '121,21';