나의 향기에 젖다...

http://pino93.tistory.com/373

SQLCODE, SQLERRM

-       WHEN OTHERS문으로 트랩(Trap) 되는 오류들의 실제 오류 코드와 설명을 볼 때 사용한다

-       SQLCODE : 실행된 프로그램이 성공적으로 종료하였을 때는 오류번호 0을 포함하며, 그렇지 못할 경우에는 해당 오류코드 번호를 포함한다.

-       SQLERRM : SQLCODE에 포함된 오라클 오류 번호에 해당하는 메시지를 가진다

  ================================================
    * Oracle Community OracleClub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 운영자 : 김정식 (oramaster _at_ empal.com)
  ================================================ 

◆ MERGE INTO 구문

 - MERGE문은 조건에 따라서 데이터의 삽입,갱신,삭제 작업을 한번에 할 수 있다.
 - 해당 행이 존재하는 경우 UPDATE(DELETE포함 할 수 있음)를 수행하고, 새로운 행일경우 INSERT를 수행한다.
 - 대상 테이블에 대한 UPDATE/INSERT 조건은 ON절에 의해 결정된다.
 - MERGE문에서 CLOB 사용시 업데이트 할 내용이 2000bytes가 넘을때 ORA-00600 오류가 발생하며, patch set 11.2.0.2 버전으로 해결할 수 있다.

◆ 문법

MERGE INTO 테이블 a               -- update또는 insert할 테이블

       USING 대상테이블/뷰 b      -- 비교대상 (동일 테이블이라면 using dual 사용)

           ON (조건)                   -- a와 b의 조인식

        WHEN MATCHED THEN

                  UPDATE SET

                              컬럼1 = 값1

                              컬럼2 = 값2

                  DELETE WHERE (조건)

        WHEN NOT MATCHED THEN

                  INSERT(컬럼1, 컬럼2...)

                  VALUES(값1, 값2...)

 - INTO : DATA가 UPDATE되거나 INSERT 될 테이블 또는 뷰를 지정.
 - USING : 비교할 SOURCE 테이블 또는 뷰나 서브쿼리를 지정, INTO절의 테이블과 동일하거나 다를 수 있다.
 - ON : UPDATE나 INSERT를 하게 될 조건으로, 해당 조건을 만족하는 DATA가 있으면 WHEN MATCHED 절을 실행하게 되고, 없으면 WHEN NOT MATCHED 이하를 실행하게 된다.
 - WHEN MATCHED : ON 조건절이 TRUE인 ROW에 수행 할 내용 (UPDATE, DELETE포함 될 수 있음)
 - WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용 (INSERT)

※ 오라클 9버전이상에서 사용가능하다.

◆ SAMPLE

MERGE INTO STO_TEMP1 T1
     USING (SELECT EBELN, EBELP
              FROM STO_TEMP
             WHERE EBELN = '4501134859' AND EBELP = '00010') T
        ON (T1.EBELN = T.EBELN AND T1.EBELP = T.EBELP)
WHEN MATCHED
THEN
   UPDATE SET T1.MBLNR = T.MBLNR, T1.BUDAT = T.BUDAT
   DELETE
           WHERE T1.MBLNR = '4926768928'
WHEN NOT MATCHED
THEN
   INSERT     (T1.EBELN,
               T1.EBELP,
               T1.MBLNR,
               T1.BUDAT)
       VALUES (T.EBELN,
               T.EBELP,
               T.MBLNR,
               T.BUDAT)

1. 16진수 -> 10진수

SELECT  TO_CHAR(15,'0X'), TO_CHAR(16,'0X'), TO_CHAR(17,'0X') 
  FROM  DUAL;

 >> OF   10   11

SELECT  TO_CHAR(15,'000X'), TO_CHAR(16,'000X'), TO_CHAR(17,'000X') 
  FROM  DUAL;

2. 10진수 -> 16진수

SELECT  TO_NUMBER('F', 'XX'), TO_NUMBER('10', 'XX'), TO_NUMBER('11', 'XX') 
  FROM  DUAL;

 >> 15   16   17

SELECT  S.FIRST_LOAD_TIME, TO_CHAR(S.BUFFER_GETS,'999,999,999,990') BUFFER_GETS,

              S.DISK_READS, S.ROWS_PROCESSED, S.EXECUTIONS,
              SUBSTR(U.NAME,1,1) USERNAME, S.MODULE, S.SQL_TEXT
   FROM  V$SQLAREA S, SYS.USER$ U
 WHERE  S.PARSING_USER_ID = U.USER#
     AND  U.NAME <> 'SYS'
     AND  S.DISK_READS > 10000
     AND  S.FIRST_LOAD_TIME >= TO_CHAR(SYSDATE,'yyyy-mm-dd')||'/00:00:00'
 ORDER BY  S.FIRST_LOAD_TIME DESC;

## 테이블 사이즈 조회 쿼리

SELECT A.SEGMENT_NAME,
             ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB",

             ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB",
             A.SEGMENT_TYPE
  FROM DBA_SEGMENTS A,
            DBA_TABLES B
WHERE A.SEGMENT_NAME = B.TABLE_NAME
     AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
     AND A.OWNER = '유저아이디'
 GROUP BY A.SEGMENT_NAME, A.SEGMENT_TYPE
 ORDER BY 2 DESC;

## 인덱스 사이즈 조회 쿼리

SELECT A.SEGMENT_NAME,
             ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB",

             ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB",
             A.SEGMENT_TYPE
  FROM DBA_SEGMENTS A,
            DBA_INDEXES B
WHERE A.SEGMENT_NAME = B.INDEX_NAME
     AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')
     AND A.OWNER = '유저아이디'
 GROUP BY A.SEGMENT_NAME, A.SEGMENT_TYPE
 ORDER BY 2 DESC;

*** TABLE PARTITION

 세그먼트가 분할된 테이블을 말한다.

 분할에 사용되는 정보입력은 Value 이며 이는 Hash / Range / List 등으로 나뉘어진다.

// TABLESPACE_NAME 별로 합처리

SELECT  SUBSTR(A.TABLESPACE_NAME,1,30) TABLESPACE,
              ROUND(SUM(A.TOTAL1)/1024/1024,1) "TOTALMB",
              ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1) "USEDMB",
              ROUND(SUM(A.SUM1)/1024/1024,1) "FREEMB",
              ROUND((ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1))

              /ROUND(SUM(A.TOTAL1)/1024/1024,1)*100,2) "USED%"
  FROM
             (SELECT  TABLESPACE_NAME,0 TOTAL1,SUM(BYTES) SUM1,MAX(BYTES) MAXB,

                            COUNT(BYTES) CNT
                 FROM  DBA_FREE_SPACE
               GROUP  BY TABLESPACE_NAME
              UNION
              SELECT  TABLESPACE_NAME,SUM(BYTES) TOTAL1,0,0,0
                 FROM  DBA_DATA_FILES
               GROUP  BY TABLESPACE_NAME) A
GROUP BY A.TABLESPACE_NAME
ORDER BY TABLESPACE;

// 전체 합처리

SELECT  ROUND(SUM(A.TOTAL1)/1024/1024/1024,1) "TOTALGB",
              ROUND(SUM(A.TOTAL1)/1024/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024/1024,1) "USEDGB",
              ROUND(SUM(A.SUM1)/1024/1024/1024,1) "FREEGB",
              ROUND((ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1))

              /ROUND(SUM(A.TOTAL1)/1024/1024,1)*100,2) "USED%"
  FROM
             (SELECT  TABLESPACE_NAME,0 TOTAL1,SUM(BYTES) SUM1,MAX(BYTES) MAXB,

                            COUNT(BYTES) CNT
                 FROM  DBA_FREE_SPACE
               GROUP BY TABLESPACE_NAME
              UNION
              SELECT  TABLESPACE_NAME,SUM(BYTES) TOTAL1,0,0,0
                 FROM  DBA_DATA_FILES
               GROUP BY TABLESPACE_NAME) A

가끔 모니터링을 하다보면 OS의 cpu 혹은 memory를 많이 잡아먹는 Oracle session들이 발견된다.

그 넘들이 어떤 쿼리를 날리고 있는지 확인할 수 있다.

## oracle sid를 이용한 sql문 찾기

select a.username,a.program,a.machine,a.module,b.spid,a.sid,a.serial#,a.status,
       c.sql_text
  from v$session a,
       v$process b,
       v$sql     c
 where a.sid = '&sid'
   and b.addr = a.paddr
   and a.sql_hash_value = c.hash_value(+)
   and a.sql_address    = c.address(+);

## os pid를 이용한 sql문 찾기

 
 select /*+ use_hash(a,b,c) */
       a.username,a.program,a.machine,a.module,b.spid,a.sid,a.serial#,a.sql_hash_value,
       d.PHYSICAL_READS, d.BLOCK_GETS,
       c.sql_text
  from v$session a,
       v$process b,
       v$sql     c,
       v$sess_io d
 where b.spid = '&pid'
   and b.addr = a.paddr
   and a.sid  = d.sid
   and a.sql_hash_value = c.hash_value(+)
   and a.sql_address    = c.address(+);

## SAMPLE 1

 SELECT SUBSTRB(MAX(SYS_CONNECT_BY_PATH(CARTONID,', ')),2) CARTONID
   FROM (
         SELECT ROWNUM RW, NVL(B.CARTONID,C.CARTONID) CARTONID
           FROM TTASKINHI A, TCONTENT B, TCONTENTHI C
          WHERE A.OWNERID = '1474'
            AND A.ORDERID = '131001-901642'
            AND A.ITEMID  = '1118825'
            AND A.CONTENTID = B.CONTENTID(+)
            AND A.CONTENTID = C.CONTENTID(+)
        )
  START WITH RW=1
CONNECT BY PRIOR RW=RW-1;

1. 내부 쿼리 실행시

RW CARTONID

1    32021092K
2    32021092K
3    32021092K
4    32021092K

2. 전체 쿼리 실행시

CARTONID

32021092K, 32021092K, 32021092K, 32021092K

## SAMPLE 2

 SELECT A, MAX(SYS_CONNECT_BY_PATH(B,'/')) B
    FROM (
                SELECT ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
                           , A, B
                   FROM (
                                SELECT 1 A, '엄마' B FROM DUAL
                                UNION
                                SELECT 1 A, '아빠' B FROM DUAL
                                UNION
                                SELECT 1 A, '이모' B FROM DUAL
                                UNION
                                SELECT 2 A, '삼촌' B FROM DUAL
                                UNION
                                SELECT 2 A, '오빠' B FROM DUAL
                             )
              )
 START WITH RNUM = 1
 CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A=A
 GROUP BY A

1. 내부 쿼리 실행시

A B

1 아빠
1 엄마
1 이모
2 삼촌
2 오빠

2. 전체 쿼리 실행시

A B

1 /아빠/엄마/이모
2 /삼촌/오빠

3. RNUM = 1 -> RNUM = 2 변경시

A B

1 /엄마/이모
2 /오빠

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

### 타블로그 참조 ###

1. 컬럼명과 데이블명은 원도우,리눅스,유닉스에서 대소문자를 가리지 않습니다.
 

2. SQL문은 대소문자를 구분하지 않습니다.
 
SELECT NAME FROM LEE;
select NAME from LEE;
-- 2개다 같은 출력을 합니다.
 
※ SQL 쿼리는 대문자로 써주는것이 좋습니다.
 

3.문자열 데이타형은 기본으로 대소문자를 구분합니다.
 

SELECT * FROM TAB WHERE NAME = 'lee';

SELECT * FROM TAB WHERE NAME = 'LEE';

-- lee컬럼의 값이 lee라면 첫번째것은 되지만 두번째것은 되지 않는다.
 

4.대소문자 구분없이 검색하는법
 

WHERE UPPER("+search+") LIKE UPPER('%"+keyword+"%')

WHERE LOWER("+search+") LIKE LOWER('%"+keyword+"%')

※검색할 단어와 비교할 컬럼을 모두 대문자나 소문자로 만든후 비교합니다.

1. 프로시저 보는 방법
 
-- 해당 DB 의 프로시저명 확인
select * from user_procedures;

-- 해당 DB 의 프로시저와 소스까지 확인
select * from user_source;

2. 프로시저, 테이블등을 스크립트로 백업 받는 방법

database->export->generate schema script