SELECT A.GAME

        ,SUM (DECODE (RNUM, 1, LTNO, 0)) NUM1

        ,SUM (DECODE (RNUM, 2, LTNO, 0)) NUM2

        ,SUM (DECODE (RNUM, 3, LTNO, 0)) NUM3

        ,SUM (DECODE (RNUM, 4, LTNO, 0)) NUM4

        ,SUM (DECODE (RNUM, 5, LTNO, 0)) NUM5

        ,SUM (DECODE (RNUM, 6, LTNO, 0)) NUM6

    FROM (SELECT GAME

                ,LTNO

                ,RANK () OVER (PARTITION BY GAME ORDER BY DBMS_RANDOM.VALUE)

                    RNUM

            FROM (    SELECT CEIL (LEVEL / 45) GAME, MOD (LEVEL - 1, 45) + 1 LTNO

                        FROM DUAL

                  CONNECT BY LEVEL <= 9999

                    ORDER BY 1, DBMS_RANDOM.VALUE)) A

        ,(    SELECT LEVEL GAME

                FROM DUAL

          CONNECT BY LEVEL < 6) B 

   WHERE A.GAME = B.GAME

GROUP BY A.GAME

ORDER BY A.GAME

프로젝트에서 LOCK 이 자주 걸리는 문제가 발생하여 연구하던 중에 아래의 sql 문을 이용하여 lock 세션을 찾아서 관리할 수 있다는 것을 알았다. 그러나 이 SQL 은 System 유저만이 사용할 수 있다는  제약이 있다. 

LOCK 이 발생하게 되면 프로그램이 동작하다가 멈추고 기다리게 된다. 원인을 파악하는데 시간이 허비하게 되는데 알고 보니 테이블에 LOCK이 걸린 것이었다면 허탈함을 금할 수 없다.  자주 LOCK 이 걸리는 테이블이라면 SELECT 할 때 WAIT 타임을 1 정도로 주면 1초동안 LOCK 이 풀리기를 대기하다가 오류 처리되므로 개발자가 쉽게 LOCK 걸린 상황을 인지할 수 있다. 

-- 락걸린 테이블 확인 
SELECT  DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE, DO.OWNER,
        VO.XIDUSN, VO.SESSION_ID, VO.LOCKED_MODE
FROM    V$LOCKED_OBJECT VO, DBA_OBJECTS DO
WHERE   VO.OBJECT_ID = DO.OBJECT_ID;

-- 해당 테이블에 LOCK 이 걸렸는지.
SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
WHERE A.SID = B.SID AND B.ID1 = C.OBJECT_ID
AND B.TYPE='TM' AND C.OBJECT_NAME IN ('TB_CO_GENO');

-- 락발생 사용자와 SQL, OBJECT 조회
SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL,
                A.PROGRAM, B.ADDRESS, B.PIECE, B.SQL_TEXT
FROM V$LOCKED_OBJECT X, V$SESSION A, V$SQLTEXT B, DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID AND 
X.OBJECT_ID = D.OBJECT_ID AND A.SQL_ADDRESS = B.ADDRESS 
ORDER BY B.ADDRESS, B.PIECE;

-- 현재 접속자의 SQL 분석
SELECT DISTINCT A.SID, A.SERIAL#,
       A.MACHINE, A.TERMINAL, A.PROGRAM,
       B.ADDRESS, B.PIECE, B.SQL_TEXT
FROM  V$SESSION A, V$SQLTEXT B
WHERE A.SQL_ADDRESS = B.ADDRESS
ORDER BY A.SID, A.SERIAL#, B.ADDRESS, B.PIECE

-- 락 세션 죽이기
  SELECT A.SID,   A.SERIAL#
  FROM V$SESSION A,  V$LOCK B,
       DBA_OBJECTS C
 WHERE A.SID = B.SID
   AND B.ID1 = C.OBJECT_ID
   AND B.TYPE = 'TM'
   AND C.OBJECT_NAME = 'TB_CO_GENO'
   
SID SERIAL#
--- -------
5   1
6   1

2. 다음 명령으로 SESSION들을 KILL한다.  ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';
SQL> alter system kill session '5, 1';
SQL> alter system kill session '6, 1';


-- 락 세션 죽이는 sql 문 
SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL,
       A.PROGRAM, A.LOGON_TIME, 'ALTER SYSTEM KILL SESSION'''||A.SID||', '||A.SERIAL#||''';'
FROM GV$LOCKED_OBJECT X, GV$SESSION A, DBA_OBJECTS D
WHERE  X.SESSION_ID = A.SID AND X.OBJECT_ID = D.OBJECT_ID
ORDER BY LOGON_TIME;

/* 테이블 컬럼 찾기 */
/* [sqlmap/mapper/com/cmm/sql-comcmm525.xml] selectTableList */SELECT   A.TABLE_NAME        ,B.COMMENTSFROM     ALL_TABLES A        ,ALL_TAB_COMMENTS BWHERE    A.TABLE_NAME = B.TABLE_NAMEAND      A.OWNER = B.OWNERAND      B.OWNER = 'USRFRMOWN'AND      ( A.TABLE_NAME NOT LIKE 'TEST%' AND A.TABLE_NAME NOT LIKE '%TEST' )AND      ( A.TABLE_NAME NOT LIKE 'TEMP%' AND A.TABLE_NAME NOT LIKE '%TEMP' )AND      A.TABLE_NAME LIKE '%%'AND      B.COMMENTS LIKE '%%'ORDER BY TABLE_NAME
/* [sqlmap/mapper/com/cmm/sql-comcmm525.xml] selectColumnList */SELECT   A.TABLE_NAME        ,(SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = A.TABLE_NAME AND OWNER='USRFRMOWN') AS TABLE_COMMENTS        ,A.COLUMN_NAME        ,B.COMMENTS        ,CASE WHEN C.COLUMN_NAME IS NOT NULL THEN 'PK' END AS PK_YN        ,A.DATA_TYPE        ,A.DATA_LENGTH        ,A.DATA_PRECISION        ,A.DATA_SCALE        ,A.NULLABLE        ,A.COLUMN_IDFROM     ALL_TAB_COLUMNS A        ,ALL_COL_COMMENTS B        ,(    SELECT B.TABLE_NAME                    ,B.COLUMN_NAME              FROM   ALL_CONSTRAINTS A                    ,ALL_CONS_COLUMNS B              WHERE  A.OWNER = B.OWNER              AND    A.TABLE_NAME = B.TABLE_NAME              AND    A.CONSTRAINT_NAME = B.CONSTRAINT_NAME              AND    A.CONSTRAINT_TYPE ='P'              AND    A.OWNER = 'USRFRMOWN' ) CWHERE    A.OWNER = 'USRFRMOWN'AND      A.OWNER = B.OWNERAND      A.TABLE_NAME = B.TABLE_NAME(+)AND      A.COLUMN_NAME = B.COLUMN_NAME(+)AND      A.TABLE_NAME = C.TABLE_NAME(+)AND      A.COLUMN_NAME = C.COLUMN_NAME(+)AND      A.TABLE_NAME LIKE '%%'—AND      A.COLUMN_NAME LIKE '%LINK_RPRJ_NO%'—AND      A.COLUMN_NAME IN ('RSCHFND_APLY_NO','PROOF_SEQ')AND      B.COMMENTS LIKE '%매입%번호%'ORDER BY A.TABLE_NAME

1. 증상
오라클 특정 유저 패스워드 변경 후 처음에는 로그인이 잘 되었는데 어느순간 부터 ORA-28000 : the account is locked 오류 발생

2. 원인
특정 유저 패스워드 변경 후 수시로 DB에 접속하는 프로그램에서 변경된 패스워드를 반영하지 않았음. 프로그램에서 계속 잘못된 패스워드로 접속을 시도하였고 오라클 설정에 따라 자동으로 특정 유저가 LOCK됨.

3. 해결방법

1) USER 패스워드 만료 상태 확인하기

2) LOCK걸린 유저 UNLOCK하기

3. 결과
우리가 은행에서 현금 인출할 때 비밀번호를 3번이상 잘못 입력하면 은행에 가서 풀어야 하는 것처럼 오라클도 이러한 기능을 제공을 하는 것 같다.
보안상 좋은 기능이지만 어찌보면 굉장히 위험한 기능인 것 같다. 외부에서 누군가가 악한 마음을 갖고 잘못된 패스워드로
계속 접근을 시도해서 유저를 LOCK시켜 버린다면... 에효... 물론 이에 대한 해결방안이 있겠지만... 나중에 찾아봐야겠다.

4. 추가로 알아보기
그럼 과연 몇번 패스워드 입력을 실패하면 LOCK이 걸릴까? 한번 알아보았음.

1) 패스워드 LOCK횟수 확인하기

select /* 오늘날짜 시분초 포함*/ 

              to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') 

    from dual

select /* 오늘날짜 00시 00분 00초 */ 

              to_char(trunc(sysdate),'yyyy/mm/dd hh24:mi:ss') 

    from dual

select /* 오늘날짜 00시 00분 00초 위와 동일*/ 

              to_char(trunc(sysdate,'dd'),'yyyy/mm/dd hh24:mi:ss') 

    from dual

select /* 이번달 1일 00시 00분 00초 */ 

              to_char(trunc(sysdate,'mon'),'yyyy/mm/dd hh24:mi:ss') 

    from dual

select /* 올해 1월 1일 00시 00분 00초 */ 

              to_char(trunc(sysdate,'year'),'yyyy/mm/dd hh24:mi:ss') 

    from dual

select /* 올해 1월 1일 00시 00분 00초 */ 

              to_char(to_date('2002','yyyy'),'yyyy/mm/dd hh24:mi:ss') 

    from dual

select /* 2월 1일 00시 00분 00초 */ 

              to_char(to_date('200202','yyyymm'),'yyyy/mm/dd hh24:mi:ss') 

    from dual

select /* 2월 2일 00시 00분 00초 */ 

              to_char(to_date('20020202','yyyymmdd'),'yyyy/mm/dd hh24:mi:ss') 

    from dual

select /* 2월 2일 00시 00분 01초 */ 

              to_char(to_date('20020202','yyyymmdd')+1/68400,'yyyy/mm/dd hh24:mi:ss') 

    from dual

select /* 2월 2일 00시 00분 00초 -> 한달뒤*/ 

              to_char(add_months(to_date('20020202','yyyymmdd'),1),'yyyy/mm/dd hh24:mi:ss')

 from dual

from en-core

laalaal~ 

날짜 빼기

밑에 날짜 빼기가 있던데 요건 약간 다르게..

(1) 현재 날자에서 하루를 빼고 싶다고 하면 

            select sysdate() - 1 from dual

(2) 1시간을 빼고 싶으면 

            select sysdate() - 1/24 from dual

(3) 1분을 빼고 싶으면 

            select sysdate() - 1/24/60

(q) 1초를 빼고 싶은면 어떻게 할까요? ^^

======================================================================================

- 날짜형 함수

    SYSDATE : 현재 시스템의 날짜 및 시간을 구함

    LAST_DAY : 지정한 날짜의 해당 월의 마지막 날짜를 구함

    MONTHS_BETWEEN : 두 날짜 사이의 개월 수를 구함

    ADD_MONTHS : 지정한 날짜로부터 몇 개월 후의 날짜를 구함

    ROUND : 날짜에 대한 반올림

    TRUNC : 날짜에 대한 버림

    SYSDATE : SYSDATE → 10-MAY-99

    LAST_DAY(날짜값) : LAST_DAY('17-FEB-98') → 28-FEB-98

   MONTHS_BETWEEN(날짜값1, 날짜값2) : MONTHS_BETWEEN('26-APR-97','22-JUL-95') → 21.1290323

   ADD_MONTHS(날짜값, 숫자값) : ADD_MONTHS('22-JUL-95',21) → 22-APR-97

      ROUND(날짜값, 자리수) : 현재 날짜가 1999년 5월 10일이라고 가정하자.

                              ROUND(SYSDATE,'MONTH') → 01-MAY-99

      TRUNC(날짜값, 자리수) : 현재 날짜가 1999년 5월 10일이라고 가정하자.

                              TRUNC(SYSDATE,'YEAR') → 01-JAN-99

  - 날짜에 대한 산술연산

    날짜 + 숫자 : 날짜 특정한 날로부터 몇일 후의 날짜 계산

    날짜 - 숫자 : 날짜 특정한 날로부터 몇일 전의 날짜 계산

    날짜 - 날짜 : 숫자 두 날짜 사이의 차이를 숫자로 계산

- 변환형 함수

    TO_CHAR : 숫자나 날짜를 문자열로 변환

    TO_NUMBER : 문자를 숫자로 변환

    TO_DATE : 문자를 날짜로 변환

      - TO_CHAR에서 숫자를 문자로 변환시에 형식에 사용되는 요소

          9 : 일반적인 숫자를 나타냄

          0 : 앞의 빈자리를 0으로 채움

          $ : dollar를 표시함

          L : 지역 통화 단위(ex \)

          . : 소숫점을 표시함

          , : 천단위를 표시함

      - TO_CHAR에서 날짜를 문자로 변환시에 형식에 사용되는 요소

          SCC : 세기를 표시 S는 기원전(BC) 

          YEAR : 연도를 알파벳으로 spelling

          YYYY : 4자리 연도로 표시

          YY : 끝의 2자리 연도로 표시

          MONTH : 월을 알파벳으로 spelling

          MON : 월의 알파벳 약어

          MM : 월을 2자리 숫자로 표시

          DAY : 일에 해당하는 요일

          DY :  일에 해당하는 요일의 약어

          DDD,DD,D : 연도,월,일 중의 날짜를 숫자로 표시

          HH , HH24 : (1-12) , (0-23)중의 시간을 표시

          MI : 분을 표시

          SS : 초를 표시

          AM(A.M.),PM(P.M.) : 오전인지 오후인지를 표시

      TO_CHAR(문자값,‘형식’)

        숫자를 문자로 변환 : TO_CHAR(350000,'$999,999')→ $350,000

        숫자를 날짜로 변환 : TO_CHAR(SYSDATE,'YY/MM/DD')→ 95/05/25

      TO_DATE(문자값, ‘형식’) : TO_DATE('10 SEPTEMBER 1992','DD MONTH YYYY')→10-SEP-92

      TO_NUMBER(문자값) : TO_NUMBER('1234')→ 1234

오라클에서 외부 접속을 허용하도록 하는 구성요소를 LISTENER라고 부릅니다. 오라클을 최초로 설치하였을 때, 기본적으로 이 LISTENER는 외부 접속을 허용하지 않도록 설정되어 있기 때문에 외부에서 Toad나 SQL Developer등과 같은 프로그램을 사용하여 접속하기 위해서는 LISTENER의 설정을 변경해 주어야 합니다.

이 포스팅에서는 Windows 2008 Server에 설치된 Oracle 11g를 기준으로 하여 설명합니다.

LISTENER 서비스 중지하기

우선 현재 동작중인 LISTENER를 중지시켜야 합니다. 이를 위해서 명령프롬프트에 다음과 같은 명령어를 수행합니다.

listener.ora 파일의 수정

이제 설정 정보를 담고 있는 listener.ora 파일을 수정해야 합니다. 우선 오라클이 설치된 소프트웨어 위치 안의 NETWORK폴더 안으로 이동해야 합니다. 이 폴더 안에는 다시 ADMIN이라는 폴더가 있고, 이 폴더 안에 우리가 수정해야 하는 listener.ora 파일이 존재합니다.

윈도우용 오라클을 기본 경로에 설치한 경우 경로는 다음과 같이 설정됩니다.

제 경우에는 다음의 경로 였습니다.

이 경로 안의 listener.ora 파일을 메모장이나 그 외의 다른 텍스트 편집기로 열어 줍니다.

파일을 연 후에 아래 화면 갈무리에서 강조한 부분을 수정해 주어야 합니다. 우선 데이터베이스의 SID값을 확인하여 수정하여 주고, 오라클이 설치된 windows 2008 server에서 사용하고 있는 컴퓨터의 이름을 HOST 속성에 지정하여 줍니다. 이전 버전까지는 HOST 속성에 IP주소를 지정하여 주어도 되었지만, 11g 부터는 제가 테스트해 본 결과 IP주소의 지정은 실패했습니다.반드시 컴퓨터의 이름을 지정하여 주세요.

SID 값은 오라클을 설치할 때 지정한 "전역 데이터베이스 이름"입니다. 아래의 화면 갈무리에서 입력하는 항목들 중, 끝에서 세 번째 항목에 지정하여 준 값 입니다.

만약 SID 값을 기억하지 못한다면 다음의 과정으로 확인할 수 있습니다.

LISTENER 서비스 재시작

중지했던 Listener 서비스를 재 시작해야 합니다. 서비스를 재 시작하기 위해서는 다음의 명령어를 명령 프롬프트에 입력합니다.

프로세스가 정상적으로 실행된 것을 볼 수 있습니다.

select level as YYYY

  from dual

 where level > TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-5

 connect by level <= to_number(to_char(sysdate, 'YYYY'))

 ORDER BY LEVEL DESC

결과 

2015

2014

2013

2012

2011

1. Export 할 DB에 접속한다.

2. Export Utility Wizard 실행

3. Table Export 선택

4. 모든 테이블을 선택한다.

5.기본 선택 사항

6. 다운 폴더.파일명 설정한다.

7. 설정 완료.

8. Export 진행..

with tb as 
( 
select '1111' docno, 1 seqno, 1 prot, 'AAAA' empno from dual union all 
select '1111' , 1 , 2 , 'BBBB' from dual union all 
select '5555' , 3 , 1 , 'KKKK' from dual union all 
select '5555' , 3 , 2 , 'MMMM' from dual union all 
select '5555' , 3 , 3 , 'PPPP' from dual 
) 
select a.docno 
, a.seqno 
, substr(max(sys_connect_by_path(empno,',')),2) as empno 
from ( 
select docno 
, seqno 
, empno 
, row_number() over(partition by docno 
, seqno 
order by rownum) as rn 
from tb) a 
start with rn = 1 
connect by prior rn = rn-1 
and prior docno = docno 
and prior seqno = seqno 
group by docno 
, seqno 
order by docno 
;

/

### Lock 확인 쿼리 

SELECT do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.session_id, 

vo.locked_mode 

FROM v$locked_object vo , dba_objects do 

WHERE vo.object_id = do.object_id ; 

####  어떤 object에 어떤 lock이 걸렸는지 확인 

SELECT  T1.object_name, DECODE(locked_mode, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE',  4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') lock_mode 

FROM  dba_objects T1, v$locked_object T2 

WHERE T1.object_id = T2.object_id; 

#### session 확인 

select * from v$session where status = 'ACTIVE' 

#### cursor 확인 

v$open_cursor 

#### 테이블의 lock 확인 

SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME 

FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C 

WHERE A.SID=B.SID 

AND B.ID1=C.OBJECT_ID 

AND B.TYPE='TM' 

AND C.OBJECT_NAME IN ('<테이블이름>'); 

/******************************************************************************* 

* LOCK 관련 

*******************************************************************************/ 

--V$LOCK 을 사용한 잠금 경합 모니터링 

SELECT s.username, s.sid, s.serial#, s.logon_time, 

  DECODE(l.type, 'TM', 'TABLE LOCK', 

        'TX', 'ROW LOCK', 

      NULL) "LOCK LEVEL", 

  o.owner, o.object_name, o.object_type 

FROM v$session s, v$lock l, dba_objects o 

WHERE s.sid = l.sid 

AND o.object_id = l.id1 

AND s.username IS NOT NULL    

--락이 걸린 세션 자세히 알아보기 

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' 

--락이 걸린 세션 간단히 알아보기 

select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait, 

      a.logon_time, a.process, a.osuser, a.terminal 

from v$session a, v$lock b, dba_objects c 

where a.sid = b.sid 

  and b.id1 = c.object_id 

  and b.type = 'TM'; 

select a.sid, a.serial#, a.username, a.process, b.object_name 

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' 

--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우 

--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다 

--kill -9 프로세스아이디 

select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID", 

s.sid "SESSION ID", s.serial#, osuser "OS USER", 

p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT" 

from v$process p, v$session s, v$access a 

where a.sid=s.sid and 

p.addr=s.paddr and 

s.username != 'SYS' 

--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다 

ALTER SYSTEM KILL SESSION '11,39061' 

with t as

(

select '123/43356/789' author from dual

)

SELECT AUTHOR FROM

(

    SELECT DISTINCT rn, LEVEL, 

    SUBSTR (author, INSTR (author, base, 1, LEVEL) + len, INSTR (author, base, 1, LEVEL + 1) - INSTR (author, base, 1, LEVEL) - len ) author FROM 

    (

        SELECT ROWNUM rn, '/' base, '/' || author || '/' author, LENGTH ('/') len FROM t

    )

    CONNECT BY LEVEL <= (LENGTH (author) - LENGTH (REPLACE (author, base))) / len - 1

)

프로젝트 서버 이전중에 문제가 발생 했다.

로컬호스트 에서는 문제가 없었으나 . 서버 테스트 과정에서 리터럴 형식이 잘못 되었다는 에러 메세지와 함께

제대로 검색이 되지 않았다.

(수정 : 확인결과 서버에서 받는 문자 형식의 리터럴 방식이 일관 되지 않게 된 경우 발생 함. 따라서 모든 데이터 타입 변경시 

되도록이면 

TO_CHAR(컬럼) , 

TO_DATE(컬럼) 이런식으로 쓸것이 아니라 반드시 그 변환 형식을 남기도록 해야 하며 예를 들자면 'YYYYMMDD'

같은 형식을 넣어야만 한다. )

TO_DATE(A.DT_FR)

이런 형식을 모두

TO_DATE(A.DT_FR,"YYYYMMDD")

로 바꾸어야만 한다.

F====>       TO_DATE ?\((.+?)\)

R====>      TO_DATE\($1,'YYYYMMDD')

이다.

예외사항 :     TO_DATE(MIN(VAT_DT)) 과 같은 경우 제한식을 ) 으로 두었기 때문에 수작업 해야 한다. 그냥 바꾸면

TO_DATE(MIN(VAT_DT,'YYYYMMDD')) 라 바뀌게 되는데 이 것은 X

TO_DATE(MIN(VAT_DT),'YYYYMMDD') 로 바꿔야 O . 이는 대도록 정규식을 피해야 할것 같다. 정규식을 좀더 수정 하면 될것도 같긴 한데 일단. ; 걍 수업.

추가 . 

TO_DATE(SYSDATE,'YYYYMMDD') 가 안먹힌다.. 지정된 월이 아니라는 에러 메세지만 배터 낸다. 좀 이해가 안되지만 어쩔수 없이 SYSDATE 에 TO_CHAR 을 적용 했더니 해결 되었다. 

TO_DATE(TO_CAHR(SYSDATE,'YYYYMMDD'),'YYYYMMDD') 로 변경.   

추신: 

컬럼 네임에 . 들어 가있는 경우도 있기 때문에 이미 바꿔져 있는 것의 경우 ,가 포함 되어 있다. 따라서 구분을 할수 없음으로

변환을 두번 하게 되면 'YYYYMMDD' 가 추가 되게 되니. 하나씩 find 해서 replace 해야 한다.

추가정규식

검색해 보고 맞는것을 사용토록 해야함 

TO_DATE\(?(.[^\,]+?)\)

TO_DATE($1,'YYYYMMDD')

1번째 --> F(find)

2번째 --> R(replace)

TO_DATE\(?(.[^\,]+?)\)

/

TO_CHARW(?(.[^\,]+?)\)

TO_CHAR($1,'YYYYMMDD')

1번째 --> F(find)

2번째 --> R(replace)

TOAD를 사용하면서 편리하게 이용할 수 있는 단축키를 정리한 것입니다.

테이블 정보 상세보기

F4 : Table, View, Proc, Funct, Package를 DESC(테이블명 위에 커서를 두고 F4키)

자동완성

Ctrl+. : Table Completion (매칭되는 테이블목록 출력)

Ctrl+T : Columns Dropdown (해당테이블의 컬럼목록 표시)

SQL문 실행

F5 : SQL Editor내의 모든 SQL문 실행

Ctrl+Enter : 현재 커서의 SQL문 실행

F9 : SQL문 실행 후 Grid에 출력

히스토리(과거 수행SQL문 조회)

F8 : 과거에 실행한SQL문 HISTORY 목록

Alt+Up : History UP

Alt+Down : History DOWN

텍스트 대/소문자 변환

CTRL+L : 텍스트를 소문자로

CTRL+U : 텍스트를 대문자로

주석처리

Ctrl+B : 주석처리

Ctrl+Shift+B : 주석해제

편집 창 전환(이동)

F6 : SQL Editor와 결과창간의 이동

F2 : SQL Editor창 전체화면 전환

Shift+F2 : Grid Output창 전체화면 전환

기타 단축키

F7 : 화면을 모두 CLEAR

Ctrl+Shift+F : 쿼리문을 보기좋게 정렬

Ctrl+F9 : SQL문 Validate (SQL문을 수행하지 않음)

주민번호는 많은데 .. 사업자 번호는 없어서. 만들어봄.. 

SELECT * FROM (

SELECT B.* ,DECODE(CHKSUM,0,DECODE(CHKSUM,B.B1,'Y','N'),DECODE(10-CHKSUM,B.B1,'Y','N')) AS CHK_YN  FROM 

 (SELECT A.* ,  MOD( A1*1+A2*3+A3*7+A4*1+A5*3+A6*7+A7*1+A8*3+A9*5+TRUNC((A9*5)/10,0) ,10)  AS CHKSUM

    FROM(

    SELECT  사업자번호

            ,SUBSTR(사업자번호 , 1, 1) A1

            ,SUBSTR(사업자번호 , 2, 1) A2

            ,SUBSTR(사업자번호 , 3, 1) A3

            ,SUBSTR(사업자번호 , 4, 1) A4

            ,SUBSTR(사업자번호 , 5, 1) A5

            ,SUBSTR(사업자번호 , 6, 1) A6

            ,SUBSTR(사업자번호 , 7, 1) A7

            ,SUBSTR(사업자번호 , 8, 1) A8

            ,SUBSTR(사업자번호 , 9, 1) A9

            ,SUBSTR(사업자번호 , 10, 1) B1  --유효성 체크자리 

    FROM    TMP_A_거래선

    )A) B)

   WHERE CHK_YN = 'N'

--  TMP_A_거래선 : 테이블 이름

     사업자번호 : 컬럼이름 

실사용 컬럼

DECODE (

                INSTR (

                   TRANSLATE (A.은행코드, '1234567890', 'XXXXXXXXXX'),

                   'X'),1, LPAD (A.은행코드, 3, '0'),

                A.은행코드)

--  은행코드 004 -> 04로 들어 가 있으며 은행코드가 영문과 같이 있어 영문은 제외한 나머지를 004 로 바꾸려고 한다. 

///

SELECT DECODE (

                INSTR (

                   TRANSLATE (컬럼명, '1234567890', 'XXXXXXXXXX'),

                   'X'),1, 'Y',

                'N') AS CONFIRM

FROM 테이블명

WHERE CONFIRM  = 'Y'    -----  'Y'  이면 숫자. 

WITH

TAB_COMMENTS_CAST AS (

        SELECT * 

          FROM USER_TAB_COMMENTS

         WHERE TABLE_NAME ='XXSB_DCT_SM_USER' --여기에 테이블 명을 입력하세요 접속한 USER 테이블만 가능

        ),

TAB_COLUMNS_CAST AS (

        SELECT A.COLUMN_NAME

             , A.COLUMN_ID

          FROM USER_TAB_COLUMNS     A

             , TAB_COMMENTS_CAST    B

         WHERE A.TABLE_NAME = B.TABLE_NAME

         ORDER BY A.COLUMN_ID

        ),

PK_COLUMNS_CAST AS (

        SELECT A.*

          FROM USER_IND_COLUMNS   A

             , USER_CONSTRAINTS   B

             , TAB_COMMENTS_CAST  C

         WHERE A.TABLE_NAME = B.TABLE_NAME

           AND A.INDEX_NAME  = B.CONSTRAINT_NAME

           AND B.CONSTRAINT_TYPE = 'P'

           AND A.TABLE_NAME = C.TABLE_NAME

         ORDER BY A.COLUMN_POSITION

        )

  -------------

  --SELECT 시작

  -------------

  --SELECT COMMENTS || ' SELECT' FROM TAB_COMMENTS_CAST UNION ALL

  SELECT DECODE(ROWNUM, 1,'SELECT ','     ,')|| DECODE(SUBSTR(COLUMN_NAME, LENGTH(COLUMN_NAME)-1,2),'YN','DECODE('||COLUMN_NAME||',''1'',''1'',''0'') AS '||COLUMN_NAME, COLUMN_NAME)

    FROM TAB_COLUMNS_CAST 

   WHERE COLUMN_NAME NOT IN('INSERT_ID','INSERT_IP','INSERT_PGM','INSERT_DATE','UPDATE_ID','UPDATE_IP','UPDATE_PGM','UPDATE_DATE')

  UNION ALL

  SELECT '  FROM '||TABLE_NAME  FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT DECODE(A.COLUMN_POSITION, 1,' WHERE ','   AND ') || A.COLUMN_NAME || LPAD(' = ',B.MAXLEN-LENGTH(A.COLUMN_NAME),' ') || '#'|| A.COLUMN_NAME ||'#' 

    FROM PK_COLUMNS_CAST A

       , (

            SELECT MAX(LENGTH(COLUMN_NAME))+3 AS MAXLEN FROM PK_COLUMNS_CAST

         ) B

  UNION ALL SELECT '' FROM DUAL UNION ALL SELECT '' FROM DUAL 

  -------------

  --INSERT 시작

  -------------

  --UNION ALL SELECT COMMENTS || ' INSERT' FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT 'INSERT INTO '||TABLE_NAME FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT '(' FROM DUAL

  UNION ALL

  SELECT DECODE(COLUMN_ID, 1,'    ',',')|| COLUMN_NAME

    FROM TAB_COLUMNS_CAST

  UNION ALL

  SELECT ')' FROM DUAL

  UNION ALL

  SELECT 'VALUES' FROM DUAL

  UNION ALL

  SELECT '(' FROM DUAL

  UNION ALL

  SELECT DECODE(COLUMN_ID, 1,'    ','  ,')|| CASE WHEN COLUMN_NAME IN('INSERT_DATE','UPDATE_DATE') THEN 'SYSDATE' ELSE DECODE(SUBSTR(COLUMN_NAME, LENGTH(COLUMN_NAME)-1,2),'YN','DECODE('||'#'||COLUMN_NAME||'#'||',''1'',''1'',''2'')', '#'||COLUMN_NAME||'#') END

    FROM TAB_COLUMNS_CAST

  UNION ALL

  SELECT ')' FROM DUAL

  UNION ALL SELECT '' FROM DUAL UNION ALL SELECT '' FROM DUAL 

  -------------

  --UPDATE 시작

  -------------

  --UNION ALL SELECT COMMENTS || ' UPDATE' FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT 'UPDATE '||TABLE_NAME FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT DECODE(ROWNUM, 1,'   SET ',',') || A.COLUMN_NAME || LPAD(' = ', B.MAXLEN-LENGTH(A.COLUMN_NAME),' ')

        || DECODE(A.COLUMN_NAME,'UPDATE_DATE','SYSDATE',DECODE(SUBSTR(A.COLUMN_NAME, LENGTH(A.COLUMN_NAME)-1,2),'YN','DECODE(#'||A.COLUMN_NAME||'#,''1'',''1'',''2'')', '#'||A.COLUMN_NAME||'#') )

    FROM ( 

            SELECT A.COLUMN_NAME

              FROM TAB_COLUMNS_CAST  A

                 , PK_COLUMNS_CAST      B

             WHERE A.COLUMN_NAME = B.COLUMN_NAME(+)

               AND B.COLUMN_NAME IS NULL

               AND A.COLUMN_NAME NOT IN ('INSERT_ID','INSERT_IP','INSERT_PGM','INSERT_DATE')

             ORDER BY A.COLUMN_ID

          ) A

        ,(

            SELECT MAX(LENGTH(A.COLUMN_NAME))+3 AS MAXLEN

              FROM TAB_COLUMNS_CAST  A

                 , PK_COLUMNS_CAST   B

             WHERE A.COLUMN_NAME = B.COLUMN_NAME(+)

               AND B.COLUMN_NAME IS NULL

               AND A.COLUMN_NAME NOT IN ('INSERT_ID','INSERT_IP','INSERT_PGM','INSERT_DATE')

         ) B

  UNION ALL

  SELECT DECODE(A.COLUMN_POSITION, 1,' WHERE ','   AND ') || A.COLUMN_NAME || LPAD(' = ', B.MAXLEN-LENGTH(A.COLUMN_NAME),' ') || '#'|| A.COLUMN_NAME ||'#' 

    FROM PK_COLUMNS_CAST A

       , (

            SELECT MAX(LENGTH(COLUMN_NAME))+3 AS MAXLEN FROM PK_COLUMNS_CAST

         ) B

  UNION ALL SELECT '' FROM DUAL UNION ALL SELECT '' FROM DUAL 

  -------------

  --DELETE 시작

  -------------

  --UNION ALL SELECT COMMENTS || ' DELETE' FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT 'DELETE FROM '||TABLE_NAME FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT DECODE(A.COLUMN_POSITION, 1,' WHERE ','   AND ') || A.COLUMN_NAME || LPAD(' = ',B.MAXLEN-LENGTH(A.COLUMN_NAME),' ') || '#'|| A.COLUMN_NAME ||'#' 

    FROM PK_COLUMNS_CAST A

       , (

            SELECT MAX(LENGTH(COLUMN_NAME))+3 AS MAXLEN FROM PK_COLUMNS_CAST

         ) B

SELECT SUBSTR (

                  XMLAGG (XMLELEMENT (

                             "A",

                             ',',

                             SC.BRAN_NM

                             ) ORDER BY

                                                        SB.SEQ).EXTRACT (

                     '//text()').GETSTRINGVAL (),

                  2)

          FROM    TGS_CONF_REG SA

               LEFT OUTER JOIN 

                  TGS_MEET_INFO  SB

                 ON SA.CONF_REG_NO = SB.CONF_REG_NO    

               INNER  JOIN 

                   TGS_BRAN_MGT  SC

                  ON SB.BRAN_CD = SC.BRAN_CD

<%@page import="oracle.jdbc.driver.OracleResultSet"%>

<%@page import="com.base.SystemException"%>

<%@page import="java.io.*"%>

<%@page import="java.sql.*"%>

    <%

request.setCharacterEncoding("utf-8");

    response.reset();

    String conversation_ID  = request.getParameter("CLS_CONVERSATION_ID");

    String file_Seq = request.getParameter("CLS_FILE_SEQ");

Blob             emptyBlob = null;

OutputStream     outstream = null;

FileInputStream finstream = null;

ResultSet rs = null;

PreparedStatement pstmt = null;

Connection conn = null;

try {

javax.naming.Context env = new javax.naming.InitialContext();

javax.sql.DataSource source = (javax.sql.DataSource) env.lookup("baseDS");

conn = source.getConnection();

conn.setAutoCommit(false);

} catch (Exception e) {

throw new SystemException(e);

}

StringBuffer query  = new StringBuffer();

query.append("SELECT FILE_NAME, FILE_BINARY FROM SYN_XXSB_DCT_FILE WHERE CONVERSATION_ID = ? AND FILE_SEQ = ?  ");

try {

pstmt = conn.prepareStatement(query.toString());

pstmt.setString(1, conversation_ID.trim());

pstmt.setString(2, file_Seq.trim());

rs = pstmt.executeQuery();

String fileName= "";

while(rs.next()){

fileName = rs.getString(1);

oracle.sql.BLOB blob = ((OracleResultSet)rs).getBLOB(2);

InputStream is = (blob.getBinaryStream());

String fileType = fileName.substring(fileName.indexOf(".")+1,fileName.length());

response.setContentType("application/x-msdownload"); 

response.setHeader("Content-Disposition", "attachment;filename="+fileName+";");

OutputStream os = response.getOutputStream();

int size = blob.getBufferSize();

byte[] buffer = new byte[size];

int length = -1;

while((length=is.read(buffer))!=-1){

os.write(buffer,0,length);

}

os.flush();

os.close();

is.close();

}

} catch (Exception e) {

throw new SystemException(e);

}finally{

try {if (rs != null)  rs.close(); } catch (Exception ex) {}

try {if (pstmt != null) pstmt.close();} catch (Exception ex) {}

try {if (conn != null) conn.close();}  catch (Exception ex) {}

}

    %>

현재 오라클에서 사용하는 SQL은 T(Transaction)-SQL로 ANSI의 표준 SQL과는 
일부 차이가 있습니다. T-SQL이 편리한 점이 많지만 같은 내용의 SQL문이 벤더
 별로 차이가 남에 따라, ANSI SQL 3-1999에서는 모든 벤더가 표준 SQL문을 
지원하도록 하고 있으며, 오라클도 9i 버젼부터 사용이 가능합니다. 
 
대표적인 특징으로는 조인의 형태가 FROM 절에서 명시적으로 지정되며, 조인 
조건이 WHERE 절의 검색 조건과 구별되어 ON 절이나 USING 절에 표시됩니다. 
OUTER 조인의 경우 (+) 표시가 아닌 LEFT / RIGHT / FULL OUTER JOIN 문법
을 사용할 수 있습니다.

1) CROSS JOIN

예문)

SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT;

크로스 조인은 두 개의 테이블에 대한 Cartesian Product와 같은 결과입니다.
의도적으로 데이터를 복제하기 위해 카테시안 프로덕트를 사용한 것이 아니라면, 
크로스 조인은 사용하지 않는 것이 바람직합니다.

2) NATURAL JOIN

예문)

SELECT *
FROM EMP NATURAL JOIN DEPT;

내츄럴 조인은 모든 동일한 이름을 갖는 칼럼들에 대해 조인을 합니다. 
즉, NATURAL 조인은 자동적으로 두 테이블에서 같은 이름을 가진 모든 칼럼에 
Equi Join을 수행합니다.

이때, 조인 칼럼들은 같은 데이터 유형이어야 하며, Alias나 테이블 명과 같은 
접두사를 붙일 수 없습니다. 만일 같은 이름을 가지는 칼럼들이 서로 다른 데이터 
형을 가질 때에는 오류가 반환됩니다.

그리고, SELECT * 문법을 사용한다면, 공통 칼럼들은 결과 집합에서 한 개만 표현
됩니다.

3) USING JOIN

예문)

SELECT E.EMPNO, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
USING (DEPTNO);

내츄럴 조인에서는 자동적으로 이름이 일치되는 모든 칼럼들에 대해 조인이 이루어지
지만, USING 절을 이용하면 원하는 칼럼에 대해서만 선택적으로 Equi Join을 할 수
가 있습니다.

만일 여러 개의 칼럼이 이름은 같지만 데이터 형이 모두 일치하지 않거나, 몇 개의 
칼럼만 선택적으로 조인 조건에 사용하고자 할 때는 Using 절을 이용하여 Equi Join
에 사용될 칼럼들을 지정할 수 있습니다.
USING 절을 이용한 Equi Join에서도 내츄럴 조인과 마찬가지로, 조인 칼럼에 대해서는
 Alias나 테이블 명과 같은 접두사를 붙일 수 없으며, Natural과 Using의 두 키워드는 
상호 배타적으로 사용됩니다.

4) ON JOIN

예문)

SELECT E.ENAME, E.DEPTNO, D.DNAME 
FROM EMP E JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
WHERE E.SAL > 1000;

조인 서술부(ON 절)와 비조인 서술부(WHERE 절)를 분리하여 이해가 쉽습니다.
ON 절을 이용하면 JOIN 이후에 논리 연산과 서브쿼리와 같은 추가 서술을 할 수 있습
니다.

Natural 조인과 달리 ON 조인은, 임의의 조인 조건을 지정하거나, 이름이 다른 칼럼
끼리 조인 조건으로 사용하거나, 조인할 칼럼을 명시하기 위해서 사용합니다.

● 여러 테이블의 조인

SELECT E.EMPNO, D.DNAME , B.SAL
FROM EMP E JOIN DEPT D  
ON ( E.DEPTNO = D.DEPTNO )
  JOIN BONUS B
ON ( E.ENAME = B.ENAME );

● WHERE 절과의 혼용

SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME 
FROM EMP E JOIN DEPT D
ON  ( E.DEPTNO = D.DEPTNO )
WHERE E.EMPNO >= 7000;

● ON 절의 조건 추가

SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D 
ON ( E.DEPTNO = D.DEPTNO  AND  E.MGR = 7698 );

● EXIST 절 사용

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, B.BONUS
FROM EMP E JOIN DEPT D 
ON ( E.DEPTNO = D.DEPTNO 
     AND NOT EXISTS (SELECT 1 
                         FROM BONUS B 
                        WHERE E.ENAME = B.ENAME) );

5) OUTER JOIN

지금까지 오라클에서 제공하였던 Outer Join 표기는 (+)를 사용했지만, 이해가 
어렵고 실수를 유발하기 쉽다는 단점이 있습니다.

ANSI SQL-3의 기준을 오라클 9i에서 수용하면서 LEFT / RIGHT OUTER 조인뿐
만 아니라, 그 동안 UNION이나 UNION ALL을 이용해서 처리하던 양쪽 아우터 
조인도 FULL OUTER JOIN 문법으로 새로 추가 되었습니다.

● LEFT OUTER JOIN

Table A와 B가 있을 때 왼쪽에 있는 Table A가 기준이 됩니다. A와 B를 비교해서
 같은 것이 있을 때 그 해당 Data를 가져오고, B가 없는 경우에도 가져오는데 B에서 
가져오는 칼럼은 NULL 값으로 표시됩니다.

SELECT E.ENAME, E.DEPTNO, D.DNAME 
FROM EMP LEFT OUTER JOIN DEPT 
ON (EMP.DEPTNO = DEPT.DEPTNO);

● RIGHT OUTER JOIN

Table A와 B가 있을 때 오른쪽에 있는 Table B가 기준이 됩니다. A와 B를 비교해서
같은 것이 있을 때 그 해당 Data를 가져오고, A가 없는 경우에도 가져오는데 A에서 
가져오는 칼럼은 NULL 값으로 표시됩니다.

SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP RIGHT OUTER JOIN DEPT 
ON (EMP.DEPTNO = DEPT.DEPTNO);

● FULL OUTER JOIN

Table A와 B가 있을 때 (Table A, B 모두 기준), Left Outer Join과 Right Outer Join의 
결과를 UNION으로 합친 것과 같습니다.

SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP FULL OUTER JOIN DEPT 
ON (EMP.DEPTNO = DEPT.DEPTNO);  
이것만은 기억하자!

[출처] http://blog.naver.com/chanseog?Redirect=Log&logNo=20000703888

경고! 

쿼리 실행시 기존에 사용한 쿼리문을 모두(또는 일부) 돌린후 쿼리 끼리 검증 함으로 반드시 일반 사용자가 사용하지 않은 시간에 돌려야 합니다.