오라클 Plan 정보 보는 방법

보통 set autotrace on 명령으로 plan 정보를 보는데, 회사의 DBA가 추천한 방법이 있어서 기록을 남긴다.

 

1. 사용자 계정으로 DB 접속 ( 시스템 계정의 경우 불필요한 쿼리까지 포함됨 )

$ sqlplus system/

 

2. 쿼리 실행

SQL> alter session set sql_trace = true;

SQL> 쿼리실행 ( 복수의 쿼리 가능 )

SQL> alter session set sql_trace = false;

          ( udump 생성을 위해 필수 )

 

3. 결과 확인

$ cd $ORACLE_BASE

$ cd ./admin/DB명/udump

$ ls ltr

     ( 가장 최근 파일 확인 )

$ tkprof 파일명.trc tt.txt sys=no;

     ( Trace 파일을 사람이 해석 가능 하도록 변환 )

$ cat tt.txt



CVS Commit 로그를 남겨보자

-. 요구사항 파악 및 설계

적용 패키지 작성을 위해 수정된 파일 목록을 추출 할 방법을 찾아 보기로 했다.
업무 때문에 소스 관리를 CVS 로 하고 있다. CVS의 단점은 개별 파일이력은 확인 할 수 있는데, 
수정된 파일 목록을 뽑기가 쉽지 않다는 것이다.

구글링을 해보니 CVS에서 Commit 시점에 뭔가 처리할 수 있다는 것을 알았다.

먼저, CVS 서버에 접속해서 저장소의 CVSROOT 디렉토리를 보자

-rwxrwxr-- 1 cvs cvs       544  4월 22 09:23 checkoutlist

-rwxrwxr-- 1 cvs cvs       694  3월 21  2009 checkoutlist,v

-rwxrwxr-- 1 cvs cvs       882  4월 26 16:03 commitinfo

-rwxrwxr-- 1 cvs cvs       959  3월 21  2009 commitinfo,v

-rwxrwxr-- 1 cvs cvs       993  3월 21  2009 config

-rwxrwxr-- 1 cvs cvs      1192  3월 21  2009 config,v

-rwxrwxr-- 1 cvs cvs       602  2월  8 16:32 cvswrappers

-rwxrwxr-- 1 cvs cvs       801  3월 21  2009 cvswrappers,v

-rwxrwxr-- 1 cvs cvs      1025  3월 21  2009 editinfo

-rwxrwxr-- 1 cvs cvs      1224  3월 21  2009 editinfo,v

-rwxrwxr-- 1 cvs cvs 292081967  4월 29 18:14 history

-rwxrwxr-- 1 cvs cvs      1245  4월 21 11:32 loginfo

-rwxrwxr-- 1 cvs cvs      1367  3월 21  2009 loginfo,v

-rwxrwxr-- 1 cvs cvs      1151  3월 21  2009 modules

-rwxrwxr-- 1 cvs cvs      1350  3월 21  2009 modules,v

-rwxrwxr-- 1 cvs cvs       564  3월 21  2009 notify

-rwxrwxr-- 1 cvs cvs       763  3월 21  2009 notify,v

-rwxrwxr-- 1 cvs cvs       640  4월 25 12:43 passwd

-rwxrwxr-- 1 cvs cvs       649  3월 21  2009 rcsinfo

-rwxrwxr-- 1 cvs cvs       848  3월 21  2009 rcsinfo,v

-rwxrwxr-- 1 cvs cvs       879  3월 21  2009 taginfo

-rwxrwxr-- 1 cvs cvs      1078  3월 21  2009 taginfo,v

-rwxrwxrw- 1 cvs cvs       187 11월 10 17:15 val-tags

-rwxrwxr-- 1 cvs cvs      1026  3월 21  2009 verifymsg

-rwxrwxr-- 1 cvs cvs      1225  3월 21  2009 verifymsg,v


위와 같은 파일들을 볼 수 있는데.. 
여기서 commitinfo 란 파일이 CVS에서 Commit 하기 바로전에 실행되는 파일이다.

방향은 정해졌다. 
작업 구성은 아래와 같은 순서로 하기로 했다.

     1. CVS가 Commit 되는 시점에 파일명을 DB에 저장한다.
         -> CVSROOT/commitinfo에 등록할 쉘 스크립트를 작성.
     2.  저장된 DB를 조회할 간단한 WEB Page 제작
         -> 평소 관심을 갖고 있던, jQuery를 이용.

[ 1. CVS가 Commit 되는 시점에 파일명을 DB에 저장한다 ]

commitinfo 파일에 아래와 같이 내용을 추가 한다.

$ vi CVSROOT/commitinfo 

ALL /home/cvs/myproject/CVSROOT/cvscommitlog.sh 


DB에 테이블을 생성 한다.

CREATE TABLE CVS_COMMIT
(
   SEQ              NUMBER(10) NOT NULL,
   USERID           VARCHAR2(20) NOT NULL,
   REG_DATE         DATE DEFAULT SYSDATE,
   PROJECT          VARCHAR2(50) NOT NULL,
   FILENAME         VARCHAR2(500) NOT NULL
);

* index는 REG_DATE, TO_CHAR(reg_date, 'YYYY/MM/DDHH24:MI'), USERID, PROJECT 를 걸었다.

commitinfo 파일이 실행할 쉘스크립트를 작성한다.

#!/bin/sh
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=ORACLE_SID
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_OWNER=oracle
export LANG=c
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib;

DATE=`date "+%Y-%m-%d %H:%M:%S"`
DT=`date "+%Y%m%d"`
LOG_FILE="/home/cvs/cvs_log/commit.$DT.log"
DIR=$1
USER=$USER
PROJECT=`echo $DIR | cut -d/ -f4`
#(echo ""; id;) >> $LOG_FILE
shift
for file in $*
do
    echo "$PROJECT, $USER, $DATE, $DIR/$file" >> $LOG_FILE
        result=`$ORACLE_HOME/bin/sqlplus oracle_user/oracle_pwd@oracledb <<EOF 
        INSERT INTO CVS_COMMIT 
        VALUES ( (SELECT NVL(MAX(SEQ), 0) +1 FROM CVS_COMMIT), 
                       '$USER', TO_DATE('$DATE', 'YYYY-MM-DD hh24:mi:ss'), 
                       '$PROJECT', '$DIR/$file');
        COMMIT;
        EOF
        `
done



[ 2.  저장된 DB를 조회할 간단한 WEB Page 제작 ]
jsp 파일 하나와 html파일 하나로 이루어졌고, jQuery와 JSON을 이용 했다.
DATE Picker는 jQuery Plugin 중에 하나를 이용했다.

html 내용 보기


jsp 내용 보기



오라클에서 다른 계정으로 데이터 마이그레이션 방법

오라클에서 다른 계정의 테이블을 Import 해오고자 한다.
테이블 이름만 동일하고 계정 및 테이블 스페이스가 다른 경우의 Import 과정이다.

oraid 계정에서 devdb 계정으로 Import 하는 과정을 설명한다.

1. Table Space 생성
SQL> CREATE TABLESPACE TS_DEV_DATA
DATAFILE '/home/dba/oradata/ts_dev_data.dbf' SIZE 2048M
AUTOEXTEND OFF;

SQL> CREATE TABLESPACE TS_DEV_IDX
DATAFILE '/home/dba/oradata/ts_dev_idx.dbf' SIZE 1024M
AUTOEXTEND OFF;

Data를 위한 TS_DEV_DATA아 Index를 위한 TS_DEV_IDX 를 생성 했다.
개발 목적의 테이블 스페이스 이므로 용량 자동 증가는 꺼두었다.

2. devdb 계정 생성 
SQL> CREATE USER devdb IDENTIFIED BY devdb
DEFAULT TABLESPACE TS_DEV_DATA
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON TS_DEV_DATA;

SQL> GRANT CONNECT,RESOURCE TO FRDEV;

계정을 생성하고, 필요한 권한을 부여 했다.

3. Export
$ exp oraid/orapwd file=tbl_exp_20100201.dmp tables="tbl_test1, tbl_test2, tbl_test3" ignore=y

Source DB에 접속 하여 필요한 테이블을 Export 한다.

4. Table 생성
SQL> CREATE TABLE TBL_TEST1 
(
SEQ              NUMBER(10) NOT NULL,
CODE             VARCHAR2(10) NOT NULL,
DATA             VARCHAR2(20) NOT NULL
CONSTRAINT PK_TEST1
    PRIMARY KEY (SEQ) USING INDEX 
    TABLESPACE TS_DEV_IDX
)
TABLESPACE TS_DEV_DATA;

SQL> CREATE TABLE TBL_TEST2
 - 생략 - 
SQL> CREATE TABLE TBL_TEST3
 - 생략 - 

Export를 하게 되면 테이블 생성 정보까지 포함된다. 
그대로 Import 하게 되면, 새로 생성한 테이블 스페이스에 저장되는 것이 아니고, 
기존 테이블 스페이스에 저장 되므로 원하는 공간에 미리 테이블을 생성 해 둔다.

5. Import
$ imp oraid/orapwd fromuser=oraid touser=devdb file=tbl_user.dmp tables=tbl_user ignore=y

Import 받고자 하는 devdb 계정을 touser 파라미터로 지정한다.
테이블 생성시 발생하는 오류를 건너 뛰기 위해 ignore 파라미터를 추가한다.




Oracle Bulk Loader

Oracle DB의 특정 테이블에 대용량의 데이터를 Insert 하고 싶을때,
Oracle Bulk Loader를 사용한다.

Loader를 사용하기 위해서는 컨트롤 파일과, 데이터 파일이 필요하다.

컨트롤 파일은 데이터 파일 정보와 테이블 정보를 가지고 있고,
데이터 파일은 DB에 넣고자 하는 데이터 목록을 가지고 있다.

1. 테이블 생성
CREATE TABLE TEST_TBL (
    NAME CHAR(20),
    SCORE NUM(5),
    UPDATE DATE );

2. 컨트롤 파일 생성
$ cat test.ctl
LOAD DATA
INFILE test.dat
INTO TABLE TEST_TBL
FIELDS TERMINATED BY ','
(NAME, SCORE, UPDATE SYSDATE)
3. 데이터 파일 생성
$ head test.dat
USER1,70
USER2,80
USER3,90
USER4,100

4. 로더 실행
$ sqlldr tiger control=test.ctl log=test.log
 * Oracle 계정명은 tiger

보다 상세한 사용법은 아래 링크를 참고 하세요
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-load.html
http://www.orafaq.com/wiki/SQL*Loader_FAQ



Oracle import / export


Export

 * 사용자 지정
$ exp oracle_id/oracle_pwd file='dump_filename.dmp' owner=oracle_id compress=y log=log_filename.log

 * 테이블 지정
$ exp oracle_id/oracle_pwd file='dump_filename.dmp' tables=table_name

 * 테이블 조건 지정
$ exp oracle_id/oracle_pwd file='dump_filename.dmp' tables=table_name query=\"where seq=\'007\'\"


Import

 * 사용자 지정
$ imp oracle_id/oracle_pwd file='dump_filename.dmp' fromuser=oracle_id touser=oracle_id log=log_filename.log

 * 테이블 지정
imp oracle_id/oracle_pwd file='dump_filename.dmp' tables=table_name ignore=y

 * 테이블 조건 지정
  테이블 조건 별로 Insert를 할 수가 없다.
  ignore=y 옵션을 주면 에러가 발생해도 계속 진행한다.
   -> DB오류가 발생하면 해당 레코드는 Insert 하지 않는다. (PK가 중복등..)

Pro*c를 이용한 오라클 PL/SQL function 호출

Proc*c를 이용해 Sub Program(Procedure or Function)을 호출 하는 방법에 대해 설명한다.

자료를 찾아보면 대부분의 샘플 코드가 Procedure 로만 되어 있다.
여기서는 Function을 호출 해 보기로 한다.
사실 호출 방법은 거의 비슷 하다.

- 컴파일 방법
PL/SQL 구문을 사용하기 때문에 proc 컴파일 옵션을 주어야 한다.
그런데, 컴파일 옵션을 주면 다른 Pro*c 구문에서 컴파일이 안되는 일이 발생을 한다.
따라서, PL/SQL 구문을 사용하는 소스는 별도 .pc 파일로 때어 내어서 별도 컴파일을 해야한다.

$ proc include=../inc iname=sub.pc SQLCHECK=SEMANTICS userid=oracleid/oraclepwd

여기서 기존 컴파일 방법과 다른 부분은 "SQLCHECK=SEMANTICS" 옵션이 추가된 것과,
userid 옵션이 추가된 것이다.
userid 옵션에는 오라클 로긴을 위한 ID와 Password를 넣어 준다.


- 샘플 소스
/** @file       sub.pc
 * @brief       Remote Function Call  
 * @author    newtype
 * @date    2009-04-10
 */

#ifndef _SUB_PC_
#define _SUB_PC_

#include "common.h"

EXEC SQL INCLUDE SQLCA;

/** @brief 오라클 Function을 호출 한다.
 * @param in [IN] 오라클 Fucntion Parameter
 * @param out [OUT] 오라클 Fucntion Return 값을 반환
 * @return 0     정상처리
 * @return 그외  오라클 에러 코드
 * @remarks 참고 사항
 * @warning 주의 사항
*/
int CallOracleFunction(char* in, char* out)
{    
    EXEC SQL BEGIN DECLARE SECTION;
        char szOut[MAX_BUF] = "";
    EXEC SQL END DECLARE SECTION;

    EXEC SQL EXECUTE
    BEGIN
        :szOut := ORA_SAMPLE_FUNC( in );
        IF szOut = 'newtype' THEN
            strcpy( out, szOut) ;
            COMMIT;
        ELSE
            strcpy( out, 'newtype.pe.kr');
            ROLLBACK;
        END IF;
    END;
    END-EXEC;

    return nErrCode;
}

#endif


Pro*C/C++ Precompiler Programmer's Guide


[ 원문 링크 ]

http://download.oracle.com/docs/cd/A58617_01/server.804/a58233/toc.htm

[ 다운로드 링크 ]


ORA-01002 fetch out of sequence


ORA-01002 fetch out of sequence 는 여러 경우에 발생을 한다.

그중 한가지에 대한 오류 발생 사례 이다.

DB Link로 Remote 테이블을 참조 하고 있는 프로세스가 작업을 수행하고 있는 상태에서,
Remote DB가 Shutdown 되는 경우 위 에러가 발생 한다.
Remote DB가 Start되고 새로운 세션을 맺어야 프로세스가 정상 동작을 한다.
세션을 유지한 상태에서 Remote DB가 Start 되어도 위 에러가 계속 발생한다.

말이 어렵다 테스트 해보자.

1. ORA TNS에 RemoteDB를 추가한다.

2. DB 링크를 생성한다.
CREATE PUBLIC DATABASE LINK test_link CONNECT TO remoteDBid IDENTIFIED BY remoteDBpwd USING 'remoteDB';
CREATE SYNONYM test_proc FOR test_proc_table@test_link
SELECT * FROM test_proc

3. 데이터를 삽입한다.
insert into test_proc values ( 1, 1 );
insert into test_proc values ( 2, 1 );
~~~~~~
insert into test_proc values ( 33, 1 );
insert into test_proc values ( 44, 1 );

4. 테스트 프로그램을 작성한다.
$ vi t.pc
#include <stdio.h>

EXEC SQL INCLUDE SQLCA;

#define MAX_ARRAY_SIZE 10

int main()
{
    int n;

    EXEC SQL BEGIN DECLARE SECTION;
        int  hSEQ; 
        char hOracleUser[20] = "localDBid";
        char hOraclePwd[20]  = "localDBpwd";
    EXEC SQL END DECLARE SECTION;  
    
    EXEC SQL CONNECT :hOracleUser IDENTIFIED BY :hOraclePwd;
    if(sqlca.sqlcode !=0)
    {
        printf( "login fail\n");  
        return 0;
    }
    
    for (n=0; n<10; n++)
    {
        printf( "start of job\n");  
 
        EXEC SQL DECLARE CUR_TEST_PROC CURSOR FOR  
            SELECT SEQ FROM TEST_PROC;
     
        EXEC SQL OPEN CUR_TEST_PROC;         

        while(1)
        {
            EXEC SQL FETCH CUR_TEST_PROC INTO :hSEQ;
                
            sleep(3);            
                
            if(sqlca.sqlcode != 0) 
            {
                if(sqlca.sqlcode == 1403) 
                {
                    printf( "data not found\n");
                    break;
                }
                else 
                {
                    printf( "fetch Error [%20s]\n" , sqlca.sqlerrm.sqlerrmc);
                    break;
                }
            }

            printf("fetch [%d]\n", hSEQ );
        } 
        EXEC SQL CLOSE CUR_TEST_PROC;      
        printf( "end of job\n");  
        
    } /* for */

    return 0;
}




$ proc iname=t.pc
$ cc -o t t.c -L/home/oracle/app/oracle/product/8.1.7/lib -lclntsh -R/home/oracle/app/oracle/product/8.1.7/lib

5. 프로세스 구동
start of job

6. Remote DB Shutdown

7. 에러 확인
fetch Error [ORA-01002: fetch out of sequence]


이거 원인을 파악하려고, 오늘 하루를 다 보냈다. ㅡ.ㅡ


참고 
DB 링크 조회 쿼리
SELECT * FROM USER_DB_LINKS 

시노님 조회 쿼리
SELECT * FROM TAB WHERE TABTYPE='SYNONYM'


Oracle 에러코드


Visual Sutdio 2008 설치 기념 첫 산출물이다.

Oracle 에러코드를 입력 하면 해당 문구를 출력 해주는
완전 초 간단 프로그램

사용자 삽입 이미지

Visual Studio 2005 보다 체감 속도가 빨라진 듯 하다.

전체 소스는 여기
소스는 사실 볼것 없고,
실행 파일만 사용하시길 ㅎㅎ



crontab


일정 주기로 처리하는 배치 프로세스의 경우 시스템 crontab에 등록 하여 자동화 시킬 수 있다.


* crontab 명령
$ crontab [-e | -l | -r]

-e: 등록된 crontab을 편집한다.
-l: 등록된 crontab명령을 출력한다.
-r: 등록된 crontab을 삭제한다.


 * crontab 편집
$ crontab -e

위 명령을 입력하면 vi 환경에서 crontab 편집이 된다

20 13 * * 7 /home/batch/script/backup.sh
0 6 1 3 * /home/batch/script/logdel.sh
0 0 * * * /home/batch/script/daybatch.sh
30 * * * * /home/batch/script/hourbatch.sh
0,5,10,15,20,25,30,35,40,45,50,55 * * * * /home/batch/script/checksys.sh

위 내용은 vi 환경에서 crontab 샘플이다.
구분자는 공백으로 앞에서 부터 분(0~59), 시(0~23), 일(1~31), 월(1~12), 주(1:월~7:일),  실행명령을 의미한다.  ","(컴마)를 구분자로 복수 시간을 지정할 수 있다.

backup.sh는 매주 일요일 13:20에 기상시간을 알리며 실행된다.
logdel.sh는 매년 3월 1일 06:00에 삼일절을 기념으로 실행된다.
daybatch.sh는 매일 00시 00분에 퇴근시간을 알리며 실행된다.
hourbatch.sh는 매시간 30분에 실행된다.
checksys.sh는 5분마다 실행된다.


* crontab 사용시 주의 사항

crontab 으로 명령이 수행되면 기존 환경변수를 인식 하지 못한다.

crontab에 아래의 프로세스가 등록되어 있다고 가정하자.
0 * * * * /home/batch/bin/run_hourbatch

run_hourbatch는 오라클을 연동하는 Pro-C로 만들어져 있다고 가정하자.
이 프로세스는 1시간 간격으로 수행되는 배치 프로세스이다.

하지만, 이렇게 프로세스를 직접 실행을 하면 수행에 실패한다.
Error while trying to retrieve text for error ORA-12154
로그를 확인해 보면 위와 같은 메시지와 함께 DB 커넥션을 하지 못한다.

이는 crontab에 등록된 프로세스가 실행되면서 .profile에 등록된 오라클 환경변수를 읽어 들이지 않기 때문에 발생하는 것이다.

crontab의 내용을 아래와 같이 수정하자
30 * * * * /home/batch/script/hourbatch.sh

그리고 아래와 같은 hourbatch.sh라는 shell script를 생성한다.
$ vi /home/batch/script/hourbatch.sh
#!/bin/ksh
# 오라클 환경 변수 설정
ORACLE_SID=TESTDB;
export ORACLE_SID
ORACLE_BASE=/home/oracle/app/oracle;
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.1.1;
export ORACLE_HOME
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data;
export ORA_NLS33
ORACLE_TERM=vt100;
export ORACLE_TERM
NLS_LANG=American_America.KO16KSC5601;
export NLS_LANG
PATH=$ORACLE_HOME/bin:$PATH;
export PATH
TNS_ADMIN=$ORACLE_HOME/network/admin;
export TNS_ADMIN

# batch 기동 로그 파일
BATCHLOG=`date '+%y%m%d_batch.log'`

# 시간 배치 실행
echo `date '+%H:%M'` + "^Hour Batch START" >> "/home/batch/log/"$BATCHLOG
/home/batch/bin/run_hourbatch
echo `date '+%H:%M'` + "^Hour Batch END" >> "/home/batch/log/"$BATCHLOG

이렇게 하고 hourbatch.sh에 실행 권한을 주면
매시간 30분이 되면 run_hourbatch가 정상적으로 실행된다.