오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가
|
1.4 숫자형 함수 - 오라클지정 교육원, 본원 시험실시, 40%바우쳐제공
오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가
오라클 공인 전문가 제도(Oracle Certified Professional)는 오라클 제품 사용자의 관련 업무별 전문 기술 지식 정도를 측정하여 대내외적으로 증명해 주며, 오라클의 제품과 최신 기술을 활용하여 최적의 솔루션을 설계, 개발, 응용하는데 필요한 인력의 역량을 인증해 주는 프로그램입니다.
2006 오라클 9I+10G 양성교육 일정은 국비지원 제도 사항으로 혜택을받으실수 있습니다.
※본원 오라클 지정 취업지원과정 교육 파트너(Oracle Workforce Development Program)
오라클의 정규 과정의 강의를 수행할 수 있는 강사를 보유한 기관을 대상으로 오라클의 교육과목에 대하여 공식교재와 S/W를 활용하여 교육과정을 진행하는 교육 기관입니다. 협력교육기관은 자체적으로 과정을 개설하며, 수강생을 모집하고, 강의를 수행하며, 교육 품질에 대한 책임도 협력 교육기관이 소유하는 형태의 교육 협력 파트너입니다.
▶ 교육과정 : ORACL-DBA 9I+10G [WDP공인지정센터]
<강남 국제자격증 시험센터>
<시스템>
▶ 오라클 9I 교육과정 및 세부내용
교육과정 세부내용 - 8주교육 [주5일 일일3시간 45분진행]
＊ 1개월
(DML, CURSOR, CURSOR FOR LOOP, CURSOR Variable, CURSOR 연산식, DCL) (Inheritance, Step by Step, 치환성(sbsstitutability), 타입의 진화(Type Evolution), 객체 뷰의 상속)
<3,4주차> Fundamental Ⅰ (1Z0-031)
＊ 2개월
<3,4주차> Performance Tuning (1Z0-033) Database Tuning 개요
※ 출처 http://www.itmoya.net/ocp/main_1.htm
오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 * 데이터 검색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 ============================================================================
＊ 교육 담당 & 관리자 : 김 윤 석
오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 DBA에게 유용한 Toad의 숨은 기능 찾기 기업에서의 데이터 증가는 기하급수적으로 늘어나고 있으며 그에 따른 데이터베이스 성능도 대용량을 처리할 수 있도록 발전하고 있다. 따라서 데이터베이스 관리자는 예전과 달리 데이터베이스의 운영을 매뉴얼하게 할 수 있는 상태에 직면해 있으며, 자동화된 모니터링 및 Alerts로 안정적인 서비스에 대응할 수 있는 툴을 원하고 있다. 이러한 요구사항을 위한 토드의 GUI 환경에서의 데이터베이스 모니터링 기능을 살펴보자.
데이터베이스 개발에 있어 토드(Toad)를 사용하는 사람들은 일반적으로 “토드는 개발자들을 위한 개발 툴”이란 생각을 많이 한다. 즉 단순히 SQL 문장이나 PL/SQL 문장을 빠르고 쉽게 개발할 수 있고, 데이터베이스 객체의 생성 및 변경 작업을 GUI 환경에서 간단하게 수행하며 소스코드 상의 문제점들을 자동으로 찾아주고, 디버깅 기능을 통해 개발자들의 수고를 덜어 줄 수 있는 툴 정도로만 인식하고 있는 것이다. 하지만 토드를 좀 더 세밀하게 들여다보면 개발자만을 위한 툴이 아닌 데이터베이스를 관리(management)하는 DBA(Database Administrator)가 사용할 수 있는 다양한 기능들이 숨겨져 있다. 이 글에서는 토드에 숨겨진 유용한 기능에 대해 소개하고자 한다.
DBA의 역할은 무엇일까?
DBA는 현재 운영되고 있는 시스템, 데이터베이스, 애플리케이션, 네트워크, 서비스 등의 다양한 환경을 구성하고 설치, 보안, 운영 및 설계나 개발 단계에 직, 간접적으로 참여해 전체 구성이 원활하게 유지되도록 하는 업무를 담당하고 있다.
DB의 문제점을 발견하라!
토드를 구입했다고 해서 DBA 기능을 전부 사용할 수 있는 것은 아니다(‘토드 DBA 모듈’은 옵션 제품이다). 제품을 구입할 당시에 DBA 모듈이라는 제품을 추가적으로 구입해야 사용이 가능하다. 하지만 개발 위주의 작업이 아닌 전사적인 방법으로 토드를 사용할 것이라면 추천할 만한 사항이라고 생각한다. 그러면 자신이 사용하고 있는 토드를 갖고 이 DBA 기능을 어떻게 사용할 수 있을까?
데이터베이스 모니터
우선 데이터베이스 운영자는 데이터베이스의 성능을 높이기 위해 물리적인 I/O의 병목현상을 제거함으로써 시스템의 메모리와 CPU 자원의 경합을 줄이며 안정적인 서비스를 제공할 수 있다. 그리고 데이터베이스 운영자는 각 세션들에서 발생되는 Wait Event(네트워크 통신이나 I/O 요청 또는 데이터베이스의 특정 자원을 여러 프로세스가 동시에 액세스할 때 발생하는 경합에 의한 대기)의 원인을 제거함으로써 원활한 응답속도를 유지해야 한다.
◆ 데이터베이스 모니터의 주요 기능
데이터베이스 모니터의 Alert
헬쓰 체크
데이터베이스 구축 후 시간이 지남에 따라 데이터의 크기는 현저하게 증가하게 되고 또한 다양한 문제점들이 나타나게 되는데, 인스턴스에서 발생하는 문제점들을 DBA가 찾아서 조치하기에는 시간과 비용이 만만치 않다. 이에 DBA는 C 검사를 원하는 항목(SGA, Analyze, Extent, JOB……)들에 대해 조건을 설정한다. 해당 조건을 만족하는 내용에 대해 자동으로 체크해 결과를 보여준다면 DBA의 역할은 그만큼 줄어들 것이며, 이를 통해 데이터베이스에 지장을 초래할 수 있는 원인들을 미연에 방지할 수 있다.
◆ 헬쓰 체크의 주요 기능
<화면 3>은 토드의 헬쓰 체크 기능을 수행한 화면이다. 여기에는 Checks and Options, Other Settings, Report Output 등 3가지 탭으로 구성되어 있는데, Check and Options 탭에서 이미 지정되어 있는 다양한 인스턴스 체크 사항 중에서 원하는 항목을 지정하고 그에 따른 값을 입력하고 실행하면 Report Output 탭에 Health Check를 수행한 결과를 보여준다. 인스턴스 매니저
이 기능은 현재 동작 중인 데이터베이스의 인스턴스에 커넥션을 자동으로 수행해 Startup 상태인지 Shutdown 상태인지를 체크할 수 있으며, 토드에서 직접 Startup/Shutdown 명령을 수행하거나 Init 파라미터 파일을 빌딩(building)할 수도 있다. DB의 문제점을 분석하라!
데이터베이스의 문제점을 파악했으면 과연 이 문제점의 원인은 무엇이며, 현재 데이터베이스 성능에서 병목현상(Bottleneck)이 발생하는 영역은 어디인지에 대해 자세하게 분석해야 한다.
Database Probe
데이터베이스의 구성은 크게 메모리(SGA), 프로세스, 데이터파일(Online Redo Logfile, User Datafile)로 구성되며 서버 프로세스와 백그라운드 프로세스에 의해 자동적으로 운영된다. Database Probe는 <화면 5>와 같이 3개의 그룹으로 나뉘어 각 그룹별로 중요한 정보를 보여주게 된다.
◆ Database Probe의 주요 기능
Top Session Finder
◆ Top Session Finder의 주요 기능
세션 브라우저
- 세션 : 선택한 세션의 ID, 프로그램, 모듈, Machine, OS 유저, DB 유저 등의 정보를 제공
OS 유틸리티
DB상의 문제점을 어떻게 해결할 것인가?
데이터베이스의 문제점을 감지(detect)하고 분석(diagnostic)했으면, 그에 따른 행동을 취해야 한다. 일반적으로 제시하는 해결방안은 시스템 튜닝, 데이터베이스 튜닝, 애플리케이션 튜닝, SQL Statement 튜닝으로 구분할 수 있는 데, 토드에서는 문제점을 해결하기 위한 다양한 기능이 존재한다.
테이블 스페이스와 테이블 스페이스 맵
이는 데이터베이스의 논리적 구조를 이루는 가장 핵심적인 요소이다. 데이터베이스의 데이터가 존재하는 물리적인 데이터파일과 연결되어 있으며, 그 안에 세그먼트, 익스턴트(Extent), 블럭이라는 구조가 존재하고 있다. 만약 테이블 스페이스의 공간이 부족하거나, 데이터파일에 Fragmentation이 발생해 장애가 발생한 경우라면 해당 테이블 스페이스의 공간을 늘려주는 작업과 그 안에 존재하는 Fragmentation을 Coalesce하는 작업을 수행해 다시 재구성하는 문제를 생각해야 할 것이다. 또한 이로 인해 I/O Wait가 발생해 성능이 떨어지는 원인이 된다면 해당 데이터파일도 다시 재구성하거나 재구축하는 절차를 수행해야 한다. 이러한 과정을 손쉽게 수행할 수 있도록 하는 기능이 바로 테이블 스페이스 기능이다.
컨트롤 파일과 리두 로그 매니저
Log Switch Frequency Map
Rebuild Objects
Repair Chained Rows
Export/Import Utility Wizard와 SQL*Loader Wizard
Server Statistics
◆ Oracle Parameter and NLS Parameter : Server Statistics 기능에서 제시한 내용을 기준으로 특정 데이터베이스 파라미터를 수정할 경우, 이 기능을 사용해 쉽게 변경할 수 있다.
데이터베이스 브라우저 기능이란?
지금까지 DBA 기능에 대해 각 기능별로 소개를 했다. 하지만 DBA가 이 모든 기능들을 일일이 하나씩 확인한다면 이것 또한 너무 불편할 것이다. ◆ 데이터베이스 브라우저의 다양한 탭
SQL 튜닝 엑스퍼트
SQL 튜닝 엑스퍼트는 토드의 DBA 모듈에 포함되어 있는 기능은 아니며, 토드의 엑스퍼트 튜닝 모듈(Xpert Tuning Module)에 있는 기능이다.
1단계, SQL Detail 여기서는 SQL 에디터나 프로시저 에디터에서 수행 중인 SQL 문장을 드래그해 Execution Plan과 해당 SQL 문장에 나타난 객체의 정보를 확인할 수 있다. Execution Plan을 통해 현재 SQL 문장이 어떻게 수행될 것이지 예측할 수 있으며, 해당 테이블에 생성되어 있는 인덱스나 컬럼의 정보를 확인할 수 있다. 2단계, View Advice 이 부분은 현재의 SQL 문장에 대해 Execution Plan의 정보만 갖고 튜닝 액션을 결정할 수가 없을 경우 개발자나 DBA에게 현재 환경에 적합한 가장 최적의 SQL 솔루션을 제공한다. - Auto Tune : 이는 오라클 옵티마이저가 판단한 근거를 기준으로 자동으로 현재 SQL 문장에 맞는 최적의 솔루션 리스트를 제공한다. 이는 튜닝 초보자에게 적합한 것으로 SQL 튜닝에 대한 지식이 없더라도 튜닝 솔루션을 찾을 수 있게 한다.
3단계, Compare Scenario Advice에서 선택한 사항을 토대로 Original SQL 문장과 Advice SQL 문장의 Explain Plan과 SQL 문장을 기준으로 비교할 수 있도록 정보를 제공한다. 4단계, Execution Scenario Compare Scenario 스텝까지는 직접 SQL 문장을 실행하지 않은 상태에서 간접적으로 비교를 수행한 것에 반해 이 부분은 직접 Original SQL 문장과 Advice SQL 문장을 실행해 비교할 수 있는 정보를 제공한다. 만약 Trace 정보를 만들고 싶다면 옵션으로 지정할 수도 있다. - Index Advice : 만약 View Advice 단계에서 선택한 Advice가 인덱스를 추가·삭제·변경하는 작업이었다면 이번 단계에서는 인덱스에 대한 DDL 명령을 수행해야 한다. 하지만 이를 적용했을 경우 다른 SQL 문장이 영향을 받을 수 있기 때문에 조심스럽게 접근해야 할 것이다. 따라서 실제로는 실행시에 인덱스에 대한 DDL 명령을 수행하지 않고 버추얼하게 수행해 현재 데이터베이스에 영향을 주지 않는 선에서 비교할 수 있도록 정보를 제공한다.
5단계, Execution Results 실행한 내용을 토대로 그 결과를 보여주며 실행시에 생성된 통계 정보를 비교할 수 있다. 토탈 CPU, Elapsed Time, Logical Read, Physical Read 등의 많은 통계 정보를 서로 비교할 수 있다.
6단계, Best Practice 앞의 Advice에서 선택한 사항을 토대로 실제 실행과정을 거친다. 예를 들어, 인덱스 생성 Advice를 선택했다면 4번 단계에서는 가상적으로 생성해 비교를 수행했는데, 이를 비교 검토 후 적용하는 과정이라고 생각하면 된다. 또한 추가적으로 수행할 때 더 적합한 내용들이 있다면, 예를 들어 분석 작업 같은 내용이 여기에 포함될 수 있는데 최적의 상태가 될 수 있는 리스트를 제시하면 튜너는 여기에서 원하는 내용을 선택할 수 있다.
7단계, Tuning Resolution 지금까지 진행해온 모든 사항을 기본으로 해 Original SQL과 Advice SQL에 대해 어느 정도 성능 효과를 보였는지를 확인할 수 있다.
토드의 다양한 기능을 습득하기 바라며 지금까지 토드에서 제공하는 DBA 모듈에 대한 일부 기능을 소개했다. 토드라는 툴은 너무나 많은 기능들을 갖고 있기 때문에 사용자의 입장에서 어떤 기능들이 토드에 있는지조차 모르는 경우가 다반사라고 생각한다. 이렇게 토드에는 숨겨진 많은 기능들이 독자의 업무에 도움이 될 수 있으면 하는 바람이다.
＊ 교육 담당 & 관리자 : 김 윤 석
오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 RowCache의 Miss율을 보여준다
/* rowCache 의 MissRatio를 조사하는 스크립트 ** <<박제용 99.11>> ** Row chache 의 Miss ratio는 15% 이하로 유지하는 것이 좋다. ** 그렇지 않을경우 shared_pool_size를 늘리는것을 고려해야 한다. */ select sum(gets) "Gets", sum(getmisses) "Misses", (1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100 "HitRate" from v$rowcache;
Library Cache의 Hitratio를 보여준다.(shared_pool의 size결정을위해)
/* library Cache Hitratio 출력 스크립트 ** <<박제용 99.11>> ** library Cache 의 hitratio 가 0.9 이하이면 ** Shared Pool Size를 늘려주거나, SQL 문의 이상을 ** 조사해야 한다. */ select sum(pins) Executions, sum(pinhits) "Execution Hits", sum(reloads) Misses, ((sum(pins) / (sum(pins) + sum(reloads))) * 100) hitratio from v$librarycache;
SQL Cursor를 보여주는 스크립트
/* SQL Cursor를 조사하는 스크립트. ** <<박제용 99.11>> ** SQL Cursor 를 조사하여 부하가 많이 걸리는 SQL문과 ** 메모리를 조사한다. ** loads : 캐쉬에서 나갔다 들어온 횟수(best=1). ** invalidations : LRU에서 무효화된 횟수. 이 값이 4이상이면 ** shared_pool_area를 확장해야한다. ** parse_calls : 이 커서의 호출 수. ** sorts : 수행된 소트횟수 ** command_type: 2 - insert, 3-select, 4-update, 7-delete */ select sql_text, loads, invalidations, parse_calls, sorts FROM v$sqlarea WHERE sql_text NOT LIKE '%$%' AND command_type IN(2,3,6,7);
explain을 보기 쉽게 출력해주는 SQL
* ** expain plan 결과를 보기 쉽게 출력해주는 스크립트. ** ** 1) expain을 처음 사용할 경우엔 [ORACLE_HOME]/rdbms/admin/utlxplan.sql을 실행, ** plan_table을 생성한다. ** 2) 처음 사용이 아니면 delete from plan_table; 을 실행하여 이전 결과를 삭제. ** ** 실행결과 파싱번호(id)가 길면 SQL이 비효율적이거나, shared_pool_size가 작은것이다. ** 기타 SQL문이 인덱스를 사용하는지 등등을 알수 있다. */ col operation format a30 col options format a20 col id format 99
select id, lpad(' ',2*level) || operation || decode(id, 0, ' Cost= ' || position )"operation", options, object_name "object" from plan_table connect by prior id=parent_id start with id =0;
세션별로 과도한 memory read를 하는 SQL문을 찾아주는 스크립트
/* ** SQL query 튜닝 스크립트.. <박제용> ** ** 유저별로 과도한 logical read를 수행하는 sql 문 찾기 ** ** */ Break on User_Name On Disk_Reads on Buffer_Gets on Rows_Processed Select A.User_Name, B.Disk_Reads, B.Buffer_Gets, B.Rows_Processed, C.SQL_Text From V$Open_Cursor A, V$SQLArea B, V$SQLText C Where A.User_Name = Upper('&&User') And A.Address = C.Address And A.Address = B.Address Order By A.User_Name, A.Address, C.Piece;
과도한 memory read를 하는 SQL문을 찾아주는 스크립트
/* ** SQL query 튜닝 스크립트.. <박제용> ** ** 과도한 logical read를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌. ** ** 원인 => 1) 인덱스 컬럼에 distinct한 값이 적은, 부적절한 인덱스의 사용. (대체로 인덱스를 지워야 할 경우) ** 2) 최적화 되지 않은 SQL 문장 */ select buffer_gets, sql_text from v$sqlarea where buffer_gets > 200000 order by buffer_gets desc;
과도한 disk read를 하는 SQL문을 찾아주는 스크립트
/* ** SQL query 튜닝 스크립트.. <박제용> ** ** 과도한 disk read를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌. ** ** 원인 => 1) SQL문이 최적화 되지 않아 disk read를 많이 할 수 밖에 없는 쿼리일경우. ** (index가 없거나 사용되지 않을때) ** 2) db_block_buffers 또는 shared_pool_size 가 작은 경우. (메모리가 적음) */ select disk_reads, sql_text from v$sqlarea where disk_reads > 10000 order by disk_reads desc;
SGA의 shared pool내에 캐쉬된 object중 큰 영역을 차지 하고 있는 것을 찾아주는 스크립트
/* ** Shared_pool에 저장된 내용보기 <박제용> ** ** 프로시져나 패키지등은 shared_pool에 저장되며 저장된 객체중 ** 그 크기가 100K 가 넘는것을 보여준다. */ col name format a30
select name, sharable_mem from v$db_object_cache where sharable_mem > 100000 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') and kept = 'NO';
SGA의 shared pool 의 현재 사용도를 출력해주는 SQL
/* ** shared_pool_size의 현재 사용 현황을 보여줌. <박제용> ** ** shared_pool_size의 현재의 사용현황을 보여준다. ** 이 데이터를 주기적으로 보관하여 분석한다. */ col value for 999,999,999,999 heading "Shared Pool Size" col bytes for 999,999,999,999 heading "Free Bytes" select to_number(v$parameter.value) value, v$sgastat.bytes, (v$sgastat.bytes/v$parameter.value)*100 "Percent Free" from v$sgastat, v$parameter where v$sgastat.name = 'free memory' and v$ parameter .name = ‘shared_pool_size;
SGA의 shared pool 의 hitratio를 연산해주는 SQL[/color][/b]
오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 * 데이터 검색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
오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 아래의 글을 정말 잘 읽어 보십시요.. 중요한 내용들이고요.. 헷갈리지 않게요... 그럼 중간중간에 토를 좀 달아보겠습니다. IntroductionOne of the intriguing aspects of Cisco routers, especially for those new to routing, is how the router chooses which route is the best among those presented by routing protocols, manual configuration, and various other means. While route selection is much simpler than you might imagine, to understand it completely requires some knowledge about the way Cisco routers work. PrerequisitesRequirementsThere are no specific prerequisites for this document. Components UsedThis document is not restricted to specific software and hardware versions. ConventionsFor more information on document conventions, see the Cisco Technical Tips Conventions. Processes InvolvedThere are three processes involved in building and maintaining the routing table in a Cisco router:
Let's examine the interaction between the routing protocols and the routing table to understand how the routing table is built. Building the Routing Table라우팅 결정을 위해서는 총 3가지의 변수가 있습니다. 바로 administrative distance, metric, 그리고 prefix length죠... AD값에 정의는 바로 밑의 표에서 확인을 할수가 있습니다.
The main considerations while building the routing table are:
As each routing process receives updates and other information, it chooses the best path to any given destination and attempts to install this path into the routing table. For instance, if EIGRP learns of a path toward 10.1.1.0/24, and decides this particular path is the best EIGRP path to this destination, it tries to install the path it has learned into the routing table. The router decides whether or not to install the routes presented by the routing processes based on the administrative distance of the route in question. If this path has the lowest administrative distance to this destination (when compared to the other routes in the table), it's installed in the routing table. If this route isn't the route with the best administrative distance, then the route is rejected. To understand this better, let's look at an example. Assume a router has four routing processes running: EIGRP, OSPF, RIP, and IGRP. Now, all four of these processes have learned of various routes to the 192.168.24.0/24 network, and each has chosen its best path to that network through its internal metrics and processes. Each of these four processes attempts to install their route toward 192.168.24.0/24 into the routing table. The routing processes are each assigned an administrative distance, which is used to decide which route to install.
Since the internal EIGRP route has the best administrative distance (the smaller the administrative distance, the higher the preference), it's installed in the routing table. Backup RoutesWhat do the other protocols, RIP, IGRP, and OSPF, do with the routes that weren't installed? What if the most preferred route, learned from EIGRP, fails? Cisco IOS® Software uses two approaches to solve this problem: The first is to have each routing process attempt to install its best routes periodically. If the most preferred route fails, the next best route (according to administrative distance) succeeds on the next attempt. The other solution is for the routing protocol that failed to install its route in the table to hang on to the route, and tell the routing table process to report if the best path fails. For protocols that don't have their own routing information tables, such as IGRP, the first method is used. Every time IGRP receives an update about a route, it attempts to install the updated information in the routing table. If there's already a route to this same destination in the routing table, the installation attempt fails. For protocols that have their own database of routing information, such as EIGRP, IS-IS, OSPF, BGP, and RIP, a backup route is registered when the initial attempt to install the route fails. If the route installed in the routing table fails for some reason, the routing tabel maintenance process calls each routing protocol process that has registered a backup route, and asks them to reinstall the route in the routing table. If there are multiple protocols with registered backup routes, the preferred route is chosen based on administrative distance. Adjusting the Administrative Distance밑의 예에서 보듯이 ip route 1.1.1.0 255.255.255.0 2.2.2.1 200 이런식으로 ad값을 조정할수가 있습니다.
The default administrative distance might not always be right for your network; you may want to adjust them so that RIP routes are preferred over IGRP routes, for instance. Before explaining how to adjust the administrative distances, we need to look at the implications of changing the administrative distance. Changing the administrative distance on routing protocols can be very dangerous! Changing the default distances can actually lead to routing loops and other oddities in your network. We recommend you change administrative distance with caution, and only after you have thought through what you want to achieve, and all the consequences of your actions. For entire protocols, changing the distance is relatively easy; simply configure the distance using the distance command in the routing process subconfiguration mode. You can also change the distance for routes learned from one source only in some protocols, and you can change the distance on just some routes. For static routes, you can change the distance of each route by entering a distance after the ip route command: ip route network subnet mask next hop distance You can't change the administrative distance for all the static routes at once. How Metrics Determine the Route Selection Processmetric은 이미 AD값에 의해서 routing table안에서 두개의 동등한 path가 존제할때 metric에 의해서 best path를 찾는 것입니다.
Routes are chosen and built in the routing table based on the routing protocol's administrative distance. The routes learned from the routing protocol with the lowest administrative distance are installed in the routing table. If there are multiple paths to the same destination from a single routing protocol, then the multiple paths would have the same administrative distance and the best path is selected based on the metrics. Metrics are values associated with specific routes, ranking them from most preferred to least preferred. The parameters used to determine the metrics differ for different routing protocols. The path with the lowest metric is selected as the optimal path and installed in the routing table. If there are multiple paths to the same destination with equal metrics, load balancing is done on these equal cost paths. For more information on load balancing see How Does Load Balancing Work? Prefix Lengths이부분이 정말 재미있죠... 라우터안에 4개의 라우팅 프로세스가 돌구 있습니다. 그리고 서로다른 prefix의 route들을 여러 라우팅 프로세스에 의해서 받았습니다. 그럼 어떻게 저것들에 대한 포워딩 결정을 하게 될까요?
Let's look at another scenario to see how the router handles another common situation: varying prefix lengths. Assume, again, that a router has four routing processes running on it, and each process has received these routes:
Which of these routes will be installed in the routing table? Since EIGRP internal routes have the best administrative distance, it's tempting to assume the first one will be installed. However, since each of these routes has a different prefix length (subnet mask), they're considered different destinations, and they will all be installed in the routing table. Let's see how the forwarding engine uses the information from the routing table to make forwarding decisions. Making Forwarding Decisions예제는 192.168.32.1로 향하는 패킷이 라우터에 도착했을때 어떤놈을 next hop으로 선정할것이냐 하는문제입니다. 여기에서 유명한 longest prefix가 나오는데요... 자 빠져봅시다! 답은 10.1.1.1 입니다. 이유는.... 첫째... 192.168.32.1이 속한 subnet이 어디인가 하는거죠... 이부분을 가장 먼저 생각해야 합니다. 그러면 D,R,O 다 포함이 되지만... longest prefix가 우선시 되므로 D가 선정이 됩니다. 고로 eigrp에서 받은 route가 선정이 됩니다. 만약에 192.168.32.100으로 향하는놈이라면 /26은 탈락이 됩니다. subnet range에서 탈락을 하고 마는거죠... 그러면 그다음엔 longest prefix가 우선시 되므로 /24 즉 R이 선정이 됩니다.
Let's look at the three routes we just installed in the routing table, and see how they look on the router. If a packet arrives on a router interface destined for 192.168.32.1, which route would the router choose? It depends on the prefix length, or the number of bits set in the subnet mask. Longer prefixes are always preferred over shorter ones when forwarding a packet. In this case, a packet destined to 192.168.32.1 is directed toward 10.1.1.1, because 192.168.32.1 falls within the 192.168.32.0/26 network (192.168.32.0 to 192.168.32.63). It also falls within the other two routes available, but the 192.168.32.0/26 has the longest prefix within the routing table (26 bits verses 24 or 19 bits). Likewise, if a packet destined for 192.168.32.100 arrives on one of the router's interfaces, it's forwarded to 10.1.1.2, because 192.168.32.100 doesn't fall within 192.168.32.0/26 (192.168.32.0 through 192.168.32.63), but it does fall within the 192.168.32.0/24 destination (192.168.32.0 through 192.168.32.255). Again, it also falls into the range covered by 192.168.32.0/19, but 192.168.32.0/24 has a longer prefix length. IP Classless자 오늘의 주인공 ip classless입니다. 중요한 말이 나오죠... 이 명령어는 라우팅과 포워딩 프로세스와 헷갈리는데.. 사실 ip classless는 포워딩 프로세스에만 관여를 하는거지 라우팅에는 전혀 상관이 없습니다. 말인즉.. 우리가 rip igrp는 classful이고 rip v2 eigrp ospf isis등은 classless하다고 했죠? 이건 우리가 아는대로 돌아가구.. 이거랑은 전혀 상관없는 말이라는거죠... 자 no ip classless가 설정이 되었다고 하지요.. 라우터는 supernets으로 패킷을 포워딩 하지 않습니다. 그리고 Note 잘 읽어 보시구요... 내용은 supernet 또는 default route가 isis 또는 ospf로부터 기인된 거라면 no ip classless로 설정된건 무시된답니다. 자 다시 빠져보자면... 172.30.32.0/24네트웍은 172.30.32.0 부터 172.30.32.255를 포함하고 있습니다. 그리고 172.30.32.0/20네트웍은 172.30.32.0 부터 172.30.47.255 까지를 포함합니다. (이거 잘 이해하셔야 합니다.) 만약에 172.30.32.1은 longest prefix match로 10.1.1.1로 갑니다. 172.30.33.1은 10.1.1.2로 가구요 192.168.10.1은 10.1.1.3으로 갑니다. 하지만 172.30.254.1은 drop되버립니다. 이게 골때리죠.... 분명 우리가 의도한것은 10.1.1.3으로 가야되는데... 이게 바로 no ip classless의 설정의 행동으로 어떻게 되는지를 알수있는 방법입니다. 좀더 specific하지 않은 네트웍이 supernet안에 존재하지 않는다면... 그놈들은 drop되어버린다는거죠... 그럼 supernet은.. 어떤의미인가.. 하면 하나의 domain이라고 생각을 해도 무관합니다. 그리고 그 네트웍은 유일하기 때문에 그 아래에서 나오는 그림과 같이 다른쪽에 10.x.x.x 대역은 없다고 생각을 합니다. 내가 이미 10.x.x.x 대역의 supernet이기 때문이죠......
Where the ip classless configuration command falls within the routing and forwarding processes is often confusing. In reality, IP classless only affects the operation of the forwarding processes in IOS; it doesn't affect the way the routing table is built. If IP classless isn't configured (using the no ip classless command), the router won't forward packets to supernets. As an example, let's again place three routes in the routing table and route packets through the router. Note: If the supernet or default route is learned via IS-IS or OSPF, the no ip classless configuration command is ignored. In this case, packet switching behavior works as though ip classless were configured. Remembering that the 172.30.32.0/24 network includes the addresses 172.30.32.0 through 172.30.32.255, and the 172.30.32.0/20 network includes the addresses 172.30.32.0 through 172.30.47.255, we can then try switching three packets through this routing table and see what the results are.
The surprising answer out of these four is the last packet, which is dropped. It's dropped because its destination, 172.30.254.1, is within a known major network, 172.30.0.0/16, but the router doesn't know about this particular subnet within that major network. This is the essence of classful routing: If one part of a major network is known, but the subnet toward which the packet is destined within that major network is unknown, the packet is dropped. The most confusing aspect of this rule is that the router only uses the default route if the destination major network doesn't exist in the routing table at all. This can cause problems in a network where a remote site, with one connection back to the rest of the network, is running no routing protocols, as illustrated.
The remote site router is configured like this: With this configuration, the hosts at the remote site can reach destinations on the Internet (through the 10.x.x.x cloud), but not destinations within the 10.x.x.x cloud, which is the corporate network. Because the remote router knows about some part of the 10.0.0.0/8 network, the two directly connected subnets, and no other subnet of 10.x.x.x, it assumes these other subnets don't exist and drops any packets destined for them. Traffic destined to the Internet, however, doesn't ever have a destination in the 10.x.x.x range of addresses, and is therefore correctly routed through the default route. Configuring ip classless on the remote router resolves this problem by allowing the router to ignore the classful boundaries of the networks in its routing table and simply route to the longest prefix match it can find. SummaryIn summary, making a forwarding decision actually consists of three sets of processes: the routing protocols, the routing table, and the actual process which makes a forwarding decision and switches packets. These three sets of processes are illustrated, along with their relationship, below.
The longest prefix match always wins among the routes actually installed in the routing table, while the routing protocol with the lowest administrative distance always wins when installing routes into the routing table.
꼭 이해해 주셨으면 하구요.... 전 가끔씩 어떤이에게 라우팅이라는걸 가르쳐줄때... 첫마디로 이런 이야기를 합니다. 라우팅은.. destination routing이다.... 알았지~~ 라고.... 이건.. 라우팅의 기본입니다. 항상 목적지를 보고 어디로 갈지를 결정하는것이지요... 가끔 connected interface가 있음에도 불구하고 static routing을 넣는 사람들이 있는데... 기본이 바로서지 못해서인거 같다는 생각에 안타깝습니다... 원 소스는 http://www.cisco.com/en/US/tech/tk365/technologies_tech_note09186a0080094823.shtml 입니다.
오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 ＜HTML에 사용되는 색 이름＞
오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 No. 10445 ARCHIVE LOG MODE 운영 방법 Oracle에서 Online Backup을 받거나 완벽한 Recovery 작업을 수행하기
1. initSID.ora file과 configSID.ora 에 다음의 parameter가 이미 setting 1) LOG_ARCHIVE_START = TRUE * ARCH process 가 기동 2) LOG_ARCHIVE_DEST = /home/oracle7/dbs/archive_file/arc * archive 장소의 디렉토리와 확장자를 포함하지 않는 파일명을 지정. * archive file의 확장자와 log sequence 번호의 형식을 지정. [ 예 ] arc123.log, arc124.log
$ svrmgrl SVRMGR> connect internal 바이너리로 만들어진 파라미터 화일을 사용하는 경우 (Oracle 9i 부터) 1. Parameter 확인 $sqlplus /nolog SQL>connect sys/passwd@orcl as sysdba SQL> select name, value from v$parameter 2. 다음과 같이 작업하여 archive log mode로 변환한다. SQL> ALTER SYSTEM SET LOG_ARCHIVE_START = TRUE System altered. SQL> shutdown immediate Total System Global Area 89201304 bytes Database altered. SQL> archive log list Database altered. 반대로, archivelog mode에서 no archivelog mode로 전환하는 방법은 다음과 먼저, 위에서 setting 했던 initSID.ora file 와 configSID.ora 에 있는 #LOG_ARCHIVE_START = TRUE $ svrmgrl 바이너리로 만들어진 파라미터 화일을 사용하는 경우 (Oracle 9i 부터) 1. Parameter 확인 $sqlplus /nolog SQL>connect sys/passwd@orcl as sysdba SQL> select name, value from v$parameter 2. 다음과 같이 작업하여 Noarchive log mode로 변환한다. SQL> ALTER SYSTEM SET LOG_ARCHIVE_START = FALSE SQL> shutdown immediate Total System Global Area 89201304 bytes Database altered. Database altered.
SQL> archive log list SQL> alter system set log_archive_start = true scope=spfile; -- os directory 설정 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 확인
-- %s 는 로그 시퀀스 번호 시스템이 변경되었습니다. SQL> shutdown immediate SQL> startup mount ORACLE 인스턴스가 시작되었습니다. Total System Global Area 101784276 bytes -- control file 의 속성을 바꾼다. SQL> alter database archivelog; 데이타베이스가 변경되었습니다. SQL> archive log list SQL> alter database open; 데이타베이스가 변경되었습니다. SQL> alter system switch logfile; 시스템이 변경되었습니다. -- 그러면 c:\arch1, c:\arch2 밑에 로그파일이 생성된다.
오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가
＊ 교육 담당 & 관리자 : 김 윤 석 ＊ 교육 문의 & 예약 접수 : 02) 6255-8046 ＊ 홈페이지 http://www.itmoya.net/ocp/main_1.htm - 오라클지정 교육원, 본원 시험실시, 40%바우쳐제공
오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가 데이터베이스관리자 9i 10g 오라클공인교육센터 오라클인증 sql 오라클 ocp 오라클학원 ocp학원 오라클자격증 ocp자격증 oracle oracle자격증 oracle학원 db db관리자 db전문가 데이터베이스전문가  시스코
네트워크전문 교육센터
http://www.neverstop.co.kr genesmer
카테고리
태그목록최근에 올라온 글최근에 달린 댓글글 보관함달력
링크
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
