Brand new JayK

Character set 이 맞지 않아 한글이 깨져 ?로 표시 된다 당혹감을 감출 수 없음..

Character Set은 각 나라는 영의 ASCII영역 뒤에 추가적인 바이트를 추가하는 방식으로 사용 한다고 한다

주로 한글은 2Byte, UTF8은 3Byte를 가진다고 한다

그러므로, 같은 ASCII문자로 구성하더라도 다른 값을 가르키거나 없기 때문에 ?가 나오는 것이다.

먼저 NLS_CHARACTERSET 을 확인 해보았다

select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

이제 sysdba로 들어가 아래와 같이수행해준다.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET KO16MSWIN949;

alter database character set ko16mswin949
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

==> 잘 수행하다 이런 에러가 뜬다.

      찾아보니 새로운 캐릭터 셋은 반드시 기존 캐릭터 셋이여야만 한다고 한다.

      무슨 말인고 하니 아래 표를 참조하면 알 수 있다.

      (자세한 내용 : http://cafe.naver.com/mudong.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=548)

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE KO16MSWIN949;

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> SELECT * FROM nls_database_parameters where parameter = 'NLS_CHARACTERSET';

다시 조회 해보자

?????????????????????????????????????????????????????????????????????

한글로 됐다..............

캐릭터셋이 변경되면서 유실됐나보다 다시 데이터를 넣고 조회해보니

깔끔하게 나왔다!

SUB QUERY(서브쿼리)

■ 단일행 서브쿼리

--단일행 SUB QUERY 연습문제1

--STUDENT 테이블과 DEPARTMENT 테이블을 사용하여 이윤나 학생과 1전공(DEPTNO1)이 동일한 학생들의 이름과

--1전공 이름을 출력하세요

SELECT S.NAME, D.DNAME

FROM STUDENT S, DEPARTMENT D

WHERE S.DEPTNO1 = (SELECT DEPTNO1

FROM STUDENT

WHERE NAME = '이윤나')

AND S.DEPTNO1 = D.DEPTNO;

--단일행 SUB QUERY 연습문제2

--PROFESSOR 테이블에서 입사일이 송도권 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요

SELECT P.NAME, P.HIREDATE, D.DNAME

FROM PROFESSOR P, DEPARTMENT D

WHERE HIREDATE > (SELECT HIREDATE

FROM PROFESSOR

WHERE NAME = '송도권')

AND P.DEPTNO = D.DEPTNO;

--단일행 SUB QUERY 연습문제3

--STUDENT 테이블에서 1전공(DEPTNO1)이 101번 인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를

--출력하세요.

SELECT NAME,WEIGHT

FROM STUDENT

WHERE WEIGHT > (SELECT AVG(WEIGHT)

FROM STUDENT

WHERE DEPTNO1 = 101);

--단일행 SUB QUERY 연습문제4

--PROFESSOR 테이블에서 심슨 교수와 같은 입사일에 입사한 교수 중에서 조인형 교수보다 월급을 적게 받는 교수의

--이름과 급여, 입사일을 출력하세요

SELECT NAME,PAY,HIREDATE

FROM PROFESSOR

WHERE PAY < (SELECT PAY

FROM PROFESSOR

WHERE NAME = '조인형')

AND HIREDATE = (SELECT HIREDATE

FROM PROFESSOR

WHERE NAME = '심슨');

■  다중 행 SUB QUERY 

SUB QUERY의 결과과 2건 이상 출력되는 것을 말한다. 다중 행 SUB QUERY는 SUB QUERY의 결과가 여러건 출력되기 때문에 단일 행 연산자를 사용할 수 없다. 그래서 다중 행 SUB QUERY의 경우 아래와 같이 별도의 연산자가 존재함

SAL > ANY (100,200,300) 으로 되면 ANY 자리에 최소값인 100이 반환

SAL > 100이라는 식으로 표현

SAL < ALL (100,200,300) 이 되면 SUB QUERY의 최소 값인 100이 반환되긴 하지만 식은

SAL < 100이 되 버림

--EMP 테이블의 부서 별로 가장 급여를 많이 받는 사원의 사원번호 , 이름, 급여 , 부서번호를 조회하여라

SELECT EMPNO,ENAME,SAL,DEPTNO

FROM EMP

WHERE SAL IN (SELECT MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

--다중 행 SUB QUERY

--EMP2 테이블과 DEPT2 테이블을 참조하여 근무지역(DEPT2 테이블의 AREA 컬럼)이 서울 지사인 모든 사원들의

--사번과 이름, 부서번호를 출력

SELECT EMPNO, NAME, DEPTNO

FROM EMP2

WHERE DEPTNO IN (SELECT DCODE

FROM DEPT2

WHERE AREA = '서울지사');

-- 업무가 SALESMAN 인 최소 한 명 이상의 사원보다 급여를 많이 받는 사원의 이름, 업무를 조회하여라

SELECT ENAME, JOB, SAL

FROM EMP

WHERE SAL > ANY( SELECT SAL

FROM EMP

WHERE JOB = 'SALESMAN')

AND JOB !='SALESMAN';

-- 다중 행 SUB QUERY 예 2

-- EMP2 테이블을 사용하여 전체 직원 중 과장 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급,

-- 연봉을 출력하세요. 단 연봉 출력 형식은 아래와 같이 천 단위 구분기호와 원 표시를 하세요

SELECT NAME, POSITION, PAY

FROM EMP2

WHERE 1=1

AND POSITION != '과장'

AND PAY > ANY(SELECT PAY

FROM EMP2

WHERE POSITION = '과장');

-- 다중 행 SUB QUERY 예 3

-- STUDENT 테이블을 조회하여 전체 학생 중에서 체중이 4학년 학생들의 체중이 4학년 학생들의 체중에서 가장 적게

-- 나가는 학생보다 몸무게가 적은 학생의 이름과 학년과 몸무게를 출력하세요.

SELECT NAME, GRADE, WEIGHT

FROM STUDENT

WHERE WEIGHT < ALL(SELECT WEIGHT

FROM STUDENT

WHERE GRADE =4)

■  다중 컬럼 SUB QUERY

서브쿼리로 출력되는 컬럼수가 2개 이상 출력되는 서브 쿼리

SELECT EMPNO, ENAME, SAL, DEPTNO

FROM EMP

WHERE (DEPTNO,SAL) IN (SELECT DEPTNO, MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

-- 다중 컬럼 SUB QUERY 1

-- STUDENT 테이블을 조회하여 각 학년별로 최대키를 가진 학생들의 학년과 이름과 키를 출력하세요

SELECT GRADE,NAME,HEIGHT

FROM STUDENT

WHERE (GRADE,HEIGHT) IN (SELECT GRADE,MAX(HEIGHT)

FROM STUDENT

GROUP BY GRADE)

ORDER BY 1;

-- 다중 컬럼sub query 연습문제 1:

-- PROFESSOR 테이블을 조회하여 각 학과 별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력하세요

-- (학과이름순으로 오름차순 정렬)

SELECT P.NAME, D.DNAME

FROM PROFESSOR P , DEPARTMENT D

WHERE (P.DEPTNO,P.HIREDATE) IN ( SELECT DEPTNO,MIN(HIREDATE)

FROM PROFESSOR

GROUP BY DEPTNO )

AND P.DEPTNO = D.DEPTNO

ORDER BY D.DNAME ;

-- 다중 컬럼 SUB QUERY 연습문제 2

-- EMP2 테이블을 조회하여 직급별로 해당 직급에서 최대 연봉을 받는 직원의 이름이 직급, 연봉을 출력하세요

-- 연봉순으로 오름차순 정렬하세요

SELECT NAME, PAY

FROM EMP2

WHERE (POSITION,PAY) IN ( SELECT POSITION,MAX(PAY)

FROM EMP2

GROUP BY POSITION)

ORDER BY PAY;

-- 다중 컬럼 SUB QUERY 연습문제 3 -- 다중행 컬럼 서브쿼리

-- EMP2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그 중에서 평균 연봉이 가장 적은 부서의

-- 평균 연봉보다 적게 받는 직원들의 부서명, 직원명, 연봉을 출력하세요.

-- ( 단, 연봉이 적은 순으로 나열하세요)

SELECT D.DNAME,E.NAME,E.PAY

FROM EMP2 E, DEPT2 D

WHERE PAY < ALL (SELECT AVG(PAY)

FROM EMP2

GROUP BY DEPTNO)

AND D.DCODE = E.DEPTNO

ORDER BY PAY;

--STUDENT 테이블을 이용하여 사용자 아이디가 BINGO인 학생과 같은 학년인 학생의

--학번, 이름, 학년을 출력하여라

SELECT STUDNO, NAME, GRADE

FROM STUDENT

WHERE GRADE = (SELECT GRADE

FROM STUDENT

WHERE ID = 'bingo');

--STUDENT 테이블을 이용하여 101번 학과 학생들의 평균 몸무게보다 적은 학생의 이름, 학과번호,몸무게를 출력하여라.

SELECT NAME, DEPTNO1, WEIGHT

FROM STUDENT

WHERE WEIGHT < ( SELECT AVG(WEIGHT)

FROM STUDENT

WHERE DEPTNO1 = 101 );

--STUDENT,DEPARTMENT 테이블을 이용하여 정보(PART:100)에 소속된 모든 학생의 학번, 이름, 학과번호를 출력하여라

SELECT STUDNO, NAME, DEPTNO1

FROM STUDENT

WHERE DEPTNO1 IN (SELECT DEPTNO

FROM DEPARTMENT

WHERE PART = 100);

--STUDENT 테이블을 이용하여 모든 학생 중에서 4학년 학생 중 키가 제일 작은 학생보다 키가 큰 학생의

--학번, 이름, 키를 출력하여라

SELECT STUDNO, NAME, HEIGHT

FROM STUDENT

WHERE HEIGHT > ANY( SELECT HEIGHT

FROM STUDENT

WHERE GRADE = 4);

-- STUDENT 테이블을 이용하여 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하여라

SELECT NAME, GRADE, WEIGHT

FROM STUDENT

WHERE (GRADE,WEIGHT) IN (SELECT GRADE,MIN(WEIGHT)

FROM STUDENT

GROUP BY GRADE);

■  상호 연관 SUB QUERY 

- 메인쿼리절과 서브쿼리간에 검색 결과를 교환하는 서브쿼리

- 메인쿼리와 서브쿼리간의 결과를 교환하기 위하여 서브쿼리의 WHERE 조건절에서 메인쿼리의 테이블과 연결

*주의*

   행을 비교할 때마다 결과를 메인으로 반환하는 관계로 처리 성능이 저하될 수 있음

--상호 연관 SUB QUERY

-- EMP2 테이블을 조회해서 직원 들 중에서 자신의 직급의 평균연봉과 같거나 많이 받는 사람들의

--이름과 직급, 현재 연봉을 출력하세요

SELECT NAME, POSITION, PAY

FROM EMP2 A

WHERE PAY >= (SELECT AVG(PAY)

FROM EMP2 B

GROUP BY A.POSITION = B.POSITION);

■ 스칼라서브쿼리 : SELECT절에서 함수처럼 사용되는 쿼리문

- 하나의 레코드만 리턴이 가능( 반환값은 한 개) 두 개이상의 레코드는 리턴 X

- 일치하는 데이터가 없더라고 NULL 값을 리턴 할 수 있다.

SELECT D.DEPTNO, (SELECT MIN(EMPNO) FROM EMP WHERE DEPTNO = D.DEPTNO)

FROM DEPT D

ORDER BY D.DEPTNO;

■ 인라인뷰(상호연관 SUB QUERY 보다 성능이 좋음)

- FROM 절에서 임시 공간에 테이블을 생성하여 사용하는 뷰와 비슷한 저장 형태

- FROM 절에 있는 서브쿼리가 인라인 뷰를 생성

- 효율적인 검색 가능

- FROM 절에 있는 서브쿼리에는 자주 별칭 사용

--STUDENT 테이블을 이용하여 각 학년 별로 평균키보다 큰 학생들의 이름, 학년, 키를 출력하여라

SELECT NAME, A.GRADE, HEIGHT

FROM STUDENT A, (SELECT GRADE,AVG(HEIGHT) AS "B_HEIGHT"

FROM STUDENT

GROUP BY GRADE) B

WHERE A.HEIGHT > B_HEIGHT

AND A.GRADE = B.GRADE

ORDER BY GRADE,HEIGHT;

JOIN

■ Cartesian Product(카티션 곱)

Join 쿼리 중에 WHERE 절에 기술하는 Join 조건이 잘못 기술 되었거나 아예 없을 경우 발생

ex) select e.first_name, d.department_name

        from employees e, departments d;

* employees : 107  , departments : 27 개의 row가 있다면 (107x27)개의 결과를 출력하게 됨

■ EQUI Join

양쪽 테이블에 같은 조건의 값이 존재할 경우 해당 데이터를 가져오는 JOIN 방법

Equal 연산자(=)를 사용해서 EQUI Join 이라고 함

select e.first_name , d.department_name
from employees e, departments d
where e.department_id = d.department_id
  and e.department_id = 110;

* 두 테이블을  JOIN 하여 dpartment_id가 110 인 row만 출력하게 함

SQL 함수 종류

■ SQL 함수의 기능

 - 기본적인 query문을 더욱 강력하게 해준다.

 - 데이터의 계산 수행

 - 개별 데이터의 항목 수정

 - 표시할 날짜 및 숫자 형식 지정

 - 일 데이터 유형 변환

■ SQL 함수 종류

단일 행 함수 : 행 당 하나의 결과 반환

다중 행 함수 : 행 그룹을 조작해 행 그룹당 하나의 결과를 반환

단일 행 함수

데이터 조작(1:1매칭)을 위해 사용

인수를 사용하여 값을 반환

인수:상수,변수,컬럼,표현식

행 당 하나의 결과를 반환

SELECT, WHERE , ORDER BY 절에 사용할 수 있다

데이터 타입을 변경 할 수 있다

중복 사용 가능

종류 - 문자함수 : 문자를 입력하면 문자 또는 숫자를 변환

* LOWER(char) 모든 문자 소문자 변환 / UPPER(char) 모든 문자를 대문자로 변환

* INITCAP(char) 단어 첫문자 대문자 나머지 소문자

* LENGTH(char) 문자의 길이를 표시

* CONCAT(char1,char2) 문자열을 결합 (||)와 같은 기능이지만 두 개의 문자만 결합가능

** SUBSTR(char,m[,n]) 문자열 중 지정한 위치(m)에서 지정한 길이(n)만큼 문자 추출(n생략시 끝까지 추출,m이 음수면 뒤부터 추출

** INSTR(char1,char2[,m][,n]) 문자열 중 지정한 위치(m)에서 특정문자(char2)가 특정 순서(n)로 나타나는 문자열 위치

--문제1)EMP 테이블에서 SCOTT의 사원번호, 성명,담당업무(소문자로),부서번호를 출력 (단,WHERE절에서 LOWER사용)

SELECT EMPNO,ENAME,lower(JOB) as JOB,DEPTNO

FROM EMP

WHERE lower(ename) ='scott';

--문제2)EMP 테이블에서 SCOTT의 사원번호, 성명,담당업무(대문자로),부서번호를 출력 (단,WHERE절에서 UPPER사용)

SELECT EMPNO,ENAME,upper(JOB),DEPTNO

FROM EMP

WHERE UPPER(ename) = 'SCOTT';

--문제3)DEPT 테이블에서 부서이름(DNAME)을 첫 글자만 대문자로 변환하여 출력하여라.

SELECT INITCAP(DNAME)

FROM DEPT;

--문제4)EMP 테이블에서 이름의 첫 글자가 'K'보다 큰 사원의 사원번호, 이름, 업무를 출력하여라.

SELECT EMPNO, ENAME, JOB

FROM EMP

WHERE SUBSTR(ENAME,1,1) > 'K';

--문제5)EMP 테이블에서 이름이 6자리 이상인 사원의 이름과 업무를 출력하여라.

SELECT EMPNO, ENAME, JOB

FROM EMP

WHERE LENGTH(ENAME) >= 6;

--문제6)EMP 테이블에서 이름 중'L'자의 위치를 출력하여라.

SELECT ENAME,instr(ENAME,'L')

FROM EMP;

SELECT ENAME , INSTR(ENAME,'L') AS E_NULL, INSTR(ENAME,'L',1,1) AS E_11

FROM EMP

ORDER BY ENAME;

* LPAD(char1,n[,char2]) : char1 왼쪽에 char2를 끼워 넣어 전체 문자가 n개가 되도록 만든다.

* RAPD(char1,n[,char2]) : char1 오른쪽에 char2를 끼워 넣어 전체 문자가 n개가 되도록 만든다.

--문제1) 이름에 *를 왼쪽에 채워 모두 동일한 15개의 이름으로 변환하고 업무와 급여를 출력한다.

SELECT LPAD(ENAME,15,'*'),JOB,SAL

FROM EMP;

--문제2) 급여를 모두 동일하게 만단위로 보여지게 변환하고 이름과 부서번호를 함께 출력한다.

SELECT LPAD(SAL,5,'0'),ENAME,DEPTNO

FROM EMP;

* LTRIM(char1[,char2]) : char1의 왼쪽에서 char2를 제거 한다. (char2 생략 시 공백 제거)

* RTRIM(char1[,char2]) : char1의 오른쪽에서 char2를 제거 한다. (char2 생략 시 공백 제거)

* TRIM :　char1에서 앞뒤로 특정문자를 제거 한다.( 제거문자 생략시 공백 제거,return 타입은 varchar2)

TRIM(

[LEADING | TRAILING | BOTH]

[제거문자]

[FROM]

제거대상

)

* TRANSLATE(char,str1,str2) : char에 있는 str1 표현을 str2로 각각 모두 변환한다.

* REPLACE(char1,str1,str2) : 문자열 그대로를 변환

--문제1) EMP 테이블에서 성명을 소문자로 바꾸어 출력하여라. (단, TRANSLATE 함수를 사용 하여라)

SELECT TRANSLATE(ENAME,'ABCDEFGHIJKLMNOPQRSTUWXYZ','abcdefghijklmnopqrstuwxyz') AS RESULT

FROM EMP;

SELECT TRANSLATE(ENAME,UPPER(ENAME),LOWER(ENAME)) AS RESULT

FROM EMP;

--문제2) EMP 테이블에서 JOB에 'A'를 '$'로 바꾸어 출력하여라.

SELECT DISTINCT TRANSLATE(JOB,'A','$')

FROM EMP;

SELECT DISTINCT REPLACE(JOB,'A','$')

FROM EMP;

■ 숫자함수 : 숫자를 입력하면 숫자 값을 변환 n m

* ROUND(n[.m]) : n값을 m자리에서 반올림 (1234.5678) = (-4,-3,-2,-1,0,1,2,3)

m > 0 이면 소수점 아래 m-1 자리에서 반올림

생략 시 정수 이하 반올림(소수점 첫 자리 반올림)

m < 0 이면 m번째 자리에서 반올림

* TRUNC(n[,m]) : n 값을 m자리에서 버림

* MOD(n,m) : n값을 m값으로 나눠 나온 나머지

* ABS(n) : n의 절대값을 계산하여 반환

* FLOOR(n) : FLOOR함수는 주어진 값보다 작지만 같은 최대 정수를 구하는 함수

* CEIL(n) : 주어진 값보다는 크지만 가장 근접하는 최소값을 구하는 함수

* SIGN(n) : 숫자가 양수이면 1, 음수이면 -1, 0이면 0을 반환

날짜함수 : 날짜 유형을 변환

* MONTHS_BETWEEN(d1,d2) : d1,d2 두 날짜 간의 개월 수 차이 값 반환

* ADD_MONTHS(d1,n) : d1날짜에 n개월 더한 값 반환

* NEXT_DAY(d1,n) : d1 다음의 지정한 요일에 해당하는 날짜 반환 (일요일1,월요일2,..토요일7)

* LAST_DAY(d1) : 해당 월의 마지막 날짜 반환

* ROUND(d1,char) : 날짜를 년, 월 단위로 반올림

* TRUNC(d1,char) : 날짜를 년,월 단위로 버림 (char = "month" , "year")

* EXTRACT(day | month | year from d1) : 특정 날짜에서 일,월,년을 각각 뽑아내는 함수 select hiredate,

extract(day from hiredate) as days,

extract(month from hiredate) as months,

extract(year from hiredate) as years

from emp;

-- 문제4) EMP 테이블에서 입사한 달의 근무 일수를 계산하여 출력하여라. 단, 토요일과 일요일도 근무 일수에 포함한다.

SELECT ENAME AS "사원명" ,HIREDATE AS "입사날짜",

EXTRACT(DAY FROM LAST_DAY(HIREDATE))-EXTRACT(DAY FROM HIREDATE) AS "입사 달 근무일수"

FROM EMP;

-- 문제5) EMP 테이블에서 10번 부서원의 입사 일자로부터 돌아오는 금요일을 계산하여 출력하여라.

SELECT ENAME AS "사원명", HIREDATE AS "입사날짜",

NEXT_DAY(HIREDATE,6) AS "돌아오는 금요일"

FROM EMP

WHERE DEPTNO = 10;

--문제6 ) 봉급과 커미션(COMM) 합계가 봉급의 10% 인상된 급여보다 많은 사원의 이름,급여 및 커미션을 표시하도록 한다.

SELECT ENAME, SAL, COMM

FROM EMP

WHERE COMM+SAL > SAL*1.1

AND COMM IS NOT NULL;

--문제7 ) 커미션을 받지 않는 사원의 급여를 10% 인상하여 표시하되, 다음의 형식으로 출력하여라

-- :The salary of SMITH after a 10% raise is 2000

SELECT ENAME 사원명,sal 원래봉급, 'The salary of ' ||ENAME||' after a 10% raise is '||(sal+sal*0.1) AS "COMMENT"

FROM EMP

WHERE COMM IS NOT NULL;

-- 문제8 ) EMP 테이블에서 현재까지 근무일수가 몇 주, 몇 일인가를 출력하여라.

-- 단, 근무일수가 많은 사람 순으로 출력하여라. TO_NUMBER(CHAR) 문자를 숫자로 바꿔줌`

select ename as "사원명",

hiredate as "입사날짜",

trunc((sysdate-hiredate)) as "근무일수" ,

trunc(trunc((sysdate-hiredate))/7) as "근무 주"

from emp

order by hiredate asc;

-- 문제9) EMP 테이블에서 부서번호 10번의 현재까지의 근무 월수를 계산하여 출력하여라.

SELECT ENAME AS "사원",ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)) AS "근무 월수"

FROM EMP

WHERE DEPTNO=10;

[이외문제 풀어보세요]

--문제1)EMP 테이블에서 scott의 사원번호, 성명,담당업무(소문자로),부서번호를 출력 (단,WHERE절에서 LOWER사용)

--문제2)EMP 테이블에서 scott의 사원번호, 성명,담당업무(대문자로),부서번호를 출력 (단,WHERE절에서 UPPER사용)

--문제3)DEPT 테이블에서 부서이름(DNAME)을 첫 글자만 대문자로 변환하여 출력하여라.

--문제4)EMP 테이블에서 이름의 첫 글자가 'K'보다 큰 사원의 사원번호, 이름, 업무를 출력하여라.

--문제5)EMP 테이블에서 이름이 6자리 이상인 사원의 이름과 업무를 출력하여라.

--문제6)EMP 테이블에서 이름 중'L'자의 위치를 출력하여라.

--문제7) 이름에 *를 왼쪽에 채워 모두 동일한 15개의 이름으로 변환하고 업무와 급여를 출력한다.

--문제8) 급여를 모두 동일하게 만단위로 보여지게 변환하고 이름과 부서번호를 함께 출력한다.

--문제9) EMP 테이블에서 성명을 소문자로 바꾸어 출력하여라. (단, TRANSLATE 함수를 사용 하여라)

--문제10) EMP 테이블에서 JOB에 'A'를 '$'로 바꾸어 출력하여라.

--문제11) 봉급과 커미션(COMM) 합계가 봉급의 10% 인상된 급여보다 많은 사원의 이름,급여 및 커미션을 표시하도록 한다.

--문제12) 커미션을 받지 않는 사원의 급여를 10% 인상하여 표시하되, 다음의 형식으로 출력하여라

-- :The salary of SMITH after a 10% raise is 2000

-- 문제 13) EMP 테이블에서 현재까지 근무일수가 몇 주, 몇 일인가를 출력하여라.

-- 단, 근무일수가 많은 사람 순으로 출력하여라. TO_NUMBER(CHAR) 문자를 숫자로 바꿔줌

-- 문제 14) EMP 테이블에서 10번 부서원의 현재까지의 근무 월수를 계산하여 출력하여라.

-- 문제 15) EMP 테이블에서 입사한 달의 근무 일수를 계산하여 출력하여라. 단, 토요일과 일요일도 근무 일수에 포함한다.

-- 문제 16) EMP 테이블에서 10번 부서원의 입사 일자로부터 돌아오는 금요일을 계산하여 출력하여라.

변환함수 : 데이터 유형 변환

* TO_CHAR : 숫자, 날짜 타입의 DATA를 VARCHAR2타입의 문자형식으로 변환

형식:TO_CHAR(char1,'type')

type에 문자나 특수문자를 넣고 싶을 경우 따옴표 사용

type의 경우 입력된 대소문자대로 출력

select to_char('34000','00000') from dual;

* TO_NUMBER : 문자 String을 number 타입으로 변환

select '1234', to_number('1234','9999') from dual;

* TO_DATE : 문자 String을 날짜 타입으로 변환

select to_date('20150925','YYYY-MM-DD') from dual;

@ 숫자코드

9: 일반적인 숫자형식을 나타내며, 자리 수를 다 채우진 않음

0: 자리 수가 비면 0으로 채워준다

$: $표시

S: 양수이면 + , 음수이면 -

@ 날짜코드

MM : 월수, MON : 월 이름을 3자리 문자로 표현 ex)JAN,FEB,MAR 등, MONTH : 월 이름( ex: JANUARY,FEBLARY,MARCH 등)

DD : 날짜, D : 그 주의 몇 번째 날짜인지(일요일:0,,,토요일:7), DY: 요일 이름을 3자리 문자로 표현(SUN,MON,TUE)

DAY : 요일이름

YYYY : 년도, YY : 년도 마지막 2자리 HH : HH12처럼 12시간제 HH12 : 12시간제 HH24 : 24시간제

MI : 분, SS : 초, SSSS : 초 두 번 출력, W : 그 날의 주 수, WW : 그 해의 주 수, Q : 분기

Language 변경 옵션-common-enviroment (american_america.ko16mswin949)

regedit 편집

-- HKEY_LOCALMACHINEWSUFTWAREWORACLE의 NLS_LANG 변경

■ 기타함수

* COALESCE(exp|,....expn) : exp1,....expn 중 null이 아닌 첫 번째 값 출력

모든 데이터타입에 적용 가능

전환되는 값의 데이터 타입을 일치시켜야 한다

case문으로 변경가능

select sal, comm, coalesce(comm,0)

from emp;

select sal, comm,

case

when (comm is not null) then comm*1.1

else 0+10

end

from emp;

-- 문제1) EMP 테이블에서 이름, 보너스, 연봉, 보너스가 null이 아닌 경우 보너스를, 보너스가 null인 경우에는 연봉을

-- 모두 null이면 50으로 표시한다.

select sal, comm, coalesce(comm,sal,50)

from emp;

select sal, comm,

case

when (comm is not null) then comm

when (sal is not null) then sal

else 50

end as result

from emp;

* DECODE : if-then-else 문의 역할 수행

데이터들을 다른 값으로 바꿔준다

형식 : decode(value,if1,then1,if2,then2,...else1)

value값이 if1일 경우에 then1값으로 바꾸어 주고, value 값이 if2일 경우엔 then2값 나머지는 else로 바꿔줌

select sal, comm, coalesce(comm,0) // 두개의 인자 형태가 같아야함

from emp;

select sal, comm, decode(comm,null,' ',comm) as result // 문자로 표현하고 싶을 때 디코더 사용

from emp;

--문제1) EMP 테이블에서 부서번호가 10이면 'A'로, 20이면 'B'로, 30이면 'C'로 바꾼 뒤

-- 이름, 업무, 부서번호와 함께 출력

SELECT ENAME,JOB,DEPTNO,

DECODE(DEPTNO,10,'A',20,'B','C')

FROM EMP;

--문제2) EMP 테이블에서 JOB이 ANALYST이면 급여증가는 10%이고, CLERK이면 15%, MANAGER이면 20%이고,

-- 다른 업무에 대해서는 급여 증가가 없다. 사원번호,이름,업무,급여,증가된 급여를 출력

SELECT EMPNO,ENAME,JOB,SAL,

DECODE(JOB,'ANALYST',SAL*1.1,'CLERK',SAL*1.15,SAL*1.2)

FROM EMP;

*NVL(col,exp1) : col이 null일 경우 exp1로 변환한다

select sal, comm, nvl(comm,0) as result

from emp;

*NVL2(col,exp1,exp2) : col 이 null이면 exp2를 null이 아니면 exp1을 리턴한다.

col 과 exp1 이 달라도 됨

select sal,comm, nvl2(comm,comm,100) as result

from EMP

order by comm nulls first;

*NULLIF(exp1,exp2) : exp1과 exp2값을 비교해 같으면 null 값을, 다르면 exp1 값을 리턴한다

select ename, job, deptno, nullif(deptno,10) as result

from EMP

order by deptno;

--문제1) EMP 테이블의 사원이름, 매니저번호(MGR)를 출력하고, 매니저번호가 NULL이면 '상위관리자'로 표시하고

-- 매니저번호가 있으면 '매니저번호담당'으로 표시하여라

SELECT ENAME,MGR,NVL2(MGR,MGR||'담당','상위관리자')

FROM EMP;

--문제2) EMP 테이블의 사원이름, 업무를 출력하고, 업무가 'CLERK'인 경우 NULL로 나오도록 한다

SELECT ENAME, JOB, NULLIF(JOB,'CLERK')

FROM EMP;

--문제1) decode 함수를 이용하여 급여가 1000보다 작으면 'A', 1000이상 2500이하 이면 'B', 2500이상이면 'C'로 표시하라

select sal ,sign(sal-1000), sign(sal-2500),

decode(sign(sal-1000),-1,'A',sign(sal-2500),'C','B')

from EMP;

--문제2) 문제 1을 case함수를 이용하여 변경하여라

SELECT SAL,

CASE

WHEN(SAL<1000) THEN 'A'

WHEN(SAL>=1000 AND SAL<=2500) THEN 'B'

ELSE 'C'

END

FROM EMP;

test_data --> 29개 테이블

student, professor, DEPARTMENT , emp2 , dept2 주로 쓸 것임.

*테이블 생성 혹은 입력시 에러 나는 경우(한글깨짐)

nls_lang 설정

1. regedit 설정

2. orange 환경변수 설정

american_america.ko16mswin949

-- ** 연습 문제 1 **

--Student 테이블을 사용하는 학생들의 이름과 주민번호, 성별을 출력하되 성별은 주민번호(Jumin)컬럼을 이용하여

--7번째 숫자가 1일 경우 "남자", 2일 경우 "여자"로 출력하세요

SELECT NAME AS "이름",JUMIN AS "주민번호", DECODE(SUBSTR(JUMIN,7,1),1,'남자',2,'여자') AS "성별"

FROM STUDENT;

-- ** 연습 문제 2 **

--Student 테이블에서 학생의 이름과 전화번호와 지역명을 출력하세요. 지역번호가 02는 서울, 031은 경기,

--051은 부산, 052는 울산, 053은 대구, 055,는 경남으로 출력하세요

SELECT NAME AS "학생이름" , TEL AS "전화번호",

DECODE(SUBSTR(TEL,1,INSTR(TEL,')')-1),

'02','서울','031','경기','051','부산','052','울산','053','대구','055','경남') AS "지역번호"

FROM STUDENT;

-- ** 연습 문제 3 **

--Student 테이블의 Jumin 컬럼을 참조하여 학생들의 이름과 태어난 달, 그리고 분기를 출력하세요.

--태어난 달이 01-03월 은 1/4분기, 04-06월 은 2/4분기, 07-09월은 3/4분기 10-12월은 4/4분기로 출력하세요

SELECT NAME AS "학생이름", TRIM(LEADING '0' FROM SUBSTR(JUMIN,3,2)) ||'월' AS "출생 월",

DECODE(-1,SIGN(9-SUBSTR(JUMIN,3,2)),'4분기',SIGN(6-SUBSTR(JUMIN,3,2)),'3분기',

SIGN(3-SUBSTR(JUMIN,3,2)),'2분기',SIGN(0-SUBSTR(JUMIN,3,2)),'1분기')

FROM STUDENT;

SELECT DECODE(TO_CHAR(TO_DATE(SUBSTR(JUMIN,3,2),'MM'),'Q'), 1,'1/4 분기', 2,'2/4 분기',3,'3/4 분기',4,'4/4 분기') FROM STUDENT;

-- ** CASE 연습 문제 **

-- 교수테이블 (PROFESSOR) 를 조회하여 교수의 급여액수(보너스포함)를 기준으로 200 미만은 4급

-- 201-300 까지는 3급, 301 - 400 까지는 2급, 401 이상은 1급으로 표시하여 교수의 번호,이름,급여,등급을 출력하시오

-- (단 BONUS가 NULL인 경우는 0으로 처리)

SELECT NAME AS "교수명", PROFNO AS "교수번호",PAY+NVL(BONUS,0) AS "급여",

CASE

WHEN(PAY+NVL(BONUS,0) < 200) THEN '4급'

WHEN(PAY+NVL(BONUS,0) >= 201 AND PAY+NVL(BONUS,0)<=300) THEN '3급'

WHEN(PAY+NVL(BONUS,0) >= 301 AND PAY+NVL(BONUS,0)<=400) THEN '2급'

ELSE '1급'

END AS "등급"

FROM PROFESSOR;

■ 그룹 함수

* COUNT : 조건을 만족하는 모든 행의 수 ( 모든 데이터 타입 가능 ) ( NULL 값은 안읽음 ) (*) 시 가능

* SUM : 조건을 만족하는 모든 행의 합계

* AVG : 조건을 만족하는 모든 행의 평균

* MAX : 조건을 만족하는 모든 행의 최댓값 ( 모든 데이터 타입 가능 )

* MIN : 조건을 만족하는 모든 행의 최솟값 ( 모든 타입 가능 )

* STDDEV : 조건을 만족하는

그룹 함수 구문

SELECT 그룹함수([DISTINCT]{ COLLUMN,.,,} -5

FROM 테이블 명 -1

[WHERE 조건] -2

[GROUBP BY COLUMN1,COLUMN2...] -3

[HAVING 그룹조건] -4

[ORDER BY COLUMN1,COLUMN2....,,] -6

* GROUP BY 절을 사용하여 테이블의 행을 더 작은 그룹으로 나 눈다.

* SELECT 목록의 열 중 그룹 함수에 없는 열은 모두 GROUP BY절에 포함 되어야한다.

* GROUP BY 열을 SELECT 절에 포함시키지 않아도 된다.

* 하나 이상의 GROUP BY 열을 나열하여 그룹에 대한 요약 결과를 조회할 수 있다.

--문제1) EMP 테이블에서 각 부서별로 사원수, 최대연봉, 최소연봉, 평균연봉을 구하고 급여평균으로 정렬 하여라.

SELECT DEPTNO,COUNT(*) AS "사원수" ,MAX(SAL) AS "최대연봉",MIN(SAL) AS "최소연봉", TRUNC(AVG(SAL)) AS "평균연봉"

FROM EMP

GROUP BY DEPTNO

ORDER BY 평균연봉;

--문제2) EMP 테이블에서 각 업무별로 사원수, 급여평균을 구하고 업무별로 정렬 하여라.

SELECT JOB, COUNT(JOB) AS "사원수",TRUNC(AVG(SAL)) AS "급여평균"

FROM EMP

GROUP BY JOB;

ORDER BY JOB;

--문제3) EMP 테이블에서 업무별,부서별로 그룹하여 각각의 인원수와 급여평균을 구하고 급여평균이 많은 순으로 정렬 하여라.

SELECT JOB,DEPTNO,COUNT(*),AVG(SAL)

FROM EMP

GROUP BY JOB,DEPTNO

ORDER BY AVG(SAL) DESC;

■ HAVING 절

WHERE 사용하여 그룹을 제한할 수 없다

HAVING 절을 사용하여 그룹을 제한한다

그룹이 형성되고 그룹함수가 계산된 후 HAVING절이 적용된다

HAVING 절이 GROUP BY 절 앞에 올 수는 있지만 뒤에 쓸 것을 권장한다

SELECT절 보다 먼저 수행되므로 ALIAS를 사용할 수 없다

--문제1)EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하여라

SELECT DEPTNO AS "부서번호“,COUNT(*) AS "인원수”,SUM(SAL) AS “급여의 합”

FROM EMP

GROUP BY DEPTNO

HAVING COUNT(*) > 4;

--문제2)EMP 테이블에서 업무별 급여의 평균이 3000이상인 업무에 대해서 업무명, 평균 급여, 급여의 합을 구하여라

SELECT JOB,ROUND(AVG(SAL)),SUM(SAL)

FROM EMP

GROUP BY JOB

HAVING AVG(SAL)>=3000;

SQL 문장들의 종류

DML

1. SELECT : 입력한 자료들을 조회해보는 SQL 문

/* employees 에 어떠한 컬럼이 있는지 조회*/

desc employees;

DataPump

● DataPump 특징

1. 수행 속도가 빠르다

- parallel 4 이상해야 수행 속도가 export보다 빠르다

2. 일시 중단 후 다시 이어서 작업을 할 수 있다

3. 작업 시간을 예상할 수 있다

4. OS 파일에 직접 접근할 수 없어 보안이 우수하다

● expdp 실행 모드

(1) Full 모드

- Full 이란 파라미터를 사용

- 데이터베이스 전체를 export 받을 수 있다

- DBA 권한을 가지고 있거나 export_full_database권한을 가지고 있어야 수행할 수 있다

(2) Schema 모드

- Schemas 파라미터를 사용

- 특정 스키마 전체를 export 받을 수 있다

(3) Tablespace 모드

- Tablespaces 파라미터를 사용

- 해당 테이블스페이스에 속한 모든 테이블을 받을 수 있다

- 만약 transport_tablespace 파라미터를 사용한다면 테이블 스페이스 정보가 다르더라도 다른 DB로 동일하게 impdp할 수 있음

* block 크기가 맞아야함

- 단 transport_tablespace를 사용하려면 양쪽 데이터베이스의 OS, block size와 characterset이 같아야 하는 단점이 있다

- 별도 옵션 사용안할시 유저가 없으면 생성(테이블오너) 해줘야함

(4) Table 모드

- Tables 파라미터를 사용

- 여러 개의 테이블을 export 받으려면 콤마로 구분

● expdp 파라미터 정리

1) directory : 디렉토리 오브젝트를 지정해서 덤프파일 및 로그 파일의 위치를 지정

directory=directory_object_name형식으로 지정하여 사용

2) dumpfile : 파일 시스템에 저장될 덤프파일의 이름을 지정해주는 파라미터

Dumpfile=dumpfile_%U와 같은 형식

%U는 파일이 여러 개로 나누어질 때 사용

01-99까지 자동증가

사용자가 덤프파일을 지정하지 않으면 expdat.dmp라는 파일으로 자동으로 지정

3) logfile / nologfile : 작업내용을 저장할 로그파일명 지정

Logfile = logfile 형식으로 로그 파일명 지정

파라미터를 지정하지 않으면 expdp를 저장하는 곳에 export.log라는 파일명으로 생성

로그파일을 남기고 싶지 않으면 Nologfile=Y을 사용

4) compression : export 파일을 압축하여 저장

ex) expdp scott/tiger dumpfile = scott00.dmp directory = datapump compression=all

* 이름 겹칠 시 : 이름포맷 바꾸기, 기존파일 삭제, reuse_dumpfiles 옵션 사용, mv

5) network_link : 원격지에 있는 데이터베이스에 접근하여 데이터를 expdp 작업을 할 경우에 사용

DB Link 기능을 이용하여 작동

원격지 DB로 DB Link가 생성되어 있어야 가능

ex) $?~~scott10.dmp network_link=emp@db_link_name

6) parallel : parallel 작업 수행 시 프로세스 개수

ex) $~~scott10.dmp …….. parallel=4

7) reuse_dumpfiles : 저장하고자 하는 덤프파일명이 있을 경우 해당 파일을 덮어쓰도록 하는 옵션

reuse_dumpfiles=Y 설정 시 덮어쓰고 설정 안 하면 N가 기본

● impdp 관련 파라미터

1) Table_exists_action : 테이블이 이미 있을 경우 action

skip : 같은 테이블을 만나면 건너뜀

append : 기존 내용에 데이터를 추가

truncate : 기존 테이블을 truncate 하고 새로 impdp

drop : 기존 테이블을 drop 하고 새로 만들어서 impdp

2) Remap_schema : 다른 유저로 impdp 할 경우 사용

remap_schema=scott:hr

export/import에서 from scott to hr 와 같음

3) Remap_datafile : 기존 서버의 데이터파일 정보를 이전 후 서버의 데이터 파일로 매핑

ex) remap_datafile='/data1/users01.dbf':'/data2/users01.dbf','/data1/test.dbf':'/data2/test.dbf'

4) Remap_tablespace : 테이블 스페이스 정보가 다를경우 명시

ex) remap_tablespace='users':'example'

5) Remap_table : 기존 테이블 이름을 impdp 하면서 변경할 경우 사용

ex) remap_table=emp:emp100

6) Network_link : 원격지에 impdp

7) Partition_options : 파티션되어 있던 테이블을 단일 테이블로 impdp할 경우

ex) partition_options=merge

8) Data_options=skip_constrait_errors : 제약조건 위배 시 에러 무시하고 진행

------------------------------------------------------------------

--------------------------------------------------------------------------

--step1. 테스트용 계정과 테스트용 테이블을 생성

create user tuser identified by abc123

default tablespace users

temporary tablespace temp2;

select systimestamp from dual; --2015/12/16 22:44:52 +09:00

grant connect, resource to tuser;

create table tuser.test1 (no number);

create table tuser.test2 (no number);

insert into tuser.test1 values(1);

insert into tuser.test1 values(2);

insert into tuser.test2 values(3);

insert into tuser.test2 values(4);

commit;

select * from dba_temp_files;

--step2. 테스트용 계정 tuser를 삭제한 후 데이터를 확인

drop user tuser cascade;

select * from tuser.test2;

--ERROR at line 1:

--ORA-00942 : table or view does not exist

--step3. Flashback 명령어로 복구

flashback database to timestamp(to_timestamp('2015/12/16 22:45:00','YYYY/MM/DD HH24:MI:SS'));

--ERROR at line 1:

--ORA-38757 : Database must be mounted and not open to FLASHBACK

SYS> shutdown immediate

SYS> startup mount;

SYS> flashback database to timestamp(to_timestamp('2015/12/16 22:45:00','YYYY/MM/DD HH24:MI:SS'));

SYS> alter database open;

--alter database open

--ERROR at line 1:

--ORA-01589 : must use RESETLOGS or NORESETLOGS option for database open

SYS> alter database open resetlogs;

select * from tuser.test1;

-------------------------------------------------------------------------------

create public database LINK my_orcl

CONNECT TO system IDENTIFIED BY "imsi00"

using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)

(HOST=172.16.1.52)(PORT=1521))

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL2)))';

select * from dba_db_links;

select instance_name,host_name from v$instance@my_orcl;

--datapump 사전 준비

SQL > !mkdir -p /oracle11/datapump;

create or replace directory datapump as '/oracle11/datapump';

grant read,write on directory datapump to scott;

select * from dba_directories;

--empdp 수행(특정테이블)

SQL > !expdp system/imsi00 tables=flashback_test1 directory=datapump dumpfile=flashback_test1.dmp;

SQL > !expdp scott/imsi00 schemas=scott directory=datapump dumpfile=scott;

SQL > !expdp systemp/imsi00 full=y directory=datapump dumpfile=full01.dmp;

SQL > !expdp system/imsi00 full=y directory=datapump parallel=4 dumpfile=full1%U.dat logfile=full1.log

-- top -c

select * from v$parameter

where name like '%pool%';

alter system set shared_pool_size=50m;

alter system set sga_target=400m scope=spfile;

alter system set sga_max_size=512m scope=spfile;

test1) expdp 받은 dump파일을 이용한 impdp

-- step1) table 삭제

drop table flashback_test1;

select * from flashback_test1;

-- step2) impdp 수행

SQL > !impdp system/imsi00 directory=datapump dumpfile=flashback_test1.dmp nologfile=y;

select * from flashback_test1;

test2) db_link를 통한 impdp 수행

--step1) db링크 걸기 위에서했음

--step2) test table 생성(ORCL)

create table scott.impdp_test1(a number);

insert into scott.impdp_test1 values(1);

insert into scott.impdp_test1 values(2);

commit;

select * from scott.impdp_test1;

--step3) impdp 수행(TESTDB)

SQL > !impdp scott/imsi00 tables=scott.impdp_test1 network_link=my_orcl nologfile=y;

-- network_link에 설정된 DB에서 expdp 받고 local에 impdp 수행

-- network_link를 이용하면 굳이 export를 수행할 필요없이 import가능

◆ impdp 관련 파라미터

1) Table_exists_action : 테이블이 이미 있을 경우 action

skip : 같은 테이블을 만나면 건너뜀

append : 기존 내용에 데이터를 추가

truncate : 기존 테이블을 truncate 하고 새로 impdp

drop : 기존 테이블을 drop 하고 새로 만들어서 impdp

2) Remap_schema : 다른 유저로 impdp 할 경우 사용

remap_schema=scott:hr

export/import에서 from scott to hr 와 같음

3) Remap_datafile : 기존 서버의 데이터파일 정보를 이전 후 서버의 데이터 파일로 매핑

   ex) remap_datafile='/data1/users01.dbf':'/data2/users01.dbf','/data1/test.dbf':'/data2/test.dbf'

4) Remap_tablespace : 테이블 스페이스 정보가 다를경우 명시

   ex) remap_tablespace='users':'example'

5) Remap_table : 기존 테이블 이름을 impdp 하면서 변경할 경우 사용

   ex) remap_table=emp:emp100

6) Network_link : 원격지에 impdp

7) Partition_options : 파티션되어 있던 테이블을 단일 테이블로 impdp할 경우

   ex) partition_options=merge

8) Data_options=skip_constrait_errors : 제약조건 위배 시 에러 무시하고 진행

Flashback

1. 논리적 오류를 복구 할 수 있다. (물리적 오류는 복구 불가)

2. Row Level / Table Level / Database Level 로 나뉜다.

3. 별도의 백업 파일 복원이 없기 때문에 전통적 복구에 비해 아주 빠르다.

4. 휴지통(recyclebin) / Undo data / Flashback log 등의 기술을 사용한다.

5. restore 없이 복구하므로 빠름. (물리적인 오류 복구는 restore[dbf+archive file])

◆ Row Level Flashback

- Flashback Query : 특정 시점의 변경 내역만 알 수 있는 쿼리

- Flashback Version Query : 해당 데이터의 과거 변경이력을 전부 찾아주는 쿼리

- Flashback Transaction Query : 변경 사항을 취소시켜 이전 값으로 돌려주는 쿼리

 반드시 supplemental_logging을 min level 이상으로 설정해야 함

 supplemental_logging을 min level 이상으로 설정하면 Undo와 Redo log, archive log 사용량 증가(상세히 기록)

● Row Level Flashback

- 특정 테이블의 특정 Row만 Flashback 해주는 기능

- commit된 데이터만 복구 가능

- Undo data의 정보를 이용하여 복구

- Undo segment가 재사용된 경우 ORA-01555 snapshot too old 에러 발생

- 한 두건은 Row level을 쓰지만 많은 데이터 복구는 테이블 단위로 함

----------------------------------row level-------------------------------------

-- 실습 test table 및 data 만들기

create table flashback_test1

( name varchar2(10),

addr varchar2(10),

tel varchar2(10)

);

insert into flashback_test1 values('홍은혜','용산','111');

insert into flashback_test1 values('김예진','제천','222');

insert into flashback_test1 values('양순정','부산','333');

commit;

select * from flashback_test1;

update flashback_test1 set name = '홍길동' where tel = '111';

commit;

update flashback_test1 set name = '김범수' where tel = '333';

commit;

select * from flashback_test1;

-- 변경 이력을 찾는 Flashback Version Query 수행

select versions_startscn st_scn,

versions_endscn endscn,

versions_xid txid,

versions_operation opt,

name

from flashback_test1 versions between scn minvalue and maxvalue

where tel = '111';

-- 동시에 여러 번의 수정이 일어났을 경우 변경 history 찾는 방법

update flashback_test1 set name = '전준민' where tel = '111';

commit;

update flashback_test1 set name = '홍민택' where tel = '111';

commit;

-- 변경 이력을 찾는 Flashback Version Query 수행

select versions_startscn st_scn,

versions_endscn endscn,

versions_xid txid,

versions_operation opt,

name

from flashback_test1 versions between scn minvalue and maxvalue

where tel = '111';

alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

-- 변경된 DATA별로 변경 시점 조회

select scn_to_timestamp(4260670) from dual;

-- 변경 전 data로 직접 UPDATE(row level flashback)

update flashback_test1 set name = '홍길동' where tel = '111';

commit;

-- 변경 이력을 찾아 복구하는 Flashback Transaction Query 수행

select undo_sql

from flashback_transaction_query

where table_name = 'FLASHBACK_TEST1'

and commit_scn between 4260443 and 4268760

order by start_timestamp desc;

=================> supplemental_logging min을 안해서 안나옴

-- min level supplemental logging 조회 및 변경

select supplemental_log_data_min from v$database;

alter database add supplemental log data; --> MIN을 YES로 변경

select supplemental_log_data_min from v$database;

-- 데이터 변경

update flashback_test1 set name = '조우성' where tel = '111';

commit;

update flashback_test1 set name = '조규현' where tel = '111';

commit;

update flashback_test1 set name = '김일주' where tel = '111';

commit;

-- 변경 이력을 찾아 복구하는 Flashback Transaction Query 수행

select undo_sql

from flashback_transaction_query

where table_name = 'FLASHBACK_TEST1'

and commit_scn between 4268746 and 4278716

order by start_timestamp desc;

=================> logging 이후 변경된 데이터만 조회됨 (dml 쿼리)

update "SYSTEM"."FLASHBACK_TEST1" set "NAME" = '조우성' where ROWID = 'AAASTtAABAAAeI5AAA';

update "SYSTEM"."FLASHBACK_TEST1" set "NAME" = '조규현' where ROWID = 'AAASTtAABAAAeI5AAA';

update "SYSTEM"."FLASHBACK_TEST1" set "NAME" = '홍민택' where ROWID = 'AAASTtAABAAAeI5AAA';

◆ Table Level Flashback

- 특정 테이블 전체 내용을 장애 발생 전 상태로 flashback하는 방법

- Row level과는 달리 테이블 전체의 내용이 변경된다.

- 복구 방법

 DML 에러에 대한 복구(undo data 이용)

 특정 테이블이 Drop 되었을 때 사용하는 방법 (recyclebin 이용)

-----------------------★Table Level Flashback★------------------

select * from flashback_test1;

select systimestamp from dual; -- 2015/12/15 22:40:10 +09:00

delete from flashback_test1;

select * from flashback_test1;

commit;

-- 복구 방법 1 ( 확인하고 select insert 하거나 임시 테이블 만들어서 제공 )

create table flashback_test1_imsi

as

select *

from flashback_test1

as of timestamp to_timestamp('20151215 2240','yyyymmdd hh24mi');

-- 복구 방법 2 --기존데이터가 뭐든 간에 바로 복구(=데이터유실)(그래서 잘 안씀)

1) flashback table flashback_test1

to timestamp(systimestamp - interval '30' minute);

--ERROR at line 1:

--ORA-08189 : cannot flashback the table because row movement is not enabled

--flashback table 사용 시 row movement enable설정해야 가능

--바로 복구를 원할 경우, 시간이 명확하고 중간 트랜잭션이 없을 경우 더욱 빠름

alter table flashback_test1 enable row movement;

2) flashback table flashback_test1

to timestamp(to_timestamp('20151215 2240','yyyymmdd hh24mi'));

select * from flashback_test1;

--시간을 너무 이전 시점으로 설정할 경우 에러 발생

flashback table flashback_test1

to timestamp(systimestamp - interval '0' second);

--ERROR at line 1:

--ORA-01466: unable to read data - table definition has changed

-------------drop 테이블 recyclebin에서 복구(purge로 날리면 recyclebin으로 저장 안되므로 복구 불가)------------------------

--삭제하고 누군가 동일 테이블을 만들어 동일 테이블이 있을 때 복구가 안됨

select * from scott.emp;

drop table scott.emp;

select *

from dba_recyclebin

where original_name like 'EMP%';

flashback table scott."BIN$JvGK2YFr+9LgUAB/AQAMsg==$0" to before drop;

--drop된 테이블이 여러 개 일수도 있어서 original_name사용안함 scott.emp(object_name 사용 권고)

select * from scott.emp;

--동일한 이름의 table이 있는 경우

create table scott.dept2

as select * from scott.dept

where 1=2;

drop table scott.DEPT;

alter table scott.dept2 rename to dept;

select * from scott.dept;

select * from dba_recyclebin

where original_name like 'DEPT%';

--이미 동일 테이블명이 존재해서 ORA-38312 : original name is used by an existing object

flashback table scott."BIN$JvzOjsrODFjgUAB/AQAP2Q==$0"

to before drop;

--rename 옵션 이용하여 복구

flashback table SCOTT."BIN$JvzOjsrODFjgUAB/AQAP2Q==$0"

to before drop rename to dept_imsi;

select * from scott.dept_imsi;

insert into scott.dept

select * from scott.dept_imsi;

commit;

select * from dba_tables

where table_name like '%DEPT%';

◆ Database Level Flashback

- 전통적인 복구 방식 : 장애 발생 시 백업된 데이터 파일을 복원해서 Redo log와 Archive log를 적용시켜 복구

- Flashback database 복구 방식 : 장애가 발생한 데이터 파일에 Flashback log로 복구

- 물리적인 백업 파일을 복원할 필요가 없으므로 빠른 복구 가능

- Redo log와 Archive log를 사용하기도 함

●Flashback database 복구 방식

●Database Level Flashback 사용 준비

- db_recovery_file_dest 설정 필요

: flashback log 쌓이는 장소

- db_recovery_file_dest_size 설정 필요

: flashback log 쌓이는 최대 size(default : 4G)

* 해당 공간이 full이면 DB Hang

- db_flashback_retention_target 분 단위 설정 필요(default : 1440분/24시간)

* 2일 전으로는 못 돌림, size도 키워야 함

- archive log mode

- flashback database mode

----------------------------------------

-- 아카이브 모드 확인

SQL> archive log list;

-- flashback 모드 확인

select flashback_on from v$database;

-- 관련 파라미터 확인

select *

from v$parameter

where name in ('db_recovery_file_dest', -- flashback log 위치

'db_recovery_file_dest_size', -- flashback log 위치 크기

'db_flashback_retention_target', -- 분(설정시간까지만)

'log_archive_dest_1' ,-- 아카이브 로그 위치

'log_archive_format');

-- 아카이브 모드 변경

SQL> startup mount;

SQL> alter database archivelog; <-> alter database noarchivelog;

SQL> archive log list;

SQL> alter database flashback on; <-> alter database flashback off;

SQL> alter database open;

-- flashback log 확인

SQL> alter system switch logfile; -- 물리적으로 로그 발생 확인

select * from v$flash_recovery_area_usage;

-- 아카이브 위치 변경

alter system set log_archive_dest_1='location=/oracle11/arch';

--flashback log와 archive log 위치를 분리 --> full 차는 거 예방

--------------------------------------recyclebin---------------------------

purge recyclebin; -- 리사이클빈 지우기

purge dba_recyclebin; -- sys 계정에서 수행

select * from recyclebin;

select * from dba_recyclebin;

drop table PCT_TEST2; -- scott 계정 수행

drop table job_test02; -- system 계정 수행

--********system tablespace의 테이블을 drop 하면 recyclebin에 들어가지 않음*******

purge table scott."";

purge table scott.SAL_HISTORY;

--recyclebin 끄기

alter session set recyclebin=off; -- 세션 별

create table honghong3

(a number)

tablespace users;

drop table honghong3;

select * from dba_recyclebin;

--system tablespace의 table은 drop해도 recyclebin에 가지 않음

--------------------------------------------------------------------------

select * from flashback_test1;

select systimestamp from dual;--2015/12/16 21:22:28 +09:00

truncate table flashback_test1;

--set1)

alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

flashaback table FLASHBACK_TEST1

to timestamp('2015/12/16 21:21:00');

--set2)

flashback table flashback_test1

to timestamp(to_timestamp('2015/12/16 21:21:00','YYYY/MM/DD HH24:MI:SS'));

--ORA-01466 : unable to read data - table definition has changed

--DDL발생(truncate) 구조적 변경 됐으므로 위에 에러 발생

select * from scott.flashback_test1;

--flashback database 복구

--mount 단계에서 수행, DBA권한 필요

SQL > shutdown immediate;

SQL > startup mount;

SQL> flashback database to timestamp (to_timestamp('2015/12/16 21:21:00','YYYY/MM/DD HH24:MI:SS'));

SQL > alter database open;

--ERROR at line 1:

--ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

--과거로 돌렸기 때문에 SCN이 맞지 않아서 에러가 남

SQL > alter database open resetlogs; --> scn 정보를 최상으로 맞춤

select * from flashback_test1;

DBMS_JOB

●DBMS_JOB 패키지

- SNP 백그라운드 프로세스에 의해 등록된 JOB이 설정한 INTERVAL 간격으로 수행됨

- 초 단위 수행가능이 장점 but, 초단위 작업이 별로 없어서 crontab 이용

- job_queue_processes와 job_queue_interval 이 설정되어 있어야 동작 가능

 job_queue_processes : SNP 프로세스 개수

 _job_queue_interval : SNP 프로세스가 sleep 상태에서 깨어나는 간격을 초로 지정

●DBMS_JOB 패키지 구성

- submit : 새로운 작업을 job queue 목록에 등록

- remove : job queue에 등록된 job 제거

- change : job queue에 등록된 job 변경

- next_date : job queue에 등록된 job의 작동 시간 변경

- interval : job queue에 등록된 job의 수행 주기 변경

- what : 수행할 procedure or package 변경

- run : 등록되어 있는 특정 job을 수동으로 수행

●job 관리하기

- 기본 문법

- job : job 번호로 다른 프로시저에서 호출될 수도 있다

- what : 수행할 pl/sql or procedure or package 이름을 지정

이곳에 직접 수행하기를 원하는 sql문장을 써도됨

- next_date : 다음에 수행될 시간을 지정

- interval : 수행되는 주기를 지정하며 초 단위까지 지정 가능

- no_parse : Parse 여부를 지정. (기본 값 false로 parse 수행)

- -job 프로그램 등록

create sequence seq_job_seq1; 

create table job_test01 (no number, name varchar2(5));

create or replace procedure insert_job_test01

is begin

insert

into job_test01

values(seq_job_seq1.nextval,

dbms_random.string('a', 3));

end;

/

-- job 프로그램 등록

!vi job1.sql

begin dbms_job.submit(:jno,

'insert_job_test01;',

sysdate,

'sysdate + 1/24/60', -- 1분에 한 번 수행

false);

end;

/

SQL > variable jno number;

SQL > @job1.sql

SQL > print jno; -- random으로 할당되는 job번호 확인

SQL > commit;

-- 이 시간부터 job이 수행되며 commit 되지 않으면 수행되지 않음

-- rac 환경이라면 아래와 같이 수행

exec dbms_job.instance(job=>25, instance=>1);

-- 현재 수행되고 있는 job 확인

select *

from dba_jobs

where 1=1

and what='insert_job_test01;' -- 이름에 ; 들어간 거 주의!

-- and job=3

;

-- 테이블 확인

select * from job_test01;

-- job 수정

exec dbms_job.change(

3,

'insert_job_test01;',

sysdate,

'sysdate + 5/24/60'

);

commit;

-- job 삭제

exec dbms_job.remove(3);

commit;

 DBMS_SCHEDULER

- 오라클에서 생성된 프로시저나 함수 외에 OS에서 생성된 각종 프로그램 실행 가능

- 데이터베이스 내부 이벤트까지 추적가능하기 때문에 OS나 dbms_job 보다 훨씬 다양한 작업을 체크하고 수행할 수 있음

●DBMS_SCHEDULER 등록

양식

begin

dbms_scheduler.create_job(

job_name=>'',

job_type=>'',

job_action=>'',

start_date => ,

repeat_interval =>'');

end;

/

* 사용편이성 때문에 UNIX Crontab 많이 이용함!

user Management

◆ Schema란?

- 특정 사용자가 생성한 object의 집합을 의미

- user가 생성될 때 관련 schema가 생성

- user와 schema를 혼용해서 사용하기도 함

Schema Object ( Table, Indexes, Views, Triggers, Constraints, Database links, Synonyms, Stored Procedures, Sequences )

* tablespace는 미리 생성 해놓아야 함!!!!!

◆ User 생성하기

1. 사용자를 생성하는 목적을 정리

2. 해당 사용자의 default tablespace를 결정하고 해당 테이블 스페이스를 먼저 생성

-- User 생성 실습

-- SMS 서비스용 user 생성

-- 계정명 : smsuser / passwd: smspwd

-- default tablespace : TS_SMS

-- temporary tablespace : temp_sms

-- quota : ts_sms : unlimited / system : 0m

--1. 테이블 스페이스 생성

CREATE TABLESPACE TS_SMS

datafile '/home/oracle/oradata/testdb/ts_sms.dbf' size 10M;

--2. temp 테이블 스페이스 생성

CREATE TEMPORARY TABLESPACE temp_sms

tempfile '/home/oracle/oradata/testdb/temp_sms.dbf' size 10M;

--3. user 생성

CREATE USER smsuser IDENTIFIED BY smspwd

DEFAULT TABLESPACE TS_SMS

TEMPORARY TABLESPACE temp_sms

quota unlimited on TS_SMS

quota 0m on system;

--선언된 link 확인

select * from dba_db_links;

--db link 생성

create public database LINK yang

CONNECT TO scott IDENTIFIED BY "imsi00"

using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)

(HOST=172.16.1.53)(PORT=1521))

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL2)))';

-- scott계정에 모두 다 들어갈 수 있어서 public으로 선언 잘 안함

--DB Link 삭제

drop public database link yang;

--yoon 계정의 emp 조회

select * from emp@yang;

select * from dba_tables

where table_name='TEST';

select * from dba_synonyms

where synonym_name='TEST';

create or replace public synonym test for test@yang; -- synonym을 등록

select * from test; -- synonym 설정 후 하면 출력됨

select * from test@yang;

◆ Profile 관리하기

●Password 관련

1) FAILED_LOGIN_ATTEMPTS : LOGIN 시도를 실패할 경우 계정을 잠그는데 여기 설정된 횟수

2) PASSWORD_LOCK_TIME : 위에서 계정이 잠기면 며칠 동안 잠글 것 인지 기간을 정하는 파라미터 (일 단위)

-일수와 무관하게 DBA가 UNLOCK 할 수 있음

3) PASSWORD_LIFE_TIME : 동일한 암호를 며칠간 사용하게 할 것인지 설정하는 파라미터. (일 단위)

이 기간이 지나도 안 바꾸면 다음 로그인 할 때 강제로 바꾸게 프롬프트를 보여줌

4) PASSWORD_GRACE_TIME : 위 3번 항목에서 만료되어도 이 파라미터에 지정된 값 만큼 더 암호를 변경할 기간을 허용

5) PASSWORD_REUSE_TIME : 동일한 암호를 다시 사용할 수 없도록 설정하는 기간 즉, 암호를 변경하라고 했는데 사용자가 동일한

암호를 다시 사용하려고 할 수 있기에 같은 암호를 다시 쓸 수 없게 만들어야 할 때 사용하는 파라미터

6) PASSWORD_REUSE_MAX : 동일한 암호를 위 5번 설정을 피해 재사용을 할 경우 최대 사용 가능한 횟수를 지정하는 파라미터

7) PASSWORD_VERIFY_FUNCTION : 암호를 보다 복잡하게 만들기 위해 특정 함수를 적용시켜 사용자의 암호를 점검 할 수 있음.

ORACLE에서 만들어 둔 기본 함수인 VERIFY_FUNCTION을 사용할 수 있고 사용자가 별도의

함수를 이용할 경우 어떤 조건을 점검하는 가

· 암호는 최소한 4글자 이상 되어야 한다

· 암호는 사용자 계정과 달라야 한다

· 암호는 하나의 특수문자나, 알파벳, 숫자가 포함되어야 한다

· 암호는 이전 암호와 3글자 이상 달라야 한다

-- password 관련 profile 생성하기

-- 다음의 profile을 만드시오. profile이름은 profile_sms

--smsuser에 profile_sms를 적용

--조건1 : 로그인 시도 3회 실패 시 계정을 5일 동안 사용 못하게 할 것

--조건2 : 계정의 암호는 15일에 한 번씩 변경하게 할 것

--조건3 : 동일한 암호는 15일 동안 사용 못하게 할 것

--profile 생성

CREATE PROFILE profile_sms LIMIT

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 5

PASSWORD_LIFE_TIME 15

PASSWORD_REUSE_TIME 15;

--user‘s profile 변경

ALTER USER SMSUSER PROFILE profile_sms;

◆ 리소스 관련 PROFILE

(1) CPU_PER_SESSION : 하나의 세션이 CPU를 연속적으로 사용할 수 없는 최대 시간을 설정합니다. 무한루프 같은 쿼리가 작동되면 혼자 서 CPU를 연속적으로 점유하기 때문에 이 설정을 사용해서 그런 일을 막고자 하는 것입니다. 1/100초 단위입니다.

(2) SESSIONS_PER_USERS : 하나의 사용자 계정으로 몇 명의 사용자가 동시에 접속 할 수 있는지를 설정하는 파라미터입니다

(3) CONNECT_TIME : 하루 동안 DB SERVER에 접속을 허락하는 총 시간을 설정합니다. (단위는 분)

(4) IDLE_TIME : 연속적으로 휴면 시간이 여기 값을 넘으면 접속을 해제 합니다. 예를 들어 IDLE_TIME 5 이렇게 하면 5분 동안 활동이 없는 세션은 강제로 접속이 끊어지게 됩니다 (분 단위)

(5) LOGICAL_READS_PER_SESSION : 한 SESSION에서 사용 가능한 최대 BLOCK 수를 지정합니다.

(6) PRIVATE_SGA : MTS / shared server 일 경우 해당 session의 SGA 사용량을 byte 단위로 설정

(7) CPU_PER_CALL : 하나의 Call 당 cpu 점유할 수 있는 시간 1/100초 단위

(8) LOGICAL_READS_PER_CALL : 하나의 call당 읽을 수 있는 block의 개수 지정

-- Resource 관련 profile 생성

--profile_sms에 다음의 resource 관련 parameter 값을 수정

-- SYS> alter system set resource_limit=true;

--조건1 : 1명당 연속적으로 CPU를 사용할 수 있는 시간을 10초로 제한할 것

--조건2 : 하루 중 8시간만 DB에 접속 가능하게 할 것

--조건3 : 10분 동안 사용하지 않으면 강제로 접속을 끊을 것

ALTER PROFILE PROFILE_SMS LIMIT

CPU_PER_SESSION 1000

CONNECT_TIME 480

IDLE_TIME 10 ;

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; -- 리소스 내용이 적용 되려면 TRUE로 해야함

SELECT * FROM DBA_PROFILES

WHERE PROFILE='PROFILE_SMS';

◆ Privilege (권한) 관리하기

· SYSTEM 관련 privilege