root 계정으로 접속 하였을 경우
오라클 로 권한 변경
~]$ su - oracle
오라클 폴더로 이동
~]$ cd /oracle
sqlplus 로 노로그인 상태로 접속
~]$ sqlplus /nolog
sysdba 접속
SQL> connect /as sysdba
중지시키기
SQL> shutdown abort
시작
SQL> startup
종료
SQL> exit
리스너 종료
~]$ lsnrctl stop
리스너 재시작
~]$ lsnrctl start
오라클 SYS_GUID() 함수
MSSQL 에 NEWID() 라는 함수가 있다. 이와 비슷한 함수가 오라클에는 SYS_GUID 함수이다.
데이터베이스 레코드는 각 레코드별로 무결성을 유지 해야한다. 즉 서로다른 레코드가 같은 값을 가지면 않된다. 테이블의 어느 한 필드 값은 반드시 달라야 한다. 어떤경우 이러한 상태를 유지하기가 어려울 때가 종종 있다. 이런경우 테이블의 한 필드를 반드시 서로 다른 값을 넣어야 한다.
우리가 다른 레코드와 다른 값을 갖도록 유지 하려면 다른 레코드들을 모두 검색해 보아야 할 것이다. 그러나 레코드 수가 많아지면 속도의 유지를 보장할 수 없다. 그러므로,, 항상 어느상황에서든 난수적으로 다른 값이 나오도록 하는 함수가 필요하다. 이런경우 SYS_GUID함수를 사용한다.
SYS_GUID함수의 리턴값은 반드시 호출할때마다 다른 값의 문자열을 출력하도록 설계 되어 있다.
SYS_GUID
문법
sys_guid::=
목적
SYS_GUID함수는 16바이트로 구성된 고유전역식별자(globally unique identifier,RAW 값)을 생성하여 반환한다. 대부분의 플랫폼에서는, 생성된 식별자는 호스트 식별자, 프로세스 또는 프로세스의 thread 식별자 또는 함수를 호출하는 thread, 프로세스 또는 thread에 대한 비반복치값(바이스의 순서)로 구성된다.
예제
다음 예제는 hr.locations 테이블에서 열을 추가하고, 각행에 고유 인식자를 삽입하고, 고유전역식별자의 16바이트 행값을 32-문자 16진수 표기를 반환한다.
ALTER TABLE locations ADD (uid_col RAW(32));UPDATE locations SET uid_col = SYS_GUID();SELECT location_id, uid_col FROM locations;LOCATION_ID UID_COL----------- ---------------------------------------- 1000 7CD5B7769DF75CEFE034080020825436 1100 7CD5B7769DF85CEFE034080020825436 1200 7CD5B7769DF95CEFE034080020825436 1300 7CD5B7769DFA5CEFE034080020825436. . .참조: http://www.statwith.pe.kr/ORACLE/functions153.htm#i79194
데이터베이스 레코드는 각 레코드별로 무결성을 유지 해야한다. 즉 서로다른 레코드가 같은 값을 가지면 않된다. 테이블의 어느 한 필드 값은 반드시 달라야 한다. 어떤경우 이러한 상태를 유지하기가 어려울 때가 종종 있다. 이런경우 테이블의 한 필드를 반드시 서로 다른 값을 넣어야 한다.
우리가 다른 레코드와 다른 값을 갖도록 유지 하려면 다른 레코드들을 모두 검색해 보아야 할 것이다. 그러나 레코드 수가 많아지면 속도의 유지를 보장할 수 없다. 그러므로,, 항상 어느상황에서든 난수적으로 다른 값이 나오도록 하는 함수가 필요하다. 이런경우 SYS_GUID함수를 사용한다.
SYS_GUID함수의 리턴값은 반드시 호출할때마다 다른 값의 문자열을 출력하도록 설계 되어 있다.
SYS_GUID
문법
sys_guid::=
목적
SYS_GUID함수는 16바이트로 구성된 고유전역식별자(globally unique identifier,RAW 값)을 생성하여 반환한다. 대부분의 플랫폼에서는, 생성된 식별자는 호스트 식별자, 프로세스 또는 프로세스의 thread 식별자 또는 함수를 호출하는 thread, 프로세스 또는 thread에 대한 비반복치값(바이스의 순서)로 구성된다.
예제
다음 예제는 hr.locations 테이블에서 열을 추가하고, 각행에 고유 인식자를 삽입하고, 고유전역식별자의 16바이트 행값을 32-문자 16진수 표기를 반환한다.
ALTER TABLE locations ADD (uid_col RAW(32));UPDATE locations SET uid_col = SYS_GUID();SELECT location_id, uid_col FROM locations;LOCATION_ID UID_COL----------- ---------------------------------------- 1000 7CD5B7769DF75CEFE034080020825436 1100 7CD5B7769DF85CEFE034080020825436 1200 7CD5B7769DF95CEFE034080020825436 1300 7CD5B7769DFA5CEFE034080020825436. . .참조: http://www.statwith.pe.kr/ORACLE/functions153.htm#i79194
Toad에서 DB 접속시 "Can't initialize OCI. -1 Error" 발생
Toad에서 DB 접속시 "Can't initialize OCI. -1 Error" 발생 Oracle
Oracle을 설치하고 Toad를 설치 한 후,
기존에 사용하던 tnsnames.ora 파일을 복사하여
Oracle\product\10.2.0\client_1\NETWORK\ADMIN 에 붙여넣고
DB에 접속했다.
하지만 "Can't initialize OCI. -1 Error" alert 발생
[해결]
ORACLE_HOME의 환경 변수가 잘못 되었음
C:oracle\product\10.2.0\client_1
Oracle을 설치하고 Toad를 설치 한 후,
기존에 사용하던 tnsnames.ora 파일을 복사하여
Oracle\product\10.2.0\client_1\NETWORK\ADMIN 에 붙여넣고
DB에 접속했다.
하지만 "Can't initialize OCI. -1 Error" alert 발생
[해결]
ORACLE_HOME의 환경 변수가 잘못 되었음
C:oracle\product\10.2.0\client_1
[오라클] 실수로 삭제한 데이터 복구하기
[오라클] 실수로 삭제한 데이터 복구하기
예) kfm08ot1이라는 테이블의 bnk_cd ='04' 인 데이터를 실수로 삭제를 했다.
commit; 도 완료된 상태라면..
앞이 막막할것이다.
이럴땐 이렇게 데이터를 불러보자..
오라클 실수로 삭제한 데이터 복구하기
SELECT * FROM KFM08OT1
as of timestamp ( systimestamp - interval '10' minute)
where bnk_cd = '04'
조회후 파일을 txt나 엑셀로 저장후..
다시 임포트 해야 합니다.
아래와같은 방법으로 해보니 된다....ㅋㅋ 엑셀로 임포트 작업안해도됨!!
INSERT INTO EMP (SELECT *
FROM EMP
AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '1' MINUTE))
예) kfm08ot1이라는 테이블의 bnk_cd ='04' 인 데이터를 실수로 삭제를 했다.
commit; 도 완료된 상태라면..
앞이 막막할것이다.
이럴땐 이렇게 데이터를 불러보자..
오라클 실수로 삭제한 데이터 복구하기
SELECT * FROM KFM08OT1
as of timestamp ( systimestamp - interval '10' minute)
where bnk_cd = '04'
조회후 파일을 txt나 엑셀로 저장후..
다시 임포트 해야 합니다.
아래와같은 방법으로 해보니 된다....ㅋㅋ 엑셀로 임포트 작업안해도됨!!
INSERT INTO EMP (SELECT *
FROM EMP
AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '1' MINUTE))
테이블 스페이스 이동 (오라클, alter table)
테이블 및 인덱스에 대하여서도 현재의 테이블스페이스에서 다른 테이블 스페이스로 온라인상에서 이동 할수 있다.(기존에는 해당 테이블에 대하여 백업을 수행하고, 스키마를 생성 하고, 데이터 로딩 작업을 수행 했다.ddl-->imp>)
1. 테이블에 대한 테이블스페이스 이동 :
SQL> alter table move tablespace ;
alter table dept move tablespace tools ; (users --> tools)
SQL> select table_name, tablespace_name
2 from user_tables
3 where table_name like 'DEPT%'
TABLE_NAME TABLESPACE_NAME
------------------ ------------------------------
DEPT USERS
DEPT_1 USERS
DEPT_2 USERS
SQL> alter table dept_1 move tablespace tools;
Table altered.
SQL> select table_name, tablespace_name
2 from user_tables
3 where table_name like 'DEPT%'
TABLE_NAME TABLESPACE_NAME
------------------ ------------------------------
DEPT USERS
DEPT_1 TOOLS
DEPT_2 USERS
2. 인덱스에 대한 테이블스페이스 이동 : 인덱스를 Rebuild 한다
SQL> alter index rebuild tablespace < tablespace_name> ;
alter index pk_dept rebuild tablespace tools ; (users --> tools)
SQL> select index_name, tablespace_name
2 from user_indexes
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PK_DEPT TOOLS
PK_EMP USERS
SQL> alter index pk_dept rebuild tablespace users;
Index altered.
SQL> select index_name, tablespace_name
2 from user_indexes
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PK_DEPT USERS
PK_EMP USERS
1. 테이블에 대한 테이블스페이스 이동 :
SQL> alter table
alter table dept move tablespace tools ; (users --> tools)
SQL> select table_name, tablespace_name
2 from user_tables
3 where table_name like 'DEPT%'
TABLE_NAME TABLESPACE_NAME
------------------ ------------------------------
DEPT USERS
DEPT_1 USERS
DEPT_2 USERS
SQL> alter table dept_1 move tablespace tools;
Table altered.
SQL> select table_name, tablespace_name
2 from user_tables
3 where table_name like 'DEPT%'
TABLE_NAME TABLESPACE_NAME
------------------ ------------------------------
DEPT USERS
DEPT_1 TOOLS
DEPT_2 USERS
2. 인덱스에 대한 테이블스페이스 이동 : 인덱스를 Rebuild 한다
SQL> alter index
alter index pk_dept rebuild tablespace tools ; (users --> tools)
SQL> select index_name, tablespace_name
2 from user_indexes
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PK_DEPT TOOLS
PK_EMP USERS
SQL> alter index pk_dept rebuild tablespace users;
Index altered.
SQL> select index_name, tablespace_name
2 from user_indexes
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PK_DEPT USERS
PK_EMP USERS
오라클 PARTITION BY 와 소팅 기법 BY VINS
샘플 상황 가정
TABLE : SAMPLE_TABLE
FIELD : IDX (순번), CATEGORIES (카테고리), SUBJECT(제목),
CONTENT(내용), READCOUNT(읽은수)
조건 상황 가정
-- SAMPLE_TABLE을 기본 READCOUNT필드의 역순으로 정렬하는 소팅 인덱스를 구하고
CATEGORIES필드를 기준으로 각 CATEGORIES마다의 소팅 인덱스를 값을 표현하라
SELECT
IDX, CATEGORIES, SUBJECT, CONTENT,
ROW_NUMBER() OVER ( ORDER BY READCOUNT DESC) AS READSORT,
ROW_NUMBER() OVER (PARTITION BY CATEGORIES ORDER BY READCOUNT DESC) AS PARTITIONSORT
FROM SAMPLE_TABLE;
TABLE : SAMPLE_TABLE
FIELD : IDX (순번), CATEGORIES (카테고리), SUBJECT(제목),
CONTENT(내용), READCOUNT(읽은수)
조건 상황 가정
-- SAMPLE_TABLE을 기본 READCOUNT필드의 역순으로 정렬하는 소팅 인덱스를 구하고
CATEGORIES필드를 기준으로 각 CATEGORIES마다의 소팅 인덱스를 값을 표현하라
SELECT
IDX, CATEGORIES, SUBJECT, CONTENT,
ROW_NUMBER() OVER ( ORDER BY READCOUNT DESC) AS READSORT,
ROW_NUMBER() OVER (PARTITION BY CATEGORIES ORDER BY READCOUNT DESC) AS PARTITIONSORT
FROM SAMPLE_TABLE;
[오라클]PARTITION 인덱스 테이블스페이스 변경[INDEX REBUILD]
[오라클]PARTITION 인덱스 테이블스페이스 변경[INDEX REBUILD]
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
◎ 범례
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
대문자 : Reserved Word (오라클 예약어)
소문자 : User Define (사용자가 직접 입력해야 하는 부분)
[ ] : Option (지정하지 않아도 되거나 생략시 기본 설정값으로 대체됨)
or : Choice(여러가지중 하나를 선택한다)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
◇ FORMAT
──────────────────────────────────────────────
ALTER INDEX index_name REBUILD PARTITION partition_name
[ TABLESPACE tablespace_name]
[ PARALLEL para_num ]
[ LOGGING or NOLOGGING ]
index_name : 변경 하고자 하는 인텍스 명
partition_name : 변경 하고자 하는 PARTITION INDEX명
tablespace_name : 생성시킬 테이블스페이스명을 지정한다.
생략시 기존에 생성되어 있는 테이블 스페이스에 재생성된다.
※ 기존의 테이블 스페이스와 다른 테이블스페이스를 지정하면 해당 인덱스가 이동 하는
결과를 얻을 수 있다. (테이블스페이스 변경)
para_num : PARALLEL 처리(병렬처리)를 하고자 하는 경우에 사용한다.
처리할 데이터가 많은 경우 CPU가 지정한 para_num 수치 만큼 프로세스를 분리하여
처리한다.
LOGGING or NOLOGGING : LOG처리를 할 것인지를 결정한다. 대량의 자료를 처리하는 경우
NOLOGGING으로 처리하면 처리속도를 올릴 수 있다.
◆ 예제
──────────────────────────────────────────────
예1) ALTER INDEX idx_empno REBUILD PARTITION idx_empno_part1;
: 인덱스 idx_empno의 PARTITION INDEX idx_empno_part1을 재생성한다.
예2) ALTER INDEX idx_empno REBUILD PARTITION idx_empno_part1
TABLESPACE ts_emp_management;
: 인덱스 idx_empno의 PARTITION INDEX idx_empno_part1을 테이블스페이스
ts_emp_management에 재생성한다.
이때 기존에 idx_empno가 있는 테이블스페이스와 ts_emp_management가 틀리면
기존의 테이블스페이스에 있는 idx_empno는 삭제된다.
즉, 인덱스의 테이블스페이스를 변경할 수 있게 된다.
※ 적용
──────────────────────────────────────────────
ORACLE 8i 이상
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
◎ 범례
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
대문자 : Reserved Word (오라클 예약어)
소문자 : User Define (사용자가 직접 입력해야 하는 부분)
[ ] : Option (지정하지 않아도 되거나 생략시 기본 설정값으로 대체됨)
or : Choice(여러가지중 하나를 선택한다)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
◇ FORMAT
──────────────────────────────────────────────
ALTER INDEX index_name REBUILD PARTITION partition_name
[ TABLESPACE tablespace_name]
[ PARALLEL para_num ]
[ LOGGING or NOLOGGING ]
index_name : 변경 하고자 하는 인텍스 명
partition_name : 변경 하고자 하는 PARTITION INDEX명
tablespace_name : 생성시킬 테이블스페이스명을 지정한다.
생략시 기존에 생성되어 있는 테이블 스페이스에 재생성된다.
※ 기존의 테이블 스페이스와 다른 테이블스페이스를 지정하면 해당 인덱스가 이동 하는
결과를 얻을 수 있다. (테이블스페이스 변경)
para_num : PARALLEL 처리(병렬처리)를 하고자 하는 경우에 사용한다.
처리할 데이터가 많은 경우 CPU가 지정한 para_num 수치 만큼 프로세스를 분리하여
처리한다.
LOGGING or NOLOGGING : LOG처리를 할 것인지를 결정한다. 대량의 자료를 처리하는 경우
NOLOGGING으로 처리하면 처리속도를 올릴 수 있다.
◆ 예제
──────────────────────────────────────────────
예1) ALTER INDEX idx_empno REBUILD PARTITION idx_empno_part1;
: 인덱스 idx_empno의 PARTITION INDEX idx_empno_part1을 재생성한다.
예2) ALTER INDEX idx_empno REBUILD PARTITION idx_empno_part1
TABLESPACE ts_emp_management;
: 인덱스 idx_empno의 PARTITION INDEX idx_empno_part1을 테이블스페이스
ts_emp_management에 재생성한다.
이때 기존에 idx_empno가 있는 테이블스페이스와 ts_emp_management가 틀리면
기존의 테이블스페이스에 있는 idx_empno는 삭제된다.
즉, 인덱스의 테이블스페이스를 변경할 수 있게 된다.
※ 적용
──────────────────────────────────────────────
ORACLE 8i 이상
오라클에서 테이블명을 변경하는 쿼리 RENAME TABLE
- 오라클에서 테이블명을 변경하는 쿼리는 RENAME 명령어
* ALTER TABLE org_tbl_name RENAME TO tar_tbl_name
* RENAME org_tbl_name TO tar_tbl_name
* ALTER TABLE org_tbl_name RENAME TO tar_tbl_name
* RENAME org_tbl_name TO tar_tbl_name
SQL 인젝션 방지
이 포스트를 보낸곳 ()
SQL 인젝션 방지
개발/DB 2006/01/05 15:50
** Sql injection 필요조건
1. 사용자의 입력 값을 필터링, 인코딩 없이 그대로 서버 페이지에서 사용한다.
2. 그 입력 값을 이용해서 서버 페이지는 쿼리를 수행한다.
3. 명령 실행을 위해 사용되는 SQL 서버 계정이 SA일 경우, 피해는 더 커질 수 있다.
--상세한 방법
http://www.taeyo.net/Lecture/NET/Secure_Injection.asp
http://www.unixwiz.net/techtips/sql-injection.html
http://www.securiteam.com/securityreviews/5DP0N1P76E.html
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
** SQL injection 사전방지
- 사용자의 입력은 결코 신뢰하지 않는다
- 정규 표현식을 사용하여 적법한 입력 외에는 거부한다.
- 저장 프로시저를 사용한다(웹페이지에서 문자열 동적 쿼리는 피한다)
* 최대 문자열의 길이나 타입을 한정한다?
- Sysadmin 계정이 아닌 최소 권한 계정으로 데이터베이스에 연결하게 한다(sa계정을 사용하지 않는다)
- sql 쉘 실행권한을 없앤다. (주기적인 확인필요, 공격시 쉘권한을 복구하여 실행하는경우가 있음)
- 데이터베이스 연결 문자열을 가급적 config 파일에 저장하지 않는다(asp.net의 경우 web.config에 암호화하여 지정)
- 공격자에게 너무 많은 오류 정보를 노출하지 않는다
** SQL injection 방법과 대책
* 웹페이지에서 동적으로 쿼리 빌드 시, 문자열 연결이 아닌 매개변수 쿼리를 사용조작 및 주석처리화 및 에러분석가능
1. 사용자 인증공격 : 정상적인 sql을 변조하여 비정상적으로 통과
** 방식
입력창 : http://duck/index.asp?category=food' or 1=1--
처리되는 쿼리 : SELECT * FROM product WHERE PCategory='food' or 1=1--'
** 로그인경우
rs = select * from user_table where id='ID' and passwd='PWD'
if rs==1
//성공시
else
//실패시.
==> select * from user_table where id='admin' and passwd='' or 'x'='x';
: true, false, true => 결론적으로 true
** 대책
- 사용자의 모든입력값 및 url인자값에 대하여 특수문자 필터링(',<,>,;,--)
- 사용자의 모든 입력값, 크기 및 url인자값에 대하여 sql명령어 금지("/\;:Space--+등과 or, and, union, select, update, insert 등검사)
=> 해당변수의 특수문제 제거및 변형 Request.QueryString["ID"].ToString().Replace("'","\"");
//입력값일 경우 client 자바스크립트로 처리 실제
//한글포함여부
function is_han(val) { //한글이 하나라도 섞여 있으면 true를 반환
var judge = false;
for(var i = 0; i < val.length; i++) {
var chr = val.substr(i,1);
chr = escape(chr);
if (chr.charAt(1) == "u") {
chr = chr.substr(2, (chr.length - 1));
if((chr >= "3131" && chr <= "3163") || (chr >= "AC00" && chr <= "D7A3")) {
judge = true;
break;
}
}
else judge = false;
}
return judge;
}
//한글만 입력가능
function han_only(val) { //한글로만 되있으면 true를 반환 = 영어, 숫자, 특수문자가 있으면 false
var judge = false;
for(var i = 0; i < val.length; i++) {
var chr = val.substr(i,1);
chr = escape(chr);
if (chr.charAt(1) == "u") {
chr = chr.substr(2, (chr.length - 1));
if((chr >= "3131" && chr <= "3163") || (chr >= "AC00" && chr <= "D7A3")) judge = true;
}
else {
judge = false;
break;
}
}
return judge;
}
//영문만 입력확인
function eng_only(val) { //영어로만 되있으면 true를 반환 = 한글, 숫자, 특수문자가 있으면 false
var re = /^[A-Za-z]+$/g;
var rs = re.test(val);
return rs;
}
//이메일주소확인
function is_email(str) { //규정에 맞는 email 주소인지 체크
var r1 = new RegExp("(@.*@)|(\\.\\.)|(@\\.)|(^\\.)");
var r2 = new RegExp("^.+\\@(\\[?)[a-zA-Z0-9\\-\\.]+\\.([a-zA-Z]{2,3}|[0-9]{1,3})(\\]?)$");
return (!r1.test(str) && r2.test(str));
}
//숫자만 입력 확인
function is_number(str) {
var r = new RegExp("^[0-9]+$");
return r.test(str);
}
//전화번호 입력 확인
function is_phone(str) {
var r = new RegExp("^[0-9]{2,4}-[0-9]{2,4}-[0-9]{4,4}$");
return r.test(str);
}
//공백제거
function trim(str) { //trim()함수 구현
var newStr = str.replace(/^\s+/,"").replace(/\s+$/,"");
return newStr;
}
//엔터키처리
function enter_key(form) { //enter key를 눌렀을 때 submit
//처럼 사용한다
if (event.keyCode ==13) {
form.submit();
}
}
//날짜형식확인
function is_date(datein){ // 입력날짜의 기본값은 mm/dd/yy이고 다른 형식이면 arguments를 줘야한다.
var type = isDate.arguments[1];
var rval = false;
var indate=datein;
if (indate.indexOf("-")!=-1) var sdate = indate.split("-");
else var sdate = indate.split("/");
if(type=="yy/mm/dd") {
var newdate = Array(3);
newdate[0] = sdate[1];
newdate[1] = sdate[2];
newdate[2] = sdate[0];
indate = newdate.join("/");
sdate = indate.split("/");
}
var chkDate=new Date(Date.parse(indate))
var cmpDate=(chkDate.getMonth()+1)+"/"+(chkDate.getDate())+"/"+(chkDate.getYear())
var indate2=(Math.abs(sdate[0]))+"/"+(Math.abs(sdate[1]))+"/"+(Math.abs(sdate[2]))
if (indate2!=cmpDate) rval = false;
else {
if (cmpDate=="NaN/NaN/NaN") rval = false;
else rval = true;
}
return rval;
}
//주민번호 확인
function is_ssn(SSN1, SSN2) {
if (SSN1.length != 6 || SSN2.length != 7) return false;
var SSN = SSN1 + SSN2;
var strA, strB, strC, strD, strE, strF, strG, strH, strI, strJ, strK, strL, strM, strN, strO;
var nCalA, nCalB, nCalC;
strA = SSN.substr(0, 1);
strB = SSN.substr(1, 1);
strC = SSN.substr(2, 1);
strD = SSN.substr(3, 1);
strE = SSN.substr(4, 1);
strF = SSN.substr(5, 1);
strG = SSN.substr(6, 1);
strH = SSN.substr(7, 1);
strI = SSN.substr(8, 1);
strJ = SSN.substr(9, 1);
strK = SSN.substr(10, 1);
strL = SSN.substr(11, 1);
strM = SSN.substr(12, 1);
// CheckSum
strO = strA*2 + strB*3 + strC*4 + strD*5 + strE*6 + strF*7 + strG*8 + strH*9 + strI*2 + strJ*3 + strK*4 + strL*5;
nCalA = eval(strO);
nCalB = nCalA % 11;
nCalC = 11 - nCalB;
nCalC = nCalC % 10;
strv = '19';
strw = SSN.substr(0, 2);
strx = SSN.substr(2, 2);
stry = SSN.substr(4, 2);
// 날짜수 체크
strz = strv + strw;
if ((strz % 4 == 0) && (strz % 100 != 0) || (strz % 400 == 0)) yunyear = 29;
else yunyear = 28;
if ((strx <= 0) || (strx > 12)) return false;
if ((strx == 1 || strx == 3 || strx == 5 || strx == 7 || strx == 8 || strx == 10 || strx == 12) && (stry > 31 || stry <= 0)) return false;
if ((strx == 4 || strx == 6 || strx == 9 || strx == 11) && (stry > 30 || stry <= 0)) return false;
if (strx == 2 && (stry > yunyear || stry <= 0)) return false;
if (!((strG == 1) || (strG == 2) || (strG == 3) || (strG ==4))) return false;
if ( nCalC != strM ) return false;
return true;
}
//입력시 값 체크 onkeydown="handlerNum()", 최대값 제한 : MaxLength="5"
function handlerNum()
{
e = window.event; //윈도우의 event를 잡는것입니다. 그냥 써주심됩니당.
//숫자열 0 ~ 9 : 48 ~ 57, 키패드 0 ~ 9 : 96 ~ 105 ,8 : backspace, 46 : delete -->키코드값을 구분합니다.
if(e.keyCode >= 48 && e.keyCode <= 57 || e.keyCode >= 96 && e.keyCode <= 105 || e.keyCode == 8 || e.keyCode == 46)
{ //delete나 backspace는 입력이 되어야되니까..
if(e.keyCode == 48 || e.keyCode == 96)//0을 눌렀을경우
{
if(txtBox1.value == "" ) //아무것도 없는상태에서 0을 눌렀을경우
e.returnValue=false; //-->입력되지않는다.
else //다른숫자뒤에오는 0은
return; //-->입력시킨다.
}
else //0이 아닌숫자
return; //-->입력시킨다.
}
else //숫자가 아니면 넣을수 없다.
{
alert('숫자만 입력가능합니다');
e.returnValue=false;
}
}
** 동적 쿼리르 사용하지 않고 스토어 프로시저와 파라미터를 사용
private void Get_List(int ID)
{
try
{
string ConnectStr = ConnectionString();
SqlConnection Conn = new SqlConnection(ConnectStr);
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "test_GetJobsID"; //스토어 프로시저사용
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@ID",SqlDbType.Int,4); //파라미터사용
Cmd.Parameters["@ID"].Value = ID; //입력값 체크한후 처리
da.SelectCommand= Cmd;
DataSet ds = new DataSet();
da.Fill(ds,"test");
DataGrid1.DataSource = ds.Tables[0].DefaultView;
DataGrid1.DataBind();
}
catch(SqlException ex)
{
//SQL에러시 처리
}
catch(Exception exp)
{
//에러처리
}
finally
{
//연결종결
}
}
//디비 연결스트링
public string ConnectionString()
{
Base64Code bc = new Base64Code();
return bc.Base64Decode(ConfigurationSettings.AppSettings["ConnectionString"]);
}
// Web.config 설정 추가
- 사용자의 모든 입력값에 대하여 불필요한 에러 메시지 숨김
//Web.config 파일 설정
mode="RemoteOnly" defaultRedirect="/error/errorinfo.aspx">
- 웹어플리케이션이 사용하는 데이타베이스 유저의 권한을 제한
2. MS-SQL상에서 시스템 명령어 실행
** 방식
- xp_cmdshell 을 이용한 시스템 명령실행.
- 기타명령어(xp_startmail, xp_sendmail, xp_dirtree, xp_regdeletekey,
xp_regenumvalues, xp_regread, xp_regwrite, sp_makewebtask, sp_adduser, ...)
** 웹페이지에 악성코드 삽입 방식 실제
; exec master..xp_cmdshell 'ping 10.10.1.2'--
; exec master..xp_cmdshell 'echo >> c:\inetpub\wwwroot\index.html';
** 명령을 삭제하여도 복구후 해킹할수 있으므로 수시확인 필요
num=119' user master dbcc addextendedproc('xp_cmdshell','xplog70.dll')
** 대책
DB의 권한 축소 및 불필요한 sp 제거 : db_owner 권한의 제거 및 일반 user권한 부여
SQL 인젝션 방지
개발/DB 2006/01/05 15:50
** Sql injection 필요조건
1. 사용자의 입력 값을 필터링, 인코딩 없이 그대로 서버 페이지에서 사용한다.
2. 그 입력 값을 이용해서 서버 페이지는 쿼리를 수행한다.
3. 명령 실행을 위해 사용되는 SQL 서버 계정이 SA일 경우, 피해는 더 커질 수 있다.
--상세한 방법
http://www.taeyo.net/Lecture/NET/Secure_Injection.asp
http://www.unixwiz.net/techtips/sql-injection.html
http://www.securiteam.com/securityreviews/5DP0N1P76E.html
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
** SQL injection 사전방지
- 사용자의 입력은 결코 신뢰하지 않는다
- 정규 표현식을 사용하여 적법한 입력 외에는 거부한다.
- 저장 프로시저를 사용한다(웹페이지에서 문자열 동적 쿼리는 피한다)
* 최대 문자열의 길이나 타입을 한정한다?
- Sysadmin 계정이 아닌 최소 권한 계정으로 데이터베이스에 연결하게 한다(sa계정을 사용하지 않는다)
- sql 쉘 실행권한을 없앤다. (주기적인 확인필요, 공격시 쉘권한을 복구하여 실행하는경우가 있음)
- 데이터베이스 연결 문자열을 가급적 config 파일에 저장하지 않는다(asp.net의 경우 web.config에 암호화하여 지정)
- 공격자에게 너무 많은 오류 정보를 노출하지 않는다
** SQL injection 방법과 대책
* 웹페이지에서 동적으로 쿼리 빌드 시, 문자열 연결이 아닌 매개변수 쿼리를 사용조작 및 주석처리화 및 에러분석가능
1. 사용자 인증공격 : 정상적인 sql을 변조하여 비정상적으로 통과
** 방식
입력창 : http://duck/index.asp?category=food' or 1=1--
처리되는 쿼리 : SELECT * FROM product WHERE PCategory='food' or 1=1--'
** 로그인경우
rs = select * from user_table where id='ID' and passwd='PWD'
if rs==1
//성공시
else
//실패시.
==> select * from user_table where id='admin' and passwd='' or 'x'='x';
: true, false, true => 결론적으로 true
** 대책
- 사용자의 모든입력값 및 url인자값에 대하여 특수문자 필터링(',<,>,;,--)
- 사용자의 모든 입력값, 크기 및 url인자값에 대하여 sql명령어 금지("/\;:Space--+등과 or, and, union, select, update, insert 등검사)
=> 해당변수의 특수문제 제거및 변형 Request.QueryString["ID"].ToString().Replace("'","\"");
//입력값일 경우 client 자바스크립트로 처리 실제
//한글포함여부
function is_han(val) { //한글이 하나라도 섞여 있으면 true를 반환
var judge = false;
for(var i = 0; i < val.length; i++) {
var chr = val.substr(i,1);
chr = escape(chr);
if (chr.charAt(1) == "u") {
chr = chr.substr(2, (chr.length - 1));
if((chr >= "3131" && chr <= "3163") || (chr >= "AC00" && chr <= "D7A3")) {
judge = true;
break;
}
}
else judge = false;
}
return judge;
}
//한글만 입력가능
function han_only(val) { //한글로만 되있으면 true를 반환 = 영어, 숫자, 특수문자가 있으면 false
var judge = false;
for(var i = 0; i < val.length; i++) {
var chr = val.substr(i,1);
chr = escape(chr);
if (chr.charAt(1) == "u") {
chr = chr.substr(2, (chr.length - 1));
if((chr >= "3131" && chr <= "3163") || (chr >= "AC00" && chr <= "D7A3")) judge = true;
}
else {
judge = false;
break;
}
}
return judge;
}
//영문만 입력확인
function eng_only(val) { //영어로만 되있으면 true를 반환 = 한글, 숫자, 특수문자가 있으면 false
var re = /^[A-Za-z]+$/g;
var rs = re.test(val);
return rs;
}
//이메일주소확인
function is_email(str) { //규정에 맞는 email 주소인지 체크
var r1 = new RegExp("(@.*@)|(\\.\\.)|(@\\.)|(^\\.)");
var r2 = new RegExp("^.+\\@(\\[?)[a-zA-Z0-9\\-\\.]+\\.([a-zA-Z]{2,3}|[0-9]{1,3})(\\]?)$");
return (!r1.test(str) && r2.test(str));
}
//숫자만 입력 확인
function is_number(str) {
var r = new RegExp("^[0-9]+$");
return r.test(str);
}
//전화번호 입력 확인
function is_phone(str) {
var r = new RegExp("^[0-9]{2,4}-[0-9]{2,4}-[0-9]{4,4}$");
return r.test(str);
}
//공백제거
function trim(str) { //trim()함수 구현
var newStr = str.replace(/^\s+/,"").replace(/\s+$/,"");
return newStr;
}
//엔터키처리
function enter_key(form) { //enter key를 눌렀을 때 submit
//처럼 사용한다
if (event.keyCode ==13) {
form.submit();
}
}
//날짜형식확인
function is_date(datein){ // 입력날짜의 기본값은 mm/dd/yy이고 다른 형식이면 arguments를 줘야한다.
var type = isDate.arguments[1];
var rval = false;
var indate=datein;
if (indate.indexOf("-")!=-1) var sdate = indate.split("-");
else var sdate = indate.split("/");
if(type=="yy/mm/dd") {
var newdate = Array(3);
newdate[0] = sdate[1];
newdate[1] = sdate[2];
newdate[2] = sdate[0];
indate = newdate.join("/");
sdate = indate.split("/");
}
var chkDate=new Date(Date.parse(indate))
var cmpDate=(chkDate.getMonth()+1)+"/"+(chkDate.getDate())+"/"+(chkDate.getYear())
var indate2=(Math.abs(sdate[0]))+"/"+(Math.abs(sdate[1]))+"/"+(Math.abs(sdate[2]))
if (indate2!=cmpDate) rval = false;
else {
if (cmpDate=="NaN/NaN/NaN") rval = false;
else rval = true;
}
return rval;
}
//주민번호 확인
function is_ssn(SSN1, SSN2) {
if (SSN1.length != 6 || SSN2.length != 7) return false;
var SSN = SSN1 + SSN2;
var strA, strB, strC, strD, strE, strF, strG, strH, strI, strJ, strK, strL, strM, strN, strO;
var nCalA, nCalB, nCalC;
strA = SSN.substr(0, 1);
strB = SSN.substr(1, 1);
strC = SSN.substr(2, 1);
strD = SSN.substr(3, 1);
strE = SSN.substr(4, 1);
strF = SSN.substr(5, 1);
strG = SSN.substr(6, 1);
strH = SSN.substr(7, 1);
strI = SSN.substr(8, 1);
strJ = SSN.substr(9, 1);
strK = SSN.substr(10, 1);
strL = SSN.substr(11, 1);
strM = SSN.substr(12, 1);
// CheckSum
strO = strA*2 + strB*3 + strC*4 + strD*5 + strE*6 + strF*7 + strG*8 + strH*9 + strI*2 + strJ*3 + strK*4 + strL*5;
nCalA = eval(strO);
nCalB = nCalA % 11;
nCalC = 11 - nCalB;
nCalC = nCalC % 10;
strv = '19';
strw = SSN.substr(0, 2);
strx = SSN.substr(2, 2);
stry = SSN.substr(4, 2);
// 날짜수 체크
strz = strv + strw;
if ((strz % 4 == 0) && (strz % 100 != 0) || (strz % 400 == 0)) yunyear = 29;
else yunyear = 28;
if ((strx <= 0) || (strx > 12)) return false;
if ((strx == 1 || strx == 3 || strx == 5 || strx == 7 || strx == 8 || strx == 10 || strx == 12) && (stry > 31 || stry <= 0)) return false;
if ((strx == 4 || strx == 6 || strx == 9 || strx == 11) && (stry > 30 || stry <= 0)) return false;
if (strx == 2 && (stry > yunyear || stry <= 0)) return false;
if (!((strG == 1) || (strG == 2) || (strG == 3) || (strG ==4))) return false;
if ( nCalC != strM ) return false;
return true;
}
//입력시 값 체크 onkeydown="handlerNum()", 최대값 제한 : MaxLength="5"
function handlerNum()
{
e = window.event; //윈도우의 event를 잡는것입니다. 그냥 써주심됩니당.
//숫자열 0 ~ 9 : 48 ~ 57, 키패드 0 ~ 9 : 96 ~ 105 ,8 : backspace, 46 : delete -->키코드값을 구분합니다.
if(e.keyCode >= 48 && e.keyCode <= 57 || e.keyCode >= 96 && e.keyCode <= 105 || e.keyCode == 8 || e.keyCode == 46)
{ //delete나 backspace는 입력이 되어야되니까..
if(e.keyCode == 48 || e.keyCode == 96)//0을 눌렀을경우
{
if(txtBox1.value == "" ) //아무것도 없는상태에서 0을 눌렀을경우
e.returnValue=false; //-->입력되지않는다.
else //다른숫자뒤에오는 0은
return; //-->입력시킨다.
}
else //0이 아닌숫자
return; //-->입력시킨다.
}
else //숫자가 아니면 넣을수 없다.
{
alert('숫자만 입력가능합니다');
e.returnValue=false;
}
}
** 동적 쿼리르 사용하지 않고 스토어 프로시저와 파라미터를 사용
private void Get_List(int ID)
{
try
{
string ConnectStr = ConnectionString();
SqlConnection Conn = new SqlConnection(ConnectStr);
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "test_GetJobsID"; //스토어 프로시저사용
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@ID",SqlDbType.Int,4); //파라미터사용
Cmd.Parameters["@ID"].Value = ID; //입력값 체크한후 처리
da.SelectCommand= Cmd;
DataSet ds = new DataSet();
da.Fill(ds,"test");
DataGrid1.DataSource = ds.Tables[0].DefaultView;
DataGrid1.DataBind();
}
catch(SqlException ex)
{
//SQL에러시 처리
}
catch(Exception exp)
{
//에러처리
}
finally
{
//연결종결
}
}
//디비 연결스트링
public string ConnectionString()
{
Base64Code bc = new Base64Code();
return bc.Base64Decode(ConfigurationSettings.AppSettings["ConnectionString"]);
}
// Web.config 설정 추가
- 사용자의 모든 입력값에 대하여 불필요한 에러 메시지 숨김
//Web.config 파일 설정
mode="RemoteOnly" defaultRedirect="/error/errorinfo.aspx">
- 웹어플리케이션이 사용하는 데이타베이스 유저의 권한을 제한
2. MS-SQL상에서 시스템 명령어 실행
** 방식
- xp_cmdshell 을 이용한 시스템 명령실행.
- 기타명령어(xp_startmail, xp_sendmail, xp_dirtree, xp_regdeletekey,
xp_regenumvalues, xp_regread, xp_regwrite, sp_makewebtask, sp_adduser, ...)
** 웹페이지에 악성코드 삽입 방식 실제
; exec master..xp_cmdshell 'ping 10.10.1.2'--
; exec master..xp_cmdshell 'echo >> c:\inetpub\wwwroot\index.html';
** 명령을 삭제하여도 복구후 해킹할수 있으므로 수시확인 필요
num=119' user master dbcc addextendedproc('xp_cmdshell','xplog70.dll')
** 대책
DB의 권한 축소 및 불필요한 sp 제거 : db_owner 권한의 제거 및 일반 user권한 부여
ORACLE JOB
1. ORACLE JOB 생성.
DECLARE jobno NUMBER(5);
begin
dbms_job.submit(jobno, 'USP_TEST;', sysdate, 'sysdate + 1/24/60', FALSE );
commit;
end;
-- 위에꺼 재설명 하자면...
DECLARE jobno NUMBER(5);
begin
dbms_job.submit(
jobno, -- job의 번호. output 변수임.
'USP_TEST;', -- job이 실행할 쿼리 및 SP. single quatation('~') 으로 감싸야함.
sysdate, -- job이 실행될 시간.
'sysdate + 1/24/60' -- job의 실행간격. 여기서는 1분간격. '~' 으로 감싸야함.
);
commit;
end;
SELECT * FROM user_jobs; 실행하여 생성여부 확인.
2. ORACLE JOB 정지, 수정, 삭제
실행 : exec dbms_job.run(jobno);
정지 : exec dbms_job.broken(jobno, TRUE);
수정 : exec dbms_job.change(:jobno, USP_TEST;', sysdate, 'sysdate + 1/24/6');
-- 10분 간격
삭제 : exec dbms_job.remove(jobno);
DECLARE jobno NUMBER(5);
begin
dbms_job.submit(jobno, 'USP_TEST;', sysdate, 'sysdate + 1/24/60', FALSE );
commit;
end;
-- 위에꺼 재설명 하자면...
DECLARE jobno NUMBER(5);
begin
dbms_job.submit(
jobno, -- job의 번호. output 변수임.
'USP_TEST;', -- job이 실행할 쿼리 및 SP. single quatation('~') 으로 감싸야함.
sysdate, -- job이 실행될 시간.
'sysdate + 1/24/60' -- job의 실행간격. 여기서는 1분간격. '~' 으로 감싸야함.
);
commit;
end;
SELECT * FROM user_jobs; 실행하여 생성여부 확인.
2. ORACLE JOB 정지, 수정, 삭제
실행 : exec dbms_job.run(jobno);
정지 : exec dbms_job.broken(jobno, TRUE);
수정 : exec dbms_job.change(:jobno, USP_TEST;', sysdate, 'sysdate + 1/24/6');
-- 10분 간격
삭제 : exec dbms_job.remove(jobno);
오라클 스케줄러 샘플 oracle schedule sample
오라클 스케줄러 샘플 oracle schedule sample
[추가]
-- 30분 후부터 30분에 한번
exec dbms_job.submit(:jobno, '프로시저명;', sysdate + 1/24/2, 'sysdate + 1/24/2',true,2);
-- 오늘오후 6시부터 매일 오후6시
exec dbms_job.submit(:jobno, '프로시저명;', TRUNC(SYSDATE) + ( 36/24/2 ), 'TRUNC(SYSDATE+1) + ( 36/24/2 )',true,2);
-- 오늘오후 6시부터 10분단위로
exec dbms_job.submit(:jobno, '프로시저명;', TRUNC(SYSDATE) + ( 36/24/2 ), 'SYSDATE + 1/24/6 ',true,2);
-- 오늘오후 6시부터 30분단위로
exec dbms_job.submit(:jobno, '프로시저명;', TRUNC(SYSDATE) + ( 36/24/2 ), 'SYSDATE + 1/24/2',true,2);
1. 10분에 한번씩 실행하는 경우
sysdate + 1/24/6 또는 sysdate + 1/144
-> 1/24 (1시간-60분) / 6 : 10분 단위
1/144 : 24*6 으로 나누어도 같은 의미가 된다.
2. 1분에 한번으로 지정하는 경우
sysdate + 1/24/60 또는 sysdate + 1/1440
3. 매일 새벽 2시로 지정하는 경우
trunc(sysdate) + 1 + 2/24 -> 다음날 새벽 2시를 지정함.
4. 매일 밤 11시로 지정하는 경우
trunc(sysdate) + 23/24 -> 오늘 밤 11시를 지정했음.
[출처] 오라클 스케줄러|작성자 레인보우
[추가]
-- 30분 후부터 30분에 한번
exec dbms_job.submit(:jobno, '프로시저명;', sysdate + 1/24/2, 'sysdate + 1/24/2',true,2);
-- 오늘오후 6시부터 매일 오후6시
exec dbms_job.submit(:jobno, '프로시저명;', TRUNC(SYSDATE) + ( 36/24/2 ), 'TRUNC(SYSDATE+1) + ( 36/24/2 )',true,2);
-- 오늘오후 6시부터 10분단위로
exec dbms_job.submit(:jobno, '프로시저명;', TRUNC(SYSDATE) + ( 36/24/2 ), 'SYSDATE + 1/24/6 ',true,2);
-- 오늘오후 6시부터 30분단위로
exec dbms_job.submit(:jobno, '프로시저명;', TRUNC(SYSDATE) + ( 36/24/2 ), 'SYSDATE + 1/24/2',true,2);
1. 10분에 한번씩 실행하는 경우
sysdate + 1/24/6 또는 sysdate + 1/144
-> 1/24 (1시간-60분) / 6 : 10분 단위
1/144 : 24*6 으로 나누어도 같은 의미가 된다.
2. 1분에 한번으로 지정하는 경우
sysdate + 1/24/60 또는 sysdate + 1/1440
3. 매일 새벽 2시로 지정하는 경우
trunc(sysdate) + 1 + 2/24 -> 다음날 새벽 2시를 지정함.
4. 매일 밤 11시로 지정하는 경우
trunc(sysdate) + 23/24 -> 오늘 밤 11시를 지정했음.
[출처] 오라클 스케줄러|작성자 레인보우
DBMS_JOB PACKAGE의 사용 방법과 예제
DBMS_JOB PACKAGE의 사용 방법과 예제
Unix의 cron과 같이 오라클에서도 일정한 시점, 또는 간격으로 반복해서 job을 수행시킬 수 있다. DBMS_JOB package를 이용하여 수행시킬 수 있는데, 이것을 위해서는 SNP background process가 start되어 있어야 한다.
다음의 parameter를 init.ora file에 설정한 후 oracle을 startup하면 SNP process가 올라온다.
job_queue_processes = 1
→ 이 파라미터는 snp process를 몇 개 띄울지를 결정한다.
default=0
job_queue_interval = 60
→ 이 파라미터는 snp process가 깨어나는 간격을 초로 설정한다.
DBMS_JOB Package는 다음과 같은 procedure를 이용하여 사용한다.
DBMS_JOB.submit(job out binary_integer,
what in varchar2,
next_date in date defalut sysdate,
interval in varchar2 default 'null',
no_parse in boolean default false)
→ dbms_job.submit procedure는 job의 내용을 정의하고 oracle이 job을 수행할 수 있도록 한다.
다음의 예제를 통하여 실제 사용법을 알아보자.
[ 예제 ] file jobcre.sql
begin
dbms_job.submit(:jobno,
-- job 의 번호
'insert into scott.testdate values(1, sysdate);',
-- job의 내용 : ' '으로 감싸준다.
-- procedure를 실행하는 경우 ' username.procedure_name;' 만 쓰면 된다.
sysdate,
-- job이 실행될 시간
'sysdate + 5/24/60' ,
-- job이 실행되는 간격 , 위의 경우는 5분마다 실행하도록 했다.
-- ' '으로 감싸준다.
FALSE );
end;
/
$ sqlplus scott/tiger
SQL> variable jobno number;
SQL> @jobcre
SQL> print jobno -- job 번호 확인 : 여기서는 166번
SQL> exec dbms_job.run(166);
SQL> commit;
지금부터 interval에 따라 job이 실행된다.
job 실행 여부를 알아보기 위해서 다음의 sql 문장을 수행한다.
SQL> col what format a20
SQL> select what, job, next_date, next_sec, failures, broken from user_jobs;
그 외에
SQL> exec dbms_job.run(jobno);
- job의 강제 실행, job이 16번 fail되어 broken된 경우는 위의 명령어로 강제로 run을 시켜서 실행되면 다시 interval마다 실행된다.
SQL> exec dbms_job.broken(jobno, TRUE);
- job을 disable시킴
SQL> exec dbms_job.remove(jobno);
- job의 삭제
snapshot과 job과의 관계
snapshot 도 job 으로 등록되어 돌아갑니다.
즉, select job, what from dba_jobs; 를 조회하면, what 부분에 snapshot 이 정의되어 있습니다.
따라서, snapshot 에 대한 disable 방법 등은 job 과 같습니다.
interval 시간 지정 예제
1. 10분에 한번씩 실행하는 경우
sysdate + 1/24/6 또는 sysdate + 1/144
→ 1/24 (1시간-60분) / 6 : 10분 단위, 1/144 : 24*6 으로 나누어도 같은 의미가 된다.
2. 1분에 한번으로 지정하는 경우
sysdate + 1/24/60 또는 sysdate + 1/1440
3. 매일 새벽 2시로 지정하는 경우
trunc(sysdate) + 1 + 2/24 -> 다음날 새벽 2시를 지정함.
4. 매일 밤 11시로 지정하는 경우
trunc(sysdate) + 23/24 -> 오늘 밤 11시를 지정했음.
** 자신이 등록한 job의 목록을 보고 싶다면 아래의 쿼리를 날려도된다. **
select * from user_jobs;
Unix의 cron과 같이 오라클에서도 일정한 시점, 또는 간격으로 반복해서 job을 수행시킬 수 있다. DBMS_JOB package를 이용하여 수행시킬 수 있는데, 이것을 위해서는 SNP background process가 start되어 있어야 한다.
다음의 parameter를 init.ora file에 설정한 후 oracle을 startup하면 SNP process가 올라온다.
job_queue_processes = 1
→ 이 파라미터는 snp process를 몇 개 띄울지를 결정한다.
default=0
job_queue_interval = 60
→ 이 파라미터는 snp process가 깨어나는 간격을 초로 설정한다.
DBMS_JOB Package는 다음과 같은 procedure를 이용하여 사용한다.
DBMS_JOB.submit(job out binary_integer,
what in varchar2,
next_date in date defalut sysdate,
interval in varchar2 default 'null',
no_parse in boolean default false)
→ dbms_job.submit procedure는 job의 내용을 정의하고 oracle이 job을 수행할 수 있도록 한다.
다음의 예제를 통하여 실제 사용법을 알아보자.
[ 예제 ] file jobcre.sql
begin
dbms_job.submit(:jobno,
-- job 의 번호
'insert into scott.testdate values(1, sysdate);',
-- job의 내용 : ' '으로 감싸준다.
-- procedure를 실행하는 경우 ' username.procedure_name;' 만 쓰면 된다.
sysdate,
-- job이 실행될 시간
'sysdate + 5/24/60' ,
-- job이 실행되는 간격 , 위의 경우는 5분마다 실행하도록 했다.
-- ' '으로 감싸준다.
FALSE );
end;
/
$ sqlplus scott/tiger
SQL> variable jobno number;
SQL> @jobcre
SQL> print jobno -- job 번호 확인 : 여기서는 166번
SQL> exec dbms_job.run(166);
SQL> commit;
지금부터 interval에 따라 job이 실행된다.
job 실행 여부를 알아보기 위해서 다음의 sql 문장을 수행한다.
SQL> col what format a20
SQL> select what, job, next_date, next_sec, failures, broken from user_jobs;
그 외에
SQL> exec dbms_job.run(jobno);
- job의 강제 실행, job이 16번 fail되어 broken된 경우는 위의 명령어로 강제로 run을 시켜서 실행되면 다시 interval마다 실행된다.
SQL> exec dbms_job.broken(jobno, TRUE);
- job을 disable시킴
SQL> exec dbms_job.remove(jobno);
- job의 삭제
snapshot과 job과의 관계
snapshot 도 job 으로 등록되어 돌아갑니다.
즉, select job, what from dba_jobs; 를 조회하면, what 부분에 snapshot 이 정의되어 있습니다.
따라서, snapshot 에 대한 disable 방법 등은 job 과 같습니다.
interval 시간 지정 예제
1. 10분에 한번씩 실행하는 경우
sysdate + 1/24/6 또는 sysdate + 1/144
→ 1/24 (1시간-60분) / 6 : 10분 단위, 1/144 : 24*6 으로 나누어도 같은 의미가 된다.
2. 1분에 한번으로 지정하는 경우
sysdate + 1/24/60 또는 sysdate + 1/1440
3. 매일 새벽 2시로 지정하는 경우
trunc(sysdate) + 1 + 2/24 -> 다음날 새벽 2시를 지정함.
4. 매일 밤 11시로 지정하는 경우
trunc(sysdate) + 23/24 -> 오늘 밤 11시를 지정했음.
** 자신이 등록한 job의 목록을 보고 싶다면 아래의 쿼리를 날려도된다. **
select * from user_jobs;
DBMS_JOB PACKAGE의 사용 방법과 예제
DBMS_JOB PACKAGE의 사용 방법과 예제
Unix의 cron과 같이 오라클에서도 일정한 시점, 또는 간격으로 반복해서 job을 수행시킬 수 있다. DBMS_JOB package를 이용하여 수행시킬 수 있는데, 이것을 위해서는 SNP background process가 start되어 있어야 한다.
다음의 parameter를 init.ora file에 설정한 후 oracle을 startup하면 SNP process가 올라온다.
job_queue_processes = 1
→ 이 파라미터는 snp process를 몇 개 띄울지를 결정한다.
default=0
job_queue_interval = 60
→ 이 파라미터는 snp process가 깨어나는 간격을 초로 설정한다.
DBMS_JOB Package는 다음과 같은 procedure를 이용하여 사용한다.
DBMS_JOB.submit(job out binary_integer,
what in varchar2,
next_date in date defalut sysdate,
interval in varchar2 default 'null',
no_parse in boolean default false)
→ dbms_job.submit procedure는 job의 내용을 정의하고 oracle이 job을 수행할 수 있도록 한다.
다음의 예제를 통하여 실제 사용법을 알아보자.
[ 예제 ] file jobcre.sql
begin
dbms_job.submit(:jobno,
-- job 의 번호
'insert into scott.testdate values(1, sysdate);',
-- job의 내용 : ' '으로 감싸준다.
-- procedure를 실행하는 경우 ' username.procedure_name;' 만 쓰면 된다.
sysdate,
-- job이 실행될 시간
'sysdate + 5/24/60' ,
-- job이 실행되는 간격 , 위의 경우는 5분마다 실행하도록 했다.
-- ' '으로 감싸준다.
FALSE );
end;
/
$ sqlplus scott/tiger
SQL> variable jobno number;
SQL> @jobcre
SQL> print jobno -- job 번호 확인 : 여기서는 166번
SQL> exec dbms_job.run(166);
SQL> commit;
지금부터 interval에 따라 job이 실행된다.
job 실행 여부를 알아보기 위해서 다음의 sql 문장을 수행한다.
SQL> col what format a20
SQL> select what, job, next_date, next_sec, failures, broken from user_jobs;
그 외에
SQL> exec dbms_job.run(jobno);
- job의 강제 실행, job이 16번 fail되어 broken된 경우는 위의 명령어로 강제로 run을 시켜서 실행되면 다시 interval마다 실행된다.
SQL> exec dbms_job.broken(jobno, TRUE);
- job을 disable시킴
SQL> exec dbms_job.remove(jobno);
- job의 삭제
snapshot과 job과의 관계
snapshot 도 job 으로 등록되어 돌아갑니다.
즉, select job, what from dba_jobs; 를 조회하면, what 부분에 snapshot 이 정의되어 있습니다.
따라서, snapshot 에 대한 disable 방법 등은 job 과 같습니다.
interval 시간 지정 예제
1. 10분에 한번씩 실행하는 경우
sysdate + 1/24/6 또는 sysdate + 1/144
→ 1/24 (1시간-60분) / 6 : 10분 단위, 1/144 : 24*6 으로 나누어도 같은 의미가 된다.
2. 1분에 한번으로 지정하는 경우
sysdate + 1/24/60 또는 sysdate + 1/1440
3. 매일 새벽 2시로 지정하는 경우
trunc(sysdate) + 1 + 2/24 -> 다음날 새벽 2시를 지정함.
4. 매일 밤 11시로 지정하는 경우
trunc(sysdate) + 23/24 -> 오늘 밤 11시를 지정했음.
** 자신이 등록한 job의 목록을 보고 싶다면 아래의 쿼리를 날려도된다. **
select * from user_jobs;
Unix의 cron과 같이 오라클에서도 일정한 시점, 또는 간격으로 반복해서 job을 수행시킬 수 있다. DBMS_JOB package를 이용하여 수행시킬 수 있는데, 이것을 위해서는 SNP background process가 start되어 있어야 한다.
다음의 parameter를 init.ora file에 설정한 후 oracle을 startup하면 SNP process가 올라온다.
job_queue_processes = 1
→ 이 파라미터는 snp process를 몇 개 띄울지를 결정한다.
default=0
job_queue_interval = 60
→ 이 파라미터는 snp process가 깨어나는 간격을 초로 설정한다.
DBMS_JOB Package는 다음과 같은 procedure를 이용하여 사용한다.
DBMS_JOB.submit(job out binary_integer,
what in varchar2,
next_date in date defalut sysdate,
interval in varchar2 default 'null',
no_parse in boolean default false)
→ dbms_job.submit procedure는 job의 내용을 정의하고 oracle이 job을 수행할 수 있도록 한다.
다음의 예제를 통하여 실제 사용법을 알아보자.
[ 예제 ] file jobcre.sql
begin
dbms_job.submit(:jobno,
-- job 의 번호
'insert into scott.testdate values(1, sysdate);',
-- job의 내용 : ' '으로 감싸준다.
-- procedure를 실행하는 경우 ' username.procedure_name;' 만 쓰면 된다.
sysdate,
-- job이 실행될 시간
'sysdate + 5/24/60' ,
-- job이 실행되는 간격 , 위의 경우는 5분마다 실행하도록 했다.
-- ' '으로 감싸준다.
FALSE );
end;
/
$ sqlplus scott/tiger
SQL> variable jobno number;
SQL> @jobcre
SQL> print jobno -- job 번호 확인 : 여기서는 166번
SQL> exec dbms_job.run(166);
SQL> commit;
지금부터 interval에 따라 job이 실행된다.
job 실행 여부를 알아보기 위해서 다음의 sql 문장을 수행한다.
SQL> col what format a20
SQL> select what, job, next_date, next_sec, failures, broken from user_jobs;
그 외에
SQL> exec dbms_job.run(jobno);
- job의 강제 실행, job이 16번 fail되어 broken된 경우는 위의 명령어로 강제로 run을 시켜서 실행되면 다시 interval마다 실행된다.
SQL> exec dbms_job.broken(jobno, TRUE);
- job을 disable시킴
SQL> exec dbms_job.remove(jobno);
- job의 삭제
snapshot과 job과의 관계
snapshot 도 job 으로 등록되어 돌아갑니다.
즉, select job, what from dba_jobs; 를 조회하면, what 부분에 snapshot 이 정의되어 있습니다.
따라서, snapshot 에 대한 disable 방법 등은 job 과 같습니다.
interval 시간 지정 예제
1. 10분에 한번씩 실행하는 경우
sysdate + 1/24/6 또는 sysdate + 1/144
→ 1/24 (1시간-60분) / 6 : 10분 단위, 1/144 : 24*6 으로 나누어도 같은 의미가 된다.
2. 1분에 한번으로 지정하는 경우
sysdate + 1/24/60 또는 sysdate + 1/1440
3. 매일 새벽 2시로 지정하는 경우
trunc(sysdate) + 1 + 2/24 -> 다음날 새벽 2시를 지정함.
4. 매일 밤 11시로 지정하는 경우
trunc(sysdate) + 23/24 -> 오늘 밤 11시를 지정했음.
** 자신이 등록한 job의 목록을 보고 싶다면 아래의 쿼리를 날려도된다. **
select * from user_jobs;
오라클 및 리스너 재부팅 for LUNUX
CASE 1. 오라클 및 리스너 전체를 재시작 해야 할 경우
root 계정으로 접속
오라클 로 권한 변경
~]$ su - oracle
오라클 폴더로 이동
~]$ cd /oracle
sqlplus 로 노로그인 상태로 접속
~]$ sqlplus /nolog
sysdba 접속
SQL> connect /as sysdba
중지시키기
SQL> shutdown abort
시작
SQL> startup
종료
SQL> exit
리스너 종료
~]$ lsnrctl stop
리스너 재시작
~]$ lsnrctl start
## 그래도 시작이 안될 경우 top 에서 리스너의 갯수 확인 해보고
## 1개 보다 많으면 kill -signal번호[또는 시그널 이름] PID
CASE 2. 리스너 개수 확인(리스너가 2개 이상 떠 있을 경우)
1. oracle 개정으로 접근 한 후
2. ps -ef
3. oracle 계정으로 실행된 리스트 중
oracle 20514 1 0 Sep24 ? 01:30:35 /opt/oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
............................ 중략.........................................
oracle 2205 1 0 Sep24 ? 01:30:35 /opt/oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
4. 인스턴스 번호를 확인 한 후 프로세스 하나을 kill (ex: kill 2205)
5. 리스너가 하나만 남은 상태임을 확인하고 디비 접속 테스트를 해 보면 성공
6 CASE 2를 성공하였음에도 불구하고 복구 되지 않는 경우 CASE 1의 전 과정을 다시 실행
root 계정으로 접속
오라클 로 권한 변경
~]$ su - oracle
오라클 폴더로 이동
~]$ cd /oracle
sqlplus 로 노로그인 상태로 접속
~]$ sqlplus /nolog
sysdba 접속
SQL> connect /as sysdba
중지시키기
SQL> shutdown abort
시작
SQL> startup
종료
SQL> exit
리스너 종료
~]$ lsnrctl stop
리스너 재시작
~]$ lsnrctl start
## 그래도 시작이 안될 경우 top 에서 리스너의 갯수 확인 해보고
## 1개 보다 많으면 kill -signal번호[또는 시그널 이름] PID
CASE 2. 리스너 개수 확인(리스너가 2개 이상 떠 있을 경우)
1. oracle 개정으로 접근 한 후
2. ps -ef
3. oracle 계정으로 실행된 리스트 중
oracle 20514 1 0 Sep24 ? 01:30:35 /opt/oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
............................ 중략.........................................
oracle 2205 1 0 Sep24 ? 01:30:35 /opt/oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
4. 인스턴스 번호를 확인 한 후 프로세스 하나을 kill (ex: kill 2205)
5. 리스너가 하나만 남은 상태임을 확인하고 디비 접속 테스트를 해 보면 성공
6 CASE 2를 성공하였음에도 불구하고 복구 되지 않는 경우 CASE 1의 전 과정을 다시 실행
오라클 시작/ 종료 관련 팁
1. 오라클 시작/ 종료
cmd
sqlplus /nolog
connect /as sysdba : sysdba 계정에 로그인
startup : 오라클 시작
shutdown immediate : 오라클 종료
참고 : 반드시 Oracle 계정으로 수행해야 합니다.
2. 오라클 버젼 확인하기
select * from v$version;
3. 리스너 시작/ 종료
cmd
lsnrctl
start : 시작
stop : 종료
reload : 재시작
status : 상태확인
help : 명령어보기
4. 오라클 서버 listener.ora 설정하기
위치 : %oracle_home%\NETWORK\ADMIN\listener.ora
5. 오라클 클라이언트 tnsname.ora 설정하기
위치 : %oracle_home%\NETWORK\ADMIN\tnsname.ora
추가방법 :
# tradiknowledge real server
CONNECT_NAME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 000.000.0.00)(PORT = 0000))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBNAME)
)
)
참고 :
CONNECT_NAME : 직접 지정 보통
HOST : 접속할 서버 주소
PORT : 접속 포트 디폴트는 1521
DBNAME : 접속할 서비스명(오라클 설치할때 입력한 데이터베이스명)
listener.ora와 tnsname.ora의 설정은 보통 오라클 설치시 NET8 Configuration 작업을 통해서 할 수 있다.
6. initSID.ora / spfileSID.ora 파일 위치 : %ORACLE_HOME%\database
검색하기01
SQL> show parameter optimizer_mode;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string CHOOSE
검색하기02
SQL> SELECT name, value
FROM v$parameter
WHERE UPPER(name) LIKE '%DB%';
NAME VALUE
-------------------------------------------------------------
dbwr_io_slaves 0
db_file_direct_io_count 64
db_block_buffers 2048
db_block_checksum FALSE
db_block_size 8192
db_block_lru_latches 1
db_writer_processes 1
db_files 1024
db_file_multiblock_read_count 8
db_block_checking FALSE
dblink_encrypt_login FALSE
db_name storm
기타 관련내용 : http://blog.naver.com/rlaaudtnr8/50026706521
오라클 DB 에 연결된 불필요한 세션 삭제하기
아래의 SQL 을 실행하여서 오라클 DB 에 연결된 세션을 검색한다.
SELECT SID, SERIAL#, USERNAME FROM V$SESSION;
SID SERIAL# USERNAME
------ --------- -------------
1 1
위에서 검색된 결과 중에서 삭제할 세션을 아래의 SQL 문장으로 삭제할 수 있다.
ALTER SYSTEM KILL SESSION 'SID, SERIAL#';
예) ALTER SYSTEM KILL SESSION '1, 1';
[출처] 오라클 DB 에 연결된 불필요한 세션 삭제하기|작성자 까미유
[oracle] 실행 중인 SQL 문장 검색
아래와 같이 실행하면 현재 실행 중인 SQL 문장을 모두 찾을 수 있다.
select s.sid, s.status, s.process, s.osuser, a.sql_text, p.program
from v$session s, v$sqlarea a, v$process p
where s.sql_hash_value=a.hash_value and s.sql_address=a.address and s.paddr=p.addr and s.status='ACTIVE'
select s.sid, s.status, s.process, s.osuser, a.sql_text, p.program
from v$session s, v$sqlarea a, v$process p
where s.sql_hash_value=a.hash_value and s.sql_address=a.address and s.paddr=p.addr and s.status='ACTIVE'
oracle dbms_crypto (오라클 암호화)
1. oracle md5
ex :
select rawtohex(DBMS_CRYPTO.Hash(to_clob(to_char('mcpicdtl.blogspot.com')),2))
from dual;
함수 DBMS_CRYPTO.Hash 의 2번자 인자에 2가 들어가 있다.
이 파라미터가 1 : md4, 2 : md5, 4 : sh1 암호화 방식을 지징한다
위 함수를 실행 시키기 위해서는 sysdba 으로 로그인 해야 하거나 .
sysdba로 부터 DBMS_CRYPTO 에 대한 EXECUTE 권한을 위임 받으면 된다. (GRANI 참조)
ex :
select rawtohex(DBMS_CRYPTO.Hash(to_clob(to_char('mcpicdtl.blogspot.com')),2))
from dual;
함수 DBMS_CRYPTO.Hash 의 2번자 인자에 2가 들어가 있다.
이 파라미터가 1 : md4, 2 : md5, 4 : sh1 암호화 방식을 지징한다
위 함수를 실행 시키기 위해서는 sysdba 으로 로그인 해야 하거나 .
sysdba로 부터 DBMS_CRYPTO 에 대한 EXECUTE 권한을 위임 받으면 된다. (GRANI 참조)
PHP+LOB 두개 이상의 CLOB 이용하기
PHP와 Oracle을 연동할 때는 다음과 같은 두가지의 문제가 가장 크게 대두됩니다.
* 페이징 처리
* LOB 타입의 처리
페 이징 처리야 사실 쿼리문을 개발하는 문제이니 Oracle SQL문만 익숙해지면 처리하는데 크게 어렵지 않습니다. 그런데 LOB타입은 쿼리문말고도 별도의 추가작업을 해줘야 하므로 아주 골치를 겪게 됩니다. 그래서 PHP와 Oracle 연동할 때 컬럼 타입을 되도록 LOB 타입으로 안잡으려 노력합니다만... 그게 그렇게 잘 되지 않죠... -_-ㅋ
요즘 그누보드 4를 Oracle 버전으로 포팅하는 작업을 하고 있습니다. 오랜만에 PHP와 Oracle을 연동하다보니 MYSQL의 text 타입을 전부 CLOB으로 처리해줘야 되더군요. 물론 그 중 상당수는 varchar2(4000)로 충분히 커버되는 것이지만, 최대한 그누보드 4의 골격을 해치지 않기 위해서 모두 CLOB으로 변환했습니다. 그러다 보니 한번에 두개 이상의 CLOB을 insert/update하는 일이 발생하게 되었습니다.
이전에도 PHP상에서 CLOB 타입을 핸들링한 적이 많았지만, 보통 한 테이블에 하나정도의 LOB타입이 있는 경우가 많았습니다. 그래서 두개 이상 해본적이 거의 없었던 것 같아서 이리저리 테스트해봤습니다. 되도록 한방에 처리할 수 있는 방안쪽으로 방향을 잡았습니다.
결국 한방에는 되지 않는다로 결론이 나버렸지만, 이 기회에 두개 이상의 CLOB을 핸들링하는 방법을 정리하게 되어 그리 헛된 고생을 한 것은 아닌 것 같습니다. 아 참! Oracle 10g 기반 최신 OCI를 받으면 MYSQL의 text 필드에 내용을 넣듯이 바로 한번에 된다는 글을 검색하는 도중 찾게 되었습니다. 다음에 한번 테스트 해봐야 겠네요.
각설하고... Oracle에서 LOB 처리를 하기 위해서는 다음과 같은 함수들이 추가적으로 필요합니다.
string OCINewDescriptor ( int connection [, int type])
int OCIBindByName ( int stmt, string ph_name, mixed & variable, int length [, int type])
그리고 두개 이상의 CLOB을 insert할 경우의 처리 순서는 다음으로 요약할 수 있습니다.
1. ROWID에 대한 Descriptor를 생성한다.
2. clob 칼럼을 제외한 나머지 칼럼이 포함된 insert 구문을 실행한다. 이 때 insert된 행의 ROWID를 Descriptor와 연결해 놓는다.
3. ROWID를 이용해 clob 칼럼을 하나씩 update를 한다.
4. 3의 과정을 CLOB 칼럼수만큼 반복한다.
5. 작업이 끝났으면 불필요한 리소스를 반환한다.
update의 경우는 위의 3~5과정과 동일하므로 생략하겠습니다. 그럼 위의 과정을 직접 코딩해보겠습니다. 먼저 테스트 테이블을 만듭니다.
create table aaa ( rnum number(7), clob1 clob, clob2 clob);
그리고 위의 테이블에 데이터를 넣어보겠습니다.
$dbconn = @OCILogon('test', 'test123', 'ora10g');
$sql = "insert into aaa (rnum) values (1) returning ROWID into :rid"; --- [1]
$stmt1 = OCIParse($dbconn, $sql); --- [2]
$rowid = OCINewDescriptor($dbconn, OCI_D_ROWID); --- [3]
OCIBindByName($stmt1, ":rid", &$rowid, -1, OCI_B_ROWID); --- [4]
OCIExecute($stmt1, OCI_DEFAULT); --- [5]
OCIFreeStatement($stmt1); --- [6]
$clob = OCINewDescriptor($dbconn, OCI_D_LOB); --- [7]
$content = array(); --- [8]
$content[0] = "CLOB 첫번째 내용입니다..";
$content[1] = "CLOB 두번째 내용입니다.";
$result = true;
for($i=0; $i<2; $i++) { --- [9]
$update = "update aaa set clob".($i+1)." = empty_clob() where ROWID = :rid returning clob".($i+1)." into :clob"; --- [10]
$stmt2 = OCIParse($dbconn, $update);
echo $update."
";
OCIBindByName($stmt2, ":rid", &$rowid, -1, OCI_B_ROWID);
OCIBindByName($stmt2, ":clob", &$clob, -1, OCI_B_CLOB); --- [11]
OCIExecute($stmt2, OCI_DEFAULT);
if($clob->save($content[$i])) { --- [12]
echo "
$result &= true; --- [13]
} else {
echo "
$result &= false;
}
OCIFreeStatement($stmt2);
}
echo "최종결과: ".(($result)? "true": "false")."
";
if($result) OCICommit($dbconn); --- [14]
else OCIRollback($dbconn);
OCIFreeDesc($rowid); --- [15]
OCIFreeDesc($clob); --- [16]
OCILogoff($dbconn);
?>
[1]: clob 칼럼은 나중에 update를 이용해서 집어넣을 것이므로, clob이 아닌 칼럼만 일단 insert를 합니다. 만일 clob 형 타입을 NOT NULL로 잡았다면 empty_clob() 함수를 이용해서 일단 빈공간을 만들어야 겠죠.
[2]: 일단 쿼리를 파싱합니다.
[3]: ROWID를 받아오기 위해 Descriptor를 생성합니다. 나중에 해당행에 CLOB을 집어넣기위한 식별자로 사용할 것입니다.
[4]: 파싱된 쿼리와 Descriptor를 바인딩시킵니다.
[5]: 쿼리 실행! 오라클은 기본으로 트랜잭션을 이용합니다. 이런 장점을 그대로 가지고 가려면, 두번째 인자에 OCI_DEFAULT를 지정하는 것을 잊지 말아야겠죠?
[6]: Statement를 해제합니다.
[7]: 이제 본격적으로 CLOB을 처리하기 위해 우선 CLOB Descriptor를 생성합니다.
[8],[9]: 이부분은 반복적인 업데이트 구문처리를 편하게 하기 위해 그냥 만든겁니다. 꼭 이렇게 해야한다는 법은 없습니다.
[10]: 실제로 CLOB 데이터를 넣기 위한 SQL입니다. 엄밀히 말하면 데이터가 들어갈테니 공간을 마련해 놓으라는 지시를 하는 것이죠. 실제 데이터 저장은 [12]에서 하게 됩니다.
[11]: 파싱된 쿼리에 CLOB Descriptor를 연결시킵니다.
[12]: 쿼리가 성공적으로 실행되면 실제로 데이터를 저장해야 합니다. LOB 객체의 save() 함수를 사용합니다.
[13]: 이건 나중에 트랜잭션의 Commit/Rollback을 정하기 위한 장치입니다. 비트연산을 이용해서 처리했습니다.
[14]: Commit 혹은 Rollback 합니다.
[15],[16]: 이제 모든 작업이 완료되었으므로 필요없는 Descriptor를 해제하는 작업을 합니다.
-------------------------------------------------------
이 것은 두개의 이상의 CLOB 데이터를 처리하기 위한 것입니다. 만약 한개의 CLOB 데이터를 처리한다면 insert 구문에서 모두 처리가 가능합니다.
* 페이징 처리
* LOB 타입의 처리
페 이징 처리야 사실 쿼리문을 개발하는 문제이니 Oracle SQL문만 익숙해지면 처리하는데 크게 어렵지 않습니다. 그런데 LOB타입은 쿼리문말고도 별도의 추가작업을 해줘야 하므로 아주 골치를 겪게 됩니다. 그래서 PHP와 Oracle 연동할 때 컬럼 타입을 되도록 LOB 타입으로 안잡으려 노력합니다만... 그게 그렇게 잘 되지 않죠... -_-ㅋ
요즘 그누보드 4를 Oracle 버전으로 포팅하는 작업을 하고 있습니다. 오랜만에 PHP와 Oracle을 연동하다보니 MYSQL의 text 타입을 전부 CLOB으로 처리해줘야 되더군요. 물론 그 중 상당수는 varchar2(4000)로 충분히 커버되는 것이지만, 최대한 그누보드 4의 골격을 해치지 않기 위해서 모두 CLOB으로 변환했습니다. 그러다 보니 한번에 두개 이상의 CLOB을 insert/update하는 일이 발생하게 되었습니다.
이전에도 PHP상에서 CLOB 타입을 핸들링한 적이 많았지만, 보통 한 테이블에 하나정도의 LOB타입이 있는 경우가 많았습니다. 그래서 두개 이상 해본적이 거의 없었던 것 같아서 이리저리 테스트해봤습니다. 되도록 한방에 처리할 수 있는 방안쪽으로 방향을 잡았습니다.
결국 한방에는 되지 않는다로 결론이 나버렸지만, 이 기회에 두개 이상의 CLOB을 핸들링하는 방법을 정리하게 되어 그리 헛된 고생을 한 것은 아닌 것 같습니다. 아 참! Oracle 10g 기반 최신 OCI를 받으면 MYSQL의 text 필드에 내용을 넣듯이 바로 한번에 된다는 글을 검색하는 도중 찾게 되었습니다. 다음에 한번 테스트 해봐야 겠네요.
각설하고... Oracle에서 LOB 처리를 하기 위해서는 다음과 같은 함수들이 추가적으로 필요합니다.
string OCINewDescriptor ( int connection [, int type])
int OCIBindByName ( int stmt, string ph_name, mixed & variable, int length [, int type])
그리고 두개 이상의 CLOB을 insert할 경우의 처리 순서는 다음으로 요약할 수 있습니다.
1. ROWID에 대한 Descriptor를 생성한다.
2. clob 칼럼을 제외한 나머지 칼럼이 포함된 insert 구문을 실행한다. 이 때 insert된 행의 ROWID를 Descriptor와 연결해 놓는다.
3. ROWID를 이용해 clob 칼럼을 하나씩 update를 한다.
4. 3의 과정을 CLOB 칼럼수만큼 반복한다.
5. 작업이 끝났으면 불필요한 리소스를 반환한다.
update의 경우는 위의 3~5과정과 동일하므로 생략하겠습니다. 그럼 위의 과정을 직접 코딩해보겠습니다. 먼저 테스트 테이블을 만듭니다.
create table aaa ( rnum number(7), clob1 clob, clob2 clob);
그리고 위의 테이블에 데이터를 넣어보겠습니다.
$dbconn = @OCILogon('test', 'test123', 'ora10g');
$sql = "insert into aaa (rnum) values (1) returning ROWID into :rid"; --- [1]
$stmt1 = OCIParse($dbconn, $sql); --- [2]
$rowid = OCINewDescriptor($dbconn, OCI_D_ROWID); --- [3]
OCIBindByName($stmt1, ":rid", &$rowid, -1, OCI_B_ROWID); --- [4]
OCIExecute($stmt1, OCI_DEFAULT); --- [5]
OCIFreeStatement($stmt1); --- [6]
$clob = OCINewDescriptor($dbconn, OCI_D_LOB); --- [7]
$content = array(); --- [8]
$content[0] = "CLOB 첫번째 내용입니다..";
$content[1] = "CLOB 두번째 내용입니다.";
$result = true;
for($i=0; $i<2; $i++) { --- [9]
$update = "update aaa set clob".($i+1)." = empty_clob() where ROWID = :rid returning clob".($i+1)." into :clob"; --- [10]
$stmt2 = OCIParse($dbconn, $update);
echo $update."
";
OCIBindByName($stmt2, ":rid", &$rowid, -1, OCI_B_ROWID);
OCIBindByName($stmt2, ":clob", &$clob, -1, OCI_B_CLOB); --- [11]
OCIExecute($stmt2, OCI_DEFAULT);
if($clob->save($content[$i])) { --- [12]
echo "
CLOB".($i+1)." 입력 성공
";$result &= true; --- [13]
} else {
echo "
CLOB".($i+1)." 입력 실패
";$result &= false;
}
OCIFreeStatement($stmt2);
}
echo "최종결과: ".(($result)? "true": "false")."
";
if($result) OCICommit($dbconn); --- [14]
else OCIRollback($dbconn);
OCIFreeDesc($rowid); --- [15]
OCIFreeDesc($clob); --- [16]
OCILogoff($dbconn);
?>
[1]: clob 칼럼은 나중에 update를 이용해서 집어넣을 것이므로, clob이 아닌 칼럼만 일단 insert를 합니다. 만일 clob 형 타입을 NOT NULL로 잡았다면 empty_clob() 함수를 이용해서 일단 빈공간을 만들어야 겠죠.
[2]: 일단 쿼리를 파싱합니다.
[3]: ROWID를 받아오기 위해 Descriptor를 생성합니다. 나중에 해당행에 CLOB을 집어넣기위한 식별자로 사용할 것입니다.
[4]: 파싱된 쿼리와 Descriptor를 바인딩시킵니다.
[5]: 쿼리 실행! 오라클은 기본으로 트랜잭션을 이용합니다. 이런 장점을 그대로 가지고 가려면, 두번째 인자에 OCI_DEFAULT를 지정하는 것을 잊지 말아야겠죠?
[6]: Statement를 해제합니다.
[7]: 이제 본격적으로 CLOB을 처리하기 위해 우선 CLOB Descriptor를 생성합니다.
[8],[9]: 이부분은 반복적인 업데이트 구문처리를 편하게 하기 위해 그냥 만든겁니다. 꼭 이렇게 해야한다는 법은 없습니다.
[10]: 실제로 CLOB 데이터를 넣기 위한 SQL입니다. 엄밀히 말하면 데이터가 들어갈테니 공간을 마련해 놓으라는 지시를 하는 것이죠. 실제 데이터 저장은 [12]에서 하게 됩니다.
[11]: 파싱된 쿼리에 CLOB Descriptor를 연결시킵니다.
[12]: 쿼리가 성공적으로 실행되면 실제로 데이터를 저장해야 합니다. LOB 객체의 save() 함수를 사용합니다.
[13]: 이건 나중에 트랜잭션의 Commit/Rollback을 정하기 위한 장치입니다. 비트연산을 이용해서 처리했습니다.
[14]: Commit 혹은 Rollback 합니다.
[15],[16]: 이제 모든 작업이 완료되었으므로 필요없는 Descriptor를 해제하는 작업을 합니다.
-------------------------------------------------------
이 것은 두개의 이상의 CLOB 데이터를 처리하기 위한 것입니다. 만약 한개의 CLOB 데이터를 처리한다면 insert 구문에서 모두 처리가 가능합니다.
ORACLE Cursor Sample (with LOOP)
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2
3 v_employeeID employee.id%TYPE;
4 v_FirstName employee.first_name%TYPE;
5 v_LastName employee.last_name%TYPE;
6
7
8 v_city employee.city%TYPE := 'Vancouver';
9
10
11 CURSOR c_employee IS
12 SELECT id, first_name, last_name FROM employee WHERE city = v_city;
13 BEGIN
14
15
16 OPEN c_employee;
17 LOOP
18
19 FETCH c_employee INTO v_employeeID, v_FirstName, v_LastName;
20 DBMS_OUTPUT.put_line(v_employeeID);
21 DBMS_OUTPUT.put_line(v_FirstName);
22 DBMS_OUTPUT.put_line(v_LastName);
23
24 EXIT WHEN c_employee%NOTFOUND;
25 END LOOP;
26
27
28 CLOSE c_employee;
29 END;
30 /
02
Alison
Mathews
03
James
Smith
04
Celia
Rice
05
Robert
Black
08
James
Cat
08
James
Cat
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2
3 v_employeeID employee.id%TYPE;
4 v_FirstName employee.first_name%TYPE;
5 v_LastName employee.last_name%TYPE;
6
7
8 v_city employee.city%TYPE := 'Vancouver';
9
10
11 CURSOR c_employee IS
12 SELECT id, first_name, last_name FROM employee WHERE city = v_city;
13 BEGIN
14
15
16 OPEN c_employee;
17 LOOP
18
19 FETCH c_employee INTO v_employeeID, v_FirstName, v_LastName;
20 DBMS_OUTPUT.put_line(v_employeeID);
21 DBMS_OUTPUT.put_line(v_FirstName);
22 DBMS_OUTPUT.put_line(v_LastName);
23
24 EXIT WHEN c_employee%NOTFOUND;
25 END LOOP;
26
27
28 CLOSE c_employee;
29 END;
30 /
02
Alison
Mathews
03
James
Smith
04
Celia
Rice
05
Robert
Black
08
James
Cat
08
James
Cat
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
피드 구독하기:
글 (Atom)