1. 데이터를 옮길 대상 테이블을 생성한다. : "TEMP_20130607" 라하자.;

 

CREATE TABLE TEMP_20130607
(
  TXDATE               VARCHAR2(8 BYTE)         NOT NULL,
  WRAP_ACCOUNT_NO      VARCHAR2(20 BYTE)        NOT NULL,
  WRAP_ACCOUNT_AMOUNT  VARCHAR2(4000 BYTE)
)
TABLESPACE APLUS_DATA_TS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

 

2. FUNCTION 생성

 

CREATE OR REPLACE FUNCTION APLUSORA.FNC_LONG2CHAR (  
    ori_rowid        ROWID,  
    ori_column       VARCHAR2,
    ori_table        VARCHAR2  
)  
    RETURN VARCHAR
AS  
    longCont   VARCHAR2 (32767);  
    sqlQuery   VARCHAR2 (2000);
BEGIN  
    sqlQuery :=         'select ' || ori_column || ' from '|| ori_table ||         ' where rowid = '|| CHR (39)|| ori_rowid|| CHR (39);    EXECUTE IMMEDIATE sqlQuery                INTO longCont;        longCont := SUBSTR (longCont, 1, 4000);   RETURN longCont;END;
/

 

3. 실제 사용 쿼리

INSERT INTO TEMP_20130607
(TXDATE,WRAP_ACCOUNT_NO,WRAP_ACCOUNT_AMOUNT)
(SELECT TXDATE,WRAP_ACCOUNT_NO,FNC_LONG2CHAR(ROWID,'WRAP_ACCOUNT_AMOUNT','IFK_CUSTOMER') FROM IFK_CUSTOMER
WHERE  TXDATE = (SELECT MAX(TXDATE) DT FROM IFK_CUSTOMER)
AND     ACCOUNT_STATUS_DESC = '활동')

 

댓글을 달아 주세요

[ORACLE] MERGE INTO

Database 2013.03.08 11:39 Posted by 애플자라

-- MERGE UPDATE
MERGE INTO IFT_FUND_STANDARD_PRICE_RESULT A
USING (
    SELECT TIMS_DATE,FUND_CODE,BM_INDEX,BM_RATE FROM IFT_FUND_STANDARD_PRICE_RESULT
    WHERE FUND_CODE ='10110'
    AND TIMS_DATE > '20130115'
) B
ON (
    A.TIMS_DATE = B.TIMS_DATE
    AND A.FUND_CODE ='10112'
)
WHEN MATCHED THEN
    UPDATE SET A.BM_INDEX = B.BM_INDEX, A.BM_RATE = B.BM_RATE

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

MERGE INTO를 사용하면 INSERT, UPDATE를 각 케이스 별로 처리할 수 있다.

한꺼번에 데이터를 덮어쓰거나 갱신할때 UPDATE와 INSERT가 동시에 처리되어야 할때가 있는데,

그러한 경우에 유용하게 쓸수 있는 구문이다.


MERGE INTO 대상테이블 A

USING (

) B

ON (

-- 매칭 조건

)

WHEN MATCHED THEN

-- 조건에 매칭될 경우 실행

UPDATE 문 (UPDATE SET ...)

WHEN NOT MATCHED THEN

-- 조건에 매칭되지 않을 경우 실행

INSERT 문 (특이한 점은 INSERT INTO가 아니다.)


예제

MERGE INTO TCOGRNTNUM A

USING (

    SELECT '0009' AS GTNO_KD_CD, GTNO_KEY_VAL, MAX(POL_SEQ) AS SEQ

    FROM (

        SELECT PDGP_CD || SUBSTR(TO_CHAR(INS_BGN_DT, 'YYYYMMDD'), 3,2) AS GTNO_KEY_VAL, SUBSTR(POL_NO, 6, 7) POL_SEQ FROM TCTTCOT

        WHERE

            BIZ_SYS_CD = 'CTK'

            AND JNT_TNG_YN = '2'

            AND INS_BGN_DT BETWEEN TO_DATE('20121001', 'YYYYMMDD') AND TO_DATE('20121115', 'YYYYMMDD')

    )

    GROUP BY GTNO_KEY_VAL

) B

ON (

    A.GTNO_KD_CD = B.GTNO_KD_CD

    AND A.GTNO_KEY_VAL = B.GTNO_KEY_VAL

)

WHEN MATCHED THEN

    UPDATE SET A.SEQ = B.SEQ, A.SYS_DEL_DIV_CD = '0', A.INPPE_ORG_ID = '0000000000', A.SYS_OCC_DTM = SYSDATE,

    A.OCC_IP = '0.0.0.0', A.APP_ID = 'MIG', A.DATA_CHNG_DTM = SYSDATE

WHEN NOT MATCHED THEN

    INSERT (A.GTNO_KD_CD, A.GTNO_KEY_VAL, A.SEQ, A.SYS_DEL_DIV_CD, A.INPPE_ORG_ID, A.SYS_OCC_DTM, A.OCC_IP, A.APP_ID, A.DATA_CHNG_DTM)

    VALUES (B.GTNO_KD_CD, B.GTNO_KEY_VAL, B.SEQ, '0', '0000000000', SYSDATE, '0.0.0.0', 'MIG', SYSDATE) 

;

댓글을 달아 주세요

[ORACLE] 바이트수 확인

Database 2013.03.07 09:40 Posted by 애플자라

SELECT
 CUSTOMER_NAME AS 고객명,
 LENGTHB(ADDR) BYTE, -- 바이트 수 길이
 LENGTHB(SUBSTRB(ADDR, 0, 70)) BYTE_1,
 LENGTHB(SUBSTRB(ADDR, 70, 140)) BYTE_2,
 SUBSTRB(ADDR, 0, 70) AS SUB_LEN1, -- 바이트 수 만큼 문자열 짜르기
 SUBSTRB(ADDR, 70, 140) AS SUB_LEN1,
 ADDR   AS 동이상주소,
 '' AS 동이하주소
FROM APLUSORA.TEMP_KIUM_CUSTOMER;

댓글을 달아 주세요

[ORACLE]Oracle Default Profile 확인

Database 2013.02.21 09:06 Posted by 애플자라

오라클 운영을 할때!!
신규DB설치를 하거나, DB upgrade로 작업으로
오라클 버젼이 바뀔때면 주의해야 될것이 default profile인것 같습니다.

처음에는 운영 잘하다가
나중에 profile에 의해 유저로그인이 되지 않아 서비스를 중지될수도 있기 때문입니다.
가령 패스워드 시도(FAILED_LOGIN_ATTEMPTS) 횟수라던가?  패스워드 지속시간(PASSWORD_LIFE_TIME) 이라던가?
(물론 보안과 리소스 제한이라는 장점도 있지만.. 서비스 중지보다는 중요하지 않겠죠. ㅋ)

그래서 한번 집고 넘어가려고 합니다.
오라클 버젼별 Default Profile은 어떻게 변화 되있을까요?

오라클 10g default profile

sqlplus "/as sysdba"

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT'; 

- 기존패스워드 변경 및 접속확인

SQL> alter user APLUSORA identified by "**********" ;

SQL> conn APLUSORA/**********

RESOURCE_NAME LIMIT ------------------------------ ---------------------------------------- COMPOSITE_LIMIT UNLIMITED SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED IDLE_TIME UNLIMITED CONNECT_TIME UNLIMITED PRIVATE_SGA UNLIMITED FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION NULL PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME UNLIMITED 16 rows selected.


오라클 11g default profile

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';

RESOURCE_NAME                   LIMIT  
------------------------------  ---------------------------------------- 
FAILED_LOGIN_ATTEMPTS           10 
PASSWORD_VERIFY_FUNCTION        NULL 
PASSWORD_REUSE_MAX              UNLIMITED 
PASSWORD_REUSE_TIME             UNLIMITED 
PRIVATE_SGA                     UNLIMITED 
CONNECT_TIME                    UNLIMITED 
IDLE_TIME                       UNLIMITED 
LOGICAL_READS_PER_CALL          UNLIMITED 
LOGICAL_READS_PER_SESSION       UNLIMITED 
CPU_PER_CALL                    UNLIMITED 
CPU_PER_SESSION                 UNLIMITED 
SESSIONS_PER_USER               UNLIMITED 
COMPOSITE_LIMIT                 UNLIMITED 
PASSWORD_GRACE_TIME             7 
PASSWORD_LIFE_TIME              180 
PASSWORD_LOCK_TIME              1 

16 rows selected. 


그럼 변경은 어떻게 해야될까요?
아래처럼 각 RESOURCE_NAME별로 변경이 가능하고, default profile이니까 unlimited로 설정을 했씁니다.

ALTER PROFILE DEFAULT limit PASSWORD_GRACE_TIME UNLIMITED ; 
ALTER PROFILE DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED ; 
ALTER PROFILE DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED ; 
ALTER PROFILE DEFAULT limit PASSWORD_LOCK_TIME UNLIMITED ; 



여담입니다..
인터페이스 유저처럼 타시스템에서 접속하는 유저에 대해서는 꼭 profile를 생성하고 세션갯수(SESSIONS_PER_USER ) 나 IDLE_TIME등 지정하여 리소스 제한를 통한 타시스템의 영향을 줄이는 방법으로 운영하셔야 할것 같습니다.
(리소스 제한을 하기 위해서는 init parameter로 resource_limit = true 로 설정이 되어 있어야합니다.)

출처 - http://kosate.tistory.com/144

 

SQL> select resource_name,limit from dba_profiles;

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT                  UNLIMITED
SESSIONS_PER_USER                UNLIMITED
CPU_PER_SESSION                  UNLIMITED
CPU_PER_CALL                     UNLIMITED
LOGICAL_READS_PER_SESSION        UNLIMITED
LOGICAL_READS_PER_CALL           UNLIMITED
IDLE_TIME                        30
CONNECT_TIME                     UNLIMITED
PRIVATE_SGA                      UNLIMITED
FAILED_LOGIN_ATTEMPTS            3
PASSWORD_LIFE_TIME               60

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME              1800
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         VERIFY_FUNCTION
PASSWORD_LOCK_TIME               .0006
PASSWORD_GRACE_TIME              UNLIMITED
COMPOSITE_LIMIT                  DEFAULT
SESSIONS_PER_USER                DEFAULT
CPU_PER_SESSION                  DEFAULT
CPU_PER_CALL                     DEFAULT
LOGICAL_READS_PER_SESSION        DEFAULT
LOGICAL_READS_PER_CALL           DEFAULT

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
IDLE_TIME                        DEFAULT
CONNECT_TIME                     DEFAULT
PRIVATE_SGA                      DEFAULT
FAILED_LOGIN_ATTEMPTS            UNLIMITED
PASSWORD_LIFE_TIME               DEFAULT
PASSWORD_REUSE_TIME              DEFAULT
PASSWORD_REUSE_MAX               DEFAULT
PASSWORD_VERIFY_FUNCTION         DEFAULT
PASSWORD_LOCK_TIME               DEFAULT
PASSWORD_GRACE_TIME              DEFAULT

댓글을 달아 주세요

[ORACLE]주민번호로 태어난 연도 알아내기

Database 2013.01.18 10:24 Posted by 애플자라

9: 1800 ~ 1899년에 태어난 남성
0: 1800 ~ 1899년에 태어난 여성
1: 1900 ~ 1999년에 태어난 남성
2: 1900 ~ 1999년에 태어난 여성
3: 2000 ~ 2099년에 태어난 남성
4: 2000 ~ 2099년에 태어난 여성
5: 1900 ~ 1999년에 태어난 외국인 남성
6: 1900 ~ 1999년에 태어난 외국인 여성
7: 2000 ~ 2099년에 태어난 외국인 남성
8: 2000 ~ 2099년에 태어난 외국인 여성

 UPDATE CUSTOMER X
 SET (BIRTH)
 = (SELECT
    CASE
       WHEN SUBSTR(REGIDENT_ID, 7, 1) IN ('1','2','5','6')  THEN '19'||SUBSTR(REGIDENT_ID,1,6)
       WHEN SUBSTR(REGIDENT_ID, 7, 1) IN ('3','4','7','8')  THEN '20'||SUBSTR(REGIDENT_ID,1,6)
    END AS BIRTH
    FROM CUSTOMER B
    WHERE X.REGIDENT_ID=B.REGIDENT_ID)

댓글을 달아 주세요

[ORACLE]Tablespace별로 용량 체크하기

Database 2012.12.05 14:40 Posted by 애플자라

SELECT 
    SUBSTR(MAX(A.TABLESPACE_NAME),1,16) "Tablespace",
    --TO_CHAR(A.FILE_ID, '9999') "File ID",
    SUBSTR(MAX(A.FILE_NAME),1,43) "Data file",
    SUBSTR(MAX(A.STATUS),1,10) "Status",
    MAX(A.BYTES)/1024/1024 "TOTAL SIZE(Mb)" ,
    (MAX(A.BYTES)-NVL(SUM(B.BYTES),0))/1024/1024 "USED SIZE(Mb)",
    NVL(SUM(B.BYTES),0)/1024/1024 "FREE SIZE(Mb)",    
    --MAX(A.BLOCKS)-NVL(SUM(B.BLOCKS),0) "USED BLOCKS",
    TO_CHAR((MAX(A.BYTES)-NVL(SUM(B.BYTES),0))*100/MAX(A.BYTES),'990.99')||'%' "USED USAGE",
    --NVL(SUM(B.BLOCKS),0) "FREE BLOCKS",
    TO_CHAR(NVL(SUM(B.BYTES),0)*100/MAX(A.BYTES),'990.99')||'%' "FREE USAGE"
    --MAX(A.BLOCKS) "TOTAL BLOCKS"
FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
WHERE A.FILE_ID=B.FILE_ID(+)
--AND   A.FILE_ID IN ('6','7','8')
GROUP BY A.FILE_ID
ORDER BY 1,2;

 

 

SELECT U.TABLESPACE_NAME "테이블 스페이스"
, U.BYTES / 1048576 "크기(mb)"
, (U.BYTES - SUM(NVL(F.BYTES,0))) / 1048576 "사용됨(mb)"
, (SUM(NVL(F.BYTES,0))) / 1048576 "남음(mb)"
, TRUNC((SUM(NVL(F.BYTES,0)) / U.BYTES) * 100,2) "남은 %"
, U.FILE_NAME "저장위치"
FROM DBA_FREE_SPACE F
, DBA_DATA_FILES U
WHERE F.FILE_ID(+) = U.FILE_ID
GROUP BY U.TABLESPACE_NAME
, U.FILE_NAME
, U.BYTES
ORDER BY U.TABLESPACE_NAME;

 

 

select file_name, tablespace_name, bytes, autoextensible
  from dba_data_files
  where tablespace_name='AMAIL_DATA_TS';

댓글을 달아 주세요

[ORACLE]이메일 도메인 걸러내기

Database 2012.11.22 17:36 Posted by 애플자라

SELECT SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL))) AS DOMAIN,
COUNT(SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))) AS DOMAIN_CNTS
FROM AUTO120
WHERE WORKDAY='20121116'
AND SEQNO=5
GROUP BY SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))
HAVING COUNT(SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))) > 0
ORDER BY COUNT(SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))) DESC

 

SELECT
DOMAIN,
COUNT(DOMAIN) AS DOMAIN_CNTS
FROM EBAD1060
WHERE POST_ID = '2008101400001'
GROUP BY DOMAIN
HAVING COUNT(DOMAIN) > 100
ORDER BY COUNT(DOMAIN) DESC

댓글을 달아 주세요

[ORACLE]전월 1일, 말일

Database 2012.11.22 14:01 Posted by 애플자라

SELECT TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE,-1)+1)-TO_CHAR(SYSDATE,'DD'),'YYYYMMDD')||TO_CHAR(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))+0.99999421,'YYYYMMDD') AS "대상기간" FROM DUAL

댓글을 달아 주세요

regexp_like 함수

【형식】
    regexp_like (search_string, pattern [,match_option])

【예제】
SQL> select * from test;
 
        ID NAME       EMAIL
---------- ---------- ------------------------------
         1 한라산     hanlasan@abc.co.kr
         2 백두산     backdusan@abc.co.kr
         3 금강산     gumgangsan@abc.co.kr
 
SQL> select * from test
  2  where regexp_like(name, '^[한백]'); ☜  name 필드에서 한 또는 백자로 시작하는 레코드를 찾는다.
 
        ID NAME       EMAIL
---------- ---------- ------------------------------
         1 한라산     hanlasan@abc.co.kr
         2 백두산     backdusan@abc.co.kr
 
SQL> select * from test
  2  where regexp_like(name,'강산$'); ☜  name 필드에서 강산자로 끝나는 레코드를 찾는다.
 
        ID NAME       EMAIL
---------- ---------- ------------------------------
         3 금강산     gumgangsan@abc.co.kr
 
SQL> where REGEXP_LIKE('ABCDEFGH','CD')  -- 조건에 포함되는 재료추출

regexp_substr 함수

 문자열에 존재하는 임의의 패턴을 추출하여 출력한다.

【형식】 
      REGEXP_SUBSTR (source_char, pattern 
                      [, position
                      [, occurrence
                      [, match_param
                      [subxpr]]]])

source_char 찾을 문자로 데이터타입은 :CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB임
pattern 512바이트까지의 정규표현식으로 데이터타입이 source_char과 다르면 pattern의 데이터타입을 source_char과 같게 변환
position 찾을 문자의 위치로 디폴트는 1이며, 이는 첫 글자부터 찾는 다는 의미
occurrence source_char에 pattern이 발생한 횟수로 디폴트는 1임
match_parameter 디폴트 매칭을 변경할 때 사용
subexpr 0∼9의 정수로 subexpression의 위치를 나타내며, 0은 pattern과 substring이 완전히 일치함을 의미

【예제】
SQL> select * from test;

        ID NAME                 EMAIL
---------- -------------------- ------------------------------
         1 Hello1234            hello@abc.c.kr
         2 you3456              you@abc.co.kr

SQL> select email, regexp_substr(email, '[^@]+') ID from test;

EMAIL                ID
-------------------- --------------
hello@abc.c.kr       hello
you@abc.co.kr        you


SQL> 
 문자열 중에서 @ 이전의 문자만 출력하는 예제이다.

【예제】
SQL> select REGEXP_SUBSTR('500  Oracle Parkway, Redwood Shores, CA',
  2                         ',[^,]+,') "REGEXP_SUBSTR"
  3  from dual;

REGEXP_SUBSTR
----------------------------------
, Redwood Shores,

SQL> select REGEXP_SUBSTR('http://www.oracle.com/products',
  2     'http://([[:alnum:]]+\.?){3,4}/?')
  3                       "REGEXP_SUBSTR"
  4  from dual;

REGEXP_SUBSTR
--------------------------------------------
http://www.oracle.com/

SQL> select REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))',1,1,'i',1)
  2                       "REGEXP_SUBSTR"
  3  from dual;   ☜ 첫 번째 subexpression

REGEXP
------
123

SQL> select REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))',1,1,'i',4)
  2                       "REGEXP_SUBSTR"
  3  from dual;  ☜ 4번째 subexpression

REGE
----
78

SQL>
참조 : http://bugcaps.springnote.com/pages/10827864.xhtml

댓글을 달아 주세요

[ORACLE]나이구하기쿼리

Database 2012.11.09 15:40 Posted by 애플자라

SELECT *
FROM
(
SELECT CUSTOMER_NM,
        SUBSTR(REGIDENT_ID, 7, 1),
        REGIDENT_ID,
        TRANSLATE (SUBSTR(REGIDENT_ID, 0, 2), 'X0123456789', 'X'),
        2012 - TO_NUMBER(DECODE(SUBSTR(REGIDENT_ID, 7, 1), '3', 20, '4', 20, 19) || SUBSTR(REGIDENT_ID, 0, 2)) AS AGE,
        REGISTER_DT
FROM CUSTOMER
WHERE SERVICE_CD1 ='Y'
AND ACTIVE_YN = '1'
AND TRANSLATE (SUBSTR(REGIDENT_ID, 0, 2), 'X0123456789', 'X') IS NULL -- 숫자만 가져오는
--AND TRANSLATE (SUBSTR(REGIDENT_ID, 0, 2), 'X0123456789', 'X') IS NOT NULL
-- 문자만 가져오는
) X
WHERE AGE > 65

 

SELECT DECODE (
          SIGN (
             TRANSLATE ('4312a',
                        '0123456789abcdefghijklmnopqrstuvwxyz',
                        '000000000011111111111111111111111111')),1, '문자','숫자')T
  FROM DUAL;

SELECT TRANSLATE ('123AB', 'X0123456789', 'X') FROM DUAL

댓글을 달아 주세요