오라클 테이블의 레코드 사이즈 계산을 쉽게하자

오라클 DB 테이블의 레코드 사이즈 계산을 할 필요가 있었다.

구할려고 보니, 데이터 형별로 계산 공식이 틀리다.


쉽게 계산하기 위해 HTML 로 작성해봤다.

사용방법은 DB테이블 Description의 레코드별 사이즈 부분을 TextArea에 붙여 넣고 실행하면 된다.


주요 소스 내용은 아래와 같다.


<head>
	<title>테이블 레코드 계산</title>
	<script src="http://code.jquery.com/jquery-1.5.min.js" type="text/javascript" 
                      charset="utf-8"></script>
    <script>
        /* 계산 공식 출처 
        	http://epoonet.egloos.com/4479971
        */
    	var pTable = [
    		{ type: "char", 	calc:function(n){ return n; }},
    		{ type: "varchar2", calc:function(n){ return n; }},
    		{ type: "nchar",	calc:function(n){ return 2 * n;  }},
    		{ type: "nvarchar2",calc:function(n){ return 2 * n; }},
    		{ type: "number",   calc:function(n){ return 1+ Math.ceil(n/2); }},
    		{ type: "date",		calc:function(n){ return 7; }},
    		{ type: "raw",		calc:function(n){ return n; }},
    		{ type: "long",		calc:function(n){ return n; }},
    		{ type: "long raw", calc:function(n){ return n; }},
    		{ type: "bfile", 	calc:function(n){ return 530; }},
    		{ type: "rowid", 	calc:function(n){ return 6; }},
    		{ type: "timestamp",calc:function(n){ return 13; }}
    	];
    	
        $(document).ready(
            function(){
            $("#DoIt").click(function() {
				var resultText = "";
				var inputText = $("textarea#src").val().toLowerCase().split("\n");
				var lineString;
				var size =0, sum =0;
				var buf;
				
				for(var i=0; i<inputText.length; i++) {
				
					size = 0;
				
					if ( lineString=$.trim(inputText[i]) ) {
						for(var j=0; j<pTable.length; j++) {
							if ( lineString.substr(0, pTable[j].type.length) 
                                                              == pTable[j].type ) {
								try {
								   size = Number(lineString.split("(")[1].split(")")[0]);
								} catch (e) { 
								   size = 0;
								}

								size = pTable[j].calc(size);
								break;
							}
						}
					}
					
					sum += size;
					resultText += lineString + " : " + size + "<br/>";
					
				}
				
				resultText += "<br/><b>sum: " + sum + "</b>";
				$("#result").html( resultText );
				
            });
        });
    </script>
	<style type="text/css">
        * { font-size:12px; font-family:맑은고딕 }
    </style>
</head>


<body>
<textarea id="src" rows="20" cols="80"></textarea>
<br/>
<input type="button" value="Do It!" id="DoIt">
<br/>
<br/>
<span id="result"></span>


직접 실행을 위한 URL은 아래와 같다.

http://host.newtype.pe.kr/tools/ora_table_size.htm 

오라클 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



오라클에서 조회한 결과를 Text 파일로 저장하기

SELECT 결과를 CSV 포멧으로 저장 하려면 아래와 같이 처리 한다.


sqlplus에 로긴 하여 위와 같이 입력하면, output.lst 파일에 원하는 내용이 저장된다.

다른 방법으로 파이프를 이용하는 방법이 있다.


쉘에서 이렇게 입력을 하면, 원하는 결과를 얻을 수 있다.

[출처]

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


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가 정상적으로 실행된다.




JSP 만으로 오라클 접속


서블릿을 사용하지 않고 JSP만 이용
commit, rollback 지원 하지 않고 단순 조회용

접속 정보와 쿼리를 날릴 Index.jsp


쿼리 결과를 조회할 result.jsp


VB 에서 오라클 DB 조회

1. 오라클 클라이언트를 설치하고

2. TNS 설정을 해주고.

3. ADODB를 이용해 접속 하면 됩니다.

예제 코드는 아래와 같습니다.

전체 소스 보기




주요 서브루틴을 설명하면..

Private Sub dbCon() 에서 오라클 DB에 연결하고,

Private Sub Command1_Click() 에서는 쿼리를 이용해 실제 Data를 가져와서
MSFlexGrid에 데이타를 넣습니다.

Private Sub btnExport_Click() 에서는 엑셀을 열어 워크시트를 하나 생성하고,
MSFlexGrid에 있던 데이타를 넣습니다.

오랜만에 VB를 할려니 삽질이네요.
하지만 역시 잘아는 VC보다도 생산성이 좋네요 ㅎㅎ