'ocp자격증' 태그의 글 목록 (11 Page)

오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원 오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원 오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원 오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원 오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원 오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원

* 데이터 검색
SELECT 칼럼명 , GROUP 함수
FROM 테이블명
WHERE 조건식
GROUP BY 칼럼명
HAVING 조건식
ORDER BY 칼럼이나 표현식 ;
- DISTINCT : 중복행을 제거함.
항상 SELECT 바로 다음에서 기술
DISTINCT뒤에 나타나는 컬럼들은 모두 DISTINCT의 영향를 받음
기본적으로 오름차순 정렬됨
- ALIAS : 나타날때 컬럼에 대하여 다른 이름을 부여함
- GROUP BY : 전체 데이터를 소그룹으로 나눌 칼럼을 명시한다.
- HAVING : GROUP에 대한 조건을 기술한다.
- WHERE절에 사용되는 연산자의 3가지 부류
논리 연산자 - AND : 여러조건을 동시에 만족한다.
- OR : 여러조건들 중 어느 하나라도 만족한다.
- NOT : 조건에 대한 반대를 돌려준다.
논리 비교 연산자 - = : 같다.
- > : 보다 크다.
- >= : 보다 크거나 같다.
- < : 보다 작다.
- <= : 보다 작거나 같다.
SQL 비교 연산자 - BETWEEN a AND b : a와 b사이에 있다.(a,b값 포함)
- IN (list) : list의 값 중 어느 하나와 일치한다.
- IS NULL : NULL값을 가졌다.
LIKE '%문자_문자%‘ : % 는 0개서부터 여러개까지의 문자열
을 나타내는 와일드카드 (wildcard)이고
_ 는 단 하나의 문자를 나타내는 와일드카드입니다.
와일드 카드를 일반문자처럼 쓰고 싶은 경우에는 ESCAPE 옵션을 사용
WHERE name LIKE '%X_Y%' ESCAPE ''

SQL> SELECT name,salary*18 AS 연봉
2 FROM s_emp;
SQL> SELECT name||' '||title
2 FROM s_emp ;
SQL> SELECT name, salary*18 연봉 , title
2 FROM s_emp
3 ORDER BY salary*18 DESC ;
SQL> SELECT name , salary
2 FROM s_emp
3 WHERE salary BETWEEN 1000 AND 1500;
SQL> SELECT name, title , dept_id
2 FROM s_emp
3 WHERE dept_id IN (110,113) ;
SQL> SELECT name, start_date , title
2 WHERE start_date BETWEEN '01-JAN-91'
3 AND '31-DEC-91' ;
SQL> SELECT dept_id , count(*) 인원수
2 FROM s_emp
3 WHERE dept_id = 110
4 GROUP BY dept_id ;
SQL> SELECT dept_id , AVG(salary)
2 FROM s_emp
3 GROUP BY dept_id ;
SQL> SELECT dept_id , AVG(salary)
2 FROM s_emp
3 WHERE title = '사원'
4 GROUP BY dept_id ;
SQL> SELECT dept_id , title , COUNT(*)
2 FROM s_emp
3 GROUP BY dept_id , title ;
SQL> SELECT title , AVG(salary) , COUNT(*)
2 FROM s_emp
3 GROUP BY title
4 HAVING COUNT(*) > 2 ;
SQL> SELECT title , SUM(salary) 급여총합
2 FROM s_emp
3 WHERE title NOT LIKE ‘%부장’
5 HAVING SUM(salary) > 10000
6 ORDER BY SUM(salary) ;

* SQL*PLUS의 기능

- 버퍼에 있는 명령어 편집하기
A(PPEND) text : line의 끝에 text를 추가함
C(HANGE)/old/new : old를 new로 바꿈
DEL n : n line을 지움
I(NPUT) text : 다음 line에 text를 추가함
L(IST) : 전체 문장을 보여줌
n text : n line전체를 text로 바꿈
R(UN) : buffer에 있는 문장을 실행함(/ 와 같음)
EDIT : buffer에 있는 문장을 파일로 부름(afiedt.buf)

- 파일에 있는 명령어 편집하기
SAVE a : buffer에 있는 내용을 a.sql 파일로 저장
GET a : 파일 a에 있는 내용을 buffer로 부름
START a (=@a) : 파일 a를 실행함
! : UNIX Shell로 나들이
!vi a.sql : 파일 a.sql을 vi편집기로 부름

- 환경설정 : SET 환경변수 값
COLSEP (text) : 칼럼이 표시될때 칼럼간의 구별문자 기본값은 공백
FEEDBACK (off|on) : 선택된 행이 몇행인지를 표시함 기본값은 6행 이상인 경우에 on
HEADING (off|on) : 칼럼에 대한 HEADING를 표시함 기본 값은 on
LINESIZE (n) : 한 라인당 표시되는 문자의 수 기본값은 80
PAGES (n) : 한 페이지당 표시되는 라인수 기본값은 24
PAUSE (off|on|text) : 「ENTER」키를 누를 때마다 화면이 지나감 기본값은 off
TIMING (off|on) : SQL문장이 처리되는데 걸리는 시간을 표시 기본값은 off
SHOW : SET 환경이 어떻게 설정되어 있는지 보는 명령어
SHOW ALL : 전체가 어떻게 설정되어 있는지 보고 싶은 경우
자신이 쓰는 환경을 항상 맞추고 싶으면
login.sql 파일에 SET환경을 설정하면 됩니다.

- 표시형식
COL(UMN) 칼럼이나 ALIAS 옵션
- 옵션의 종류 - CLE(AR) : 칼럼에 지정된 형식을 지움
- FOR(MAT) : 형식 칼럼에 대한 표시 형식과 폭을 바꿈
- HEADING text : 칼럼에 대한 HEADING를 지정함
SQL> COL name HEADING ‘사원이름’ FORMAT A10
SQL> COL salary FORMAT $9,999,999
SQL> COL salary CLEAR
CLEAR COLUMNS : 지정된 형식을 다 지우고 싶은 경우

- 변수사용
&변수 사용 : 사용자가 원하는 데이터를 사용할 때마다 입력하고 싶은 경우에
치환변수를 사용. 사용시에는 &기호를 이용
&&기호를 사용하면 한번 입력받은 변수값을 다시 적용할 수 있음
&기호를 이용하여 조건, 칼럼명, 테이블명, 전체 SELECT구문도 받을 수 있음
SQL> SELECT id,name,dept_id
2 FROM s_emp
3 WHERE dept_id = &부서번호 ;
Enter value for 부서번호: 112
old 3: WHERE dept_id = &부서번호
new 3: WHERE dept_id = 112
ACCEPT를 사용한 변수
사용자가 SELECT구문을 사용하기 전에 미리 변수를 설정해 놓는 경우에
ACCEPT를 사용할 수 있습니다.
> ACCEPT 변수명 데이터타입 FORMAT PROMPT text HIDE
데이터타입 : NUMBER,CHAR,DATE 중에서 지정
FORMAT : A10, 9,999등의 표시 형식지정
PROMPT text : 사용자가 데이터를 입력할 때 보여주는 문장
HIDE : password등을 지정시 화면에 보이지 않도록 함
SQL> ACCEPT p_salary NUMBER PROMPT ‘급여 입력 :’
SQL> ACCEPT pswd CHAR PROMPT 'Password : ' HIDE

* 단일행 함수
- 문자형 함수
UPPER : 모든 문자를 대문자로 전환
LOWER : 모든 문자를 소문자로 전환
INITCAP : 문자를 단어별로 앞머리는 대문자 나머지는 소문자로 전환
CONCAT : 두 문자열을 합성. ||연산자와 같은 용도로 사용
SUBSTR : 특정문자열의 부분을 선택
LENGTH : 문자열의 길이를 구함
LPAD : 왼쪽 문자 자리를 채움
RPAD : 오른쪽 문자 자리를 채움
LTRIM : 왼쪽 문자를 지움
RTRIM : 오른쪽 문자를 지움
TRANSLATE : 특정 문자열을 대체
REPLACE : 특정 문자열을 대신

UPPER(문자값) : UPPER('Oracle Server')
→ ORACLE SERVER
LOWER(문자값) : LOWER('Oracle Server')
→ oracle server
INITCAP(문자값) : INITCAP('Oracle Server')
→ Oracle Server
CONCAT(문자값1, 문자값2) : CONCAT('Oracle',' Server')
→ Oracle Server
SUBSTR(문자값, a, b)
a 선택할 문자열의 시작위치.
음수면 끝에서부터 시작
b 선택할 문자열의 개수.
이 인자는 생략할 수 있으며,
생략할 경우 문자열의 끝까지 선택
: SUBSTR('강남구 역삼동‘,5,2)
→ 역삼
LENGTH(문자값1, a, 문자값2) : LENGTH(‘홍길동’)
→ 3
LPAD(문자값1, a, 문자값2)
RPAD(문자값1, a, 문자값2)
a : 전체 채울 자리수
문자값2 : 채울 문자
생략할 수 있으며, 생략되면
공백값임
: LPAD(‘홍길동’,10 ‘*’)
→ ****홍길동
LTRIM(문자값1, 문자값2)
RTRIM(문자값1, 문자값2)
문자값1에서 왼쪽(오른쪽)에서부터
더이상 문자값2를 만나지 않을 때까지 지움
: LTRIM('XXAXBA','X')
→ AXBA
TRANSLATE(문자값, a, b)
a 대체하고 싶은 문자(from)
b 대체할 결과의 문자(to)
: TRANSLATE('AABBA','B','C')
→ AACCA
REPLACE (문자값, a, b)
a 바꾸고 싶은 문자(from)
b 바꿀 결과의 문자(to)
: REPLACE ('JACK and JUE','J','BL')
→ BLACK and BLUE

- 숫자형 함수
ROUND : 숫자를 반올림
TRUNC : 숫자를 절사
MOD : 나누기 연산에서 나머지 구함
POWER : 거듭제곱
SQRT : 제곱근
SIGN : 양수인지 음수인지 0인지를 구벌함
CHR : ASCII 값에 해당하는 문자를 구함

ROUND(숫자값, a), TRUNC(숫자값, a)
a 숫자값을 반올림(버림)하여 a자리까지
돌려줌.a가 양수이면 소수이하자리를,
음수이면 정수 부분 자리임
생략할 수 있으며, 생략하면 0
: ROUND(35.735,2)→35.74
MOD(숫자값, a )
a 숫자값을 나누기 할 수 있음
: MOD(7,2)→1
POWER(숫자값1, 숫자값2) : POWER(3,2)→9
SQRT (숫자값) : SQRT(25)→5
SIGN(숫자값) : SIGN(-15)→-1
CHR(숫자값) : CHR(65)→A

- 날짜형 함수
SYSDATE : 현재 시스템의 날짜 및 시간을 구함
LAST_DAY : 지정한 날짜의 해당 월의 마지막 날짜를 구함
MONTHS_BETWEEN : 두 날짜 사이의 개월 수를 구함
ADD_MONTHS : 지정한 날짜로부터 몇 개월 후의 날짜를 구함
ROUND : 날짜에 대한 반올림
TRUNC : 날짜에 대한 버림

SYSDATE : SYSDATE → 10-MAY-99
LAST_DAY(날짜값) : LAST_DAY('17-FEB-98') → 28-FEB-98
MONTHS_BETWEEN(날짜값1, 날짜값2) : MONTHS_BETWEEN('26-APR-97','22-JUL-95') → 21.1290323
ADD_MONTHS(날짜값, 숫자값) : ADD_MONTHS('22-JUL-95',21) → 22-APR-97
ROUND(날짜값, 자리수) : 현재 날짜가 1999년 5월 10일이라고 가정하자.
ROUND(SYSDATE,'MONTH') → 01-MAY-99
TRUNC(날짜값, 자리수) : 현재 날짜가 1999년 5월 10일이라고 가정하자.
TRUNC(SYSDATE,'YEAR') → 01-JAN-99

- 날짜에 대한 산술연산
날짜 + 숫자 : 날짜 특정한 날로부터 몇일 후의 날짜 계산
날짜 - 숫자 : 날짜 특정한 날로부터 몇일 전의 날짜 계산
날짜 - 날짜 : 숫자 두 날짜 사이의 차이를 숫자로 계산

- 변환형 함수
TO_CHAR : 숫자나 날짜를 문자열로 변환
TO_NUMBER : 문자를 숫자로 변환
TO_DATE : 문자를 날짜로 변환

- TO_CHAR에서 숫자를 문자로 변환시에 형식에 사용되는 요소
9 : 일반적인 숫자를 나타냄
0 : 앞의 빈자리를 0으로 채움
$ : dollar를 표시함
L : 지역 통화 단위(ex )
. : 소숫점을 표시함
, : 천단위를 표시함
- TO_CHAR에서 날짜를 문자로 변환시에 형식에 사용되는 요소
SCC : 세기를 표시 S는 기원전(BC)
YEAR : 연도를 알파벳으로 spelling
YYYY : 4자리 연도로 표시
YY : 끝의 2자리 연도로 표시
MONTH : 월을 알파벳으로 spelling
MON : 월의 알파벳 약어
MM : 월을 2자리 숫자로 표시
DAY : 일에 해당하는 요일
DY : 일에 해당하는 요일의 약어
DDD,DD,D : 연도,월,일 중의 날짜를 숫자로 표시
HH , HH24 : (1-12) , (0-23)중의 시간을 표시
MI : 분을 표시
SS : 초를 표시
AM(A.M.),PM(P.M.) : 오전인지 오후인지를 표시

TO_CHAR(문자값,‘형식’)
숫자를 문자로 변환 : TO_CHAR(350000,'$999,999')→ $350,000
숫자를 날짜로 변환 : TO_CHAR(SYSDATE,'YY/MM/DD')→ 95/05/25
TO_DATE(문자값, ‘형식’) : TO_DATE('10 SEPTEMBER 1992','DD MONTH YYYY')→10-SEP-92
TO_NUMBER(문자값) : TO_NUMBER('1234')→ 1234

- DECODE 함수
DECODE 함수는 값을 비교하여 해당하는 값을 돌려주는 함수
> DECODE (형식,비교값1,결과치1,비교값2,결과치2,...기본치 )
형식 : 컬럼이나 값
비교값1 : 형식이 비교값1에 맞는지를 비교
결과값1 : 형식이 비교값1에 맞을 때 갖는 값
기본치 : 형식이 비교값1,2,...에 맞지 않을 때 가지는 값
생략될 수 있으며, 생략되면 NULL이다.

SQL> SELECT name,title,DECODE(SUBSTR(title,-2,2),
2 '부장',salary*1.1,
3 '과장',salary*1.07,
4 '사원',salary*1.05,
5 salary) 이번달급여
6 FROM s_emp
7 ORDER BY 3 DESC ;

* 다중행 함수
- 그룹함수
COUNT( a ) : a의 행의 개수를 구함
AVG( a ) : a의 평균을 구함
SUM( a ) : a의 합계를 구함
MIN( a ) : a의 최소값을 구함
MAX( a ) : a의 최대값을 구함
STDDEV( a ) : a의 표준 편차를 구함
VARIANCE( a ) : a의 분산을 구함

COUNT(*)를 제외한 모든 그룹함수는 NULL값을 고려하지 않습니다.
중복값을 제거하고 싶은 경우는 a의 앞에 DISTINCT를 기술합니다.
MAX , MIN , COUNT를 제외한 그룹함수는 숫자타입의 데이터에만 가능합니다

* JOIN
- EQUIJOIN
컬럼에 있는 값들이 정확하게 일치하는 경우에 =연산자를 사용하여 JOIN하는 방법을 말합니다.
WHERE 절에서 JOIN조건을 기술하도록 합니다.

SELECT 테이블명.컬럼명, 테이블명.컬럼명, ...
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼1 = 테이블2.컬럼2 ;

SQL> SELECT s_emp.name, s_emp.dept_id , s_dept.name
2 FROM s_emp , s_dept
3 WHERE s_emp.dept_id = s_dept.id ;
SQL> SELECT e.name 사원명, e.dept_id , d.name 부서명
2 FROM s_emp e, s_dept d
3 WHERE e.dept_id = d.id ;
SQL> SELECT e.name 사원명, d.name 부서명
2 FROM s_emp e, s_dept d , s_region r
3 WHERE e.dept_id = d.id
4 AND d.region_id = r.id
5 AND r.name LIKE '%서울%‘ ;

- NON-EQUIJOIN
Non-Eqijoin이란 한 칼럼의 값이 다른 칼럼의 값과 정확히 일치하지 않는 경우에
=연산자외의 다른 연산자를 사용하여 JOIN하는 방법을 말합니다

SQL> SELECT e.name, e.salary , g.grade 급여등급
2 FROM s_emp e, salgrade g
3 WHERE e.salary BETWEEN g.losal AND g.hisal ;

- OUTER JOIN
Outer join이란 JOIN조건을 만족하지 않는 경우에도 모든 행들을 다 보고자하는 경우에 JOIN하는 방법
SQL> SELECT e.name 사원명 ,e.id,c.name 고객명
2 FROM s_emp e, s_customer c
3 WHERE e.id (+) = c.sales_rep_id
4 ORDER BY 2;

- SELF JOIN
Self join이란 한 테이블의 행을 같은 테이블에 있는 행과 연결하는 방법으로 같은
테이블을 마치 두개의 테이블인 것처럼 사용하여 JOIN하는 방법을
SQL> SELECT w.id 사번, w.name 사원명 ,
2 m.id 부서장사번, m.name 부서장명
3 FROM s_emp w, s_emp m
4 WHERE w.manager_id = m.id ;

- SET 연산자의 활용
UNION : 각 QUERY결과의 합집합
UNION ALL : 각 QUERY결과의 합집합에 공통부분을 더함
INTERSECT : 각 QUERY결과의 교집합
MINUS : 첫번째 QUERY결과와 두번째 QUERY결과의 차집합

SELECT 칼럼1, 칼럼2 . . .
FROM 테이블1 . . .
SET 연산자
SELECT 칼럼1`, 칼럼2` . . .
FROM 테이블2 . . .
ORDER BY ;

SQL> SELECT name , dept_id , title
2 FROM s_emp
3 WHERE dept_id = 110
4 UNION
5 SELECT name , dept_id , title
6 FROM s_emp
7 WHERE dept_id = 113
8 ORDER BY 1 ;

* SUBQUERY
- SUBQUERY의 문형

SELECT 검색할 컬럼들
FROM 테이블명
WHERE 형식 연산자 (SELECT 검색할 컬럼들
FROM 테이블명
. . . );

SUBQUERY는 괄호로 묶여 있어야 합니다.
SUBQUERY구문에서는 ORDER BY절을 포함할 수 없습니다.
SUBQUERY는 연산자의 오른쪽에 나타나야 합니다.
SUBQUERY에서 사용할 수 있는 연산자의 종류에는
- 단일행 연산자(=, >, >=, <, <=, <>)
- 복수행 연산자 (IN, NOT IN)가 있습니다.

SUBQUERY를 사용할 수 있는 절의 종류
WHERE 절
HAVING절
UPDATE절
INSERT구문의 INTO절
UPDATE구문의 SET절
SELECT나 DELETE의 FROM절

- SINGLE ROW SUBQUERY
SUBQUERY에서 Main Query로 전달되는 행이 단 하나인 경우
이런 경우는 단일 행 연산자를 사용합니다
SQL> SELECT name, title , dept_id
2 FROM s_emp
3 WHERE dept_id = 김정미가 근무하는 부서;
SQL> SELECT dept_id
2 FROM s_emp
3 WHERE name = '김정미';
SQL> SELECT name, title , dept_id
2 FROM s_emp
3 WHERE dept_id = (SELECT dept_id
4 FROM s_emp
5 WHERE name = ‘김정미’) ;
SQL> SELECT name, salary ,title
2 FROM s_emp
3 WHERE title = (SELECT title
4 FROM s_emp
5 WHERE name = ‘최정선’)
6 ORDER BY salary ;

- MULTI ROW SUBQUERY
SUBQUERY에서 Main Query로 전달되는 행이 여러 개인 경우를 말합니다.
이런 경우는 다중 행 연산자를 사용합니다.
SUBQUERY에서 넘어오는 행이 여러 개이므로, 이때 사용되는 연산자는 IN임을 주의하세요.
SQL> SELECT name, dept_id
2 FROM s_emp
3 WHERE dept_id IN (SELECT id
4 FROM s_dept
5 WHERE region_id =3 ) ;
SQL> SELECT name , dept_id
2 FROM s_emp
3 WHERE dept_id IN
4 (SELECT id
5 FROM s_dept
6 WHERE region_id =
7 (SELECT id
8 FROM s_region
9 WHERE name = ‘서울특별시’) ) ;

- MULTI COLUMN SUBQUERY
SUBQUERY구문을 작성할 때 WHERE절에서 비교하는 컬럼이 하나가 아니라
여러개의 컬럼을 동시에 비교하는 경우를 말하며, 이런 경우를 Pair-wise되었다고 합니다.

Non-Pairwise SUBQUERY
SQL> SELECT name, dept_id, salary
2 FROM s_emp
3 WHERE salary IN (SELECT MIN(salary)
4 FROM s_emp
5 GROUP BY dept_id );

Pairwise SUBQUERY
SQL> SELECT name, dept_id, salary
2 FROM s_emp
3 WHERE (salary,dept_id) IN
4 (SELECT MIN(salary),dept_id
5 FROM s_emp
6 GROUP BY dept_id );

- FROM절에서의 SUBQUERY
한 테이블에 데이터 양이 많은 경우에는 FROM절에 테이블 전체를 기술하여
사용하면 효율이 떨어질 수 있으므로 이런 경우에는 필요한 행과 열만을
선택하여 FROM절에 SUBQUERY로 기술함으로써 효율적인 데이터 검색을 할 수 있습니다.
SQL> SELECT e.name , e.title , d.name
2 FROM (SELECT name ,title , dept_id
3 FROM s_emp
4 WHERE title = ‘사원’) e , s_dept d
5 WHERE e.dept_id = d.id ;

- HAVING절에서의 SUBQUERY
일반적인 조건은 WHERE절에서 기술하지만, GROUP에 대한 조건은 HAVING절에서 기술합니다.
이 때 HAVING의 조건에 기술할 값이 주어져 있지 않은 경우에 모르는 값에 대한 데이터를
검색하기 위하여 SUBQUERY를 사용할 수 있습니다
SQL> SELECT dept_id , AVG(salary)
2 FROM s_emp
3 GROUP BY dept_id
4 HAVING AVG(salary) > (SELECT AVG(salary)
5 FROM s_emp
6 WHERE dept_id = 113) ;

- CORRELATED SUBQUERY
Outer Query의 candidate row가 더 이상 남지 않을때까지 반복됩니다.
CORRELATED SUBQUERY인 경우는 Outer Query의 각 행에 대해서 Inner Query가 매번 수행됩니다.

SQL> SELECT name, salary ,dept_id
2 FROM s_emp outer
3 where salary < (SELECT AVG(salary)
4 FROM s_emp
5 WHERE dept_id = outer.dept_id) ;

바로 2번째 LINE의 OUTER라는 테이블 ALLAS를 5번 LINE에서 사용하게 되면
내부적으로 CORRELATE SUBQUERY의 진행방식을 따르게 됩니다.

* 테이블 생성
- ORACLE8 데이터 타입
VARCHAR2(size) : 최대 길이가 size인 가변길이 문자값으로 최소길이는 1,최대길이는 2000
CHAR(size) : 길이가 size인 고정길이 문자값으로 기본길이는 1, 최대길이는 255
NUMBER : 38자리까지 유효한 부동 소수점 숫자
NUMBER(p,s) : 38의 범위 중에서 p의 자릿수까지 유효한 숫자값으로 전체 자릿수 p, 소수점 자릿수 s
DATE : B.C.4712년 1월에서 A.D.4712년 12월 31일 사이의 일자와 시간
LONG : 2GB까지의 가변길이 문자값으로 테이블당 한 개의 LONG열만 허용
RAW와 LONG RAW : 각각 VARCHAR2, LONG과 같지만 이진 데이터를 저장하는데 사용

- 테이블 생성
CREATE　TABLE　[스키마]테이블명
(칼럼명1 DATATYPE [DEFAULT 형식] ,
칼럼명2 DATATYPE [DEFAULT 형식] ,
..... ) ;
스키마 : 객체의 집합으로서 테이블의 소유자를 의미
테이블명 : 새로 생성하고자 하는 테이블의 이름
칼럼명 : 테이블에 포함된 칼럼의 이름
DATATYPE : 칼럼이 가질 데이터의 타입과 크기
DEFAULT : 데이터 입력(INSERT)시에 기본 값을 명시

- Constraint
제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터의
무결성을 유지하기 위한 가장 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 성질입니다.
테이블을 생성할 때 제약 조건을 반드시 줄 필요는 없습니다.

+ Constraint 성질
Constraint란 데이터 무결성 유지를 위하여 사용자가 지정할 수 있는 성질
모든 Constraint은 데이터 사전(Data Dictionary)에 저장됨
의미있는 이름을 부여했다면 Constraint을 참조하기 쉬움
Constraint의 이름은 표준 객체 명명법을 따르는게 좋음
Constraint의 이름은 한 사용자 내에서 중복될 수 없음
Constraint의 이름을 생략했다면 ORACLE은 SYS_Cn형식의 이름을 만듦

+ Constraint 정의 방법
+ 컬럼레벨 제약조건
각 컬럼별로 제약조건을 정의
제약조건의 어떤 유형도 정의 가능

문형
컬럼 [CONSTRAINT 제약조건 이름] 제한조건 유형

+ 테이블레벨 제약조건
컬럼에 대한 정의와는 별개로 정의
하나 이상의 컬럼(composite column)에 대하여 정의할때 사용
NOT NULL을 제외한 어떤 제약조건도 정의 가능

문형
컬럼 , ...
[CONSTRAINT 제약조건 이름] 제한조건 유형 (컬럼1,컬럼2..)

+ Constraint 종류
o NOT NULL 제약 조건
NOT NULL 제약 조건은 NULL값을 컬럼에 사용할 수 없게 합니다.
NOT NULL 제약 조건이 없는 컬럼은 NULL값을 가질 수 있습니다.
NOT NULL 제약 조건은 컬럼 레벨로만 정의할 수 있습니다.
... last_name VARCHAR2(25)
CONSTRAINT friend_last_name_nn NOT NULL , ...

o UNIQUE 제약 조건
UNIQUE 제약조건은 컬럼이나 컬럼의 조합을 고유 키로 지정합니다.
테이블에는 이 키에 대해 같은 값을 갖는 행이 하나 밖에 없습니다.
UNIQUE 키가 한 컬럼에 대한 것이라면 NULL 값을 쓸 수 있습니다.
컬럼 레벨이나 테이블 레벨에서 모두 정의할 수 있습니다.
UNIQUE 컬럼에 대해 UNIQUE 인덱스가 자동으로 생성됩니다.
(Column) ....phone VARCHAR2(10)
CONSTRAINT s_emp_phone_uk UNIQUE ,...
(Table) ....phone VARCHAR2(10),
CONSTRAINT s_emp_phone_uk UNIQUE(phone),...

o PRIMARY KEY 제약 조건
테이블에 단 하나의 Primary key만 허용합니다.
여러 컬럼일지라도 NULL값을 허용하지 않습니다.
테이블이나 컬럼 레벨에서 정의할 수 있습니다.
UNIQUE 인덱스를 자동으로 생성합니다.
(Column) .... id NUMBER(7)
CONSTRAINT s_emp_id_pk PRIMARY KEY....
(Table) .... id VARCHAR2(7),
CONSTRAINT s_emp_id_pk PRIMARY KEY(id),...

o FOREIGN KEY 제약 조건
foreign key는 자식(child) 테이블에 정의합니다.
부모(Parent) 테이블의 값과 일치하거나 NULL이어야 합니다.
테이블이나 컬럼 레벨에서 정의할 수 있습니다.
Foreign Key 제약조건을 줄 때 주의사항
1. 참조하고자 하는 테이블이 먼저 생성되어 있어야 합니다.
2. 참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 있어야 합니다.
3. ·참조하고자 하는 컬럼의 데이터 타입이 일치해야 합니다.
ON DELETE CASCDE옵션
FOREIGN KEY제약조건을 줄 때 ON DELETE CASCDE옵션을 줄 수 있습니다.
참조하는(child) 테이블쪽에 이 옵션을 주면 참조되는(parent) 테이블의
행을 삭제 가능하게 할 뿐만 아니라 참조하는 행들도 자동으로 삭제해 주는 옵션입니다.
(Column) 　....dept_id NUMBER(7)
CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id)....

(Table) 　....dept_id NUMBER(7),
CONSTRAINT s_emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES s_dept(id)....

o CHECK 제약 조건
CHECK 제약 조건은 각 행이 만족해야 하는 조건을 정의합니다.
CHECK 제약 조건에서 사용할 수 없는 것은 다음과 같습니다.
CURRVAL, NEXTVAL, LEVEL, ROWNUM에 대한 참조
SYSDATE, UID, USER, USERENV 함수에 대한 호출
다른 행의 값을 참조
ORACLE7 Server의 예약어는 쓸 수 없다.
컬럼이나 테이블 레벨에서 모두 정의할 수 있다.
(Column) .... salary NUMBER(11,2)
CONSTRAINT s_emp_salary_ck CHECK (salary between 500 and 10000)..

(Table) ... salary NUMBER(11,2) ,
CONSTRAINT s_emp_salary_ck CHECK (salary between 500 and 10000)..

o SQL> CREATE TABLE c_emp
2 (id　　　　 NUMBER(5)
3 CONSTRAINT c_emp_id_pk PRIMARY KEY ,
4 name VARCHAR2(25)
5 CONSTRAINT c_emp_name_nn NOT NULL,
6 salary NUMBER(7,2) ,
7 phone VARCHAR2(15)
8 CONSTRAINT c_emp_phone_ck CHECK
9 (phone like '3429-%') ,
10　dept_id NUMBER(7)
11 CONSTRAINT c_emp_dept_id REFERENCES S_DEPT(ID) ) ;
SQL> CREATE TABLE emp_113(사번, 이름,메일,입사일)
2 AS SELECT id, name, mailid, start_date
3 FROM s_emp
4 WHERE dept_id = 113 ;

* 데이터 조작어
- INSERT
SQL> INSERT INTO emp(id,name,salary,title,in_date,dept_name)
2 VALUES (100,'무궁화',950,'대리',TO_DATE('1998/05/01','YYYY/MM/DD'),'영업부');
SQL> INSERT INTO emp
2 VALUES (100,'무궁화',950,'대리',TO_DATE('1998/05/01', 'YYYY/MM/DD'),'영업부');
SQL> INSERT INTO emp
2 VALUES (110, USER,1000,'과장',SYSDATE,'관리부');
SQL> INSERT INTO emp
2 VALUES (120, '진달래',NULL,NULL,SYSDATE,'관리부');
SQL> INSERT INTO emp
2 VALUES (120, '진달래','','',SYSDATE,'관리부');
SQL> INSERT INTO emp(id, name, in_date, dept_name)
2 VALUES (130, '개나리',SYSDATE,'관리부');

- UPDATE
SQL> UPDATE emp
2 SET dept_name = '영업부', salary = 800
3 WHERE id = 130 ;

- DELETE
DELETE FROM 테이블명
[WHERE 조건식];

- SUBQUERY 이용
SQL> INSERT INTO emp_113
2 (id, name, mailid , start_date)
3 SELECT id, name, mailid , start_date
4 FROM s_emp
5 WHERE dept_id = 118 ;
SQL> UPDATE s_emp
2 SET dept_id= ( SELECT dept_id
3 FROM s_emp
4 WHERE title = '사장')
5 WHERE name = '안창환' ;

* TRANSACTION CONTROL 과 DICTIONARY
- 명시적인 (Explicit) 트랜잭션 제어 명령어
COMMIT : 아직 저장되지 않은 모든 데이터 변경 사항을 데이터베이스에 저장하고 현재의 트랜잭션을 종료
SAVEPOINT 이름 : 현재의 트랜잭션에 savepoint를 지정
ROLLBACK [TO SAVEPOINT 이름] :아직 저장되지 않은 모든 데이터 변경 사항을 취소하고
현재의 트랜잭션을 종료

- 암시적인 (Implicit) 트랜잭션 제어 명령어
자동 COMMIT : DDL 명령이나 DCL 명령 실행
COMMIT나 ROLLBACK을 명시적으로 실행하지 않고 SQL*Plus를 정상적으로 종료
ROLLBACK [TO SAVEPOINT 이름] : SQL*Plus의 비정상적 종료나 시스템 실패

- COMMIT나 ROLLBACK 이전 데이터 상태
단지 buffer에만 영향을 받았기 때문에 데이터의 이전 상태는 복구 가능
현재 사용자는 SELECT 문으로 DML의 결과를 확인 가능
다른 사용자는 현재 사용자가 수행한 DML문의 결과를 볼 수 없음
변경된 행은 Lock이 설정되어서 다른 USER가 변경할 수 없음

- SAVEPOINT를 이용한 ROLLBACK
SAVEPOINT명령어로 현재의 트랜잭션중에 위치를 지정할 수 있음
ROLLBACK TO SAVEPOINT문을 써서 특정 SAVEPOINT까지 ROLLBACK 할 수 있음
SQL>INSERT...
SQL> SAVEPOINT in_done;
Savepoint created.
SQL> UPDATE..
SQL> ROLLBACK TO in_done;
Rollback complete.

* DICTIONARY
- DICTIONARY의 개념
데이터베이스가 생성될 때 ORACLE8 SERVER에 의해서 자동으로 생성되며
그 내용도 자동으로 갱신, 유지보수됩니다.
DATA Dictionary는 주로 읽기 전용의 테이블들의 집합으로써 사용자가 직접 access하여
데이터를 조작할 수 없고 단지 사용자의 DDL에 연관한 작업이 자동으로 DATA Dictionary에 반영됩니다
ORACLE7 Server 사용자명, 사용자에게 허가된 권한, 데이터베이스 객체명(TABLE, SEQUENCE, VIEW, INDEX 등)
테이블 제약조건 감사(Auditing) 정보 등이 기록되어 있습니다.

- DICTIONARY의 종류
View의 종류
접두어(prefix) 설 명
USER_ 사용자가 소유한 객체에 관한 정보를 저장
ALL_ 사용자에게 액세스가 허용된 객체에 관한 정보를 저장
DBA_ DBA권한을 가진 사용자가 액세스 할 수 있는 정보를 저장
V$ 서버의 성능과 Locking에 관한 정보를 저장. 일반적으로 DBA에게만 허용됨.

기타 뷰
뷰 이름 설 명
DICTIONARY 모든 데이터 사전 테이블, 뷰, 동의어 저장
TABLE_PRIVILEGES 사용자가 권한을 부여했거나(GRANTOR) 부여받은(GRANTEE) 오브젝트에 대한 권한
IND USER_INDEXES 의 동의어(SYNONYMS)

- DICTIONARY 검색
SQL> DESC dictionary
SQL> SELECT *
2 FROM dictionary ; 사용자가 엑세스할 수 있는 모든 데이터 사전 뷰를 검색
SQL> SELECT *
2 FROM dictionary
3 WHERE table_name LIKE 'USER%';
SQL> SELECT table_name
2 FROM dictionary
3 WHERE table_name LIKE 'USER%'; 사용자가 소유한 모든 데이터 사전 뷰의 이름을 검색
SQL> SELECT object_name
2 FROM user_objects
3 WHERE object_type = 'TABLE'; 사용자가 소유한 모든 테이블을 조회
SQL> SELECT *
2 FROM dictionary
3 WHERE LOWER(comments) LIKE '%constraint%';
DICTIONARY 테이블의 COMMENTS 칼럼에서 제약조건과 관련된 주제에 대한
데이터 사전 테이블에 대해 조회하시오.
SQL> DESC user_constraints; USER_CONSTRAINTS 테이블의 구조를 확인하시오.
SQL> SELECT constraint_name, constraint_type,
2 search_condition, r_constraint_name
3 FROM user_constraints
4 WHERE table_name = 'S_EMP'; S_EMP 테이블에 대한 제한조건을 검색하시오.
SQL> SELECT constraint_name,column_name
2 FROM user_cons_columns
3 WHERE table_name = 'S_EMP'; S_EMP 테이블에 대한 칼럼 제한 조건을 검색하시오.

* 데이터 정의어
- 명령어의 종류
CREATE TABLE 새로운 테이블을 생성
DROP TABLE 기존 테이블의 구조 및 모든 행을 삭제
ALTER TABLE 기존 테이블을 변경
- 컬럼의 추가 , 수정
- 제약조건 추가,삭제,활성화,비활성화
TRUNCATE 기존 테이블의 구조는 남기고 모든 행을 삭제
RENAME Object의 이름을 바꿈
COMMENT 테이블이나 컬럼에 주석문 달기

이러한 명령어는 모두 데이터 정의 명령어(DDL)로서 실행시 자동 COMMIT되고
ROLLBACK될 수 없으므로 주의해서 실행해야 합니다.

- 테이블 삭제
DROP TABLE 테이블명 [CASCADE CONSTRAINTS ] ;
모든 데이터가 테이블에서 삭제됨
트랜잭션은 자동 COMMIT되므로 ROLLBACK할 수 없음.
해당테이블의 모든 인덱스가 삭제됨
CASCADE CONSTRAINTS옵션은 종속된 제약조건을 삭제함

- 테이블 구조 변경
o 칼럼의 변경
- 칼럼의 추가
ALTER TABLE 테이블명
ADD (컬럼 datatype [DEFAULT 형식][CONSTRAINT 정의][, 컬럼 datatype] ...) ;
SQL> ALTER TABLE c_emp
2 ADD (title VARCHAR2(25)
3 CONSTRAINT c_emp_title_ck CHECK
4 (title IN ('사장','부장','과장','대리','사원' ) ),
5 in_date date DEFAULT SYSDATE );

- 칼럼의 수정
ALTER TABLE 테이블명
MODIFY (컬럼 datatype [DEFAULT 형식][NOT NULL제약조건][, 컬럼 datatype] ...) ;
컬럼의 크기, 기본값 (DEFAULT), NOT NULL 제약조건을 변경가능
컬럼의 크기를 확장
컬럼이 NULL이거나 테이블에 데이터가 없을때 컬럼의 크기를 축소하거나
데이터 타입을 변경 가능
DEFAULT를 변경하면 다음 입력할 때부터 적용됨
컬럼에 NULL값이 없을 경우에만 NOT NULL 제약 조건을 추가 가능
SQL> ALTER TABLE c_emp
2 MODIFY (phone VARCHAR2(25));
SQL> ALTER TABLE c_emp
2 MODIFY (title VARCHAR2(10));
SQL> ALTER TABLE c_emp
2 MODIFY (phone VARCHAR2(15));

o CONSTRAINT의 변경
- 제약조건의 추가
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약 조건명] 제약조건유형 (칼럼명) ;
NOT NULL제약조건은 테이블 레벨 제약조건으로 기술할 수 없으므로,
칼럼의 MODIFY절에서 가능합니다
SQL> ALTER TABLE c_emp
2 ADD CONSTRAINT c_emp_phone_uk UNIQUE(phone) ;

- 제약조건의 삭제
ALTER TABLE 테이블명
DROP CONSTRAINT 제약 조건명 [CASCADE] ;
SQL> SELECT constraint_name,constraint_type , search_condition
2 FROM user_constraints
3 WHERE table_name = 'C_EMP' ;
SQL> ALTER TABLE c_emp
2 DROP CONSTRAINT c_emp_dept_id_nn ;

- 제약조건의 활성화/비활성화
> ALTER TABLE 테이블명
ENABLE CONSTRAINT 제약 조건명 ;
> ALTER TABLE 테이블명
DISABLE CONSTRAINT 제약 조건명 [CASCADE] ; CASCADE는 참조 조건과 함께 수정
SQL> ALTER TABLE s_emp
2 DISABLE CONSTRAINT s_emp_id_pk CASCADE;
SQL> ALTER TABLE s_emp
2 ENABLE CONSTRAINT s_emp_id_pk ;

o 그 외의 DDL
- RENAME-OBJECT의 이름 변경
테이블, VIEW, SEQUENCE, SYNONYM의 이름 변경
객체의 소유자만이 바꿀 수 있음
> RENAME 이전이름 TO 새로운 이름 ;
SQL> RENAME salgrade TO salary_grade:

- TRUNACTE-테이블의 구조를 남기고 모든 데이터 삭제하기
테이블의 모든 행을 삭제
테이블이 사용한 저장 공간을 반환
TRUNCATE 명령이 행의 삭제를 Rollback할 수 없는데 비해,
DELETE 명령은 삭제된 행을 Rollback가능
> TRUNCATE TABLE 테이블명 ;
SQL> TRUNCATE TABLE salary_grade ;

- COMMENT -테이블이나 칼럼에 주석문 달기
입력된 주석은 다음의 dictionary뷰를 통해 볼 수 있습니다
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
> COMMENT ON TABLE 테이블명 IS '주석문장' ;
> COMMENT ON COLUMN 테이블명.칼럼명 IS '주석문장' ;
SQL> COMMENT ON TABLE s_emp IS '사원정보' ;
SQL> COMMENT ON COLUMN s_emp.name IS '사원명' ;
SQL> SELECT *
2 FROM user_tab_comments
3 WHERE table_name = 'S_EMP' ;

* 데이터 제어어
- DATABASE SECURITY
o Privileges
Privileges란 특정한 SQL문장을 수행하거나 특정 Object에 허가된 작업을 의미합니다.
Privileges에는 특정한 SQL 문장을 실행할 수 있는 권한인 시스템 권한(system privilege)과
특정한 오브젝트에 대한 접근을 제어하는 권한인 오브젝트 권한(object privilege)로 나눌 수 있습니다.
o Role
권한 부여와 접근제어 관리를 손쉽게 하기 위해 연관된 권한들의 집합을 묶어서
정의해 놓은 것을 역할(role)이라고 합니다.
o Schema
Schema란 Table, View, Sequence, Procedure, Function 등의 오브젝트들의 집합으로서
Schema는 데이터베이스 사용자에 의하여 소유되어 있고 사용자의 이름과 같은 이름을 가지고 있습니다.
자신의 스키마에 속한 오브젝트가 아닌 것을 액세스하려고 할 때는 그 오브젝트에 대한
액세스 권한을 부여받아야 합니다.

- PRIVILEGES
o SYSTEM PRIVILEGES
- DBA이 갖는 상위 레벨의 시스템 권한
새로운 사용자 생성(CREATE USER)
사용자 삭제(DROP USER)
테이블 삭제(DROP ANY TABLE) 　
테이블 백업(BACKUP ANY TABLE) 등

o 새로운 사용자 생성 및 삭제 문형
> CREATE USER 사용자명 IDENTIFIED BY 암호 :
사용자를 생성하면 DBA는 GRANT 명령을 실행하여 사용자에게 권한을
부여할 수 있으며 사용자는 다음과 같은 시스템 권한을 부여받을 수 있습니다.
CREATE SESSION : 데이터베이스에 접속가능한 권한
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE 등
> DROP USER 사용자명 [CASCADE] ;
CASCADE옵션은 사용자가 포함한 object까지 같이 drop하는 옵션입니다
SQL> CREATE USER march
2 IDENTIFIED BY spring ;

o 사용자 암호 변경 문형
> ALTER USER 사용자명 IDENTIFIED BY 암호 :
SQL> ALTER USER scott
2 IDENTIFIED BY lion ;

o 시스템 권한 부여 및 박탈
DBA는 특정 시스템 권한을 사용자에게 허가할 수 있습니다.
사용자가 권한을 받으면 즉시 그 권한을 쓸 수 있습니다.
> GRANT 권한 [, 권한...] TO 사용자 [, 사용자 ...] ;
> REVOKE 권한 [, 권한...] FROM 사용자 [, 사용자 ...]
SQL> GRANT create table, create view, create sequence
2 TO scott ;
SQL> REVOKE create sequence
2 FROM scott ;

- OBJECT PRIVILEGES
DBA는 특정한 오브젝트에 대해서 사용자에게 작업을 수행하도록 허가할 수
있습니다. 오브젝트 권한은 오브젝트 유형에 따라서 다양하고, 오브젝트의 소유자는
다른 사용자에게 특정 오브젝트 권한을 부여할 수 있습니다.
> GRANT 권한 [(컬럼명)] [ 권한 ...|ALL ]
ON Object명
TO 사용자 | ROLE | PUBLIC [, 사용자...」
[WITH GRANT OPTION ] ;
> REVOKE 권한 [(컬럼명)] [ 권한 ...|ALL ]
ON Object명
FROM 사용자 | ROLE | PUBLIC [, 사용자...」 ;

o 각 Object에 따른 PRIVILEGES 유형을 알아봅시다.
객체 권한 테이블 뷰 SEQUENCE PROCEDURE SNAPSHOT
ALTER V V
DELETE V V
EXECUTE V
INDEX V v
INSERT V V
REFERENCES V
SELECT V V V
UPDATE V V
WITH GRANT OPTION으로 부여된 권한을 회수하면 그 권한을 받은
다른 사용자에게서도 권한이 회수됩니다.
SQL> GRANT SELECT , INSERT (id,name)
2 ON s_emp
3 TO usera , userb ;
SQL> REVOKE INSERT
2 ON s_emp
3 FROM userb ;
usera.SQL> GRANT select, insert
2 ON s_dept
3 TO scott
4 WITH GRANT OPTION ;
userb.SQL> GRANT select
2 ON usera.s_dept
3 TO PUBLIC ;
usera.SQL> REVOKE select, insert
2 ON s_dept
3 FROM userb ;

- ROLE
ROLE이란 사용자에게 허가할 수 있는 관련된 PRIVILEGES의 그룹을 말합니다.
ROLE을 이용하면 권한부여와 회수를 쉽게 할 수 있음
한 사용자가 여러 ROLE을 access할 수 있고, 여러 사용자에게 같은 ROLE을 부여할 수 있음
사용자는 ROLE에 ROLE을 부여할 수도 있음

o ROLL의 생성
> CREATE ROLE role이름 ;
ROLE의 생성 및 부여는 다음과 같은 순서에 따라야 합니다.
① ROLE의 생성
② ROLE에 권한 부여
③ ROLE을 사용자 또는 ROLE에게 부여
SQL> CREATE ROLE create_obj ;
SQL> GRANT create table, create view TO create_obj ;
SQL> GRANT create_obj TO usera, userb ;

- PRIVILEGES와 ROLE에 관한 DICTIONARY 정보
PRIVILEGES와 ROLE에 관련된 정보를 가지고 있는 데이터 사전 뷰의 종류는 다음과 같습니다.
데이터 사전 뷰 설 명
ROLE_SYS_PRIVS Role에 부여된 시스템 권한
ROLE_TAB_PRIVS Role에 부여된 테이블 권한
USER_ROLE_PRIVS 현재 사용자가 액세스할 수 있는 Role
USER_TAB_PRIVS_MADE 현재 사용자의 객체에 부여한 객체권한
USER_TAB_PRIVS_RECD 현재 사용자의 객체에 부여된 객체권한
USER_COL_PRIVS_MADE 현재 사용자 객체의 특정 칼럼에 부여한 객체권한
USER_COL_PRIVS_RECD 현재 사용자 객체의 특정 칼럼에 부여된 객체권한
SQL> SELECT username, granted_role
2 FROM user_role_privs;

* OBJECT
- OBJECT의 종류
오브젝트 설 명
테이블(TABLE) 행과 열의 조합으로 구성된 데이터의 기본 저장단위
시퀀스(SEQUENCE) 자동으로 고유한 숫자값을 생성해주며 주로 기본 키값을 생성하기 위해 사용
인덱스(INDEX) 쿼리(query) 속도를 향상
뷰(VIEW) 하나 또는 그 이상의 테이블로부터 논리적으로 데이터를
추출한 부분집합으로 논리적이고 가상적인 테이블
시노님(SYNONYM) OBJECT에 대한 또다른 이름으로 ALIAS역할
프로그램 유닛(PROGRAM UNIT) SQL, PL/SQL문으로 작성한 Procedure, Function, Trigger , Package

- SEQUENCE
SEQUENCE란 고유한 숫자값을 생성해 주는 객체로서 SEQUENCE GENERATOR가 자동으로 그 값을 생성해 주며
주로 PK값 생성을 위해 사용됩니다.
SEQUENCE는 공유 가능한 객체로서 한 사용자가 생성하면 다른 사용자도 사용할 수 있을 뿐 아니라,
SEQUENCE는 테이블과 독립적이므로 여러 테이블에서 이 값을 참조하여 사용할 수 있습니다.
> CREATE SEQUENCE 이름
[ INCREMENT BY n ]
[ START WITH n ]
[ { MAXVALUE n | NOMAXVALUE } ]
[ { MINVALUE n | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { CACHE n | NOCACHE } ]
이 름 기 능
INCREMENT BY n SEQUENCE 번호간의 간격.기본값 1씩 증가.
START WITH n 생성되는 첫번째 SEQUENCE 번호.기본값 1부터 시작
MAXVALUE n 생성가능한 SEQUENE의 최대값.
NOMAXVALUE 기본옵션으로 최대값은 1027
MINVALUE n 생성가능한 SEQUENCE의 최소값.
NOMINVALUE 최소값을 1로 지정.
CYCLE | NOCYCLE 최대값이나 최소값까지 값이 생성된 경우에도 값을 생성시키는 것으로 기본은 NOCYCLE
CACHE n|NOCACHE 메모리에 유지할 값의 수로, 기본값은 20.
SQL> CREATE SEQUENCE c_emp_id
2 INCREMENT BY 1
3 START WITH 26
4 MAXVALUE 9999999
5 NOCACHE
6 NOCYCLE ;
SQL> INSERT INTO c_emp (id, name, salary)
2 VALUES (c_emp_id.NEXTVAL, '개나리', 1500 ) ;

SEQUENCE가 가지는 PSEUDO COLUMN은 NEXTVAL, CURRVAL 두 가지가 있습니다.
주로 데이터 입력시에 NEXTVAL을 이용해서 데이터를 입력하게 되면,
CURRVAL은 SEQUENCE의 현재 값으로 NEXTVAL가 할당되어야만 그 값이 CURRVAL로 참조 가능해집니다.
SEQUENCE 자체는 일련적으로 값을 생성하지만 SEQUENCE는 테이블에는 독립적인 Object이므로
같은 테이블에서 SEQUENCE가 사용되거나 ROLLBACK이나 SYSTEM CRASH 에 의해서 GAP이 발생할 수 있습니다.
따라서 SEQUENCE가 특정 테이블에 사용된 경우는 일련번호로 입력되는 것을 보장받기는 어려우며,
단지 UNIQUE NUMBER만 생성할 뿐입니다.

SQL> SELECT sequence_name, min_value,
2 max_value, increment_by, last_number
3 FROM user_sequences ; SEQUENCE 정보 확인

- SEQUENCE의 변경 및 삭제
o SEQUENCE의 변경
> ALTER SEQUENCE SEQUENCE이름
[ INCREMENT BY n ]
[ { MAXVALUE n | NOMAXVALUE } ]
[ { MINVALUE n | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { CACHE n | NOCACHE } ]
자신의 SEQUENCE이거나 그 SEQUENCE에 대한 ALTER 권한을 가져야만 SEQUENCE를 수정할 수 있습니다.
유효성 검사를 하게 됩니다.
예를 들어 새로운 MAXVALUE는 현재의 SEQUENCE 값보다 작지 않아야 합니다.
START WITH 옵션은 ALTER SEQUENCE를 써서 변경할 수 없습니다.
다른 번호에서 시작하려면 이전 SEQUENCE를 삭제하고 다시 생성해야 합니다.

o SEQUENCE의 삭제
> DROP SEQUENCE SEQUENCE이름 ;
SEQUENCE를 제거할 DROP ANY SEQUENCE권한을 가지고 있거나 그 SEQUENCE의 소유자이어야 합니다.

- INDEX
ORACLE에서 제공하는 INDEX는 Pointer를 이용하여 좀더 빠르게 데이터를 검색하도록 하는 Object입니다.
만약 특정 컬럼에 INDEX가 생성되어 있지 않으면 일반적으로 검색시에 Full Scan을 하게 됩니다.

o INDEX의 생성방법에 따른 종류
Unique INDEX NON-Unique INDEX
Primary key , Unique 제약조건에 사용자에 의해서 생성
의해 자동으로 생성
각 INDEX(Table)의 값이 유일함을 보장 각 INDEX(Table)의 값이 유일하지 않음

o INDEX의 생성
> CREATE INDEX 인덱스명 ON 테이블명 (칼럼 [, 칼럼] ... ) ;
- 인덱스를 만드는 때
조건절(WHERE)이나 조인(Join) 조건에서 칼럼을 자주 이용할 때
칼럼이 넓은 범위값을 가질 때
많은 NULL 값을 갖는 칼럼일 때
테이블의 데이터가 많고 그 테이블에서 조회되는 행의 수가 전체의 10-15%정도일 때
- 인덱스를 만들지 않아야 할 때
테이블이 작을 때
칼럼이 조회의 조건으로 사용되는 경우가 별로 없을 때
대부분의 조회가 행의 10-15% 이상을 검색한다고 예상될 때
테이블이 자주 변경될 때
SQL> CREATE INDEX c_emp_name_idx
2 ON c_emp (name) ;
SQL> SELECT id , name , salary
2 FROM c_emp
3 WHERE name = '구원상' ;

o INDEX의 정보 확인
USER_INDEXES 는 인덱스의 이름과 고유성 정보를 갖고 있습니다.
USER_IND_COLUMNS 는 인덱스명, 테이블명, 칼럼명을 갖고 있습니다.
SQL> SELECT ic.index_name, ic.column_name,
2 ic.column_position, ix.uniqueness
3 FROM user_indexes ix, user_ind_columns ic
4 WHERE ic.index_name = ix.index_name
5 AND ic.table_name = 'C_EMP' ;

o INDEX의 삭제
> DROP INDEX 인덱스명 ;
인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무 영향이 없습니다.
SQL> DROP INDEX C_emp_name_idx ;

인덱스를 삭제하려면 그 인덱스의 소유자이거나 DROP ANY INDEX권한을 가지고 있어야 합니다.
특정 칼럼에 생성된 인덱스를 다른 칼럼으로 변경하는 ALTER는 불가능하므로 인덱스된 칼럼을
변경하려면 삭제한 후 다시 만들어야 합니다.

- VIEW
View란 테이블이나 다른 뷰를 기초로 한 논리적이고 가상(Virtual)의 테이블입니다.
View는 자체의 데이터는 없지만 테이블의 데이터를 보거나 변경할 수 있는 창과 같고,
View는 실제적으로는 질의문장을 가집니다.

o VIEW의 종류 및 특성
Simple View Complex View
테이블 수 ONE MANY
Function의 포함여부 NO YES
Grouping 여부 NO YES
View를 통한 DML여부 YES NO

o VIEW의 생성
CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW 이름
[ (alias[, alias] ...)]
AS subquery
[ WITH CHECK OPTION [ CONSTRAINT 제약 조건 ] ]
[ WITH READ ONLY ] ;

종 류 역 할
FORCE 기본 테이블 유무에 관계없이 뷰를 만듦.
Alias Subquery로 선택된 값에 대한 칼럼명
WITH CHECK OPTION 뷰에 의해 액세스될 수 있는 행만이 입력되거나 변경될 수 있음
WITH READ ONLY 뷰에서 DML이 수행될 수 없음.

뷰를 정의하는 질의어는 조인, 그룹, Subquery를 포함하는
복잡한 SELECT문장으로 구성될 수 있음
뷰를 정의하는 질의어에는 ORDER BY 절을 쓸 수 없음
제약조건의 이름을 명시하지 않으면 시스템이 SYS_Cn형태의 이름을 지정함
뷰를 삭제하거나 재생성하지 않고 뷰의 정의를 변경하려면 OR REPLACE 옵션을 쓸 수 있음

- 뷰에 대한 DML문 사용 규칙
간단한 뷰에서는 DML연산 수행가능.
뷰가 다음 사항을 포함하는 경우 행을 삭제할 수 없음
- 조인(Join) 조건
- 그룹 함수
- GROUP BY 절
- DISTINCT 명령
뷰가 다음 사항을 포함하는 경우 데이터를 수정할 수 없음
- 위의 조건
- 식으로 정의된 칼럼
ex) SARARY*12
뷰가 다음 사항을 포함하는 경우 데이터를 추가할 수 없음
- 위의 조건
- VIEW에 선택되지 않은 NOT NULL 칼럼

SQL> CREATE VIEW empvu113
2 AS SELECT id, name, title
3 FROM s_emp
4 WHERE dept_id = 113 ;
SQL> CREATE or REPLACE VIEW empvu113
2 AS SELECT id, name NAME, salary MONTHLY_SALARY
3 FROM s_emp
4 WHERE dept_id = 113 ;
SQL> CREATE or REPLACE VIEW empvu113(ID,NAME,MONTHLY_SALARY)
2 AS SELECT id, name , salary
3 FROM s_emp
4 WHERE dept_id = 113 ;
SQL> CREATE OR REPLACE VIEW dept_sum_vu
2 (name, minsal, maxsal, avgsal)
3 AS SELECT d.name, min(e.salary),
4 max(e.salary), avg(e.salary)
5 FROM s_emp e, s_dept d
6 WHERE e.dept_id = d.id
7 GROUP BY d.name;
SQL> CREATE OR REPLACE VIEW empv
2 AS SELECT id,name,dept_id
3 FROM c_emp
4 WHERE dept_id=113 ;

o VIEW 생성시 사용되는 OPTION
OPTION종류 기 능
With CHECK Option View의 연산이 CHECK 조건이 만족될때만 수행되도록 합니다.
With Read Only View 정의에 WITH READ ONLY 옵션을 추가하여 DML 연산이 발생하지
않도록 합니다. View의 행에 대한 DML을 수행하려 한다면
Oracle7 Server error인 ORA-01732가 발생합니다.

SQL> CREATE OR REPLACE VIEW empvu113
2 AS SELECT *
3 FROM s_emp
4 WHERE dept_id=113
5 WITH CHECK OPTION
SQL> CREATE OR REPLACE VIEW empvu113
2 AS SELECT *
3 FROM s_emp
4 WHERE dept_id=113
5 WITH READ ONLY ;

o VIEW의 정보 확인
SQL> DESC user_views
SQL> SELECT view_name , text
2 FROM user_views ;

o VIEW의 삭제
> DESC VIEW view이름 ;
SQL> DROP VIEW empv ;
View에 대한 ALTER 명령어는 제공되지 않습니다. 기존의 View에 대한 정보를
변경하고 싶은 경우에는 OR REPLACE OPTION을 써서 VIEW를 생성합니다.

- SYNONYM
Synonym이란 특정 Object에 부여하는 또다른 이름으로, 사용자의 편의나 참조를 빠르게 하기 위해서
사용합니다.
> CREATE [ PUBLIC ] SYNONYM Synonym명
FOR Object명 ;

PUBLIC은 모든 사용자가 접근가능한 동의어를 생성합니다.
PUBLIC 동의어의 생성 및 삭제는 DBA 만이 할 수 있습니다.

USER A가 가 USER B의 s_dept 테이블에 대한 동의어인 dept를 생성하시오.
usera.SQL> CREATE SYNONYM dept
2 FOR userb.s_dept ;

Posted by jobs at 16:56 | Comments (0)
ORACLE LOCK 문제를 일으키는 SQL 명령 찾기
* 다음 Query는 Lock과 관련된 transaction을 출력해준다.

column username format a10
column sid format 999
column lock_type format a15
column MODE_HELD format a11
column MODE_REQUESTED format a10
column LOCK_ID1 format a8
column LOCK_ID2 format a8
select a.sid,
decode(a.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
a.type) lock_type,
decode(a.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.lmode)) mode_held,
decode(a.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.request)) mode_requested,
to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
from v$lock a
where (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=a.id1 and
b.id2=a.id2 and b.request>0);

( 위의 Query를 실행시키면 다음과 같은 내용이 출력된다.

SID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
--- --------------- ---------- ---------- -------- --------
5 Transaction Exclusive None 262172 90
6 Transaction None Exclusive 262172 90
9 Transaction None Exclusive 262172 90

SID 6과 9는 SID 5가 걸고 있는 Lock이 풀리기를 기다리고 있음을 알 수 있다.

* 다음 Query는 Lock과 관련된 테이블을 출력해 준다.

column username format a10
column lockwait format a10
column sql_text format a80
column object_owner format a14
column object format a15
select b.username username, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address and
a.hash_value=b.sql_hash_value and
b.sid = c.sid and c.owner != 'SYS';
/

( 위의 Query를 실행하면 다음과 같은 결과가 출력된다.

USERNAME SID OBJECT_OWNER OBJECT LOCKWAIT
--------------- --- ------------- -------------- ----------
SQL
---------------------------------------------------------------
LTO2 6 LTO EMP C3D320F4
update lto.emp set empno =25 where empno=7788
LTO3 9 LTO EMP C3D320C8
delete from lto.emp where empno=7788
LTO 5 LTO DEPT
insert into lto.dept values (60,'PROGRAMMER','LOS ANGELOS')

여기서는 USERNAME에 나와있는 유저가 OBJECT에 나와있는 테이블을 수정하려고
함을 나타낸다. LT02,LT03는 LT0가 Commit,Rollback 하기를 기다리고 있음을 알
수 있다. 하지만 여기에는 가장 최근의 DML 명령 하나만 나와있기 때문에 여기
나온 명령이 반드시 Lock을 걸고 있는 명령이라고 단정지을 수는 없다.

관련된 프로세스
---------------
* 다음 Query를 실행해 보면 프로세스와 관련된 정보를 얻을 수 있다.

column "ORACLE USER" format a11
column SERIAL# format 9999999
column "OS USER" format a8
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';
/

* 위의 Query를 실행하면 다음과 같은 결과가 출력된다.

ORACLE PROCESS SESSION SERIAL# OS USER PROC SESS LOCKWT
USER ID ID SPID SPID
------ ------- ------- ------- ------- ---- ---- ------
LTO 19 5 31 usupport 17312 17309
LTO2 25 6 43 usupport 17313 17310 C3D320F4
LTO3 26 9 1 usupport 17314 17311 C3D320D8

이 때는 다음과 같이 조치한다.

1. LTO에게 Commit,Rollback 할 것을 요구한다.
2. SQLDBA>ALTER SYSTEM KILL SESSION '5,31';
3. %kill -9 17309 (유닉스상의 Shadown Process)
stop/id= (PROC SPID=SESS SPID on vms running single task)

여기서 SYS 유저는 제외시켰는데 필요하다면 Query의 Where 조건에서
s.username != 'SYS' 부분을 삭제하면 된다.

CATBLOCK.SQL & UTLLOCKT.SQL
---------------------------
$ORACLE_HOME/rdbms/admin 디렉토리에 있는 스크립트 가운데 catblock.sql과
utlockt.sql을 사용하여서 Lock 상황을 쉽게 파악할 수 있다. 이들은 다음과
같이 실행한다.

%cd $ORACLE_HOME/rdbms/admin
%sqldba lmode=y (svrmgrl
SVRMGR>connect internal
SVRMGR>@catblock

결과는 다음 Query 문으로 알아 본다.

column waiting_session format a8
select lpad(' ',3*(level-1)) || waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;

위의 Query에 의한 출력은 다음과 같다.

WAITING_ LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2
-------- ----------------- ---------- ---------- ---------- ----------
5 None
6 Transaction Exclusive Exclusive 262172 90
9 Transaction Exclusive Exclusive 262172 90

여기서 Session 6, Session 9가 Session 5를 기다리고 있음을 알 수 있다.

Lock & Hanging 문제를 추정하는 방법
-----------------------------------
프로그램 상에서 어느 부분이 Lock, Hanging 문제를 일으키는지 알아내기가
여의치 않을때 다음과 같은 방법을 사용해 보기 바란다.

1. init.ora의 sql_trace=ture로 세팅하면 연관된 SQL 명령이 출력될
것이다.
2. OS 상에서도 Process 상태를 점검하여 본다.
3. OS 상의 Debugging 기능을 사용하거나 만약 가능하다면 oradbx를 사용한다.
Platform 에 따라서 없을 수도 있다.
4. 여러가지 Monitoring 방법과 Locking/Blocking 스크립트를 이용한다.

PROCESS STACKS 확인
-------------------
때로는 Hanging Process나 리소스를 점유하고 있는 Process의 Process Stack을
점검해 보는 것이 문제 해결에 도움이 되는 경우가 있다.

1. OS Debugging 프로그램을 이용하여 Hangup 되기 전의 마지막 Call을 확인
한다.

ex)%truss -p

2. oradbx(오라클 debugging 프로그램)은 Support,Development 시에만 사용
된다.

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"
from v$session s, v$access a, v$process p
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS';
/

위의 Query를 실행하면 다음과 같은 결과가 출력된다.

ORACLE PROCESS SESSION SERIAL# OS USER PROC SESS LOCKWT
USER ID ID SPID SPID
------ ------- ------- ------- ------- ---- ---- ------
LTO 19 5 31 usupport 17312 17309
LTO2 25 6 43 usupport 17313 17310 C3D320F4
LTO3 26 9 1 usupport 17314 17311 C3D320D8

만약 oradbx가 없다면 다음과 같이 해서 만들어 준다.

%cd $ORACLE_HOME/rdbms/lib
%make -f oracle.mk oradbx

LTO Process가 무엇을 하고 있는지 알고 싶으면 Process Stack을 보면 알수
있다.

ps -ef | grep 17312
usupport 17312 17309 0 Sep 15 ? 0:00 oracleV713(DESCRIPTION=(LOCAL=YE

type
debug 17312 (이 유저의 oracle shadow process)
dump stack
dump procstat

위에서 생성된 트레이스 화일(user_dump_dest 에 생성됨)을 이용하면 Hanging
문제를 해결하는데 큰 도움이 된다.

자주 발생하는 LOCK 문제들
-------------------------
1. Parent-Child 관계로 묶인 테이블에서 Child 테이블에 Index가 없는 상황
에서 Child 테이블을 수정하게 되면 Parent테이블에 TABLE LEVEL SHARE
LOCK이 걸리게 되어서 Parent 테이블에 대한 모든 Update가 금지된다.
2. 블럭의 PCTFREE가 매우 작다면 한 블럭에 여러개의 레코드가 들어 있기
때문에 한 블럭에 과도한 트랜잭션이 들어와서 블럭의 Transaction Layer가
Release 되기를 기다리게 되는 경우도 있다.

Ex)
create table test (a number) initrans 1 maxtrans 1;

SYSTEM: insert into test values (5); /* completed */
SCOTT: insert into SYSTEM.test values (10); /* Scott waits */

SID OWNER LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
---- ----- ------------- ----------- ---------- -------- --------
7 System Transaction Exclusive None 196639 54
10 Scott Transaction None Share 196639 54

============================================================================

＊ 교육 담당 & 관리자 : 김 윤 석
＊ 교육 문의 & 예약 접수 : 02) 6255-8046
＊ 홈페이지 http://www.itmoya.net/ocp/main_1.htm
- 오라클지정 교육원, 본원 시험실시, 40%바우쳐제공

추천학원 http://www.itmoya.com

Posted by genesmer

[OCP학원/국제자격증] 오라클 기본 명령어 정리

오라클 / OCP 10g 2008. 3. 31. 10:22

추천학원 http://www.itmoya.com

Posted by genesmer

오라클OCP 9i 10g 자격증 100%취득 교육 - 40%바우쳐 4장제공

오라클 / OCP 10g 2008. 3. 31. 10:22

★ 오라클 9i + 10g 실무중심 자격증취득 교육

오라클 공인 전문가 제도(Oracle Certified Professional)는 오라클 제품 사용자의 관련 업무별 전문

기술 지식 정도를 측정하여 대내외적으로 증명해 주며, 오라클의 제품과 최신 기술을 활용하여 최적의 솔루션을 설계, 개발, 응용하는데 필요한 인력의 역량을 인증해 주는 프로그램입니다.

2006 오라클 9I+10G 양성교육 일정은 국비지원 제도 사항으로 혜택을받으실수 있습니다.
자세한 교육내용를 알고 싶으시면 운영자에게 개인상담 / 전화 /메일로 주시면 자세한
세부 교육커리 와 수강료에 대해서 안내해 드리겠습니다.

※본원 오라클 지정 취업지원과정 교육 파트너(Oracle Workforce Development Program)

오라클의 정규 과정의 강의를 수행할 수 있는 강사를 보유한 기관을 대상으로 오라클의 교육과목에

대하여 공식교재와 S/W를 활용하여 교육과정을 진행하는 교육 기관입니다.

협력교육기관은 자체적으로 과정을 개설하며, 수강생을 모집하고, 강의를 수행하며, 교육 품질에 대한 책임도 협력 교육기관이 소유하는 형태의 교육 협력 파트너입니다.
오라클은 우수 IT인력 양성과 실업자/미취업자의 취업 지원을 위한 취업지원과정을 통해 오라클의 교육과정을 수강하고자 하는 수강희망자에게 폭넓은 기회를 제공하고자 노력하고 있습니다.

▶ 교육과정 : ORACL-DBA 9I+10G [WDP공인지정센터]
▶ 개강안내 : 매월 초 개강 / 수시접수 / 교육시간 조정가능 / 평일,주말반 운영
▶ 교육대상 : 데이터베이스 전문가가 되기 원하는 사람
OCP 자격증 취득을 원하는 사람
오라클을 사용하여 SQL 프로그래밍을 하려는 사람
▶ 모집정원 : 15명[선착순마감]
▶ 교육혜택 : WDP지정센터로 오라클 교육과정 수강신청시 바우처 40% 할인쿠폰 증정 [재수강 무료]
▶ 교육장소 : 강남ITWILL교육센터 7층[오라클 공인 강의장]

<강남 국제자격증 시험센터>
전 과정 15명이내 소수 집중 강의와 30석 세미나형 강의
전 강의장 빔 프로젝트 또는 모니터 분배기를 통한 비쥬얼 교육
대학교수(경희대,중앙대,청강대 등) 및 현장경력 6년의 실무진 강사
담임선생님의 철저한 회원관리
1인 1대의 교육시스템
우수수강생중 선별하여 보원 프로젝트 참여기회 부여
노동부 지정 교육기관 - 고용보험 환급제도 실시 교육기관
제1회 KOEX전자상거래박람회 웹마스터대표교육기관선정
매일경제 TV방영
(크리에이티브21,인터넷 전문교육기관선정- 2월 18일 방영)
HOW PC 웹전문 교육기관 선정(1999년 11월호)
정통부,노동부 지정교육원
iMBC 라이프 협력업체 ,
동아일보 donga.com제휴업체
리눅스원지정교육기관
호산나넷 IT 지정교육원
KTF 지정교육기관

<시스템>
펜티엄 5 512MB 80GHDD LCD 모니터
최고급 강의 시스템-XGA Beam Projector
최고급 서버
전용 교육서버
HP Netserver LH4 Xeon 55O 4 Way RAID 5
및 Inter PⅢ-866 Dual 8대운영
Ciscon CCNA, CCNP, CCIE 실습용 랙 완비
SUN Ultra 10 10대보유
Unix 전용서버 운영
10M급 전용회선

▶ 오라클 9I 교육과정 및 세부내용
---------------------------------------------------------
1)Introduction to Oracle SQL and PL/SQL
2)Oracle9i Database: Fundamentals I
3)Oracle9i Database: Fundamentals II
4)Oracle9i Database: Performance Tuning
5)Oracle Database 10g : New Features for Administrators
---------------------------------------------------------

교육과정 세부내용 - 8주교육 [주5일 일일3시간 45분진행]

＊ 1개월

<1,2주차> PL/SQL (1Z0-007)
OCP 자격증 개요
PL/SQL의 기초, 블록(선언부,실행부,예외 처리부)
제어문의 구조(IF문, CASE문, LOOP문, GOTO문, NULL문)
예외 처리(종류, 처리부의 형식, 처리 방식)
오라클 데이터 조작

(DML, CURSOR, CURSOR FOR LOOP, CURSOR Variable, CURSOR 연산식, DCL)
타입(COLLECTION, BULK BINDING, RECORD)
모듈 - 프로시저, 함수, 패키지(PRIVILEGE, 모듈 호출, FUNCTION, 패키지, 오버로딩, 외부 프로시저)
트리거(DML 트리거, INSTEAD OF TRIGGER, 시스템 이벤트 트리거)
Native Dynamic SQL(DBMS_SQL 패키지, 고유 동적 SQL)
객체(Object Type, COLLECTION, 객체 뷰, 이름 분석(Name Resolution)과 엘리어스)
상속

(Inheritance, Step by Step, 치환성(sbsstitutability), 타입의 진화(Type Evolution), 객체 뷰의 상속)

<3,4주차> Fundamental Ⅰ (1Z0-031)
Basic of the Oracle Database Architecture
(오라클 서버구조, 오라클 서버 시작하기, Oracle Instance의 관리,오라클 데이터베이스 생성)
Managing the Physical Database Structure
(데이터 딕셔너리의 내용과 용도, 컨트롤 파일의 유지, 리두 로그 파일 유지)
Managing Tablespaces and Datafiles
(데이터베이스의 논리적 구조에 대한 설명)
Storage Structures and Undo Data
(저장 구조 및 관계, 언두 데이터의 관리)
Managing Database Objects
(테이블의 관리, 인덱스 관리, 데이터 무결성 관리)
Managing Database Users
(사용자 관리, 비밀번호 보안 및 리소스 관리, 권한의 관리, 롤의 관리(Managing Roles)

＊ 2개월

<1,2주차> Fundamental Ⅱ (1Z0-032)
Networking 개요
Oracle Net Architecture 기본
Net Server-Side 구성의 기본
Oracle Net Services Client-Side 구성의 기본
오라클 공유 서버의 활용과 구성
Backup과 Recovery 개요
인스턴스와 미디어 복구 개요
Database Archive 모드 구성
Oracle Recovery Manager 개요와 구성
User-Managed Backups
RMAN Backups
User-Managed Complete Recovery
RMAN Complete Recovery
User-Managed Incomplete Recovery
RMAN Incomplete Recovery
RMAN Maintenance
Recovery Catalog 생성과 유지보수
데이터베이스간의 데이터 전송
데이터베이스내로 데이터 로딩

<3,4주차> Performance Tuning (1Z0-033)
Oracle DB 10g upgrade : new feature 10g

Database Tuning 개요
오라클 성능 튜닝 방법
진단과 튜닝 도구
동적 성능 뷰의 통계
스태츠팩을 사용한 통계수집
경고 로그와 추적 파일
공유 풀 크기 조정
버퍼 개시의 크기 조정
나머지 SGA 구조의 크기 조정
데이터베이스 구성과 I/O 문제
정렬 작업의 최적화
래치 경합의 진단
롤백
언두세그먼트 튜닝
락 경합의 모니터링과 탐지
오라클 공유 서버의 튜닝
애플리케이션 튜닝
오라클 블록의 효율적 사용
SQL 구문 튜닝
OS 고려사항
데이터베이스 Resource Manager
- Oracle DB 10g DBUA 업그레이드 인터페이스와 업그레이드 인포메이션 툴
- 새로운 SYSAUX 테이블스페이스와 자동화된 저장 관리(ASM) 기능
- 데이터베이스 자원 관리자와 개선된 통계 수집
- 새로운 잡 스케줄러
- 대기 인터페이스에서의 튜닝 개선
- SQL Access 조언자와 SQL 쿼리 개선된 가상 프라이빗 데이터베이스와 오디팅
- RMAN, 플래쉬백, 트랜잭션 복구, 데이터 가드의 새로운 기능
- 새로워진 데이터 웨어하우징, 데이터 마트, 데이터 스토어

추천학원 http://www.itmoya.com

Posted by genesmer

[데이터베이스/OCP] 오라클 서버의 구조의 백업/복구 원리

오라클 / OCP 10g 2008. 3. 31. 10:22

1과목	1Z0-007 - Introduction to Oracle9i: SQL
2과목	1Z0-031 - Oracle 9i Database: Fundamentals I
3과목	1Z0-032 - Oracle9i Database: Fundamentals II
4과목	1Z0-033 - Oracle9i Database: Performance Tuning
5과목	1Z0-040 - Oracle Database 10g : New Features for Administrators

<강의 시간>
- 주간반 : 09:30 ~ 13:15 [월~금]
- 야간반 : 19:00 ~ 22:45 [월~금]
- 주말반 : 토요일 (14:00 ~ 21:00) / 일요일 (10:00 ~ 21:00)

과목	주차	세부교육내용
1Z0-007 1Z0-031	1 주차	- SQL의 기초, 블록(선언부,실행부,예외 처리부) - 제어문의 구조(If문, CaseE문, Loop문, Goto문, Null문) - 예외 처리(종류, 처리부의 형식, 처리 방식) - 오라클 데이터 조작 - DML, Cursor, Cursor For Loop, Cursor Variable, Cursor 연산식, DCL - 타입(Collection, Bulk, Binding, Record) - 모듈: 프로시저, 함수, 패키지 - Privilege, 모듈 호출, Function, 패키지, 오버로딩, 외부 프로시저
	2 주차	- 트리거(DML 트리거, Instead Of Trigger, 시스템 이벤트 트리거) - Native Dynamic SQL(DBMS_SQL 패키지, 고유 동적 SQL) - 객체(Object Type, Collection, 객체뷰, 이름 분석과 엘리어스) - 상속(Inheritance, Step by Step, 치환성, 타입의 진화, 객체 뷰의 상속) - 오라클 서버구조, 오라클 서버 시작하기 - Oracle Instance의 관리,오라클 데이터베이스 생성 - 데이터 딕셔너리의 내용과 용도, 컨트롤 파일의 유지 - 리두 로그 파일 유지
	3 주차	- 데이터베이스의 논리적 구조 설명 - 저장 구조 및 관계, 언두 데이터의 관리 - 테이블의 관리, 인덱스 관리, 데이터 무결성 관리 - 사용자 관리, 비밀번호 보안 및 리소스 관리, - 권한의 관리, 롤의 관리
	4 주차	- Networking 개요 - Oracle Net Architecture 기본 - Net Server Side 구성의 기본 - Oracle Net Services Client Side 구성의 기본 - 오라클 공유 서버의 활용과 구성
1Z0-032 1Z0-033 1Z0-040	5 주차	- Backup과 Recovery 개요 - 인스턴스와 미디어 복구 개요 - Database Archive 모드 구성 - Oracle Recovery Manager 개요와 구성 - User-Managed Backups, RMAN Backups - User-Managed Complete Recovery, RMAN Complete Recovery - User-Managed Incomplete Recovery - RMAN Incomplete Recovery, RMAN Maintenance - Recovery Catalog 생성과 유지보수 - 데이터베이스간의 데이터 전송 - 데이터베이스내로 데이터 로딩
	6 주차	- Database Tuning 개요 - 오라클 성능 튜닝 방법, 진단과 튜닝 도구 - 동적 성능 뷰의 통계 - 스태츠팩을 사용한 통계수집 - 경고 로그와 추적 파일, 공유 풀 크기 조정 - 버퍼 개시의 크기 조정
	7 주차	- 나머지 SGA 구조의 크기 조정 - 데이터베이스 구성과 I/O 문제 - 정렬 작업의 최적화, 래치 경합의 진단 - 롤백, 언두세그먼트 튜닝, 락 경합의 모니터링과 탐지, 오라클 공유 서버의 튜닝 - 애플리케이션 튜닝, 오라클 블록의 효율적 사용 - SQL 구문 튜닝, OS 고려사항 - 데이터베이스 Resource Manager
	8 주차	Oracle DB 10g upgrade : new feature 10g - Oracle DB 10g DBUA 업그레이드 인터페이스와 업그레이드 인포메이션 툴 - 새로운 SYSAUX 테이블스페이스와 자동화된 저장 관리(ASM) 기능 - 데이터베이스 자원 관리자와 개선된 통계 수집 - 새로운 잡 스케줄러 - 대기 인터페이스에서의 튜닝 개선 - SQL Access 조언자와 SQL 쿼리 개선된 가상 프라이빗 데이터베이스와 오디팅 - RMAN, 플래쉬백, 트랜잭션 복구, 데이터 가드의 새로운 기능 - 새로워진 데이터 웨어하우징, 데이터 마트, 데이터 스토어

1장 오라클 서버의 구조와 백업/복구원리

주종면 | 플랜정보기술

오라클 데이터베이스를 사용하다 보면 다양한 에러코드와 함께 장애가 발생하는 경우를 한두 번쯤 경험해 보았을 것 입니다. 이런 경우 대부분의 사용자들은 자신이 알고 있는 모든 지식을 기반으로 복구작업을 수행하게 됩니다. 하지만, 막연한 방법과 절차는 복구작업에 도움이 되지 않을 뿐 아니라 오히려 불필요한 시간의 낭비를 초래할 수 있기 때문에 무엇보다도 철저한 계획과 준비가 요구됩니다. 또 한가지 염두에 두어야 할 점은 다양한 장애와 복구방법에 대한 이해를 하기 전에 반드시 오라클 서버 구조에 대한 이해를 하셔야 합니다. 구조에 대한 명확한 이해 없이는 장애에 대한 복구작업을 수행할 수가 없기 때문입니다.

자~ 그럼 오라클 서버의 전체 구조와 복구 원리를 소개하도록 하겠습니다.

1-1 오라클 서버의 구조

먼저, 오라클 서버에 대한 장애와 복구를 체계적으로 습득하기 위해서는 물리적 구조에 대한 이해를 명확히 하셔야 합니다. 예를 들어, 어느날 여러분이 소유하고 계신 자동차가 갑자기 정상적으로 시동이 걸리지 않는다면 어떻게 하시겠습니까?

만약, 여러분이 자동차 구조에 대한 해박한 지식을 가지고 있다면 직접 원인을 찾기 위해 다양한 점검을 하게 되고, 또한 조치를 하게 될 것 입니다. 하지만, 자동차 구조에 대해 전혀 이해를 못한다면 이러한 경우 여러분이 할 수 있는 것은 아무것도 없을 것 입니다.

이와 같은 원리로 오라클 서버환경에서도 장애가 발생했을 때 적절한 조치와 복구작업이 수행되기 위해서는 장애가 발생한 원인을 분석할 수 있어야 합니다. 또한, 그 원인을 분석하기 위해서는 오라클 서버에 대한 철저한 이해가 요구됩니다. 다음은 오라클 서버의 기본구조에 대한 설명입니다. 오라클 서버는 다음과 같이 3가지 영역으로 구성되어 있습니다.

1) 메모리 영역

오라클 서버가 사용 가능한 상태가 되면 모든 사용자들은 데이터베이스에 접속할 수 있으며, 또한 테이블을 통해 데이터를 검색할 수 있게 됩니다. 이때, 읽혀진 테이블 정보들이 잠시 저장되는 공간이 메모리 영역 입니다. 이 공간은 SGA (System Global Area) 영역이라고 부르며 다음과 같이 4가지 영역으로 구성되어 있습니다.

• 공유-풀 영역 (Shared Pool Area)
• 데이터버퍼 캐시 영역 (Data Buffer Cache Area)
• 로그버퍼 영역 (Log Buffer Area)
• 라지-풀 영역 (Large Pool Area)
• 자바-풀 영역 (Java Pool Area)
• 스트림-풀 영역 (Stream Pool Area)

2) 프로세스 영역

사용자가 오라클 서버에 접속하기 위해서는 SQL*PLUS 또는 사용자의 애플리케이션 프로그램을 통해 접속하게 됩니다. 이때, 활성화 되는 영역을 사용자 프로세스(User Process)라고 합니다. 그리고, 사용자가 실행한 SQL문이 실행되기 위해서는 서버 프로세스(Server Process)에 의해 분석되며 테이블로부터 읽기 또는 쓰기 작업을 수행하게 됩니다.

또한, 오라클 서버가 효과적으로 운영되기 위해서 다음과 같은 백그라운드 프로세스(Background Process)가 기본적으로 제공됩니다.

• DBWR (Database Writer Process)
• LGWR (Log Writer Process)
• PMON (Process Monitor Process)
• SMON (System Monitor Process)
• CKPT (Check-Point Process)
• ARCH (Archive Process)
• RVWR
• MMAN (Memory Manager Process)
• RBAL
• ORBn
• RFS (Remote File System Proces)
• MRO

3) 파일 영역

사용자 또는 오라클 서버에 의해 생성되는 모든 테이블 정보가 저장되는 물리적 구조를 파일 영역 또는 데이터베이스(Database) 영역이라고 합니다.

다음은 오라클 서버의 기본 파일구조 입니다.

• 파라메터 파일 (Parameter File)
• 데이터 파일 (Data-File)
• 리두로그 파일 (Redo-Log File)
• 컨트롤 파일 (Control File)
• 플래시-백 데이터베이스 로그 파일 (Flash-Back Database Log File)
• 아카이브 리두로그 파일 (Archive Redo-Log File)

자~ 그럼 보다 구체적으로 오라클 서버의 각 구조를 알아 보도록 하겠습니다.

1-2 SELECT문의 처리과정

이번에는 SELECT문의 처리과정을 통해 오라클 서버의 구조를 보다 구체적으로 알아 보도록 하겠습니다. 사용자들이 실행하는 SELECT문의 결과가 리턴될 때 오라클 서버의 각 구조를 어떻게 사용하는지를 이해해 보십시오.

1) STEP-1 : Parsing

SQL*PLUS 툴을 통해 SCOTT 사용자 계정과 TIGER 암호로 오라클 서버에 접속합니다.

사용자가 실행한 SELECT문은 사용자 프로세스를 거쳐 서버 프로세스로 전송이 됩니다. 서버 프로세스는 사용자가 실행한 SELECT문에 대해 다음과 같이 구문분석(Parsing) 작업을 수행합니다.

<구문분석>
(1)	메모리 영역 중에 공유-풀 영역을 검색하여 사용자가 실행한 문장과 동일한 문장이 존재하는지 확인합니다. 동일한 문장이 발견되면 구문분석 작업을 중단하고, 동일한 문장이 발견되지 않으면 다음 단계의 작업을 계속 수행합니다.

(2)	사용자가 실행한 SELECT문의 문법을 확인합니다.

(3)	SELECT문에 작성된 테이블과 컬럼 들이 사용가능한지 확인합니다.

(4)	해당 사용자가 테이블과 컬럼 들을 검색할 수 있는 권한이 있는지 확인합니다.

(5)	모든 검증작업이 완료되고 나면 구문 분석된 결과를 공유-풀 영역에 저장합니다. 분석결과를 저장하는 이유는 다른 사용자에 의해 동일한 문장이 재 실행되는 경우 구문분석 작업을 재 수행하는 불필요한 작업을 피하게 함으로써 SELECT문의 실행성능을 빠르게 하기 위함 입니다.

2) STEP-2 : Execution

해당 테이블이 저장되어 있는 데이터 파일로부터 데이터를 읽어서 메모리 영역인 데이터-버퍼 캐시 영역(Data-Buffer Cache Area)에 저장해 둡니다. 먼저, 테이블과 데이터 파일과의 관계를 이해하기 위해서는 다음 문장에 대한 이해가 필요합니다.

SQL> CREATE TABLESPACE insa
DATAFILE 'C:\ORACLE\ORADATA\ORA92\INSA01.DBF' SIZE 10M;
→ 인사 업무에 관련된 테이블이 저장될 공간

SQL> CREATE TABLE emp
(EMPNO NUMBER(4),
ENAME VARCHAR2(15))
TABLESPACE insa;
→ EMP 테이블이 저장될 공간은 INSA 테이블스페이스 입니다. 다른 의미로 표현한다면
EMP 테이블이 저장될 공간은 INSA01.DBF 파일입니다.

3) STEP-3 : Fetch

STEP-2 단계에 의해 데이터 버퍼 캐시영역에 저장되어 있는 데이터를 읽어서 사용자의 SQL*PLUS 화면에 출력해 주는 단계를 인출(FETCH)라고 합니다. 이 단계는 SELECT문을 실행하는 경우에만 수행되며 UPDATE, DELETE, INSERT문을 실행할 때는 수행되지 않습니다. 즉, SQL문을 실행한 후 처리된 결과가 화면에 출력되는 것을 인출이라고 합니다.

1-3 DML문의 처리과정

이번에는 DML문 (UPDATE, INSERT, DELETE)의 처리과정에 대해 자세히 알아 보도록 하겠습니다. 사용자가 실행한 DML문에 대해 구문분석을 수행하는 단계까지는 SELECT문과 동일한 방법과 절차에 의해 수행됩니다.

먼저, 처리과정에 대해 알아보기 전에 DML문에서 만 사용되는 오라클 서버의 기본구조에 대해 알아 보겠습니다.

[C:\] sqlplus SCOTT/TIGER
SQL>	UPDATE emp SET sal = sal * 1.1 WHERE empno = 7934;
SQL>	SELECT * FROM emp WHERE empno = 7934; 7934 KING 1100

SQL>	ROLLBACK;
SQL>	SELECT * FROM emp WHERE empno = 7934; 7934 KING 1000

SQL 문장 중에 DML문들은 항상 실행한 후 COMMIT 또는 ROLLBACK문을 실행해야 해당 트랜잭션이 종료됩니다. 위 예제와 같이 UPDATE 문을 실행한 후 ROLLBACK문을 실행하게 되면 변경되었던 모든 행 정보는 취소됩니다. 이러한 과정을 처리하기 위해서는 UPDATE문이 실행될 때 마다 변경 전 데이터 (7934 KING 1000)와 변경 후 데이터(10 KING 1100)를 모두 어딘가에 저장해 두었다가 ROLLBACK문을 만나는 순간 변경 전 데이터로 변경 후 데이터를 복구해야 할 것 입니다. 이때 변경 전 데이터를 잠시 저장해 두는 공간을 롤백-세그멘트(Rollback Segment) 또는 언두 세그멘트(Undo Segment)라고 합니다. 위 그림에서 데이터 파일에 생성되어 있는 RBS01은 언두 세그멘트 입니다. 현재, 위 그림의 공유-풀 영역을 보면 사용자가 실행한 UPDATE문의 구문 분석된 결과가 저장되어 있으므로 UPDATE문이 파싱까지 수행되어 있는 것을 알 수 있습니다.

1) STEP-1/STEP-2

데이터 파일로부터 해당 테이블을 읽어 행 데이터를 데이터 버퍼 캐시영역에 저장합니다.
이때, 메모리에 저장되는 행 데이터에 대한 변경 작업을 수행하기 위해서는 만약 변경 작업 후 ROLLBACK문을 수행할 것을 대비하며 변경 전 데이터를 저장해 둘 수 있는 언두 세그멘트의 공간을 확보해 두어야 합니다. 위 그림에서 RBS01 데이터 파일과 데이터 버퍼 캐시영역에 일정한 공간을 확보합니다.

2) STEP-3

공유-풀 영역을 구성하는 데이터 딕션어리 캐시영역 (Data Dictionary Cache Area)은 사용자가 실행한 DML문의 구문분석을 수행하기 위한 테이블 정보와 락(Lock) 정보를 저장해 주는 메모리 영역입니다.
UPDATE문을 실행했을 때 어떤 행에 락(LOCK)이 발생하였는지를 기록해 두었다가 다른 사용자들이 똑 같은 행 데이터에 대해 변경작업을 수행하려고 하면 더 이상 작업을 수행하지 못하도록 DML문을 대기상태로 만들게 됩니다.

3) STEP-4

마지막 단계는 리두로그 버퍼영역에 변경 전 데이터(1 주종면 100)와 변경 후 데이터(1 주종면 110)를 모두 백업(Backup)하게 됩니다.(이렇게 모든 변경정보를 백업하는 이유는 "백업과 복구 원리"에서 자세히 소개됩니다.)

1-4 COMMIT문의 처리과정

마지막으로 DML문을 수행한 후 트랜잭션(Transaction)을 종료하기 위해 실행하는 COMMIT 문의처리 과정에 대해 자세히 알아보도록 하겠습니다.

1) STEP-1

사용자가 DML문을 실행한 후 COMMIT문을 실행하면 서버 프로세스로 전송됩니다.

2) STEP-2

서버 프로세는 로그버퍼 영역에 백업되어 있는 데이터에 대해 시스템 변경번호(System Change Number)를 부여합니다. 사용자가 COMMIT문을 수행할 때 마다 오라클 서버에 의해 연속적으로 부여되며 해당 트랜잭션이 데이터베이스 생성 후 몇 번째로 COMMIT 된 것인지를 나타냅니다. (SCN의 사용 용도에 대해서는 "백업과 복구 원리"에서 자세히 설명됩니다.)

3) STEP-3

일정한 시점이 되면 LGWR는 로그버퍼 영역에 백업되어 있는 데이터를 리두로그 파일로 저장합니다. 왜냐하면, STEP-2 단계에서 언급한대로 리두로그 버퍼는 사용자들이 실행한 DML문에 의해 발생한 모든 변경된 데이터를 저장하는 공간인데, 만약, 시스템이 갑자기 정전되면 리두로그 버퍼에 저장되어 있던 모든 데이터들이 유실되기 때문입니다. 그래서, 일정시점이 되면 LGWR 프로세는 리두로그 버퍼의 데이터들을 영구히 저장될 수 있는 운영체계 상의 리두로그 파일로 저장하게 되는 것 입니다.

다음은 LGWR가 리두로그 버퍼의 내용을 리두로그 파일로 저장하는 시점의 경우입니다.

• 사용자가 COMMIT문을 실행하는 경우
• 매 3초가 지날 때 마다 오라클 서버에 의해 실행되는 경우
• CHECKPOINT가 발생하는 경우
• 오라클 서버가 정상적으로 SHUTDOWN 되는 경우

4) STEP-4

LGWR 프로세스에 의해 리두로그 파일에 백업 데이터가 저장되고 나면 사용자의 화면에 "Commited"라는 메시지를 출력하게 됩니다.
즉, "커밋 되었습니다"라는 메시지의 의미는 변경 데이터를 테이블에 저장했다는 의미가 아니라 관련 데이터를 백업했다는 의미를 가지고 있습니다.

5) STEP-5

LGWR 프로세스가 리두로그 버퍼의 모든 데이터를 리두로그 파일에 기록하지 못한 경우에는 다음 리두로그 파일로 이동하여 계속적으로 데이터를 기록하게 됩니다.

6) STEP-6

LGWR 프로세스가 하나의 리두로그 파일에 모든 로그버퍼의 데이터를 저장하지 못하면 다음 로그파일로 이동하게 되는데 이때 CKPT 프로세스는 체크포인트(CHECKPOINT)를 발생시켜 현재 시점까지의 모든 변경 상태를 컨트롤 파일(Control File)과 데이터 파일(Data File)에 기록하게 됩니다. (모든 변경상태를 기록해 두는 이유는 "백업과 복구 원리"에서 자세히 설명됩니다.)

7) STEP-7

DBWR 프로세스는 데이터 버퍼 캐시영역에 저장되어 있는 테이블의 변경 후 데이터를 최종적으로 테이블에 기록하게 됩니다.

1-5 오라클 서버의 시작과 종료

지금까지 사용자가 실행하는 SELECT문, DML문, COMMIT문의 처리과정을 통해 오라클 서버의 각 구조가 어떤 역할을 하게 되는지 자세히 알아 보았습니다.

이번에는 오라클 서버의 시작과 종료단계에 대해 보다 구체적인 이해를 해 보도록 하겠습니다. 또한, 기본구조를 설명하면서 소개되지 않았던 다른 구조들과 시작과 종료의 기본 원리에 대해서도 함께 소개될 것 입니다.

오라클 데이터베이스를 사용하면서 발생하는 장애에 대한 원인을 쉽게 파악하고 복구 절차를 이해하기 위해서는 지금부터 소개 드리는 내용이 매우 중요합니다.

1)NOMOUNT 단계

STARTUP 명령어에 의해 오라클 서버를 시작하는 첫 번째 단계를 NOMOUNT 단계라고 합니다. 이 단계를 수행하게 되면 오라클 서버의 기본구조 중에 메모리 영역(SGA 영역)과 백그라운드 프로세스가 사용 가능한 상태로 활성화 됩니다.

결론적으로 메모리 영역이 활성화 되기 위해서는 데이터 버퍼 캐시영역과 로그버퍼 영역 그리고, 공유-풀 영역을 활성화 해야 하며 각 영역에 대한 크기를 결정해야 할 것 입니다.

각 메모리 영역의 크기는 init<SID>ora 파라메터 파일을 참조하면 다음과 같은 파라메터 값을 참조하게 됩니다.

[C:\] EDIT initORCL.ora

SHARED_POOL_SIZE		= 33000000	← 공유-풀 영역의 크기를 결정
LOG_BUGGER		= 8192	← 로그버퍼 영역의 크기를 결정
DB_CACHE_SIZE		= 33000000	← 데이터 버퍼 캐시영역의 크기를 결정

(1)	init<SID>.ora 파라메터 파일을 읽어 관련된 파라메터 값을 참조합니다.
(2)	관련 파라메터 값을 기반으로 SGA 영역을 활성화 합니다.
(3)	관련 파라메터 값을 기반으로 백그라운드 프로세스를 활성화 합니다.
(4)	BACKGROUND_DUMP_DEST 파라메터가 지정하는 경로에 있는 ALERT!_<SID>.LOG 파일에 데이터베이스의 시작과 관련된 모든 로그 정보를 기록해 둡니다.

만약, init<SID>.ora 파라메터 파일을 읽을 수 없다면 어떻게 될까요 ?

[C:\] CD C:\ORACLE\ORA92\DATABASE
[C:\] MOVE initORCL.ora initORCL.bak
[C:\] sqlplus "/as sysdba"
SQL> STARTUP
ORA- ERROR
→ NOMOUNT 단계를 수행하기 위해서 init<SID>.ora 파일을 읽어야 하지만 파라메터 파일은 이미 MOVE 된 상태이기 때문에 읽을 수가 없습니다.

2) MOUNT 단계

오라클 서버의 두 번째 시작 단계를 MOUNT 단계라고 합니다. 이 단계는 현재 데이터베이스의 모든 상태 정보를 읽어오게 됩니다.

(1)	먼저, 오라클 데이터베이스의 모든 상태정보를 저장하고 있는 컨트롤 파일을 읽습니다.

(2)	오라클 데이터베이스의 환경을 결정하는 init.ora 파라메터 파일을 읽습니다.

(3)	컨트롤 파일과 파라메터 파일에 저장되어 있는 여러가지 환경 정보(DB_NAME 명)가 일치하는지를 검증합니다. 만약, 이 단계에서 환경정보가 일치하지 않으면 MOUNT 단계는 실패하게 되고 사용자들은 데이터베이스를 사용할 수 없게 됩니다. 그러므로, init<SID>.ora 파일 내에 환경설정 정보를 임의로 변경해서는 안됩니다.

(4)	기타, 데이터 파일과 리두로그 파일의 이름과 경로, 현재 상태정보도 읽습니다.

만약, control01.ctl 컨트롤 파일을 읽을 수 없다면 어떻게 될까요 ?

[C:\] CD C:\ORACLE\ORADATA\ORA92
[C:\] MOVE control01.ctl control01.bak

[C:\] sqlplus "/as sysdba"
SQL> STARTUP

ORA- ERROR
→ MOUNT 단계를 수행하기 위해서 control01.ctl 파일을 읽어야 하지만 컨트롤 파일은 이미 MOVE 된 상태이기 때문에 읽을 수가 없습니다. NOMOUNT 단계 수행 후 MOUNT 단계를 수행하지 못해 에러가 발생합니다.

3) OPEN 단계

오라클 서버의 마지막 시작 단계를 OPEN 단계라고 합니다. 이 단계는 MOUNT 단계에서 수집한 데이터베이스의 상태정보가 정상적인지 확인하는 작업을 수행하게 됩니다.
예를 들어, 컨트롤 파일 내에는 SYSTEM 테이블스페이스가 c:\oracle\oradata\ora92\users01.dbf 파일로 존재한다는 상태 정보를 가지고 있는데, 실제 운영체계 상의 해당 경로에 해당 파일이 존재하는지를 검증하게 됩니다.
참고로, SQL*PLUS 상에서 테이블스페이스를 생성하게 되면 관련된 모든 정보가 컨트롤 파일에 저장됩니다. 이렇게 저장하는 이유는 데이터베이스의 시작단계에서 사용자가 생성한 테이블스페이스 인지를 검증하기 위해서 입니다. 컨트롤 파일에 정의되어 있는 파일 및 경로가 운영체계 상에 실제 파일 및 경로로 존재하지 않으면 데이터베이스를 더 이상 OPEN 하지 않으며 사용할 수 없게 됩니다.
만약, 모든 상태정보가 일치한다면 사용자들은 오라클 서버에 접속할 수 있게 되고 또한 테이블을 검색 및 조작할 수 있게 됩니다.

만약, system01.dbf 데이터 파일을 읽을 수 없다면 어떻게 될까요 ?

[C:\] CD C:\ORACLE\ORADATA\ORA92
[C:\] MOVE users01.dbf users01.bak
[C:\] sqlplus "/as sysdba"
SQL> STARTUP
ORA- ERROR c:\oracle\oradata\ora92\users01.dbf
→ OPEN 단계를 수행하기 위해서 컨트롤 파일에 정의되어 있는 데이터 파일의 상태정보와 실제 운영체계 상의 파일의 상태정보가 일치 하는지를 확인하게 됩니다. SYSTEM01.DBF 데이터 파일은 이미 MOVE 된 상태이기 때문에 읽을 수가 없습니다.

1-6 백업과 복구 원리

데이터베이스를 운영하다 보면 예상하지 못한 시스템 에러나 사용자의 실수 또는 갑작스런 정전 등과 같은 주변환경의 문제로 인해 더 이상 데이터베이스를 운영할 수 없는 상황에 부딪히게 됩니다. 이런 경우가 발생하더라도 데이터베이스 관리자는 사용자의 데이터를 안전하게 복구할 수 있는 준비를 평소에 해야 하는데 오라클 데이터베이스에서는 여러 가지 백업과 복구방법을 제공하고 있습니다. 자신이 운용하는 데이터베이스가 어떤 성격의 데이터를 가지고 있으며, 얼마나 많은 데이터를 가지고 있는지, 장애가 발생한 경우 데이터의 복구시간이 더 중요한지 아니면 데이터의 복구율이 더 중요한지를 잘 이해하고 분석하여 적절한 백업 방법을 준비해야 합니다.

다음은 오라클 서버구조에서 사용자들이 실행하는 DML문에 의해 변경되는 데이터들에 대한 백업과 복구 원리에 대해서 알아 보도록 하겠습니다.
오라클 데이터베이스의 구조에서 로그버퍼 영역에는 사용자가 DML문을 통해 변경한 모든 데이터가 백업되며 로그기록기(LGWR)에 의해 로그파일에 저장됩니다. 로그버퍼와 로그파일에 변경 전 데이터와 변경 후 데이터 모두를 저장하는 이유는 갑작스런 시스템 에러나 사용자 실수, 정전 등으로부터 데이터베이스가 다운되거나 시스템이 다운될 때 변경된 데이터가 미처 테이블에 저장되지 못 했을 경우를 위해 백업작업을 수행하는 것입니다. 이런 경우, 모든 변경 데이터는 리두로그 파일에 저장되어 있다가 데이터베이스가 다시 정상적으로 시작될 때(STARTUP) 리두로그 파일에 저장되어 있던 데이터를 통해서 데이터를 복구하게 됩니다.

예를 들어, 대부분의 사용자들은 한두 가지의 워드 프로세스 툴을 사용하고 계실 것 입니다. 만약, 여러분이 자신이 수행하고 있는 업무에 대해 담당 관리자에게 보고서를 제출하기 위해 열심히 워드 프로세스 작업을 수행하고 있다고 가정해 보십시오.
약 10장 정도의 보고서를 작성하고 있는데 갑자기 정전이 되었다고 생각해 보십시오. 불행하게도 지금까지 작성한 보고서를 미처 저장도 하기 전에 정전이 되었다면 그때까지 입력한 모든 문서들은 삭제가 되고 말 것입니다. 바로 이런 문제를 해결하기 위해서는 워드 프로세스 툴의 환경설정에서 사용자가 직접 문서에 대해 저장하지 않더라도 툴에서 자동 저장해 주는 기능을 설정해 둘 수 있을 것 입니다.

오라클 데이터베이스 환경은 워드 프로세스와 같이 한명의 사용자가 데이터를 저장하는 구조가 아닙니다. 무수히 많은 사용자들이 데이터를 입력, 수정, 삭제, 조회하는 아주 복잡하고 까다로운 구조를 가지고 있는 시스템 소프트웨어 입니다.
이런 환경에서 어떤 사용자는 입력을 수행하고, 또 다른 사용자는 수정과 삭제를 수행하다가 갑자기 시스템이 정전에 의해 다운된다면 그때까지 처리되고 있던 모든 데이터들은 어떻게 될까요 ?
기본적으로 정전이 되던 시점까지 사용자가 COMMIT문을 수행하였다면 그 모든 데이터들은 리두로그 파일에 백업이 되어 있으며, 만약, COMMIT문을 수행하지 못했다면 모든 데이터들은 자동 롤백(Rollback)이 되게 됩니다.
그리고, COMMIT문을 처리하는 단계에서 보았던 것처럼, 리두로그 파일에는 백업되었지만 테이블에는 저장되지 못한 데이터들은 다시 데이터베이스가 재 시작하게 될 때 SMON 백그라운드 프로세스에 의해 리두로그 파일의 백업 데이터들이 읽혀져서 복구작업이 수행되게 됩니다.

1-7 시스템 변경 번호

이번에는 오라클 데이터베이스의 파일구조와 SCN 에 대해서 좀 더 자세히 알아보겠습니다.
먼저, 모든 데이터베이스 파일에는 사용자들이 COMMIT문을 실행할 때마다 발행되는 SCN(SYSTEM CHANGE NUMBER)이 CKPT 프로세스에 의해 모든 데이터 파일과 컨트롤 파일에 저장됩니다. (앞에서 소개 드린 "COMMIT문의 처리과정"을 참조하십시오.) SCN을 파일의 헤드 영역들에 저장하는 이유는 데이터베이스에서 COMMIT된 현재 시점을 구분하기 위해서 입니다. 만약, 향후에 데이터베이스에 문제가 발생하여 복구 작업을 수행해야 할 경우 특정시간, 특정 SCN을 기준으로 복구작업을 수행해야 할 때 경우도 있습니다.

SCN은 데이터베이스가 시작될 때(STARTUP) OPEN 단계에서 데이터 파일의 무결성을 검증할 때 사용되기도 합니다. ("오라클 서버의 시작과 종료"를 참조하십시오.)
예를 들어, 위 그림에서 2001년 6월 30일 백업 당시에 SCN은 100 이었다고 합니다. 그리고, 2001년 7월 31일 백업 당시에 SCN은 101 이었다고 합니다. 2001년 8월 10일 현재 시점에 컨트롤 파일에 장애가 발생하여 마지막에 백업되었던 컨트롤 파일(2001년 7월31일)을 현재 컨트롤 파일 위치로 복사 하였다고 합니다. 이런 경우, 데이터베이스를 다시 재 시작하면 어떻게 될까요 ?
결론적으로 데이터베이스는 정상적으로 시작되지 않습니다.

왜 그럼 정상적으로 사용할 수 없는 걸까요 ? 데이터베이스의 시작단계 중 OPEN 단계는 오라클 서버의 모든 데이터 파일과 컨트롤 파일들이 같은 시점의 데이터들 인지를 검증하기 위해 시스템 변경번호(System Change Number)를 검증하게 되는데, 컨트롤 파일에는 현재 SCN이 101이라고 되어 있고, 다른 데이터 파일들은 102 라고 되어 있기 때문에 이 파일들은 같은 시점의 데이터가 아니라고 판단하여 정상적으로 시작하지 않는 것 입니다.
시스템 변경번호는 COMMIT문을 실행할 때 마다 오라클 서버에 의해 발행되며 데이터베이스의 시작과 종료단계에서 데이터의 무결성을 보장하기 위해 사용되는 중요한 상태 값 중에 하나입니다. SCN은 컨트롤 파일과 데이터 파일의 헤드영역에 항상 기록되어 있습니다.

1-8 오라클 데이터베이스의 파일 구조

자~ 이번에는 오라클 서버의 구조 중에 파일(Files)에 대해 자세히 알아 봅시다.
오라클 데이터베이스를 자동으로 설치해 주는 유니버셜 인스톨러(Universal Installer)에 의해 오라클 서버를 설치하면 위 그림과 같이 CREATE DATABASE~문에 의해 기본적인 파일 구조들이 생성됩니다.

1) 파라메터 파일	CREATE DATABASE ~ ; 문법이 실행되기 전에 생성하며, 오라클 서버의 환경설정과 관련된 정보를 저장해 두는 파일입니다. 사용자가 직접 내용을 편집할 수 있는 텍스트 파일구조 입니다.

2) 리두로그 파일	CREATE DATABASE ~ LOGFILE ~ ; 문법에 의해 생성되며 사용자들이 실행하는 DML문에 의해 발생하는 모든 변경 데이터를 백업해 줍니다.

3) 컨트롤 파일	CREATE DATABASE ~ ; 문법에 의해 생성되며 이 문법에 정의되어 있는 모든 파일들에 대한 이름과 경로,크기,상태에 대한 정보를 저장합니다.

4) 데이터 파일	CREATE DATABASE ~ ; 문법에 의해 기본적으로 SYSTEM.DBF, UNDO.DBF, TEMP.DBF 파일을 생성해 주면 사용자에 의해 추가적으로 데이터 파일이 생성 됩니다.

추천학원 http://www.itmoya.com

Posted by genesmer

[국제자격증/SQL] 오라클 9i 아카이브 모드 셋팅

오라클 / OCP 10g 2008. 3. 31. 10:22

No. 10445

ARCHIVE LOG MODE 운영 방법
==========================

Oracle에서 Online Backup을 받거나 완벽한 Recovery 작업을 수행하기
위해서는 DB를 Archive log mode로 운영하여야 한다.

텍스트로 만들어진 파라미터 화일을 사용하는 경우 (Oracle 8i 까지)
Archive log mode로 운영하기 위해서는 다음과 같이 변경하여야 한다.

1. initSID.ora file과 configSID.ora 에 다음의 parameter가 이미 setting
되어 있는지 확인한 후에 없을 경우 initSID.ora 에 setting한다.

1) LOG_ARCHIVE_START = TRUE

* ARCH process 가 기동
* log switch 발생 시 automatic archive를 수행한다.
만약 이 parametrer가 false이면 manual archive를 실시하여야 한다.

2) LOG_ARCHIVE_DEST = /home/oracle7/dbs/archive_file/arc

* archive 장소의 디렉토리와 확장자를 포함하지 않는 파일명을 지정.
* 여기에서 archive_file까지는 directory이며 마지막에 있는 arc는
archive log file의 initial 명이다.

3) LOG_ARCHIVE_FORMAT = %s.log

* archive file의 확장자와 log sequence 번호의 형식을 지정.
* 이는 (2)에서 정의된 archive log의 initial file 명과 함께 나타난다.

   [ 예 ] arc123.log, arc124.log
          (123과 124는 log sequence number 이다.)
           와 같은 형태의 화일이 생성된다.

2. 다음과 같이 작업하여 archive log mode로 변환한다.

$ svrmgrl

SVRMGR> connect internal
SVRMGR> startup mount                - (1)
SVRMGR> alter database archivelog;   - (2)
SVRMGR> archive log list             - (3)
        Database log mode                ARCHIVELOG - (4)
        Automatic archival               ENABLED    - (5)
        Archive destination           ?/dbs_ar/offline_log/offline - (6)
        Oldest online log sequence       123 - (7)
        Next log sequence to archive     125 - (8)
        Current log sequence             125 - (9)
        SVRMGR> alter database open;       - (10)


(1) DB를 startup mount까지만 한다.
(2) 이 Command를 이용하여 archivelog mode로 DB를 변경한다.
(3) Archivelog mode로 변경되었는지를 확인한다.
(4) DB가 Archivelog mode임을 나타낸다.
     만약 NOARCHIVELOG로 되어 있으면 변경되지 않은 것을 의미한다.
(5) initSID.ora file에서 LOG_ARCHIVE_START parameter를 TRUE로
     정의하였음을 나타내며 false인 경우에는 DISABLED로 나타난다.
(6) initSID.ora file의 LOG_ARCHIVE_DEST parameter에서 정의한
     archive할 장소이다.
(7) 3 개의 redo log 중 가장 오래된 redo log의 sequence가 123임을
     의미한다.
(8) 다음에 archive 받을 file의 log sequence 번호를 나타낸다.
(9) 현재 사용 중인 redo log의 sequence가 125임을 의미한다.
     만약 이전부터 archivelog mode로 운영 중이었다면 여기에서 archivelog
     file은 log sequence 124까지 archiveing되어있다는 것을 의미한다.
(10) Archive mode로 변경 후 DB를 open한다.

바이너리로 만들어진 파라미터 화일을 사용하는 경우 (Oracle 9i 부터)
Archive log mode로 운영하기 위해서는 다음과 같이 변경하여야 한다.

1. Parameter 확인

$sqlplus /nolog

SQL>connect sys/passwd@orcl as sysdba
Connected.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/dbs/
Oldest online log sequence     1
Current log sequence           3

SQL> select name, value from v$parameter
     where name = 'log_archive_start'
     or name = 'log_archive_dest'
     or name = 'log_archive_format' ;

2. 다음과 같이 작업하여 archive log mode로 변환한다.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_START = TRUE
SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area   89201304 bytes
Fixed Size                   453272 bytes
Variable Size              67108864 bytes
Database Buffers           20971520 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/dbs/archive/orcl/arc
Oldest online log sequence     1
Current log sequence           3
SQL> alter database archivelog;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/dbs/archive/orcl/arc
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
SQL> alter database open
2 ;

Database altered.

반대로, archivelog mode에서 no archivelog mode로 전환하는 방법은 다음과
같습니다. archivelog mode에서 no archivelog mode로 전환하기 전에
데이터베이스는 반드시 immediate 또는 normal 로 셧다운 되어야만 전환이
가능 합니다.

먼저, 위에서 setting 했던 initSID.ora file 와 configSID.ora 에 있는
다음 parameter 앞에 # 을 넣고 저장합니다.

#LOG_ARCHIVE_START = TRUE
#LOG_ARCHIVE_DEST = /home/oracle7/dbs/archive_file/arc
#LOG_ARCHIVE_FORMAT = %s.log

$ svrmgrl
SVRMGR> connect internal;
SVRMGR> shutdown immediate
SVRMGR> startup mount
ORACLE instance started.
Database mounted.
SVRMGR> alter database noarchivelog;
Statement processed.
SVRMGR> alter database open;
Statement processed.

바이너리로 만들어진 파라미터 화일을 사용하는 경우 (Oracle 9i 부터)
Noarchive log mode로 운영하기 위해서는 다음과 같이 변경하여야 한다.

1. Parameter 확인

$sqlplus /nolog

SQL>connect sys/passwd@orcl as sysdba
Connected.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/dbs/archive/orcl/arc
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

SQL> select name, value from v$parameter
     where name = 'log_archive_start'
     or name = 'log_archive_dest'
     or name = 'log_archive_format' ;

2. 다음과 같이 작업하여 Noarchive log mode로 변환한다.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_START = FALSE
SCOPE=SPFILE;
System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Database altered.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/dbs/archive/orcl/arc
Oldest online log sequence     1
Current log sequence           3
SQL> alter database open
2 ;

Database altered.

아카이브 데스티네이션 경로 변경
-- db archive mode 로 변경시키는 방법
-- archive mode : redo log file 을 백업을 받는 방식

SQL> archive log list
데이터베이스 로그 모드              아카이브 모드가 아님
자동 아카이브             사용 안함
아카이브 대상            C:\oracle\ora92\RDBMS
가장 오래된 온라인 로그 순서     48
현재 로그 순서           51

SQL> alter system set log_archive_start = true scope=spfile;
시스템이 변경되었습니다.

-- os directory 설정
-- oracle 에선 파일은 자동으로 만들어주는데 디렉토리는 무조건
-- 사용자가 수동으로 만들어줘야 한다.

SQL> alter system set log_archive_dest_1 ='location=c:\arch1';

시스템이 변경되었습니다.

SQL> alter system set log_archive_dest_2 = 'location=c:\arch2';

시스템이 변경되었습니다.

-- log_archive_dest 확인
SQL> show parameter log_archive_dest

-- archive file 형식 지정
SQL> alter system set log_archive_format ='%t_%s.arc' scope=spfile;

-- %s 는 로그 시퀀스 번호

시스템이 변경되었습니다.

SQL> shutdown immediate

SQL> startup mount
-- control file 을 읽는다.

ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 101784276 bytes
Fixed Size                   453332 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
데이터베이스가 마운트되었습니다.

-- control file 의 속성을 바꾼다.
-- archivelog mode 로 변경

SQL> alter database archivelog;

데이타베이스가 변경되었습니다.

SQL> archive log list
데이터베이스 로그 모드              아카이브 모드
자동 아카이브             사용
아카이브 대상            c:\arch2
가장 오래된 온라인 로그 순서     48
아카이브할 다음 로그   51
현재 로그 순서           51

SQL> alter database open;

데이타베이스가 변경되었습니다.

SQL> alter system switch logfile;

시스템이 변경되었습니다.

-- 그러면 c:\arch1, c:\arch2 밑에 로그파일이 생성된다.

추천학원 http://www.itmoya.com

Posted by genesmer

[국제자격증/데이터베이스] PL/SQL이란

오라클 / OCP 10g 2008. 3. 31. 10:22

PL/SQL 이란 ?

- PL/SQL 은 Oracle's Procedural Language extension to SQL. 의 약자 입니다.

- SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며,
오라클 자체에 내장되어 있는 Procedure Language입니다

- DECLARE문을 이용하여 정의되며, 선언문의 사용은 선택 사항입니다.

- PL/SQL 문은 블록 구조로 되어 있고 PL/SQL 자신이 컴파일 엔진을 가지고 있습니다.

PL/SQL의 장점

- PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB 로 보내서 처리하므로
수행속도를 향상 시킬수 있습니다.

- PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다.

- 보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있습니다.

- Variable, Constant, Cursor, Exception을 정의하고, SQL문장과 Procedural 문장에서
사용합니다. .

- 단순, 복잡한 데이터형태의 변수를 선언합니다.

- 테이블의 데이터 구조와 DataBase의 컬럼럼에 준하여 동적으로 변수를 선언 할 수 있습니다.

- Exception 처리 루틴을 이용하여 Oracle Server Error를 처리합니다.

- 사용자 정의 에러를 선언하고 Exception 처리 루틴으로 처리 가능 합니다.

추천학원 http://www.itmoya.com

Posted by genesmer

[오라클자격증/OCP] PL/SQL의 구조

오라클 / OCP 10g 2008. 3. 31. 10:22

PL/SQL Block Structure

- PL/SQL은 프로그램을 논리적인 블록으로 나누는 구조화된 블록 언어 입니다.

- PL/SQL 블록은 선언부(선택적), 실행부(필수적),예외 처리부(선택적)로 구성되어 있고,
BEGIN과 END 키워드는 반드시 기술해 주어야 합니다.

- PL/SQL 블록에서 사용하는 변수는 블록에 대해 논리적으로 선언할 수 있고 사용할 수 있습니다.

● DECLARE
    - Optional
    - Variables, cursors, user-defined exceptions

● BEGIN
    - Mandatory
    - SQL Statements
    - PL/SQL Statements

● EXCEPTION
    - Actions to perform when errors occur

● END;
    - Mandatory

  ◈ Declarative Section(선언부)

   - 변수, 상수, CURSOR, USER_DEFINE Exception선언

  ◈ Executable Section(실행부)

   - SQL, 반복분, 조건문실행
   - 실행부는 BEGIN으로 시작하고 END 로 끝납니다.
   -  실행문은 프로그램 내용이 들어가는 부분으로서 필수적으로 사용되어야 합니다.

  ◈ Exception Handling Section(예외처리)

    - 예외에 대한 처리.
    - 일반적으로 오류를 정의하고 처리하는 부분으로 선택 사항입니다.

PL/SQL 프로그램의 작성 요령

  - PL/SQL 블록내에서는 한 문장이 종료할 때마다 세미콜론(;)을 사용합니다. .

  - END뒤에 ;을 사용하여 하나의 블록이 끝났다는 것을 명시 합니다.

  - PL/SQL 블록의 작성은 편집기를 통해 파일로 작성할 수도 있고,
    SQL프롬프트에서 바로 작성할 수도 있습니다.

  - SLQ*PLUS환경에서는 DELCLARE나 BEGIN이라는 키워드로 PL/SQL블럭이 시작하는 것을
    알 수 있습니다.

- 단일행 주석 : --

- 여러행 주석 : /* */

- PL/SQL 블록은 행에 / 가있으면 종결 됩니다.

추천학원 http://www.itmoya.com

Posted by genesmer

[OCP자격증] UTL_SMTP PACKAGE을 사용하여 E-MAIL을 GENERATE하는 과정

오라클 / OCP 10g 2008. 3. 31. 10:21

UTL_SMTP PACKAGE을 사용하여 E-MAIL을 GENERATE하는 과정

======================================================

PURPOSE

---------

UTL_SMTP package는 Oracle8i Release 2 (8.1.6)이상에서 UTL_TCP package을 이용하여 e-mail 을 PL/SQL에서 generate하는 것이 가능하게 하였다.

Explanation

-----------

UTL_SMTP packag을 사용하기 위해서는 Database에 Java option이 install되어 있어야 하며 TCPConnection class인 $ORACLE_HOME/plsql/jlib/plsql.jar file이 load되어 있어야 한다. 그리고 JAVA_POOL_SIZE는 40M이상을 권장한다.

UTL_SMTP package을 사용하여 E-mail을 generate하는 과정을 살펴보자.

1. SMTP server을 이용하여 connection을 맺는다. (보통 25번 port을 사용한다)

이것은 UTL_SMTP.OPEN_CONNECTION() function call을 통해 가능하다.

Ex) utl_smtp.open_connection(mailhost,25)

2. SMTP server와 UTL_SMTP.HELO() call을 통해 초기화을 한다.

Ex) utl_smtp.helo(mail_conn,mailhost);

3. UTL_SMTP.MAIL()을 이용하여 'From' mail ID 를 지정한다.

Ex) utl_smtp.mail(mail_conn,sender);

4. UTL_SMTP.RCPT()을 이용하여 'To' mail ID 를 지정한다.

Ex) utl_smtp.rcpt(mail_conn,recipient);

5. Body의 message을 정렬시에는 <CR><LF>에 의해 line을 나눌 수 있다.

Ex) CHR(13)||CHR(10)

6. UTL_SMTP.DATA() 응 이용하여 UTL_SMTP buffer로 message을 pass한다.

Ex) utl_smtp.data(mail_conn,message);

7. UTL_SMTP.QUIT()을 call함으로써 SMTP connection을 끝내게 된다.

Ex) utl_smtp.quit(mail_conn);

좀더 자세한 sample은 bulletin : 12130에서 보기로 하고 여기선 간단히 E-mail을 보내는 sample을 보여주기로 한다.

Example 
-------- 

      CREATE OR REPLACE PROCEDURE send_email 
      ( sender    IN VARCHAR2, 
        recipient IN VARCHAR2, 
        message   IN VARCHAR2) 
      AS 
       
        mailhost VARCHAR2(100) := 'gmapacsmtp.oraclecorp.com'; 
        mail_conn utl_smtp.connection; 

      BEGIN 

         mail_conn :=utl_smtp.open_connection(mailhost,100);  
         utl_smtp.helo(mail_conn,mailhost);  
         utl_smtp.mail(mail_conn,sender);  
         utl_smtp.rcpt(mail_conn,recipient);  
         utl_smtp.data(mail_conn,message);  
         utl_smtp.quit(mail_conn); 

     END; 
/

SQL> exec send_email('Oracle','TTT@oracle.com','This sample is education purpose only');

 Sample2) 한글 data을 mail로 보낼때는 bulletin : 12130을 자세히 보면  
          알 수 있듯이 Sample1의 utl_smtp.data(mail_conn,message);  
          부분을 바꾸어 주어야 한다. 

      CREATE OR REPLACE PROCEDURE send_email 
      ( sender    IN VARCHAR2, 
        recipient IN VARCHAR2, 
        message   IN VARCHAR2) 
      AS 
       
        mailhost VARCHAR2(100) := 'gmapacsmtp.oraclecorp.com'; 
        mail_conn utl_smtp.connection; 

      BEGIN 

         mail_conn :=utl_smtp.open_connection(mailhost,100);  
         utl_smtp.helo(mail_conn,mailhost);  
         utl_smtp.mail(mail_conn,sender);  
         utl_smtp.rcpt(mail_conn,recipient);  
--         utl_smtp.data(mail_conn,message);  

         utl_smtp.open_data(mail_conn); 
         utl_smtp.write_raw_data(mail_conn, UTL_RAW.CAST_TO_RAW(message)); 
         utl_smtp.close_data(mail_conn); 

         utl_smtp.quit(mail_conn); 

     END; 
/