레이블이 sql인 게시물을 표시합니다. 모든 게시물 표시

2012-10-07

DBMS/오라클]3489347_오라클_LOCK_확인_프로시저

select *
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',6, 'LOCK', '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'
and a.username = 'NIOTALKUSER'
;

select substr(c.object_name,1,20),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';

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'

DBMS/오라클] 오라클에서 해당 데이타베이스의 테이블 리스트 보는 명령어.

--// 오라클에서 해당 데이타베이스의 테이블 리스트 보는 명령어.
select * from dba_tables

/

// 오라클에서 해당 테이블의 컬럼 명
select * from USER_TAB_COLUMNS where table_name = '대문자테이블명';
/

select * from dba_objects WHERE OWNER='NCHANNEL' AND OBJECT_TYPE = 'INDEX';
/
select * from dba_objects WHERE OWNER='NCHANNEL' AND OBJECT_TYPE = 'TABLE';

DBMS/오라클]_권한_부여_생성_sql스크립트

--// 특정 테이블만 제외하고 select 권한주기

select 'grant select on '||table_name||' to 유저명;'
from ALL_TABLES /* select 권한만 */ .. 혹은
--from user_tables
where owner in ('유저01','유저02','유저03') /* 원본 유저 리스트 */
AND table_name NOT IN ('테이블1 ','테이블2'); /* 권한 제외할 테이블 리스트 */

--// billuser 에게 현재 로그인한 사용자의 모든 테이블에 대한 select 권한 주는쿼리문 생성
select 'grant select on ' || table_name || ' to billuser;' a
from user_tables
order by a
;

DBMS/오라클] 오라클 10g용db_crypto함수

--[EDIT BY SINU]

conn / as sysdba

create user sinu
identified by sinu;

grant resource, connect to sinu;
grant execute on DBMS_CRYPTO to sinu;

conn sinu/sinu

CREATE OR REPLACE PACKAGE pkg_crypto
IS
FUNCTION encrypt (
input_string IN VARCHAR2 ,
key_data IN VARCHAR2 := '12345678'
) RETURN RAW;

FUNCTION decrypt (
input_string IN VARCHAR2 ,
key_data IN VARCHAR2 := '12345678'
) RETURN VARCHAR2;

END pkg_crypto;
/

CREATE OR REPLACE PACKAGE BODY pkg_crypto
IS
SQLERRMSG VARCHAR2(255);
SQLERRCDE NUMBER;

FUNCTION encrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := '12345678')
RETURN RAW
IS

key_data_raw RAW(64);
converted_raw RAW(64);
encrypted_raw RAW(64);

BEGIN

converted_raw := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
key_data_raw := UTL_I18N.STRING_TO_RAW(key_data, 'AL32UTF8');

encrypted_raw :=
DBMS_CRYPTO.ENCRYPT(
src => converted_raw ,
typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,
key => key_data_raw ,
iv => NULL);

RETURN encrypted_raw;

END encrypt;

FUNCTION decrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := '12345678')
RETURN VARCHAR2
IS
converted_string VARCHAR2(64);
key_data_raw RAW(64);
decrypted_raw VARCHAR2(64);

BEGIN

key_data_raw := UTL_I18N.STRING_TO_RAW(key_data, 'AL32UTF8');

decrypted_raw :=
DBMS_CRYPTO.DECRYPT(
src => input_string ,
typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,
key => key_data_raw ,
iv => NULL);

converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');

RETURN converted_string;

END decrypt ;
END pkg_crypto;
/

col card_number format a64
create table card_info ( id number, card_number varchar2(64) ) ;

insert into card_info values ( 1 , pkg_crypto.encrypt('1234567812345678')) ;
insert into card_info values ( 2 , pkg_crypto.encrypt('12345678')) ;
commit;

select * from card_info ;
select id , pkg_crypto.decrypt(card_number) card_number
from card_info;

insert into card_info values ( 3 , pkg_crypto.encrypt('1234567812345678','00000000')) ;
select id , pkg_crypto.decrypt(card_number,'00000000') card_number from card_info where id = 3 ;

select * from card_info ;

drop package body pkg_crypto;
drop package pkg_crypto;
drop table card_info;

conn / as sysdba
drop user sinu cascade;

--[EDIT BY SINU]

DBMS/오라클]오라클공통 SQL

/** 테이블명 조회 */
SELECT * FROM ALL_TAB_COMMENTS --WHERE OWNER = 'VRM' ORDER BY COMMENTS
/
SELECT * FROM ALL_INDEX_COMMENTS --WHERE OWNER = 'VRM' ORDER BY COMMENTS
/
/** 컬럼명 조회 */
SELECT * FROM ALL_COL_COMMENTS WHERE OWNER = 'VRM' AND TABLE_NAME LIKE 'CMZG%' ORDER BY TABLE_NAME, COMMENTS

/** 테이블 조회*/
SELECT * FROM TAB

/** 휴지통 조회*/
SELECT * FROM RECYCLEBIN;

/** DROP TABLE 복구 */
FLASHBACK TABLE CSMG_IBUSPB01 TO BEFORE DROP;

/** 휴지통 미입력 삭제*/
DROP TABLE CSMG_IBUSPB01 PURGE;

/** DELETE 데이터 복구(15분전)*/
INSERT INTO CSMG_IBUSAD01
SELECT *
FROM CSMG_IBUSAD01
AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '15' MINUTE);

/** 이전테이터 조회*/
SELECT *
FROM CSMG_IBUSAD01
AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '15' MINUTE);

DBMS/오라클]sqlplus 에서 vi 편집기 사용하기

sqlplus 에서 vi 편집기 사용하기

단계1 오라클 사용자로 터미날 열어서 cd

단계2 ls -a

-- .bash_porfile 파일이 보일것이다. 이것을 조작한다.

단계3 geidt .bash_profile

--> export EDITOR=vi 추가 후 저장

단계 4 로그 아웃 후 다시 접속

DBMS/오라클]오라클_커서_사용법

원본 출처 :: http://netme.kr/bbs/board.php?bo_table=sub_2011&wr_id=23

1. 커서의 정의

커서란 SQL Plus에서 사용자가 실행한 SQL문의 단위를 의미합니다.
오라클렝서 수행한 모든 쿼리문은 커서 단위로 처리합니다.

PL/SQL의 SQL문처럼 하나의 결과를 리턴하는 경우 커서 없이도 SQL문의 실행결과가
암시적으로 커서에 저장되므로 이를 암시적 커서라고 합니다.

SQL문을 수행한 후에 결과로 얻어지는 행이 여러 개일 경우에는 암시적인 커서에 정보를
저장할 수 없기에 에러가 발생합니다. 이럴 경우에는 반드시 명시적인 커서를 사용해야 합니다.

명시적인 커서는 PL/SQL의 레코드(RECORD)와 PL/SQL의 테이블(TABLE)을 결합한 것으로서
프로그램 언어의 구조체 배열과 유사합니다.

[커서의 사용]

1) 커서를 선언한다.
CURSOR cur_name

2) 커서를 오픈한다.
OPEN cur_name

3) 커서에 조회한 결과를 인출해 지정한다.
FECTCH cur_name ...

4) 커서를 닫는다
CLOSE cur_name

2. 20번 부서에 근무하는 사원의 정보를 출력하는 예제

ed cur01

SET SERVEROUTPUT ON
declare
   vempno   NUMBER(4);
   vename   VARCHAR2(20);
   vsal   NUMBER(7, 2);

   CURSOR   C1
   IS
   select empno, ename, sal
   from emp
   where deptno=20;

begin
   OPEN C1;

   dbms_output.put_line('empno   ename   sal');

   LOOP
      FETCH C1 INTO vempno, vename, vsal;
      EXIT WHEN C1%NOTFOUND;

      dbms_output.put_line(to_char(vempno)||'   '||vename||'   '||to_char(vsal));
   END LOOP;

end;
/

저장하고 실행합니다.

@cur01
empno   ename   sal
7369   SMITH   800
...
...
...
...
...

PL/SQL procedure successfully completed.

3. OPEN-FETCH-CLOSE가 없이 커서 처리

ed cur02

SET SERVEROUTPUT ON
declare
   vemp   emp%ROWTYPE;

   CURSOR   C1
   IS
   select empno, ename, sal
   from emp
   where deptno=20;

begin
   dbms_output.put_line('empno   ename   sal');

   FOR   vemp   IN   C1   LOOP
      EXIT WHEN C1%NOTFOUND;

      dbms_output.put_line(to_char(vemp.empno)||'   '||vemp.ename||'   '||to_char(vemp.sal));
   END LOOP;

end;
/

정장하고 실행합니다.

@cur02
empno   ename   sal
7369   SMITH   800
...
...
...
...
...

PL/SQL procedure successfully completed.

4. 커서의 상태

%NOTFOUND   커서 영역의 자료가 모두 FETCH 됬는가를 알려줌
%FOUND      커서 영역에 FETCH가 되지 않은 자료가 있는가를 알려줌
%ISOPEN      커서가 OPEN된 상태인가를 알려줌
%ROWCOUNT   FETCH된 RECORD가 몇 개 있는지 알려줌

cur02 예제를 수정합니다.

ed cur02

SET SERVEROUTPUT ON
declare
   vemp   emp%ROWTYPE;

   CURSOR   C1
   IS
   select empno, ename, sal
   from emp
   where deptno=20;

begin
   dbms_output.put_line('empno   ename   sal   record count');

   FOR   vemp   IN   C1   LOOP
      EXIT WHEN C1%NOTFOUND;

      dbms_output.put_line(to_char(vemp.empno)||'   '||vemp.ename||'   '||to_char(vemp.sal)||'   '||C1%ROWCOUNT);
   END LOOP;

end;
/

저장하고 실행합니다.

@cur02

결과는 record count 추가되었습니다.

5. 커서를 활용한 실용 예제
1) 급여 총합을 구하는 예제
ed cur03

SET SERVEROUTPUT ON
declare
   tot   NUMBER := 0;

   CURSOR   emp_cursor
   IS
   select ename, sal
   from emp;

begin
   dbms_output.put_line('name   sal');
   dbms_output.put_line('------------------------------------------');

   FOR cur_var IN emp_cursor LOOP
      tot := tot + cur_var.sal;
      dbms_output.put_line(cur_var.ename);
      dbms_output.put_line('-   '||cur_var.sal);
   END LOOP;

   dbms_output.put_line('------------------------------------------');
   dbms_output.put_line('-   '||tot);
end;
/

저장하고 실행합니다

@cur03

2) 사원별 급여 현황을 그래포로 표현
ed cur04

SET SERVEROUTPUT ON
declare
   CURSOR   emp_cursor
   IS
   select ename, sal
   from emp
   order by sal desc;

   star   varchar2(100);
   cnt   number := 0;

begin
   dbms_output.put_line('   sal of emp');
   dbms_output.put_line('------------------------------------------');

   FOR cur_var IN emp_cursor LOOP
      star := NULL;
      cnt := round(cur_var.sal/100, 0);

      for i in 1.. cnt loop
         star := star||'*';
      end loop;

   dbms_output.put_line(cur_var.ename);
   dbms_output.put_line('-   '||star||'('||cur_var.sal||')');
   END LOOP;
end;
/

저장하고 실행합니다

@cur04

2012-10-06

DBMS/오라클]SQL_Loader_사용법

출처 : http://blog.naver.com/hirokorea/20020980191
         http://nhmjh.egloos.com/1438719
문법
      SQLLDR [keyword=] value [ [keyword=] value ]...
예제
      SQLLDR scott/tiger control='c:\xxx.ctl' log='xxx.log' direct=true
키워드
USERID
  오라클 사용자 이름과 암호를 지정 합니다.

CONTROL
  콘트롤 파일 이름, SQL*Loader을 수행하기 위해서는 항상 지정해 주어야 합니다.

LOG
  로그 파일 이름을 지정 합니다. (기본 이름은 controlfile.log)

BAD
  거 부된 레코드 모두를 저장하는 배드 파일 이름을 지정 합니다.

DATA
  입력 데이터 파일 이름을 지정 합니다.

DISCARD
  Load시 선택되지 않은 레코드가 저장되는 디스카드 파일(선택 사항)

DISCARDMAX
  버림(discard)의 최대 허용 갯수를 지정 합니다.

ERRORS
  허용하 는 배드 레코드의 최대 수를 지정 합니다.

DIRECT
  TRUE로 설정되면 SQL*Loader는 DIRECT PATH를 사용.
반대의 경우는 기본 값인 CONVENTIONAL PATH를 사용 합니다.

PARFILE
  추가 파라미터 파일을 지정 합니다.

PARALLEL
  DIRECT 로드에서만 적합한 이 파라미터는 다중 병렬 DIRECT로드가
수행되도록 지정 합니다.

FILE
  병렬 DIRECT로드의 경우 임시 세그먼트가 생성될 파일을 지정 합니다.
①　SAMPLE TABLE 생성
-------------------------------------------------------------------------
CREATE TABLE TMP(
    SEQ_NUM     VARCHAR2(10) ,
    UNIQ_KEY    CHAR(7) ,
    RECORD_NUM NUMBER(7,0),
    ISS_TIME    DATE,
    IF_STTS     CHAR(2),
    MEMO        VARCHAR2(100)
)
/
②　SAMPLE 시퀀스 생성
-------------------------------------------------------------------------
CREATE SEQUENCE TMP_SEQ
INCREMENT BY 1
START WITH 1
MAXVALUE 10000000000000000
NOMINVALUE
CYCLE
CACHE 20
NOORDER
/
③　SAMPLE 데이터 파일 생성
-------------------------------------------------------------------------
C:\S_DATAFILE.DAT
      120060123125959MEMO1
     2220060124125959MEMO2
    33320060125125959MEMO3
④　SAMPLE SQLLOADER CONTROLFILE 생성
-------------------------------------------------------------------------
C:\S_DATAFILE.CTL
Options (Rows = 1000)
Load DATA
INFILE     'C:\S_DATAFILE.DAT'
BADFILE  'C:\S_DATAFILE.BAD'
APPEND
    INTO TABLE TMP (
        -- SEQ_NUM컬럼의 최대값 +1을 대입
        SEQ_NUM            SEQUENCE(MAX,1) ,
        -- 시퀀스 TMP_SEQ의 다음할당값 대입
        UNIQ_KEY            TMP_SEQ.NEXSVAL ,
        RECORD_NUM      POSITION( 1 : 7   ) DECIMAL EXTERNAL ,
        ISS_TIME             POSITION( 8 : 21 ) DATE "YYYY/MM/DD HH:MI:SS" ,
        IF_STTS               CONSTANT "00" ,
        MEMO                 POSITION( 22 : 116   ) CHAR
    )
⑤　SQLLOADER 실행
-------------------------------------------------------------------------
C:>sqlldr scott/tiger@XXXcontrol = C:\S_DATAFILE.CTL
//-----------------------------------------------------------------------------
출처 : http://cafe.naver.com/superadmin.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=42
sqlloader를 사용하기 위해서는 미리 테이블이 만들어져 있어야 함.

sqlloader 화일 샘플

#!/bin/csh
if ( $#argv != 2) then
    echo "Usage: $0 <DataFile> <logFileName>"
    exit 1
endif

sqlldr userid=oracle/oracle control=tblmacs_tmpidrlog.ctl data=$1 log=$2.log commit_discontinued=true

data는 raw데이타 들어갈 부분, log는 생략해도 무방..., commit으로 시작하는 부분도 생략가능

실행방법

sqlloader.sh "실제데이타 화일"
ex) sqlloader.sh  SWBLS1_FWEBIDR1_ID0001_T20030603000109.DAT

이런식으로 사용 가능(원격폴더 지정시)
## sqlloader.sh /bear02/macslog/20030401/SWBLS1_FWEBIDR1_ID0001_T20030401000110.DAT /bear01/brewlog/src/eshock/log/z0000

control화일 샘플

load data
INFILE *
append
into table TBLMACS_TMPIDRLOG
when RECORD_TYPE='R'
(
        RECORD_TYPE position(01:01) char
       ,RECORD_SEQ  position(02:08) char
       ,SVC_TYPE    position(09:09) char
       ,CALL_NUM    position(10:20) char
       ,CHARGE_NUM  position(21:31) char
       ,CP_NO       position(32:36) char
       ,CONTENTS_NO position(37:38) char
       ,MENU_NO     position(39:40) char
       ,CALL_START  position(41:54) char
       ,DURATION    position(55:63) char
       ,PKT_CNT     position(64:72) char
       ,PHONE_TYPE  position(73:87) char
       ,BROWSER_VER position(88:102) char
)

position은 순서대로 1부터 1까지 자름, 그다음 2칸째부터 8칸 이후까지를 의미함.

[ SQL*Loader 사용하기 ]
작성일자 : 2001년 6월 11일
작 성 자 : 조수환

1. SQL*Loader를 이용해서 Data를 넣을 파일을 Excel에서 생성한다.
        - 파일을 *.csv로 저장을 한다.
          (SQL*Loader 를 사용하기 위해서는 ,(콤마)가 필요하다)
        즉, 다음과 같은 데이타 포멧이면 된다.
                10000,CEO, 등록
                10020,CEO CEO실,등록
                150000,솔루션사업부 문,등록
                150010,솔루션사업부문 신프라팀,등록
                150020, 솔루션사업부문 기관영업팀,등록
                150030,솔루션사업부문 사업지원팀,등록
                150040, 솔루션사업부문 CS팀,등록

2. csv 파일에는 departcode, departname, gubun 의 형식을 데이타가 들어가 있다.

        Ex>        departcode,         departname,         gubun
                ---------- ------------------------------
                   180040         경영전략부문         홍보팀
                   190000         재경부문
                   190010         재경부문         재경기획팀
                   190020         재경부문         재경팀
                   190030         재경부문         물류지원팀
                   200000         인재기획실

3. Data를 넣을 테이블의 명세서는 다음과 같다.
        SQL> desc departinfo;
         이름       널?      유형
         ---------- -------- ------------
         ID         NOT NULL NUMBER
         DEPARTCODE              NUMBER(6)
         DEPARTNAME          VARCHAR2(40)
         CREATEDATE          DATE
         MODIFYDATE          DATE
         GUBUN               CHAR(4)


4. 콘트롤 파일(abc.ctl) 을 하나 만들고, 다음과 같은 내용을 기록한다.

        LOAD DATA
        INFILE '부서코드.csv'                        -- 참조할 파일은 부서코드.csv
        replace                                        -- 테이블의 내용을 모두 삭제한 후에 입력
        INTO TABLE departinfo                        -- 데이타가 들어갈 테이블 명
        fields terminated by ','                -- ,(콤마)를 기준으로 구분한다.
        (
          id                sequence(1,1),                -- 1부터 1씩 증가시킨다.
          departcode        integer external,        -- 외부의 데이타를 가져온다.
          departname        char(40),                -- 문자열은 40
          createdate        sysdate,                -- Load를 할 때 sysdate값을 넣어준다.
          modifydate    sysdate,                -- Load를 할 때 sysdate값을 넣어준다.
          gubun        char(4)                                -- 문자열은 4
        )

5. SQL*Loader를 실행시킨다.
        # sqldlr atomx/akstp@sysora control=abc.ctl log=0611.log
        - > 위와 같이 하면 부서코드.csv의 내용이 conrolfile의 정의와 함께
            테이블에 쭉 들어간다.

< 참조> 다른 컨트롤 파일의 내용

1. 컨트롤 파일(bbb.ctl)의 내용
        LOAD DATA
        INFILE '사원인사.csv'
        replace
        INTO TABLE companyinfo
        fields terminated by ','
        (
          id                sequence(1,1),
          companycode        integer external,
          name                char(20),
          departcode        integer external,
          tel                char(4) nullif tel = blanks,        -- 만일 공백이면 NULL상태로 만든다.
          enteringdate        date ,
          createdate        sysdate,
          modifydate    sysdate,
          gubun        char(4)
        )

2. sqlldr atomx/akstp@sysora control=bbb.ctl log=0611.log

//-----------------------------------------------------------------------------------
출처 : http://www.dbguide.net/dbqa/dbqa120001.jsp?mode=view&key=subject&search=Loader&divcateno=256&divcateno_=243&pg=1&idx=1
SQL*Loader 는 외부 화일의 데이타를 ORACLE 데이타베이스의 table에 넣기 위한 유틸리티입니다. SQL*Loader를 사용하려면 외부 데이타 화일과 컨트롤 화일이 필요합니다. 컨트롤 화일이라고 하는 것은 로드하는 데이타의 정보를 저장한 화일입니다.
간단한 샘플 컨트롤 화일을 설명하겠습니다.

    load data                        제어 화일의 선두에는 반드시 이 말이 필요합니다.
    infile sample.dat              외부 화일을 지정합니다.
    replace                           테이블에 데이타 넣는 방법 지정
    into table TABLE_NAME   데이타를 로드하는 테이블을 지정
    fields terminated by ','     데이타 필드의 종결 문자 지정
    (a integer external,          테이블의 열, 외부 데이타 화일의 데이타 형을 지정
     b char)

    참고로 replace 외에 다음의 옵션이 가능합니다.

    replace                 테이블의 기존 행을 모두 삭제(delete)하고 insert
    append                 새로운 행을 기존의 데이타에 추가
    insert                   비어 있는 테이블에 넣을 때
    truncate                테이블의 기존 데이타를 모두 truncate 하고 insert

    SQL*Loader를 실행하면 아래의 화일이 작성됩니다.

    * 로드 작업 중 동작에 관한 통계 등을 포함한 로그 화일(확장자는 log)
    * 데이타 에러 때문에 로드가 안된 레코드를 저장한 화일(확장자는 bad)
    * 사용자의 선택 기준에 적합하지 않은 레코드를 저장한 화일(discard 화일)
      이것은 discardfile 옵션으로 별도로 지정해야 생성됩니다.

실 행 방법은 다음과 같습니다.

$sqlldr scott/tiger control=sample.ctl data=sample.dat1.1 임의의 열에 변화없는 고정 문자열(값)을 입력한 경우테이블 구조
    create table cons_test
    (a number,
     b number,
     c number,
     d varchar(10))

컨 트롤 화일 (즉 이예에서 sample.ctl)
    load data
    infile cons.dat
    replace
    into table cons_test
    fields terminated by ','
    (a integer external,
     b integer external,
     c CONSTANT '100',
     d char)

외부 데이타 화일 (즉, 이예에서 sample.dat)
    1,2,DATA
    2,4,DATA2

검색결과
    SQL>select * from cons_test;

           A       B       C D
    ---------------------------------
           1       2     100 DATA
           2       4     100 DATA2

주 의사항 : 이 예에서 C열은 데이타 화일에 넣어서는 안 됩니다. COSNTANT는 그것으로 완결된 열 지정의 하나가 됩니다. integer external 데이타 형은 수치 데이타를 문자형식(ASCII CODE)로 나타낸 것입니다.
1.2 로드한 때의 날짜를 데이타로 로드하고 싶은 경우테 이블 구조
   create table sysdatetb
    (a number,
    b date,
    c varchar(10))

컨트롤 화일
    load data
    infile sysdate2.dat
    replace
    into table sysdatetb
    fields terminated by ','
    (a integer external,
    b sysdate,
    c char(10))

외부 데이타 화일
    111,STRINGS
    222,STRINGS2

검 색결과
SQL>select * from sysdatetb;

        A B         C
-----------------------------
      111 13-MAY-94 STRING
      222 13-MAY-94 STRING2

주의사항 : 이 예에서는 B열은 데이타 화일에 넣어서는 안됩니다. SYSDATE는 그것으로 완결된 열 지정의 하나가 됩니다. 새로운 시스템 날짜매김은 컨벤셔널 패스에서는 실행 시에 삽입된 각각의 레코드 배열마다 또, 다이렉트 패스의 경우는 로드된 각각의 레코드의 블럭마다 사용됩니다.
1.3 임의의 수치열에 연속 번호(sequence)를 붙이고 싶은 경우테이블 구조
    create table rectb
    (a varchar(10),
    b number,
    c varchar(10))

컨트롤 화일
    load data
    infile rec.dat
    replace
    into table rectb
    fields terminated by ','
    (a char,
    b recnum,
    c char)

외부 데이타 화일
   A,a
    B,b
   C,c

검색결과
SQL>select * from rectb;

   A       B C
    ---------------------
   A       1 a
   B       2 b
   C       3 c

주의사항 : 이 예에서는 B 열은 데이타 화일에 넣어서는 안됩니다. RECNUM는 그것으로 완전한 열 지정의 하나가 됩니다. 연속번호는 1부터 차례대로 1씩 더해져서 번호가 매겨집니다. 가산된 번호를 둘씩 건너 뛰거나 하는 것은 불가능 합니다.
1.4 임의의 수치열에 연속 번호(sequence)를 임의의 간격으로 붙이고 싶은 경우테이블 구조
    create table seqtb
    (a varchar(10),
    b number,
    c varchar(10))

컨트롤 화일
    load data
    infile seq.dat
    replace
    into table seqtb
    fields terminated by ','
    (a char,
    b sequence(100,5),
    c char)

외부 데이타 화일
    1,a
    2,b
    3,c

검색결과
    SQL>select * from seqtb;

    A         B C
    -------------------------
    1       100 a
    2       105 b
    3       110 c

주의 사항 : 이 예에서는 B열은 데이타 화일에 넣어서는 안 됩니다. SEQUENCE는 그것으로 완결된 열 지정의 하나가 됩니다. 초기 값 100과 늘인 값 5는 다른 수치로 변경 가능합니다.

1.5 로드하는 논리 레코드를 구성하는 물리 레코드가 복수열로 구성된 경우
     (물리 레코드의 1바이트 째로 판단되는 경우)

테이블 구조
    create table conti_test
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨트롤 화일
    load data
    infile conti.dat
    replace
   continueif this
   (1) = '%'
   into table conti_test
   fields terminated by ','
   (a char,
   b char,
   c char)

외부 데이타 화일
%1,
%2,
3
%A,B
,C
%a,b
%c
%d
,ef

검색결과
    SQL>select * from conti_test;

    A      B      C
    ---------------------
    1      2      3
    A      B      C
    a      bcd    ef

주의사항 : 이 예의 경우 1바이트 째가 계속 행의 체크를 위해서 사용되기 때문에, 실 데이타를 1 바이트 째부터 시작해서는 안됩니다. 상기 예의 경우, 레코드의 선두 바이트가 '%'일 때 다음의 레코드가 연결됩니다.

1.6 외부 데이타 화일의 물리 레코드가 복수 레코드로 구성된 경우
     (구성하는 물리 레코드 수가 모두 일정한 경우)

테이블 구조
    create table con_test
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨 트롤 화일
    load data
    infile conti.dat
    replace
    --일례로 모든 논리레코드가 그 레코드로 구성됩니다.
    concatenate 2
   into table con_test
    fields terminated by ','
    (a char,
    b char,
    c char)

외부 데이타 화일
    1,2,
    3
    a,b,
    c
    A,
    B,C

검색결과
    SQl) select * from con_test;

    A       B       C
    -------------------------
    1       2       3
    a       b       c
    A       B       C

주의사항 : 하나의 논리 레코드가 모두 일정한 갯수의 물리 레코드로부터 성립되는 것 같은 단순한 경우에 한합니다.

1.7 데이타의 잘린 문자를 데이타로 로드하고 싶은 경우테 이블 구조
    create table enc
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨트롤 화일
    load data
     infile enc.dat
    replace
    into table enc
    fields terminated by ',' optionally enclosed by ' " ' and ' " '
    (a char,
    b char,
    c char)

외부 데이타 화일
"abc,d",2,3
   "a,,d",4,5

검색결과
SQL>select * from enc;

    A       B       C
    -------------------------
    abc,d   2       3
    a,,d    4       5

1.8 포지션 지정 시 char 형 데이타 전후의 blank도 로드하고 싶은 경우테이블 구조
    create table pretb
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨트롤 화일
    load data
    infile pre.dat
    replace
   preserve blanks
    into table pretb
    (a position(01:05) char,
    b position(06:10) char,
    c position(11:20) char)

외부 데이타 화일
    12 4 67890 ab def hi
      2   67890 ab def hi

검색 결과
    SQL>select * from pretb;

    A       B       C
    --------------------------
    12 4    67890   ab def hi
      2       67890   ab def hi

결과 확인
    SQL>select length(a), length(c) from pretb;

    LENGTH(A) LENGTH(C)
    -------------------
             5        10
             5        10

1.9 어떤 데이타 열의 데이타 유무와 상관없이 데이타가 없는 경우 NULL 데이타를 넣도록 하고 싶다테 이블 구조
    create table tratb
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨트롤 화일
    load data
    infile tra.dat
    replace
    into table tratb
    fields termintated by ','
    trailing nullcols
    (a char,
    b char,
    c char)

외부 데이타 화일
    1,aa,
    2,bb,FF
    3,cc,

검색결과
    SQL>select * from tratbl

    A       B       C
    ------------------------
    1       aa
    2       bb      FF
    3       cc

주의사항 : trailing nullcols를 사용하지 않으면 1 레코드째와 3 레코드째가 데이타 에러가 됩니다. 데이타가 들어 있기도 하고 없기도 한 열의 데이타는 데이타 화일의 최후로 가져갑니다.

1.10 CHAR 형 필드가 BLANK로 채워져 있을 때 NULL을 삽입하고 싶은 경우테이블 구조
    create table nulltb
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨트롤 화일
    load data
    infile null.dat
    replace
    into table nulltb
    fields terminated by ','
    (a char,
    b char,
    c char(10) nullif c = blanks)

외부 데이타 화일
    aa,bb, ,
    11,22, ,
    99,88,AA
    00,00,BB

검색결과
    SQL>select * from nulltb;

    A       B       C
    -------------------------
    aa      bb
    11      22
    99      88      AA
    00      00      BB

주의 사항 : 검색결과의 1 행째, 2 행째의 열 C는 블랭크가 아니라 NULL 입니다.
1.11 DATE 필드가 BLANK로 채워져 있을 때 NULL을 삽입하고 싶은 경우**** 테이블 구조 ****
create table nulltb2
(a varchar(10),
b varchar(10),
c date)

**** 콘트롤 화일 ****
load data
infile null2.dat
replace
into table nulltb2
fields terminated by ','
(a char,
b char,
c date "YY/MM/DD" nullif c = blanks)

**** 외부 데이타 화일 ****
aa,bb, ,
11,22, ,
99,88,92/11/11,
00,00,94/12/12,

**** 검색결과 ****
SQL>select * from nulltb2;

A       B       C
-------------------------
aa      bb
11      22
99      88      92/11/11
00      00      94/12/12

주의사항 : 검색결과의 1 행째, 2 행째의 열 C는 블랭크가 아니라 NULL 입니다.
1.12 POSITION 지정 시 BLANK를 그대로 로드하고 싶은 경우**** 테이블 구조 ****
create table nulltb2
(a varchar(10),
b varchar(10),
c date)

**** 콘트롤 화일 ****
--- position 지정으로 블랭크를 그대로 입력 원하는 경우
--- preserve blanks를 지정한다.
load data
infile null3.dat
replace
preserve blanks
into table nulltb2
(a position(1:2) char,
b position(3:4) char nullif b = blanks,
c position(5:13) date "YY/MM/DD")

**** 외부 데이타 화일 ****
998892/11/11
    94/12/12

**** 검색결과 ****
SQL>select * from nulltb2;

A       B       C
-------------------------
99      88      92/11/11
                94/12/12

SQL>select length(a), length(b) from nulltb2;

LENGTH(A) LENGTH(B)
-------------------
        2         2
        2

주의사항 : 이 경우 2 레코드째는 A에 블랭크가 들어가고 B에 NULL이 들어갑니다.

1.13 데이타 화일의 수치 데이타 열에 BLANK가 들어가 있을 때 0을 입력하고 싶은 경우**** 테이블 구조 ****
create table def2
(a varchar(10),
b varchar(10),
c number)

**** 콘트롤 화일 ****
load data
infile def2.dat
replace
into table def2
fields terminated by ','
(a char,
b char,
c integer external defaultif c = blanks)

**** 외부 데이타 화일 ****
11,11,123
22,22, ,
33,33, ,
44,44, ,

**** 검색결과 ****
SQL>select * from deft;

A       B              C
------------------------
11      11           123
22      22             0
33      33             0
44      44             0

1.14 데이타가 NULL일 때 NULL이라고 하는 문자열을 넣고 싶은 경우**** 테이블 구조 ****
create table ifnulltb
(a varchar(10),
b varchar(10),
c varchar(10))

**** 콘트롤 화일 ****
load data
infile ifnull.dat
replace
into table ifnulltb
fields terminated by ','
(a char,
b char "nvl(:b,'NULL')",
c char)

**** 외부 데이타 화일 ****
1,2,3,
A,,B
a,b,c

**** 검색결과 ****
SQL>select * from ifnulltb;

A       B       C
-------------------------
1       2       3
A       NULL    B
a       b       c

주의 사항 : NVL과 같은 SQL 함수는 DIRECT LOAD의 경우 SQL 인터페이스를 경유하지 않기 때문에 사용할 수 없습니다.

1.15 어떤 열을 모두 대문자(소문자)로 변환하여 로드하고 싶은 경우**** 테이블 구조 ****
create table uptb
(a varchar(10),
b varchar(10))

**** 콘트롤 화일 ****
load data
infile upper.dat
replace
into table uptb
fields terminated by ','
(a char "lower(:a)",
b char "upper(:b)")

**** 외부 데이타 화일 ****
aBcDeFg,AbCdEf
ccDD11,ffGG22

**** 검색결과 ****
SQL>select * from uptb;

A         B
--------------------
abcdefg   ABCDEF
cdd11     FFGG22

1.16 ZONE 형 데이타를 로드하고 싶은 경우ZONE 의 데이타 형식은 1문자 1바이트로 나타납니다.

숫자 +123이라면

bit     1234    5678    1234    5678    1234    5678
        1111    0001    1111    0010    1100    0011
10진            1               2         +        3

최후의 8비트의 상위 4비트(부호비트)로 정부가 지정됩니다.

       1100(정) 1101(부)

가 됩니다.

원래는 EBCDIC코드에서

Hex F0 to F9 → +0 to +9
Hex C0 to C9 → +0 to +9(부호비트)
Hex D0 to D9 → +0 to -9(부호비트)

이것을 아스키로 변환했기 때문에

부호비트        {ABCDEFGHI}JKLMNOPQR
------------------------------------
부 호            ++++++++++----------
수              01234567890123456789

가 됩니다.

**** 테이블 구조 ****
create table z
(a varchar(10),
i number,
j number)

**** 콘트롤 화일 ****
load data
infile filename.dat
replace
into table z
(a position (01) char,
i position(02:07) zoned(6),
j position(08:13) zoned(6))

**** 외부 데이타 화일 ****
A12345{12345}

**** 검색결과 ****
SQL>select * from z;

A            I        J
-----------------------
A       123450 -123450

1.17 decimal 형 데이타를 로드하고 싶은 경우decimal 데이타는 1바이트에 2개의 숫자가 지정됩니다.

숫자 +123 이라면

bit    1234 5678 1234 5678
       0001 0010 0011 1100
10진      1    2    3    +

끝의 4비트는 부호비트로 불리고

     1100(정)        1101(부)

가 됩니다.

**** 테이블 구조 ****
create table dec
(a varchar(10),
col1 number,
col2 number)

**** 콘트롤 화일 ****
load data
infile filename.dat
replace
into table dec
(a position (01:01) char,
col1 position(02:05) decimal(7,4),
col2 position(06:09) decimal(7,4))

**** 외부 데이타 화일 ****
데이타화일은 바이너리이기 때문에 more 등으로 볼 수 없습니다.

4112 3456 7c12 3456 7d0a

**** 검색결과 ****
SQL>select * from dec;

A               COL1        COL2
--------------------------------
A           123.4567   -123.4567

주 의 사항 :콘트롤 화일에서 decimal 형을 사용하는 것에 따라 (length, scale)을 지정합니다.

1.18 고정 길이 레코드 데이타를 로드하고 싶은 경우**** 테이블 구조 ****
create table fixtb
(a varchar(10),
b varchar(10))

**** 콘트롤 화일 ****
load data
infile fix.dat
"FIX 6"
replace
into table fixtb
(a char(3),
b char(3))

**** 외부 데이타 화일 ****
123456abcdef

**** 외부 데이타 화일(16진) ****
3132 3334 3536 6162 6364 6566

**** 검색결과 ****
SQL>select * from fixtb;

A      B
---------------
123    456
abc    def

주 의 사항 : 데이타 화일의 끝에는 OxOa(CARRIAGE RETURN)가 들어가면 안 됩니다. OxOa도 1문자로 카운트되기 때문입니다.

# by 꾸술 | 2006/10/25 10:11 | 백업&리커버리 | 트랙백 | 덧글(3)

트랙백 주소 : http://nhmjh.egloos.com/tb/1438719

DBMS/오라클] Oracle에서_Split_생성해서_사용

출처 : http://mukeabi.egloos.com/2342599

우선 Table 타입을 선언합니다.

create or replace type split_tbl as table of varchar2(32767);
/

함수를 선언합니다.

create or replace function split
(
p_list varchar2,
p_del varchar2
) return split_tbl pipelined
is
l_idx    pls_integer;
l_list    varchar2(32767) := p_list;
l_value    varchar2(32767);
begin
loop
   l_idx := instr(l_list,p_del);
   if l_idx > 0 then
   pipe row(substr(l_list,1,l_idx-1));
   l_list := substr(l_list,l_idx+length(p_del));

   else
   pipe row(l_list);
   exit;
   end if;
end loop;
return;
end split;
/

사용법은

select * from table(split('1 2 3 4 5 6 7 8 9 10', ' '));

입니다.

출처: http://www.dulihana.com/blog/126

DBMS/오라클]Oracle에서_Split_-_쿼리로_만들기

출처 : http://syronia.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-Split-%EA%B8%B0%EB%8A%A5-%EA%B5%AC%ED%98%84%ED%95%9C-Query

오라클 FUNCTION기능을 이용하는 방법도 있지만 쿼리로 구현해보았다.

구분자가 |일 경우 ------------------------

SELECT substr(wdata,
instr(wdata, '|', 1, LEVEL) + 1,
instr(wdata, '|', 1, LEVEL + 1) - instr(wdata, '|', 1, LEVEL) - 1) name
FROM (
SELECT '|' || 'Kim|Lee|Park' || '|' wdata
FROM DUAL
)
CONNECT BY LEVEL <= length(wdata)

- length(REPLACE(wdata, '|')) - 1

만약 구분자가 2개라면 ---------------

SELECT substr(wdata,
instr(wdata, '||', 1, LEVEL) + 2,
instr(wdata, '||', 1, LEVEL + 1) - instr(wdata, '||', 1, LEVEL) - 2) id
FROM (
SELECT '||' || 'babo||kim||hello||home' || '||' wdata
FROM dual
)
CONNECT BY LEVEL <= (length(wdata) - length(REPLACE(wdata, '||')))/2 - 1

DBMS/오라클]Oracle Database 10g DBA를 위한 20가지 주요 기능

Scheduler

dbms_job 패키지의 실행 주기 설정을 수작업으로 관리하는 것이 번거로우십니까? 이제 10g데이타베이스가 제공하는 Scheduler를 이용해 보십시오.

여러분들 중 일부는 백그라운드 데이타베이스 작업 스케줄을 설정하기 위해 dbms_job 패키지를 사용하고 있을 것입니다. 하지만 필자가 알기로는 대부분의 DBA들이 dbms_job 패키지를 사용하지 않고 있습니다.

이 패키지는 PL/SQL 코드 세그먼트만을 처리할 수 있으며, 데이타베이스 외부의 운영체제 파일 또는 실행 파일 이미지를 처리할 수 없다는 기능적 한계를 갖고 있습니다. 이 때문에 DBA들은 Unix의 cron, Windows의 AT 명령 등을 사용하여 운영 체제 레벨에서 스케줄링을 설정하는 방법을 선택합니다. 또는 그래픽 사용자 인터페이스를 제공하는 써드 파티 툴을 사용하기도 합니다.

그 러나 dbms_job은 이들과 다른 장점들이 있습니다. 이 중 하나는 데이터베이스가 실행되고 있는 경우에만 활성화된다는 것입니다. 만약 데이터베이스가 다운되어 있다면 해당 Job들은 실행되지 않습니다. 데이터베이스 외부에 존재하는 툴들은 수작업을 통해 데이터베이스가 실행되고 있는지 조사되어야 하며, 복잡한 작업일 수 있습니다. Dbms_job의 다른 장점으론 데이터베이스 내부에 존재한다는 것입니다. 그러므로 SQL*Plus와 같은 유틸리티를 이용해 쉽게 접근 가능하다는 것입니다.

Oracle Database 10g Scheduler는 모든 종류의 작업을 지원하는 내장형 작업 스케줄러 유틸리티를 제공합니다. 10g Scheduler의 가장 큰 장점은, 데이타베이스에 포함된 형태로 제공되므로 추가적인 비용이 들지 않는다는 것입니다. 이번 연재에서는 Scheduler의 기능에 대해 자세히 살펴보기로 합니다.

Creating Jobs Without Programs

개념의 이해를 돕기 위해 예를 통해 설명하겠습니다. 아카이브 로그 파일을 다른 파일시스템으로 이동하기 위해 아래와 같은 이름의 셸 스크립트를 생성했다고 가정해 봅시다:

/home/arup/dbtools/move_arcs.sh

별도의 프로그램을 생성하지 않고도 작업 유형으로 “Executable”을 지정하고 스케줄링을 설정할 수 있습니다.


 

begin

   dbms_scheduler.create_job

   (

      job_name      => 'ARC_MOVE_2',

      schedule_name => 'EVERY_30_MINS',

      job_type      => 'EXECUTABLE',

      job_action    => '/home/arup/dbtools/move_arcs.sh',

      enabled       => true,

      comments      => 'Move Archived Logs to a Different Directory'

   );

end;

/

또 Schedule Name을 설정하지 않고 작업을 생성하는 것도 가능합니다.


 

begin

   dbms_scheduler.create_job

   (

      job_name        => 'ARC_MOVE_3',

      job_type        => 'EXECUTABLE',

      job_action      => '/home/arup/dbtools/move_arcs.sh',

      repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',

      enabled         => true,

      comments        => 'Move Archived Logs to a Different Directory'

   );

end;

/

앞의 두 가지 예를 통해 dbms_job과 비교했을 때의 Scheduler의 장점을 확인할 수 있습니다. Scheduler를 이용하면 PL/SQL 프로그램뿐 아니라 OS 유틸리티와 프로그램을 실행하는 것이 가능합니다. 이러한 기능을 활용하여 범용적인 데이타베이스 작업 관리 환경을 구현할 수 있습니다. 또 Scheduler는 자연 언어(natural language)를 사용하여 실행 주기를 정의할 수 있다는 매우 중요한 이점을 제공합니다. 스케줄을 매 30분 단위로 실행하고자 하는 경우, (PL/SQL 문법 대신) 자연 언어 형식의 표현을 사용하여 REPEAT_INTERVAL 매개변수를 아래와 같이 정의할 수 있습니다:

'FREQ=MINUTELY; INTERVAL=30'

좀 더 복잡한 예를 들어 설명해 보겠습니다. 운영 중인 애플리케이션이 오전 7시에서 오후 3시까지의 시간대에 집중적으로 사용된다고 가정해 봅시다. Statspack을 실행하면 월요일~금요일 오전 7시 ~ 오후 3시의 시스템 통계를 수집할 수 있습니다. DBMS_JOB.SUBMIT을 사용하여 작업을 생성하는 경우라면, NEXT_DATE 매개변수는 아래와 같이 정의됩니다:


 

DECODE

(

   SIGN

   (

      15 - TO_CHAR(SYSDATE,'HH24')

   ), 

   1,

      TRUNC(SYSDATE)+15/24,

   TRUNC

   (

      SYSDATE +

      DECODE

      ( 

          TO_CHAR(SYSDATE,'D'), 6, 3, 1

      )

    )

    +7/24

)

위의 코드가 이해하기 쉽습니까? 전혀 그렇지 않습니다.

이번에는 DBMS_SCHEDULER를 이용하는 방법을 살펴 보겠습니다. REPEAT_INTERVAL 매개변수는 아래와 같이 간단하게 정의됩니다:

'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15'

이 매개변수를 이용해 실행 주기를 다양한 형태로 설정할 수 있습니다. 몇 가지 예가 아래와 같습니다:

● 매월 마지막 일요일:
FREQ=MONTHLY; BYDAY=-1SUN

● 매월 세 번째 금요일:
FREQ=MONTHLY; BYDAY=3FRI

● 매월 뒤에서 두 번째 금요일
FREQ=MONTHLY; BYDAY=-2FRI

마이너스(-) 기호는 숫자가 뒤에서부터 계산된다는 의미입니다.

설정된 실행 주기가 올바른지 확인하려면 어떻게 해야 할까요? 캘린더 문자열을 이용해서 날짜를 미리 확인할 수 있다면 편리하지 않을까요? EVALUATE_CALENDAR_STRING 프로시저를 이용하여 실행 예정 시각을 미리 검토할 수 있습니다. 위의 예 ? 월~금7:00 AM ~ 3:00 PM Statspack 실행 ? 에서 설정된 실행 주기를 확인하는 방법이 아래와 같습니다:


 

set serveroutput on size 999999

declare

   L_start_date    TIMESTAMP;

   l_next_date     TIMESTAMP;

   l_return_date   TIMESTAMP;

begin

   l_start_date := trunc(SYSTIMESTAMP);

   l_return_date := l_start_date;

   for ctr in 1..10 loop

      dbms_scheduler.evaluate_calendar_string(

        'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15',

         l_start_date, l_return_date, l_next_date

      );

      dbms_output.put_line('Next Run on: ' ||

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

      );

      l_return_date := l_next_date;

   end loop;

end;

/

실행 결과는 다음과 같습니다:

Next Run on: 03/22/2004 07:00:00
Next Run on: 03/22/2004 15:00:00
Next Run on: 03/23/2004 07:00:00
Next Run on: 03/23/2004 15:00:00
Next Run on: 03/24/2004 07:00:00
Next Run on: 03/24/2004 15:00:00
Next Run on: 03/25/2004 07:00:00
Next Run on: 03/25/2004 15:00:00
Next Run on: 03/26/2004 07:00:00
Next Run on: 03/26/2004 15:00:00

위 결과로 미루어 설정된 내용에 문제가 없음을 확인할 수 있습니다.

작업과 프로그램의 연계

위의 사례는 특정 프로그램과 연결되지 않은 작업을 생성하는 방법을 설명하고 있습니다. 이번에는 특정 OS 유틸리티를 사용하는 프로그램을 생성하고, 실행 주기를 정의한 스케줄을 설정한 뒤, 이 두 가지를 조합하여 새로운 작업을 생성하는 방법을 설명합니다.

먼저 작업 내에서 프로그램을 사용한다는 사실을 데이타베이스가 인지하도록 해야 합니다. 이 프로그램을 생성하려면, CREATE JOB 권한을 갖고 있어야 합니다.


 

begin

    dbms_scheduler.create_program

    (

       program_name   => 'MOVE_ARCS',

       program_type   => 'EXECUTABLE',

       program_action => '/home/arup/dbtools/move_arcs.sh',

       enabled        => TRUE,

       comments       => 'Moving Archived Logs to Staging Directory'

    );

end;

/

이제 named program unit을 생성하여, 그 유형을 “executable”로 지정하고 실행될 program unit의 이름을 정의하였습니다.

다음으로, 매 30분 간격으로 실행되는 EVERY_30_MIN이라는 이름의 스케줄을 생성합니다:


 

begin

    dbms_scheduler.create_schedule

    (

       schedule_name   => 'EVERY_30_MINS',

       repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',

       comments        => 'Every 30-mins'

    );

end;

/

프로그램과 스케줄을 생성한 뒤, 이 두 가지를 연관시킴으로써 새로운 작업을 생성합니다.


 

begin

   dbms_scheduler.create_job

   (

      job_name      => 'ARC_MOVE',

      program_name  => 'MOVE_ARCS',

      schedule_name => 'EVERY_30_MINS',

      comments      => 'Move Archived Logs to a Different Directory',

      enabled       => TRUE

   );

end;

/

이제 매 30분마다 move_arcs.sh 셸 스크립트를 실행하는 작업이 생성되었습니다. 이 스케줄은 데이타베이스 내부의 Scheduler 기능을 통해 관리되며, 따라서 cron 또는 AT 유틸리티를 사용할 필요가 없습니다.

Classes, Plans, and Windows

작업 스케줄링 시스템에 효과적으로 활용하려면 작업의 우선순위 지정이 가능해야 합니다. 예를 들어 OLTP 워크로드가 실행 중인 시간대에 통계 수집 작업이 갑자기 실행되어 성능을 저하시킬 수 있습니다. 통계 수집 작업이 OLTP 성능에 영향을 미치지 않도록 하기 위해, Scheduler가 제공하는 job classes, resource plans, and Scheduler Windows기능을 활용할 수 있습니다.

Job class는 할당된 자원을 공유하는 “resource consumer group”으로 매핑됩니다. Job class를 생성하기 위해, 먼저 OLTP_GROUP이라는 이름의 resource consumer group을 정의해 보겠습니다.


 

begin

   dbms_resource_manager.clear_pending_area();

   dbms_resource_manager.create_pending_area();

   dbms_resource_manager.create_consumer_group (

       consumer_group => 'oltp_group',   

       comment => 'OLTP Activity Group'

   );

   dbms_resource_manager.submit_pending_area();

end;

/

다음에는 resource plan을 생성합니다.


 

begin

   dbms_resource_manager.clear_pending_area();

   dbms_resource_manager.create_pending_area();

   dbms_resource_manager.create_plan

      ('OLTP_PLAN', 'OLTP Database Activity Plan');

   dbms_resource_manager.create_plan_directive(

      plan => 'OLTP_PLAN',

      group_or_subplan => 'OLTP_GROUP',

      comment => 'This is the OLTP Plan',

      cpu_p1 => 80, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,

      cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,

      parallel_degree_limit_p1 => 4,

      active_sess_pool_p1 => NULL,

      queueing_p1 => NULL,

      switch_group => 'OTHER_GROUPS',

      switch_time => 10,

      switch_estimate => true,

      max_est_exec_time => 10,

      undo_pool => 500,

      max_idle_time => NULL,

      max_idle_blocker_time => NULL,

      switch_time_in_call => NULL

   );

   dbms_resource_manager.create_plan_directive(

      plan => 'OLTP_PLAN',

      group_or_subplan => 'OTHER_GROUPS',

      comment => NULL,

      cpu_p1 => 20, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,

      cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,

      parallel_degree_limit_p1 => 0,

      active_sess_pool_p1 => 0,

      queueing_p1 => 0,

      switch_group => NULL,

      switch_time => NULL,

      switch_estimate => false,

      max_est_exec_time => 0,

      undo_pool => 10,

      max_idle_time => NULL,

      max_idle_blocker_time => NULL,

      switch_time_in_call => NULL

   );

   dbms_resource_manager.submit_pending_area();

end;

/

마지막으로 앞에서 생성된 resource consumer group을 이용해 job class를 생성합니다.


 

begin

   dbms_scheduler.create_job_class(

      job_class_name => 'OLTP_JOBS',

      logging_level => DBMS_SCHEDULER.LOGGING_FULL,

      log_history => 45,

      resource_consumer_group => 'OLTP_GROUP',

      comments => 'OLTP Related Jobs'

   );

end;

/

이 프로시저에서 사용된 매개변수들을 설명해 보겠습니다. LOGGING_LEVEL 매개변수는 해당 job class를 위해 얼마나 많은 로그 데이타를 기록할 것인지 정의하는데 사용됩니다. LOGGING_FULL은 job class에 포함된 작업의 모든 활동(생성, 삭제, 실행, 변경 등)을 로그에 기록함을 의미합니다. 로그는 DBA_SCHEDULER_JOB_LOG 뷰를 통해 확인할 수 있으며, LOG_HISTORY 매개변수에 저장된 대로 45일간 보관됩니다 (디폴트 값은 30일입니다). 이 class와 연관된 resource_consumer_group 도 정의되어 있습니다. DBA_SCHEDULER_JOB_CLASSES 뷰를 통해 정의된 job class들을 확인할 수 있습니다.

작업을 생성하는 과정에서, 필요한 경우 해당 작업을 job class에 할당할 수 있습니다. 예를

들어 collect_opt_stats() 저장 프로시저를 실행하여 옵티마이저 통계를 수집하는 COLLECT_STATS 작업을 생성하면서, 아래와 같이 job class를 할당할 수 있습니다.


 

begin

   dbms_scheduler.create_job

   (

      job_name        => 'COLLECT_STATS',

      job_type        => 'STORED_PROCEDURE',

      job_action      => 'collect_opt_stats',

      job_class       => 'OLTP_JOBS',

      repeat_interval => 'FREQ=WEEKLY; INTERVAL=1',

      enabled         => true,

      comments        => 'Collect Optimizer Stats'

   );

end;

/

위 명령을 실행하면 생성된 작업이 OLTP_JOBS 클래스에 할당됩니다. OLTP_JOBS 클래스에 적용되는 OLTP_GROUP resource plan을 통해 프로세스에 할당되는 CPU 자원, 다른 그룹으로 전환되기 전에 최대 실행 가능한 횟수, 전환되는 그룹 등을 설정할 수 있습니다. 동일한 job class에 할당된 작업은 동일한 resource plan의 적용을 받습니다. 이 기능을 활용하면 서로 다른 유형의 작업이 같은 리소스를 두고 경합을 벌이는 상황을 방지할 수 있습니다.

Scheduler Window는 특정 resource plan이 사용되는 시간대를 의미합니다. 예를 들어, 실시간 의사결정 작업에 관련한 업데이트 배치 작업이 주간에는 높은 우선순위를 갖는 반면 야간에는 낮은 우선순위를 갖는다고 가정해 봅시다. 이 경우 시간대별로 다른 resource plan을 정의하고, 정의된 내용을 Scheduler Window를 이용해 적용할 수 있습니다.

모니터링

실행 중인 작업의 상태는 DBA_SCHEDULER_JOB_LOG 뷰를 통해 확인할 수 있습니다. 이 뷰의 STATUS 컬럼은 작업의 현재 상태를 표시하는데 사용됩니다. 만일 STATUS 컬럼이 FAILED로 표시된다면, DBA_SCHEDULER_JOB_RUNS_DETAILS 뷰를 통해 그 원인을 확인할 수 있습니다.

관리

지금까지 여러 가지 유형의 오브젝트(program, schedule, job, job class 등)를 생성하는 방법에 대해 설명했습니다. 이렇게 생성된 오브젝트를 변경할 필요가 있다면, DBMS_SCHEDULER 패키지가 제공하는 API를 사용하면 됩니다.

Enterprise Manager 10g 홈 페이지에서 Administration 링크를 클릭하면, 그림 1과 같이 Administration 화면이 표시됩니다. Scheduler와 관련된 작업은 우측 하단의 “Scheduler” 항목에 표시됩니다 (그림의 붉은색 원 참조).

그림 1: Administration 페이지
이 페이지에서 제공되는 하이퍼링크를 활용하면 작업의 생성, 삭제, 관리와 같은 Scheduler 관련 작업을 쉽게 수행할 수 있습니다. 몇 가지 예를 들어 설명해 보겠습니다. 이미 작업을 생성해 둔 상태이므로, Job 탭을 클릭하면 그림 2와 같은 화면이 표시될 것입니다.

그림 2: 작업 스케줄의 확인
COLLECT_STATS 작업을 클릭하여 그 속성을 변경해 보도록 합시다. “Name” 필드를 클릭하면 그림 3과 같은 화면이 표시됩니다.

그림 3: 작업 매개변수
지금까지 확인한 것처럼, EM을 이용하면 작업, 스케줄, 옵션 등에 관련한 매개변수를 수정할 수 있습니다. 변경 작업을 완료한 뒤 “Apply” 버튼을 누르면 변경사항은 영구적으로 적용됩니다. “Apply” 버튼을 누르기 전에 “Show SQL” 버튼을 눌러 실행되는 SQL 구문을 확인하는 것도 가능합니다. 또 SQL 구문을 스크립트에 저장하여 나중에 실행하거나, 템플릿 용도로 활용할 수도 있습니다.

DBMS/오라클] EXP-00003: 세그먼트... 에러가 발생하는 경우 해결 방법

증상 : EXP-00003: 세그먼트... 에러가 발생하는 경우 해결 방법

0. 오라클 클라이언트의 버전을 동일 버전으로 업그레이드 한다.

1. oracle 9.x 버전(클라이언트) 에서 10g 서버의 데이터 익스포트시 :

EXP-00003 : no storage definition found for segment(7,1955)
라는 에러 발생시

dba 권한 사용자가 아래의 명령을 실행하면 해결 된다. export시 참조하는 view를 만드는 쿼리문이다.
SQL> @?/rdbms/admin/catexp

2. ==>(원문 출처 : ) http://link.allblog.net/13777149/http://gampol.tistory.com/entry/9i-EXP-00003
When you use old version of exp to export tables with LOB column from Oracle 9.2.0.5 or higher version, you will get an error "EXP-00003 : no storage definition found for segment .....", actually this is an Oracle bug, you could temporary get it resolved by replace a view "exu9tne", as following:
Before exporting, run the following SQL under sys:

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * FROM SYS.EXU9TNEB
/

After exporting, run the following to restore the view definition according to Metalink Notes.

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1

DBMS/오라클] Database Link사용법

출처: http://bosoa.egloos.com/1442337

Database Link사용법

원격지에 있는 데이터베이스를 link하는 법은 다음과 같다.

원격지의 Database의 Service Name이 piruks.kang.com이다.
나의 tnsnames.ora파일에는 Database alias가 piruks가 잡혀있다.

piruks.kang.com(원 격지DB)의 init.ora내용
db_name = "piruks"
db_domain = kang.com
instance_name = piruks
service_names = piruks.kang.com

maddog.kang.com(로컬 DB)의 tnsnames.ora내용
PIRUKS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PIRUKS.KANG.COM)
    )
  )

원 격지의 패러미터파일(init.ora)에 global_names를 true로 하면 dblink의 이름과 접속하는 db의 이름이 동일하도록 요구한다.

global_names = true [ or false ]

이 설정은 다음과 같이 해서 알 수 있다.
SQL> select name, value from v$parameter where name='global_names';

NAME                 VALUE
-------------------- ------
global_names         FALSE

현재 데이터베이스의 이름은 global_names에 질의함으로써 알 수 있다.

SQL> connect kang/xxxxxx@piruks;
연 결되었습니다.
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------
PIRUKS.KANG.COM

local db에 kang이라는 사용자로 접속한다.
SQL> connect kang/xxxxxx
연결되었습니다.

이 제 원격지(piruks.kang.com)의 DB에 kang이라는 사용자로 연결되는 Database link
를 생성한다.
여기서 piruks.kang.com은 원격지의 [Database name].[Domain name]이다.
보통 [Database name].[Domain name]는 Service Name이라 불리운다.
하지만 위와 같이 해서 제대로 않되는 경우가 있다.
이럴때는 global_name에 있는 이름을 dblink이름으로 정하면 해결된다.
piruks는 database connect string이다.
SQL> create database link piruks.kang.com
  2  connect to kang identified by xxxxxx
  3  using 'piruks';
데이타베이스 링크가 생성되었습니다.

사 용법은 다음과 같다.
보통의 DML문장에 '@piruks.kang.com'을 추가한다.
SQL> select * from tab at piruks.kang.com;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ ------------
-- ----------
TEST                                                         TABLE

SQL> select * from test at piruks.kang.com;

NAME                        AGE
-------------------- ----------
강명규                       27

SQL> insert into test at piruks.kang.com values('홍길동', 30);

1 개의 행이 만들어졌습니다.

SQL> update test at piruks.kang.com set age=31 where age=30;

1 행이 갱신되었습니다.

SQL> select * from test at piruks.kang.com;

NAME                        AGE
-------------------- ----------
강명규                       27
홍길동                       31

SQL> delete from test at piruks.kang.com where age=31;

1 행이 삭제되었습니다.

데이터베이스링크에서 DDL문장은 적용되지 않는다.
SQL> drop table test at piruks.kang.com;
drop table test at piruks.kang.com
                *
1 행에 오류:
ORA-02021: 원격 데이터베이스에 DDL 조작들이 허용되지 않습니다

SQL> drop database link piruks.kang.com;

데이타베이스 링크가 삭제되었습니다.

SQL>

삭 제할대 DBLINK의 소유자로 삭제 하여야 한다.

========================================================================
Database Link(데이터베이스 링크)
먼저 디비 링크를 사용하기 위해서는 v$parameter 에 global_names=false로 되어 있어
야 합니다..
init.ora를 고치던지.아님 alter system set global_names=false;로 해 줘야 합니다.
그리고 grant create database link to scott; 로 해당 유저한테 권한을 부여 합니다.
그리고 tnsnames.ora파일을 수정해 해당 연결하고자 하는 database의 알리아스를 만
들어 주면 됩니다.

우선 고려되어야 사항은 ORACLE INSTANCE가 두 개 이상이고 각기 다른 PLATFORM에
서 운용된다는 가정하에서 각각의 HOST NAME과 ORACLE_SID는 다르고
NLS_CHARACTER_SET 은 동일하게 되어 있어야 합니다.

만약 같은 MACHINE에서 INSTANCE의 ORACLE_SID가 같다면 TNS ERROR가 발생할
것이다.
또한 미래를 위해 다른 MACHINE이라 할지라도 ORACLE_SID는 규칙에 의해 다르게 가
져가는 것이 좋습니다.
그리고, NLS_CHARACTER_SET이 동일하게 되어 있지 않으면 DATA 입/출력 시 한글 데
이타가 ?????로 나타날 것입니다.

그럼 환경 점검이 끝났으니 ORACLE7에서 ORACLE V6(ORACLE7의 경우도 비슷)에 있
는 TABLE의 DATA를 DB LINK를 이용하여 SELECT하거나 VIEW를 작성하여 보겠습니
다.

HOST NAME    :      HP7    ->    SUN7
ORACLE_SID   :      ORA7   -     ORATEST  이라 할 때

   1)  HP7 에서 SUN7로 dblink생성하기.

       scott/tiger 로 Login

       SQL> create public database link HP7TOSUN7
            connect to scott identified by tiger
            using 'ORATEST';
       로 하면 된다.

       이때 V2인 경우의 ORATEST는 $ORACLE_HOME/network/admin directory의
       tnsnames.ora file 내에 지정된 service name이다.
       tnsnames.ora의 service name이 잘 setting 되어 있는지 확인하는 방법
       : SQL*Plus scott/tiger@service name했을 때, SQL*Plus에 log-in되어야
       합니다.

   2> SUN7 에 있는 TABLE의 select 및 view(view는 필요에 따라 생성) 작성,
      HP에서 작업

       SQL> select * from emp@HP7TOSUN7;

       SQL> create view emp_view as select * from emp@HP7TOSUN7  a
                 where a.deptno = 10;

   3> HP7 에서 SYNONYM을 생성하여 사용하는 경우

       SQL> create synonym emp for emp@HP7TOSUN7;
       SQL> select * from emp;

로 한다면 간단히 분산 DB의 환경에서 사용 할 수 있습니다.

select 를 제외한 DML(insert,update,delete) 를 하려면,sqlplus log-in 시에 다음과 같
은 option 이 display 되어야 합니다.

   SQL*Plus: Release 3.3.3.0.0 - Production on Mon Jan 19 14:18:47 1998
   Copyright (c) Oracle Corporation 1979, 1996.  All rights reserved.

   Connected to:
   Oracle7 Server Release 7.3.3.4.0 with the 64-bit option - Production
   Release With the distributed, ......
   ------------------------------------

   remote 작업의 예

   select * from table_name@HP7TOSUN7;
   insert into table_name@HP7TOSUN7;
   delete table_name@HP7TOSUN7;

   (단 SERVER TO SERVER로 NETWORK 환경이 구축되어 있어야 하고,
   listener 가 반드시 떠 있어야 합니다.)

SQL>   create public database link link_test
  2    connect to scott identified by tiger
  3    using 'haksan21'; -- service name

데이타베이스 링크가 생성되었습니다.

SQL>  select * from all_db_links;

OWNER      DB_LINK              USERNAME   HOST       CREATED
---------- -------------------- ---------- ---------- --------
PUBLIC     HSA.WORLD            HSA        haksan21   99/03/12
PUBLIC     HSE.WORLD            HSE        haksan21   99/03/30
PUBLIC     HSR.WORLD            HSR        haksan21   99/03/23
PUBLIC     LINK_TEST.WORLD      SCOTT      haksan21   99/05/07

SQL> drop public database link link_test;

데이타베이스 링크가 삭제되었습니다.

SQL> select * from all_db_links;

OWNER      DB_LINK              USERNAME   HOST       CREATED
---------- -------------------- ---------- ---------- --------
PUBLIC     HSA.WORLD            HSA        haksan21   99/03/12
PUBLIC     HSE.WORLD            HSE        haksan21   99/03/30
PUBLIC     HSR.WORLD            HSR        haksan21   99/03/23

** export, import 작업을 수행할 경우 (또는 동일한 환경으로 새로운 유저를 만들경
우) ??

Synonym 처음
* 목적 : 다른 사용자가 소유한 테이블을 쉽게 사용하기 위해
         OBject 이름을 짧게 하기 위해

* SYNONYM의 정보를 가진 Data Dictionary
  USER_SYNONYMS
  ALL_SYNONYMS

* 예문
  create synonym tmp00 for fsr.tmp00;

* 삭제 : drop synonym synonym_name

*** DB LINK의 사용

    다른 기계의 데이타베이스를 사용하고자 할 때 이용

    create public database link link_name
    connect to user_name identified by password
    using 'db_alias_name';

    확인 : all_db_links

    사용 : select * from user_name.table_name@link_name;
    synonym : create synonym synonym_name for
user_name.table_name@link_name;

View 처음
* 뷰는 테이블이나 다른 뷰를 기반으로 한 논리적 테이블이다. 스스로 자료를 가지지 않으나 조회, 수정할 수 있는 창문과 같다. 뷰의 근거가 되는 테이블을 base table이라 한다. 뷰 는
  select 문장의 형태로 data dictionary에 저장된다.

* 잇점 : 1) DB의 특정 부분만 조회하므로 접근을 제한할 수 있다.
         2) 복잡한 질의를 단순화할 수 있다. (여러 테이블의 조인관계를 숨길 수 있다.)

* 뷰를 만드는 여러 예문
  create or replace view empvu30
  as select * from emp where deptno = 30
     with check option constraint empvu30_ck; -- deptno가 30인 자료만 가능

  create or replace view empvu30 (id_number, employee, job) -- alias 변경
  as select empno, ename, job from emp where deptno = 30 with read only; --
delete불가

  create or replace view v_yycnt as
  select sum(aa) aa, sum(bb) bb, sum(cc) cc
  from (
       select count(*) aa, 0 bb, 0 cc from emp
        where to_char(hiredate, 'yy') = '81'
       union all
       select 0 aa, count(*) bb, 0 cc from emp
        where to_char(hiredate, 'yy') = '82'
       union all
       select 0 aa, 0 bb, count(*) cc from emp
        where to_char(hiredate, 'yy') = '83');

* 뷰의 정보를 가진 Data Dictionary
  USER_VIEWS;
  ALL_VIEWS;
  DESC view_name;

* 삭제 : drop view view_name;

DBMS/오라클]Case Statement

출처 : http://www.techonthenet.com/oracle/functions/case.php

Oracle/PLSQL: Case Statement

Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.
The syntax for the case statement is:

CASE [ expression ]
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  WHEN condition_n THEN result_n
  ELSE result
END

expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a conditionis found to be true, the case statement will return the result and not evaluate the conditions any further.
result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.

Note:

If no condition is found to be true, then the case statement will return the value in the ELSE clause.
If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL.
You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.

Applies To:

Oracle 9i, Oracle 10g, Oracle 11g

For example:

You could use the case statement in an SQL statement as follows: (includes the expression clause)

select table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
from all_tables;

Or you could write the SQL statement using the case statement like this: (omits the expression clause)

select table_name,
CASE
  WHEN owner='SYS' THEN 'The owner is SYS'
  WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
from all_tables;

The above two case statements are equivalent to the following IF-THEN-ELSE statement:

IF owner = 'SYS' THEN
     result := 'The owner is SYS';
ELSIF owner = 'SYSTEM' THEN
    result := 'The owner is SYSTEM'';
ELSE
    result := 'The owner is another value';
END IF;

The case statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted.
Your SQL statement would look as follows:

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
from all_tables;

With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

For Example:

Here is an example that demonstrates how to use the case statement to compare different conditions:

select
CASE
WHEN a < b THEN 'hello'
WHEN d < e THEN 'goodbye'
END
from suppliers;

Frequently Asked Questions

Question: Can you create a case statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.
Answer: Yes, below is an example of a case statement that evaluates two different fields.

select supplier_id,
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;

So if supplier_name field is IBM and the supplier_type field is Hardware, then the case statement will return North office. If the supplier_name field is IBM and the supplier_type is Software, the case statement will return South office.

DBMS/오라클]Broken된 Job을 자동으로 재실행 시키는 방법

출처 : http://ggang-tong.tistory.com/32

[oracle]Broken된 Job을 자동으로 재실행 시키는 방법

2008/10/08 15:28 in Oracle

--------------------------------------------------------------------------------
BROKEN 된 JOB을 자동으로 재실행 시키는 방법
=========================================

1. broken job
~~~~~~~~~~~~~~
Oracle에서 특정 작업을 주기적으로 실행시키기 위해서는 job을 이용하게 된다.
이것은 snp라는 background process가 각 job의 interval간격으로 작업을 실행
하는데, snapshot과 같은 것이 job의 대표적인 예이며, dbms_job package를
이용하여 직접 job을 등록 및 관리, 삭제가 가능하다.

이러한 job이 문제가 발생하여 수행이 오류가 발생하면 1분, 2분, 4분, 8분과
같은 간격으로 자동으로 재실행을 하게 되고, 이 주기가 해당 job의 inerval보다
크게 되면 그때부터는 interval간격마다 job을 실행하도록 시도한다. job의
interval이 1분보다 작으로 interval간격대로 시도하고, 예를 들어 interval이
2 분 20초마다이면, 1분, 2분, 이후에는 2분 20초 마다씩 새로 fail된 job을
실행해 본다. 이렇게 fail이 발생한 job을 자동으로 재실행하는 것은 최대 16번
이며, 16번 시도후에는 job이 broken상태가 되어 더 이상 snp process는 시도를
하지 않게 된다.

oracle이 이렇게 16번 시도 후 job을 broken상태로 하는 이유는 16번 시도 때까지
문제가 해결되지 않은 job이라면, 예를 들어 network이 장시간 down되어 snapshot
refresh가 안 되는 것과 같이 문제가 장기화될 가능성이 많고 그러한 job을 계속
시도해 보는 것 자체가 cpu를 많이 소모하는 낭비되는 작업이라는 판단 때문이다.
그러나 일단 job이 broken되면, 그 job을 fail 상태로 만든 원인이 제거된 후에도
여전히 실행되지 않은 상태로 있게 되어 db admin이 수시로 broken된 job이 있는
지를 확인하고 manual하게 dbms_job.run등을 실행하여 다시 실행하도록 할 필요가
있다.

이러한 db admin 작업을 덜고 database 자체에서 broken job에 대해서도 계속
run을 시도하고자 하는 경우 이 문서를 이용하여 작업하면 된다.

2. dba_jobs view에 대해서
~~~~~~~~~~~~~~~~~~~~~~~~~~
dba_jobs(혹은 user_jobs)는 등록된 job에 관한 여러가지 정보를 사용자게 제공
한다.

특히 job의 broken과 관련하여 중요한 몇개의 column에 대해서 살펴본다.
broken : 해당 job이 broken되었으면 Y, 그렇지 않으면 N로 나타난다.
failures : job이 시도되었으나 fail된 숫자이다. 이 숫자가 16이 되면 broken이
Y가 되어 더 이상 snp process는 시도하지 않게 되어 이 숫자도
증가가 되지 않는다.
단, dbms_job.run을 user가 manual하게 실행해도 여전히 문제가 발생
하면 이 숫자가 이미 16이상이라도 시도할 때마다 계속 1씩 증가한다.
next_date: job이 다음에 실행될 시간이다.

job이 실행을 시작하는 시점에 last_date + interval = next_date로
계산한 후 job 실행이 끝나면 (혹은 오류 발생 후) 시작 시점에
계산된 next_date값이 dba_jobs에 기록되어 확인이 가능해 진다.
이 시간이 과거로 되어 있으면, failures가 0이고 broken이 N이라
하더라도 snp는 그 job을 run하려고 시도하지 않는다.
this_date: 이것은 현재 실행되는 job이 실행을 시작한 시간을 나타낸다. 이미
실행이 끝난 job이라면 이 부분은 null로 나타나면 이 컬럼에 값이
있는 job에 대해서는 dba_jobs_running에도 정보가 나타난다.

3. broken된 job을 실행되도록 하는 procedure

먼저, broken된 job을 찾아 broken을 false로 만들어 주고 snp가 다시 실행하도록
next_date를 지정해 주는 procedure를 만든다.
이 procedure는 아래 3-2와 같으며 수행한 기록을 남기기 위해 job_log라는
table을 만들었는데 이 부분은 빼도 무관하다.

3-1 log table 생성
아래 procedure를 실행시키기 전에 먼저 이와 같이 table을 만든다.

SQL> create table job_log (jobno number,
jobname varchar2(30),
jobdate date);

3-2 broken job을 snp가 다시 실행하도록 하기 위해 다음과 같은 release_job을
관리하고자 하는 job의 owner에서 생성한다.

(1) broken job을 선택할 때 dba_jobs 대신에 user_jobs를 이용한다.
dbms_job package는 항상 해당 owner의 job만을 대상으로 작동하므로,
dba_jobs를 확인하고 보이는 job에 대해서 연산하면 owner가 아닌 경우
그러한 job이 없다는 오류가 발생하게 된다.

(2) dbms_job.run을 바로 실행하지 않은 이유는 dbms_job.run은 procedure
내에서 call하지 못하도록 정의되어 실제 사용하면 오류가 발생하거나
수행이 되지 않는다.

(3) broken='Y' 뿐 아니라 failures가 15이상인 것을 함께 check하는 이유는
이 procedure로 인해 일단 broken이 N로 변경된 상태에서 여전히 오류가
있으면 다음 수행 때 broken이 N여서 제외되기 때문이다.

(4) dbms_job.change의 세번째 argument가 next_date인데 이렇게 next_date를
원하는 시간, 혹은 약간의 미래로 맞추어 놓아야 release_job이 수행된
이후 snp가 이 시간에 broken으로 모아진 job을 실행하게 된다.

(5) 결국 broken으로 선택된 job들을 release_jobs가 수행될 때마다 한번씩
snp process로 하여금 다시 시도되도록 지정되어 진다.

(6) procedure source
create or replace procedure release_jobs as
cursor my_broken_jobs is
select job, what from user_jobs where broken = 'Y' or failures > 15;
begin
for broken_jobs in my_broken_jobs
loop
begin
dbms_job.broken(broken_jobs.job,FALSE);
dbms_job.change(broken_jobs.job, null, sysdate+1/1440, null);
insert into job_log values (broken_jobs.job,
broken_jobs.what,
sysdate);
commit;
Exception
when others then
null;
end;
end loop;
end;
/

4. release_jobs를 job으로 등록한다.

위에서 정의한 release_job을 job으로 등록하여 broken된 job을 찾아 실행해주는
작업 자체가 주기적으로 실행되도록 한다.
release_jobs 자체는 network을 타거나 space를 필요로 하는 등의 작업이 아니라,
fail 이 발생할 우려는 거의 없다.
이 release_jobs를 등록한 job의 next_date가 미래이고 fail이 없는지만 확인하면,
나머지 broken job들은 여기에서 등록된 job이 관리하게 된다.

SQL>variable job number;
SQL>exec dbms_job.submit(:job, 'RELEASE_JOBS;',sysdate,'sysdate+1/1440');
SQL>exec dbms_job.run(:job);
SQL>commit;

출처 : http://kr.blog.yahoo.com/jhoony73/590306.html?p=1&pm=l