익스터널 테이블 preprocessor #1 - ps

익스터널 테이블 preprocessor 기능을 통해 ps 명령어의 실행 결과를 쿼리로 출력해보자.

 

아래와 같이 디렉토리를 생성하자. bin 디렉토리에 쉘 스크립트, ext 디렉토리에 데이터 파일이 저장된다. 

# mkdir -p /home/app/ora11gr2/bin
# mkdir -p /home/app/ora11gr2/ext
# chmod 777 /home/app/ora11gr2/bin
# chmod 777 /home/app/ora11gr2/ext

 

/home/app/ora11gr2/bin 디렉토리에 preprocessor가 실행할 ext_ps.sh 쉘 스크립트를 생성하자. 

#/bin/ksh

/bin/ps -ef | /bin/grep -v $$ | /bin/awk '{ if (match($5, /:/) == 0) { print $1"|"$2"|"$3"|"$4"|"$5" "$6"|"$7"|"$8" "$9" "$10" "$11" "$12" "$13" "$14" "$15 } else { print $1"|"$2"|"$3"|"$4"|"$5"|"$6"|"$7"|"$8" "$9" "$10" "$11" "$12" "$13" "$14" "$15 }; }'

 

아래와 같이 익스터널 테이블에 지정할 더미 파일을 생성하자.

# touch /home/app/ora11gr2/ext/dummy.ext
# chmod 777 /home/app/ora11gr2/ext/dummy.ext

 

sys 계정으로 디렉토리를 생성하고, tuna 유저에 권한을 부여하자. 

-- 1 : sys
CREATE DIRECTORY dir_bin AS '/home/app/ora11gr2/bin';
CREATE DIRECTORY dir_ext AS '/home/app/ora11gr2/ext';
GRANT READ, EXECUTE ON DIRECTORY dir_bin TO tuna;
GRANT READ, WRITE ON DIRECTORY dir_ext TO tuna;

 

아래와 같이 인스터널 테이블을 생성하자. 17행의 쉘 스크립트 실행 결과가 적재할 파일로 처리된다.

-- 2 : tuna
CREATE TABLE ext_ps (
    ouid     VARCHAR2(8)
  , pid      NUMBER(5)
  , ppid     NUMBER(5)
  , c        VARCHAR2(1)
  , stime    VARCHAR2(5)
  , tty      VARCHAR2(8)
  , time     VARCHAR2(8)
  , cmd      VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY dir_ext
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        PREPROCESSOR dir_bin: 'ext_ps.sh'
        LOAD WHEN OUID != 'UID'
        NOBADFILE 
        NODISCARDFILE 
        NOLOGFILE 
        FIELDS TERMINATED BY '|'
    )
    LOCATION (dir_ext:'dummy.ext')
)
REJECT LIMIT UNLIMITED;

 

아래는 ext_ps 테이블을 조회한 결과다.

-- 3
SELECT * FROM ext_ps WHERE ouid = 'ora11g' ORDER BY cmd;

OUID       PID PPID C STIME TTY TIME     CMD
-------- ----- ---- - ----- --- -------- ---------------------------------------------
ora11gr2 30867    1 0 15:05 ?   00:00:00 /app/ora11g/11g/bin/tnslsnr LISTENER -inherit
ora11gr2 31924    1 0 15:12 ?   00:00:01 ora_cjq0_r2
ora11gr2 31814    1 0 15:12 ?   00:00:01 ora_ckpt_ORA11GR2
ora11gr2 31834    1 0 15:12 ?   00:00:00 ora_d000_ORA11GR2
ora11gr2 31794    1 0 15:12 ?   00:00:00 ora_dbrm_ORA11GR2
ora11gr2 31806    1 0 15:12 ?   00:00:00 ora_dbw0_ORA11GR2
ora11gr2 31798    1 0 15:12 ?   00:00:02 ora_dia0_ORA11GR2
ora11gr2 31790    1 0 15:12 ?   00:00:00 ora_diag_ORA11GR2
ora11gr2 31786    1 0 15:12 ?   00:00:00 ora_gen0_ORA11GR2
ora11gr2 31810    1 0 15:12 ?   00:00:00 ora_lgwr_ORA11GR2
ora11gr2 31802    1 0 15:12 ?   00:00:00 ora_mman_ORA11GR2
ora11gr2 31830    1 0 15:12 ?   00:00:00 ora_mmnl_ORA11GR2
ora11gr2 31826    1 0 15:12 ?   00:00:01 ora_mmon_ORA11GR2
ora11gr2 31772    1 0 15:12 ?   00:00:00 ora_pmon_ORA11GR2
ora11gr2 31776    1 0 15:12 ?   00:00:00 ora_psp0_ORA11GR2
ora11gr2 31944    1 0 15:12 ?   00:00:00 ora_q000_ORA11GR2
ora11gr2 31948    1 0 15:12 ?   00:00:00 ora_q001_ORA11GR2
ora11gr2 31896    1 0 15:12 ?   00:00:00 ora_qmnc_ORA11GR2
ora11gr2 31822    1 0 15:12 ?   00:00:00 ora_reco_ORA11GR2
ora11gr2 31838    1 0 15:12 ?   00:00:00 ora_s000_ORA11GR2
ora11gr2 32121    1 0 15:17 ?   00:00:00 ora_smco_ORA11GR2
ora11gr2 31818    1 0 15:12 ?   00:00:00 ora_smon_ORA11GR2
ora11gr2 31780    1 0 15:12 ?   00:00:00 ora_vktm_ORA11GR2
ora11gr2   703    1 0 15:47 ?   00:00:00 ora_w000_ORA11GR2
ora11gr2   485    1 0 15:40 ?   00:00:00 oracleORA11GR2 (LOCAL=NO)
ora11gr2 32022    1 0 15:15 ?   00:00:08 oracleORA11GR2 (LOCAL=NO)

26 개의 행이 선택되었습니다.