시나리오별 내부진행상황

 1. isqlplus 를 통함 로그인
- connection이 없다면 user process는 서버측의 listner로 요청을 하게 되고 listner는 이 요청이 유효한지 판단하고 Server process로 전달함.(REDIRECT라고도 함)
- Server Process가 리스너로부터 받은 요청을 User process 측으로 응답해줌
- 로그인을 성공하면 Connection이 생성됨.

2. select * from emp; 을 실행할 경우 내부의 진행상황
일단 connection 해야함
- client측에서 user process를 생성
- userprocess는 server로 해당 문장을 요청함(request)
- server process는 instance를 통해 DB에 접속하려 함.
- 문장에 대해 SGA의 Shared pool의 Library cache에서
 ① Parsing 단계
 soft parse-이전에 사용된 문장이 있다면 검사를 진행하지 않음
 hard parse-⑴ syntax check:문장의 구조가 맞는지 확인
                  ⑵ symantics check: 스키마에 있는 오브젝트인지, 권한이 있는지 등 확인. 이때 RC에 이전에 검색했던 data dictionary가 있다면 더 빨리 처리.
                  ⑶ pased code 생성: 파싱이 완료되었다는 코드
                  ⑷ excution plan: optimizer가 실행함
 ② excute 단계
 결과값을 가지러 DB buffer cache를 검색함. 이전에 emp 테이블을 검색한적이 있다면 Server process는 메모리에서 바로 그 결과값을 가져갈 수 있음
 결과값이 있으면 cache hit=logical read, 없다면 cache miss=physical read: disk I/O 발생
 ③ fetch(인출) 단계
 fetch 단계는 select절에만 있음.

3. update emp set sal=5000 where sal=1500; 을 실행할 경우 내부의 진행상황
 ① User process가 명령을 받아 Server Process에게 요청.
 ② Parsing 단계
 ③ excute 단계
 실행이전엔  DB buffer cache에 1500이 기록되어 있음. 실행되면 DB buffer cache는  5000으로 변경되고 1500은 undo segment로 옮겨짐(before image. rollback과 read consistency를 위해)
※ cache: 순서에 따라 처리하는게 목적인 메모리
※ buffer:  순서에 상관없이 저장하는 목적의 메모리
※ buffer cache: 순서에 맞게 저장된 메모리

4. commit; 을 실행할 경우
서버프로세스에서 commit을 받으면 제일 먼저 반응하는 메모리가 Redo log buffer이다.
LGWR process에 의해 online redolog file에 기록을 남김(이게 완료되면 commited를 볼 수 있다).
그래도 db file의 값은 아직 1500이고 db buffer cache의 dirty buffer가 5000의 값으로 되어있으며
select로 조회를 하면 db buffer cache의 정보를 보여준다.
DBWR process에 의해 dirty buffer들이 data file로 옮겨진후 free buffer로 바뀐다.

admin ws1 첫날

sqlplus는 별도의 user process가 없이 client와 process가 일치형.
그외 isqlplus 등은 listner가 필요하다.

* 오라클 서버
-Instance + DB 를 Oracle Server라 부른다.
-Instance는 SGA + Background process
-구조적으로 보면 Memory + Process + Storage
-각 실행중인 오라클 database는 user들이 instance를 통해 db에 접속하기에 반드 시 instance와 연결되어 있어야 한다.
-Oracle instance 시작 후 Software(엔진)은  instance와 특정 database를 연결해야하는데 이 연결을 mount 되었다 라고 한다.

* 메모리
-SGA와 PGA로 나뉘어짐
-PGA: 각 서버 프로세스에 존재하는 개별 전용 메모리, 사용자가 요청한 작업을 개별적으로 실행하기 위해 서버 프로세스에게 개별 할당하는 메모리. 각 서버프로세스에 정보들을 저장하고 클라이언트의 request를 처리하는 메모리.
-SGA: SHARED 란 단어가 제일 중요.
 -필수메모리: shared pool, db buffer cache, redo buffer
 -옵션메모리: large pool(공유풀의 보조 메모리), java pool
 -shared pool(구분 없이 아무나 사용가능한 메모리웅덩이): library cache + data dictionary(RC:row cache-data dictionary가 전부 행으로 저장되어 있기에), user들이 공통으로 사용하는 공간.
  -library cache: shared sql room + shared pl/sql room
 -redo log buffer: 모든 변경 데이터를 기록함(언제/어떤 data가 어떤 값으로)

* 프로세스
-User, Server, Background Process
-SGA와 DB 사이에 있는 프로세스는 DBWn, LGWR 뿐임.
-LGWR: redolog를 기록하는 프로세스. 1번 파일과 2번 파일이 있을때 1번파일을 다 사용하여 2번파일을 사용할때 전환되는 것을 log switch라고 하고 로그스위치가 발생하면 checkpoint도 발생한다. 2번 파일도 다 차서 1번파일이 3번파일로 되면 1번파일을 참조해 복구할 수 없게 되는데 이걸 막기 위해 Archive를 사용해 1번 파일을 archive log file로 복사하기도 한다. 이때 사용되는 프로세스가 ARCn or ARCH 프로세스.
-SMON: instance를 관리.database 시스템이 잘못되면 instance recovery를 주로 담당
-PMON: 프로세스 관리.프로세스 정리나 복구
-CKPT: data file header와 control 파일에 동시에 최근 변경된 checkpoint 기록, 동기화를 위해 scn 남김
※SCN: commit될때마다 증가되는 번호

-DBWn or DBWR: 만약 SGA의 database buffer cache에서 data file로 기록이 필요할 경우 이벤트가 발생할 때마다 기록한다면 매번 disk I/O가 발생하기에 이걸 줄이고자 data file에 기록이 필요한 buffer cache들을 따로 dirty buffer라고 체크하여 모은 후(dirty list) 한번에 data file에 기록한다. data buffer cache의 상태에 따른 종류: pinned(사용중), free(미사용), clean(곧 age out될 대상)

* 스토리지
-필수파일: Control file(가장 중요한 파일, 구조정보 등 저장), data file, Onlie redo log file
 -parameter file: Instance를 startup하기 위한 파일
 -password file: 원격으로 db에 접속하는 user들의 암호들 저장.
 -backup file: 말그래도 data base의 백업파일
 -archive log file: redo log 파일들을 보조하는 파일
 -Trace file: database에서 일어나는 내부 에러들이 프로세스에 의해 감지되고 덤프됨
 -Alert log file: database에서 발생한 모든 이벤트를 기록. 이 내용중 구체적인 내용이 기록되는 것이 트레이스 파일

-Tablespace: database의 가장 큰 논리적 영역으로 하나의 tbs는 여러개의 datafile로 이루어질 수 있으며 datafile은 하나의 tbs와 연결 가능.
big file tablespace와 small file tablespace가 있으며 주로 쓰는건 small file tablespace.
 -database 생성시 SYSTEM(data dictionary등), SYSAUX(10g부터) 테이블스페이스가 생성됨. 이들은 db가 정상으로 돌아가려면 꼭 online 상태여야 함.

-tablespace>segment>extent>data block
 -table과 index 같은 객체가 다른 객체와 다른 점: data를 저장할 수 있는 공간을 가지는 객체임, 이걸 세그먼트라 부름.
 -segment는 하나 이상의 extent로 구성 가능
 -extent 의 block들은 반드시 연속적이어야 함. extent 자체가 data block들이 연속적으로 모인 것.
 -data block size의 기본값은 8k, DW같은 환경에선 큰 편이 좋고 OLTP 같은 환경에선 작은 편이 좋다. 단, ORACLE에서 제공하는 특징중 하나로 한 DB내에서 다른 db block size를 설정 할 수 있다.

 -OS block: 파일을 제일 작게 쪼개면 os block
 -Extent 와 Data file은 1:1 관계, Tablespace와 Data file은 1:다 관계
 -segment: 특정 논리적 객체에 할당된 extent를 모은 것. 즉, 데이터의 종류에 따른 구분. 테이블에 db를 저장하면 테이블 세그먼트, 인덱스라면 인덱스 세그먼트, 언두라면 언두 세그먼트 등. segment는 동적으로 오라클DB에 의해 할당됨.

Group Functions

Single functions

문자 함수

LOWER: 전부 소문자로
UPPPER: 전부 대문자로
INITCAP: 맨 앞만 대문자로

CONCAT('Hello', 'World')=> HelloWorld
SUBSTR('HelloWorld',1,5)=> Hello
LENGTH('HelloWorld')=> 10 INSTR('HelloWorld', 'W')=> 6
LPAD(salary,10,'*')=> *****24000 =LPAD(' ',10,'*') 식으로도 가능 RPAD(salary, 10, '*')=> 24000*****
REPLACE('JACK and JUE','J','BL')=> BLACK and BLUE
TRIM('H' FROM 'HelloWorld')=> elloWorld =첫글자나 맨 뒤글자만 됨

숫자 함수
ROUND
TRUNC
MOD: 나머지값

날짜 함수
SYSDATE

※date+number = number를 일수로 계산함, 결과는 date
   date-number = 위와 마찬가지, 결과는 date
   date-date = 둘의 차이를 일수로 계산하여 보여줌, 결과는 일수
   date+number/24 = 일수/24이므로 시간임. 시간을 + 계산하여 결과로 date를 보여줌

MONTHS_BETWEEN('date1', 'date2'): 두 날짜사이의 달수를 계산함
ADD_MONTHS('date1', number): date1에서 number만큼의 달수를 더함
NEXT_DAY('date', 'monday'):date의 다음 월요일을 나타냄
LAST_DAY('date'):해당 달의 마지막날을 나타냄
ROUND(SYSDATE,'MONTH'):16일 이후는 반올림하여 다음달로 표시함.
ROUND(SYSDATE,'YEAR'):7월 1일 이후는 반올림하여 다음 해로 표시함.
TRUNC(SYSDATE,'MONTH'):그 달의 1일로 표시함.
TRUNC(SYSDATE,'YEAR'):해당 년도의 1월 1일로 표시함.

변환 함수
TO_CHAR(date,'[fm]format_model')
TO_CHAR(number, 'format_model')
TO_NUMBER(char[, 'format_model'])
TO_DATE(char[, '[fx]format_model'])

NULL 함수
NVL(expr1, expr2): expr1에 있는 NULL 값을 전부 expr2를 리턴, date, number, char, varchar2 다 가능, 주의! expr1과 expr2는 데이터타입이 같아야함. 틀리면 to_char, to_num으로 바꿔서 해야댐.
NVL2(expr1, expr2, expr3): expr1에 있는 값이 널이 아니면 expr2를, 널이면 expr3를 리턴
NULLIF(expr1, expr2): expr1과 expr2가 같으면 null을 리턴하고, 같지 않으면 expr1을 리턴
COALESCE(expr1, expr2, ...exprn): expr1이 null이 아니면 expr1을 리턴, null이면 expr2를 리턴,  expr1과 expr2가 null이면 expr3을 리턴)

IF-THEN-ELSE 식 함수
CASE expr WHEN comparison_expr1 THEN return1
                  [WHEN comparison_expr2 THEN return_expr2
                   WHEN comparison_exprn THEN return_exprn
                   ELSE else_expr]
END

예시)
SELECT last_name, job_id, salary,
  CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
  WHEN 'ST_CLERK' THEN 1.15*salary
  WHEN 'SA_REP' THEN 1.20*salary
  ELSE salary END AS "REVISED_SALARY"
FROM employees;

DECODE(col|expression, search1, result1
                 [, search2, result2,...,]
                 [, default])

예시)
SELECT last_name, job_id, salary,
  DECODE(job_id, 'IT_PROG', 1.10*salary,
  'ST_CLERK', 1.15*salary,
  'SA_REP', 1.20*salary,
  salary)
  AS REVISED_SALARY
FROM employees;

SQL 종류

더이상의 설명은 생략합니다.

ALTER 사용법(작성중)

1. 컬럼 추가
형식
ALTER TABLE table
ADD (column datatype [DEFAULT expr] [, column datatype]...);

예시
ALTER TABLE dep80
ADD (job_id VARCHAR2(9);

2. 컬럼 변경
형식
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr] [, column datatype]...);

예시
ALTER TABLE dep80
MODIFY (job_id VARCHAR2(20));

3. 컬럼 제거
형식
ALTER TABLE table
DROP COLUMN column;

예시
ALTER TABLE dep80
DROP COLUMN job_id;

TABLE 생성 및 관리(작성중)

테이블/열 이름 규칙
-문자로 시작
-1~30자까지 가능
-특수문자는 _, $, #만 가능

* CREATE TABLE
1. CREATE 형식: CREATE TABLE table (column datatype)
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr])

다른 유저의 테이블을 보고 싶을때 schema를 사용
schema란=특정 유저가 소유하고 있는 모든 객체들의 집합을 스키마라 하고 이름을 유저이름으로 함
남의 테이블을 참조할때는 반드시 스키마를 명시해야함
예) user hr이 scott의 emp테이블을 보고 싶을때
select * from scott.emp
다른 유저의 것을 볼때는 스키마 생략 불가

DEFAULT 옵션은 값을 입력하지 않을때 자동으로 들어가는 값
다른 컬럼의 이름, nextval, currval은 못쓴다.
예)
CREATE TABLE hire_dates
(id NUMBER(8),
hire_date DATE DEFAULT SYSDATE);

2. CREATE
CREATE TABLE emp
( emp_id number(6) constraint emp_empid_pk primary key,
f_name varchar2(20),
l_name varchar2(25) constraint emp_lname_nn not null,
email varchar2(25) constraint emp_email_nn not null
constraint emp_email_uk unique,
phone_number varchar2(20),
hire_date date constraint emp_hiredate_nn not null,
job_id varchar2(10) constraint emp_jobid_nn not null,
salary number(8,2) constraint emp_salary_ck check (salary>0),
commission_pct number(2,2),
manager_id number(6),
department_id number(4) constraint emp_depid_fk references departments (department_id));