레도널스의 개발자 되어가기

* 복습

수업 들어가기 전에 할 일.

1. 리눅스 서버 부팅

2. oracle/oracle 로그인

3. 새 터미널 실행

4. $ lsnrctl start → 리스너 프로세스 시작

5. $ sqlplus / as sysdba

   SQL> startup → DB 시작

   SQL> exit

6. $ emctl start dbconsole → EM DB 홈페이지 시작

$ cd $ORACLE_HOME(반드시 대문자로) -> 오라클의 홈 디렉토리(오라클 제품이 설치된 최상위 디렉토리)로 이동

[oracle@localhost db_1]$ pwd

/u01/app/oracle/product/11.2.0/db_1

[oracle@localhost sqlplus]$ cd admin/

[oracle@localhost admin]$ ls

glogin.sql  help  libsqlplus.def  plustrce.sql  pupbld.sql

[oracle@localhost admin]$ vi glogin.sql 

아래 내용 추가하고 저장하고 나가기

SET sqlp "_USER> "

SET linesize 120

3. Configuring the Oracle Network Environment

* 파일 : $ORACLE_HOME/network/admin/listener.ora

- 이름

- Network 주소

  (Host Protocol, Port)

- DB 정보

  (ORACLE_HOME, DB 이름)

* 관리도구 : Net manager

            Net Configuration assistant

            lsnrctl

$ lsnrctl

$ netmgr (넷 매니저 실행)

[oracle@localhost admin]$ ls

listener.ora  samples  shrept.lst

[oracle@localhost admin]$ cat listener.ora 

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1522))

  )

SID_LIST_LISTENER1 =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orcl)

    )

  )

ADR_BASE_LISTENER1 = /u01/app/oracle

생성한 listener1 시작

$ lsnrctl

LSNRCTL> start listener1

종료

LSNRCTL> stop listener1

리스너를 통한 접속

$ sqlplus hr/hr@listener1

※ 리스너의 역할은 오로지 '접속'만 해 주는 것이다.

   접속이 된 후에는 아무런 간섭을 하지 않는다.

실습 시작할 때 리스너 먼저 띄우고, DB를 띄웠는데

DB가 떠 있는 상태에서 리스너를 내렸다가 올리면 DB가 등록되는 시간이 약간 걸린다.

[oracle@localhost admin]$ netmgr

넷 매니저를 실행해서 Profile 설정을 바꿔준다.

[oracle@localhost admin]$ ls

listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora

[oracle@localhost admin]$ cat sqlnet.ora 

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES)

ADR_BASE = /u01/app/oracle

→ 실제 운영중인 DB에서는 sqlnet.ora의 내용이 가장 길다.

  sqlnet.ora는 클라이언트, 서버 양측에 다 존재한다(필요하다).

※ 네트워크 기능을 잘 활용하면 원격 DB에 접속하지 않고도 내용을 볼 수 있다.

   Database link라는 객체가 그런 역할을 한다.

   select * from member@link 이름

ex)

☆ 스샷 남기면서 쓰고픈데 따라가기 바빠 그럴 시간이 없다 ㄷㄷ

레드햇 엔터프라이즈 리눅스 5로 시작

[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-JUN-2018 17:11:29

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date                07-JUN-2018 17:11:30

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))

The listener supports no services

The command completed successfully

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 7 17:12:14 2018

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: hr

Enter password: 

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux Error: 2: No such file or directory

Process ID: 0

Session ID: 0 Serial number: 0

Enter user-name:      

[oracle@localhost ~]$ 

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 7 17:14:09 2018

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  623546368 bytes

Fixed Size                  1338308 bytes

Variable Size             390071356 bytes

Database Buffers          226492416 bytes

Redo Buffers                5644288 bytes

Database mounted.

Database opened.

SQL> conn hr/hr

Connected.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost ~]$ emctl start dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0 

Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.

https://localhost.localdomain:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control .......... started. 

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

Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost.localdomain_orcl/sysman/log 

[oracle@localhost ~]$ 

※ 앞으로 수업 들어가기 전에 할 일.

1. 리눅스 서버 부팅
2. oracle/oracle 로그인
3. 새 터미널 실행
4. $ lsnrctl start → 리스너 프로세스 시작
5. $ sqlplus / as sysdba
   SQL> startup → DB 시작
   SQL> exit
6. $ emctl start dbconsole → EM DB 홈페이지 시작

정규표현식

* 메타 문자

@C:\oraclexe\labs\regexp_tab.sql;

SELECT * FROM t1;

q1.sql >

SELECT fname, lname

FROM t1

WHERE REGEXP_LIKE (fname, '^Ste(v|ph)en$');

q2.sql >

SELECT fname, phone

FROM t1

WHERE REGEXP_LIKE (phone, '...\...\.....\.......');

- . : 정규표현식에서는 문자 하나를 대신함.

- \ : 예외 처리

q3.sql >

SELECT fname, phone

FROM t1

WHERE REGEXP_LIKE (phone, '[0-9]{3}\.[0-9]{2}\.[0-9]{4}\.[0-9]{6}');

- [] : 문자(숫자)의 범위

- {} : 자리수

q4.sql >

SELECT fname, phone, REGEXP_REPLACE ( phone , '\.' , '-' ) new_format

FROM t1;

- REGEXP_REPLACE : 다른 문자로 대체

q5.sql >

SELECT fname, phone,

REGEXP_REPLACE (phone, '(\d{3})\.(\d{3})\.(\d{4})','(\1)-\2-\3') new_phone

FROM t1;

- \d : 숫자(digit). [0-9]와 같은 의미.

- 뒤의 \1, 2, 3 : 그룹을 지정, '\'는 앞의 표현식과 일치하기 위해 사용.

q6.sql >

SELECT fname, addr,

REGEXP_INSTR ( addr, '[[:alpha:]]' ) pos ,

phone,

REGEXP_INSTR ( phone, '[[:alpha:]]') pos

FROM t1;

- [:alpha:] : 범주를 지정(alpha: 알파벳, digit: 숫자)해서 해당되는 부분이 나타나는 자리수를 찍어줌.

              해당되는 것이 없으면 0.

q7.sql >

SELECT fname, addr,

REGEXP_SUBSTR ( addr, ' [^ ]+ ') road

FROM t1;

- REGEXP_SUBSTR : 첫 번째 나오는 문자를 검색한다.

- [^#] : #가 아닌, 부정의 의미.

- + : 한 개 이상.

- ' [^ ]+ ' : 공백과 공백 사이를 추출하라.

q8.sql >

SELECT fname, phone,

REPLACE(REGEXP_SUBSTR(phone,'\.(\d{3})\.'),'.') code

FROM t1;

- 정규표현식도 중첩이 가능하다.

q9.sql >

SELECT fname, addr,

REGEXP_COUNT(addr,'a') cnt

FROM t1;

- REGEXP_COUNT : 표현된 횟수

q11.sql >

SELECT REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 1 ) "Exp1" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 2 ) "Exp2" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 3 ) "Exp3" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 4 ) "Exp4"

FROM dual;

- 순서 : 123 → 45678 → 56 → 78

q10.sql >

SELECT REGEXP_INSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 0, 'i', 2 ) "Position"

FROM dual;

- 자리값

q12.sql >

SELECT

REGEXP_INSTR('ccacctttccctccactcctcacgttctcacctgtaaagcgtccctc

cctcatccccatgcccccttaccctgcagggtagagtaggctagaaaccagagagctccaagc

tccatctgtggagaggtgccatccttgggctgcagagagaggagaatttgccccaaagctgcc

tgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagttttca

ccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagag

gctcttgggtctgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggc

atgtaggggcgtggggatgcgctctgctctgctctcctctcctgaacccctgaaccctctggc

taccccagagcacttagagccag',

'(gtc(tcac)(aaag))',

1, 1, 0, 'i',

1) "Position"

FROM dual;

- 쿼리문은 DNA 유전자 배열

- 특별한 배열을 찾을 때 사용

q14.sql >

SELECT REGEXP_COUNT(

'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcag

ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggag

aatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagtt

ttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtc

tgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctg

ctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag',

'gtc') AS Count

FROM dual;

- 'gtc'가 몇 번 나오는지 count

* 기타 메타 문자들

- + : 1번 이상

- ? : 0번 또는 1번

- * : 0번 이상

- {m, } : m 이상

- {m, n} : m 이상 ~ n 이하

- ( ... ) : 표현식을 묶어줄 때 사용

- $ : 끝

- ^ : 시작

- \ : escape

- \n : 선행하는 표현식의 위치

- \d : 숫자

- [:class:] : class 종류를 지정

- [^:class:] : class가 아닌 것

SQL 끝. DB Admin 시작!

플래시백

conn / as sysdba

SELECT * FROM v$option;

WHERE parameter like 'Flashback%';  ← 플래시백 유료 옵션 확인하기

* 플래시백 실습

conn hr/hr

UPDATE employees

SET salary = salary*1.5

WHERE employee_id = 141;

commit;

SELECT salary FROM employees

WHERE employee_id = 141;

=> 롤백은 안 되지만, undo 파일에 남아있을 것임.

* 플래시백 쿼리

SELECT employee_id, salary FROM employees

as of timestamp (systimestamp-5(24*60))

WHERE employee_id = 141;

* 플래시백 버전 쿼리

col versions_starttime for a25

col versions_endtime for a25

set line 120

SELECT versions_starttime, versions_endtime, salary

FROM employees

versions between timestamp minavalue and maxvalue

WHERE employee_id = 141;

Time Zones

* 시간대

표준 시 : 그리니치(Greenwich) 천문대 시간

우리나라 : 표준 시 + 9

- sysdate : date type을 반환

- systimestamp : timestamp(date/time) type을 반환

SELECT sysdate, systimestamp FROM dual;

-> 서버 시간

SELECT current_date, current_timestamp, localtimestamp FROM dual;

-> 내 시간

ALTER SESSION set time_zone = '-07:00';

-> 나의 세션 설정 바꾸기(미국 시간)

SELECT current_date, current_timestamp, localtimestamp FROM dual;

-> 다시 확인해보면 날짜가 바뀌어 있다.

※ 글로벌 환경에서는 시간대 조정이 필요할 수 있다.

* 시간 데이터 타입 확인하기

실습)

CREATE TABLE timetest1

(a date,

 b timestamp,

 c timestamp with time zone,

 d timestamp with local time zone);

INSERT INTO timetest1

VALUES(sysdate, sysdate, sysdate, sysdate);

SELECT * FROM timetest1;

>> local time zone : 클라이언트의 시간대로 바꿔준다.

>> 시간 정보에서 초(sec)의 정밀도는 기본(default)값이 6, 최대 9까지 지정 가능(Max 11byte)

SELECT * FROM tab;

※ DB에 있는 테이블 보기

* 데이터 타입 변경하기

CREATE TABLE dept50

AS

SELECT employee_id, last_name, hire_date, job_id

FROM employees

WHERE department_id = 50;

SELECT * FROM dept50;

ALTER TABLE dept50

MODIFY hire_date TIMESTAMP(2);  ← 바로 변경 가능

ALTER TABLE dept50

MODIFY hire_date TIMESTAMP(2) with time zone;  ← with time zone 속성은 테이블에 값이 들어있으면 불가

* Interval : 간격

CREATE TABLE timetest2

(a interval year to month,

 b interval day to second);

INSERT INTO timetest2

VALUES(interval '5-10' year to month,  ← 5년 10개월

       interval '90 12:30:30' day to second);  ← 90일 12시간 30분 30초

SELECT systimestamp+a, systimestamp+b

FROM dual, timetest2;

* extract : 날짜 함수 (TO_CHAR 등으로 변환하지 않고 날짜 그대로 추출)

* TO_YMINTERVAL, TO_DSINTERVAL

* Daylight Saving Time (DST, 일광시간절약제, 서머타임)

* 고급 서브쿼리

>> Multiple-column Subquery

SELECT employee_id, manager_id, department_id

FROM empl_demo

WHERE (manager_id, department_id) IN (SELECT manager_id, department_id

                                      FROM empl_demo

                                      WHERE first_name = 'John')

                                      AND first_name <> 'John';

이 문장과 아래의 문장은 다르다.

SELECT employee_id, manager_id, department_id

FROM empl_demo

WHERE manager_id IN

                 (SELECT manager_id

                  FROM empl_demo

                  WHERE first_name = 'John')

AND department_id IN

                 (SELECT department_id

                  FROM empl_demo

                  WHERE first_name = 'John')

AND first_name <> 'John';

※ IN이 AND보다 우선순위가 높다.

* Scalar Subqueries: Examples

SELECT employee_id, last_name,

       (CASE

        WHEN department_id = (SELECT department_id FROM departments

                              WHERE location_id = 1800)

        THEN 'Canada' ELSE 'USA' END) location

FROM employees;

SELECT employee_id, last_name

FROM employees e

ORDER BY (SELECT department_name

FROM departments d

WHERE e.department_id = d.department_id);

* 상호 관련(Correlated) 서브쿼리

- 메인 쿼리의 행이 다 떨어질 때까지 서브쿼리를 실행하여 비교하는 방법으로 수행됨.

- 구조가 좋지 않은 대표적인 SQL 중 하나.

ex)

SELECT last_name, salary, department_id

FROM employees outer_table

WHERE salary > (SELECT AVG(salary)

                FROM employees inner_table

                WHERE inner_table.department_id = outer_table.department_id);

-> 서브쿼리가 여러 번 실행된다.

※ 성능을 좋게 하는 SQL 문장 튜닝의 예 >

SELECT e.employee_id, e.last_name, e.salary, b.avg_sal, e.department_id

FROM employees e JOIN (SELECT department_id, AVG(salary) avg_sal

                       FROM employees

                       GROUP BY department_id) b

ON (e.department_id = b.department_id)

WHERE e.salary > b.avg_sal;

-> 조건을 FROM절에 작성.

* EXISTS 연산자 : 상호 관련 서브쿼리

- 조건에 맞는 것이 발견되면 추가로 더 이상 찾지 않는다.

- 성능을 개선할 수 있음.

ex)

SELECT employee_id, last_name, job_id, department_id

FROM employees outer

WHERE EXISTS (SELECT 'X'

              FROM employees

              WHERE manager_id = outer.employee_id);

* Correlated UPDATE : 상호 관련 업데이트

실습)

SELECT * FROM dept50;

ALTER TABLE dept50

ADD salary NUMBER(10);  ← 행을 추가한 후,

UPDATE dept50 d

SET salary = (SELECT salary FROM employees e

              WHERE e.employee_id = d.employee_id);

← 데이터를 기존에 있던 테이블과 똑같이 넣고 싶을 때 사용.

* Correlated DELETE : 상호 관련 삭제

* WITH문 : 인라인뷰를 확장한 기능.

ex)

WITH

dept_costs AS (SELECT d.department_name, SUM(e.salary) AS dept_total

               FROM employees e JOIN departments d

               ON e.department_id = d.department_id

               GROUP BY d.department_name),

avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg

             FROM dept_costs)

SELECT * FROM dept_costs

WHERE dept_total > (SELECT dept_avg

                    FROM avg_cost)

ORDER BY department_name;

* Constraint

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');

ALTER TABLE dept

DROP primary key;  <- 삭제가 되지 않는다.

ALTER TABLE dept

DROP primary key CASCADE;  <- 옵션을 추가해 준다.

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 다시 확인해 본다.

ALTER TABLE dept

ADD primary key(department_id);

ALTER TABLE emp

ADD CONSTRAINT emp_deptid_fk foreign key(department_id)

REFERENCES departments(department_id);  <- 제약 조건을 다시 만들고,

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 다시 확인해 보면, enabled 상태.

INSERT INTO dept

VALUES(10, 'CS', null, null);

UPDATE emp

SET department_id = 300

WHERE employee_id = 176;  <- 두 문장 모두 오류 발생

ALTER TABLE dept

DISABLE primary key;

ALTER TABLE emp

DISABLE CONSTRAINT emp_deptid_fk;  <- 제약 조건을 끌 수 있다.

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 확인해 보면, 두 제약 조건은 disable 상태.

제약 조건을 disable하고, 테이블에 새로운 데이터를 입력 후 enable할 때 문제가 있으면 enable되지 않는다.

문제가 있는 해당 데이터(중복 등)를 삭제 후 enable 해주어야 한다.

DELETE FROM dept

WHERE department_name = 'CS';

UPDATE emp

SET department_id = 30

WHERE employee_id = 176;  <- 데이터 갱신 후

ALTER TABLE dept

ENABLE primary key;

ALTER TABLE emp

ENABLE CONSTRAINT emp_deptid_fk;  <- 제약 조건 enable

* Cascading Constraints

연쇄 제약 조건

@c:\oraclexe\labs\cre_test1.sql  <- TEST1 테이블 생성

DESC test1;

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name = 'TEST1';

ALTER TABLE test1

DROP COLUMN col2;  <- 삭제가 된다.

ALTER TABLE test1

DROP COLUMN col1;  <- 삭제가 안 된다.

ALTER TABLE test1

DROP COLUMN col1

CASCADE CONSTRAINTS;  <- 옵션 추가하여 삭제.

DROP TABLE test1;  <- 테스트 끝났으니 테이블 삭제.

* DROP TABLE ... PURGE : 영구 삭제

DROP TABLE emp;

SELECT * FROM tab;

SHOW RECYCLEBIN;  <- 휴지통 보기

FLASHBACK TABLE emp TO BEFORE DROP;  <- 복원

DESC emp;

DROP TABLE emp PURGE;  <- 완전 삭제, FLASHBACK 불가.

PURGE RECYCLEBIN;  <- 휴지통 비우기

※ 오라클의 휴지통 특징

- 파일 이동이 없다. 따라서, 속도가 빠르다.

* Temporary Tables : 임시 테이블

- 장바구니 개념

CREATE GLOBAL TEMPORARY TABLE emp_temp1

ON COMMIT DELETE ROWS

AS

SELECT employee_id, salary, department_id

FROM employees;

CREATE GLOBAL TEMPORARY TABLE emp_temp2

ON COMMIT PRESERVE ROWS

AS

SELECT employee_id, salary, department_id

FROM employees;

< 재접속 >

SELECT * FROM emp_temp1;

SELECT * FROM emp_temp2;  <- 테이블에 아무것도 없다.

INSERT INTO emp_temp1

SELECT employee_id, salary, department_id

FROM employees

WHERE department_id = 50;

INSERT INTO emp_temp2

SELECT employee_id, salary, department_id

FROM employees

WHERE department_id = 60;  <- 데이터 삽입

SELECT * FROM emp_temp1;

SELECT * FROM emp_temp2;

COMMIT;

SELECT * FROM emp_temp1;  <- 테이블이 비워진다.

SELECT * FROM emp_temp2;  <- 테이블의 내용이 유지된다.

※ 임시 테이블은 세션 범위를 벗어나지 못한다.

* External Tables : 외부 테이블

- 자주 사용하지는 않지만, 버리지는 못하는 자료를 빼두는 용도로 사용

  ex) 은행 거래내역 등

- Oracle 9i부터 사용

- DML 불가

- 부득이하게 수정이 필요하면, 파일을 직접 수정

실습)

• SQL*Plus에서 권한 주기

> conn / as sysdba

Connected.

SYS> CREATE DIRECTORY dir_1

AS 'c:\oraclexe\test';

Directory created.

SYS> GRANT read, write ON DIRECTORY dir_1 TO hr;

Grant succeeded.

• SQL문 실행

@C:\oraclexe\labs\cre_empxt.sql;

DESC empxt;

SELECT * FROM empxt;

Manipulating Large Data Sets

생략

★ Oracle의 Flashback 기술에 대하여

 - 빠르게 취소하는 기능(commit이 된 것)

 - 과거에는 못 했는데 가능하도록 추가된 기능.

• Flashback Query - 9i ver.~             ┐
• Flashback Versions Query               │→ Undo data 읽기 방식(영구적이지 않음)
• Flashback Transaction Query - Undo SQL ┘
• Flashback Table ($)
• Flashback Data Archive ($) - 11g ver.~
• Flashback Undrop - Recyclebin
• Flashback Database ($)

※ ($) 표시는 유료 옵션

* Synonym : 동의어

>> 먼저, 권한 주기

Command Line에서 :

SQL> conn / as sysdba

Connected.

SQL> GRANT create synonym TO hr;

Grant succeeded.

>> 돌아와서,

CREATE SYNONYM e

FOR employees;

SELECT * FROM e;

>> Synonym 삭제

DROP SYNONYM e;

SQL 1 끝.

SQL 2 시작.

Controlling User Access

• System 권한 : DB System에 ~를 할 수 있는 권한.
  

- CREATE TABLE

- CREATE VIEW

- CREATE SYNONYM

• Object 권한 : 계정 간의, ...의 ~를 Access할 수 있는 권한.

- SELECT 

- UPDATE

* System 권한

• 100개가 넘는다.

  Access    ON Schema.object

• Select    ON scott.emp
• Insert
• Update
• Delete
• Alter
• Index
• Reference

• Role 생성하기 :

• Role에 권한 추가 :

• Role에서 권한 제거 :

• 현재 사용자의 권한 확인하기 :

• 암호 바꾸기

• CONNECT
• RESOURCE
• DBA

>> ON DELETE CASCADE

>> ON DELETE SET NULL

ex) 쇼핑몰에서 물건을 구매한 이력이 있는 회원이 탈퇴했을 때, 구매 이력은 남겨놓고 회원 정보만 삭제할 때 사용.

* 테이블 만들기에서 서브쿼리 사용

CREATE TABLE dept80

AS

SELECT employee_id, last_name, salary*12 ANNSAL, hire_date

FROM employees

WHERE department_id = 80;

※ 계산식 컬럼을 만들 때 별칭은 필수

* ALTER TABLE

>> READ ONLY / READ WRITE

* Dropping a Table

>> DROP TABLE dept80;

Creating Other Schema Objects

* What Is a View? : 뷰란?

한 테이블에서 내용을 제한적으로 보여주는 테이블

물리적으로 저장되지 않음.

* Creating a View

>> 뷰를 생성하기 전에, 생성할 수 있는 권한을 부여해준다.

SQL> conn / as sysdba

Connected.

SQL> GRANT create view TO hr;

Grant succeeded.

>> 권한 부여 후, 뷰를 생성한다.

CREATE VIEW empvu80

AS

SELECT employee_id, last_name, salary*12 ann_sal, hire_date

FROM employees

WHERE department_id = 80;

* 뷰 조회하기

SELECT view_name, text FROM user_views;

* 뷰 수정하기

>> 오라클에서는 CREATE OR REPLACE VIEW 문장을 사용.

   다른 DBMS에서는 ALTER TABLE를 사용하기도 함.

CREATE OR REPLACE VIEW empvu80

AS

SELECT employee_id, last_name, salary, hire_date

FROM employees

WHERE department_id = 80;

* View에서의 DML 명령어

>> DELETE → 심플 뷰에서는 대부분 가능

- 제약이 가장 적음.

- 무조건 다 되는 것은 아님.

ex)

DELETE FROM empvu80

WHERE employee_id = 176;

>> UPDATE → 대부분 가능

- 그 외 불가능한 조건은 DELETE 명령과 비슷함.

- 표현식으로 된 컬럼은 불가능.

ex)

UPDATE empvu80

SET salary=salary*1.2;

>> INSERT → 불가능

- 제약 조건 때문에 불가.

* 뷰 삭제

>> DROP VIEW view;

※ 뷰 만들기 실습

* Sequences : 시퀀스

시퀀스란?

- UNIQUE 값을 생성해주는 오라클 객체.

- 보통 PRIMARY KEY 값을 생성하기 위해 사용.

- 메모리에 Caching 되었을 때 시퀀스 값의 액세스 효율이 증가한다.

- 시퀀스는 테이블과는 독립적으로 저장되고 생성된다.

- 한 번 사용한 번호를 재활용하지 않는다.

CREATE SEQUENCE dept_deptid_seq

INCREMENT BY 10

START WITH 200

NOCACHE

NOCYCLE;

SELECT * FROM departments;

INSERT INTO departments

VALUES(dept_deptid_seq.nextval, 'Support', null, 2500);  ← 다음에 사용할 시퀀스 번호 확인

ROLLBACK;

INSERT INTO departments

VALUES(dept_deptid_seq.nextval, 'Customer Support', null, 2500);

SELECT * FROM departments;

SELECT dept_deptid_seq.currval FROM dual;  ← 현재까지 사용한 시퀀스 번호 확인

CREATE SEQUENCE emp_empid_seq

INCREMENT BY 1

START WITH 415;

INSERT INTO employees

VALUES(emp_empid_seq.nextval, 'Jason', 'Kim', 'JKIM', '053.1236.1276', sysdate, 'IT_PROG', 6700, null, null, 60);

SELECT * FROM employees;

COMMIT;

>> 시퀀스 정보 보기

SELECT sequence_name, last_number

FROM user_sequences;

>> 시퀀스 수정하기

ALTER SEQUENCE

>> 시퀀스 삭제하기

DROP SEQUENCE

* Indexes : 색인

검색 성능을 향상시키기 위한 것.

>> Full Table Scan 방식과의 차이

=> Index 검색 방식

- ID와 Rowid(Pointer)를 함께 저장

- 밸런스 트리 구조

  Root - Branch - Leaf

- 자료가 많을 수록 효과가 좋음.

>> 인덱스가 자동으로 만들어지는 경우

- PRIMARY KEY 또는 UNIQUE : 중복 체크용

- 제약 조건 조회

SELECT table_name, constraint_name, constraint_type

FROM user_constraints

WHERE constraint_type IN ('U', 'P')

ORDER BY 1;

- 유니크 인덱스 조회

SELECT table_name, index_name, uniqueness

FROM user_indexes

ORDER BY 1;

- 자동으로 만들어지는 인덱스는 삭제 불가

>> 수동으로 인덱스 만들기

CREATE INDEX

※ BITMAP INDEX(비트맵 인덱스) : 0 or 1

* SQL 자동 추적

SQL> conn / as sysdba

Connected.

SQL> GRANT SELECT ANY DICTIONARY to HR;

Grant succeeded.

-> 권한 부여 후,

SELECT * FROM employees

WHERE employee_id = 176;

※ SQL Development의 자동 추적 기능(F6 키) → cost가 더 높을수록 Index를 만들어 주면 검색에 유리하다.

>> Sequence 만들기 실습

CREATE TABLE bigemp

AS

SELECT * FROM employees;

INSERT INTO bigemp

SELECT * FROM bigemp;

commit;

SELECT COUNT(*) FROM bigemp;

CREATE SEQUENCE bigemp_id_seq

INCREMENT BY 1

START WITH 1

NOCYCLE

NOCACHE;

UPDATE bigemp

SET employee_id = bigemp_id_seq.NEXTVAL;

>> Index 만들기 실습

CREATE INDEX bigemp_id_ix  

ON bigemp(employee_id);  ← employee_id의 index를 만듦.

CREATE INDEX bigemp_sal_ix

ON bigemp(salary);  ← salary의 index를 만듦.

SELECT * FROM bigemp

WHERE salary = 24000;

SELECT * FROM bigemp

WHERE salary*12 = 288000;

* salary의 index를 만들었으나, salary를 이용한 연산식을 조회할 때는 사용하지 않는다.

- 해결 방법 : 함수 기반 index를 만든다.

CREATE INDEX bigemp_annsal_ix

ON bigemp(salary*12);

SELECT * FROM bigemp

WHERE salary*12 = 288000;

* TRUNCATE : WHERE절 없는 DELETE 명령처럼 모든 행을 삭제

  - 차이점은 DDL이기 때문에 자동 COMMIT.

SELECT * FROM sales_reps;

DELETE FROM sales_reps;

ROLLBACK;

※ DELETE는 ROLLBACK이 가능하다.

TRUNCATE TABLE sales_reps;

ROLLBACK;

※ TRUNCATE는 ROLLBACK이 불가능하다.

=> Undo를 사용하지 않기 때문에, 속도도 매우 빠르다.

* 자동 커밋/롤백

* SAVEPOINT는 ROLLBACK 시 사용, COMMIT하면 SAVEPOINT는 날아감.

* C:\oraclexe\app\oracle\oradata\XE 내의 파일 정보

* 데이터베이스는 아파트(또는 다세대 주택)에 비유될 수 있음.

* CREATE TABLE : 테이블 생성하기

실습)

CREATE TABLE member

(id        NUMBER(4),

 name      VARCHAR2(20),

 phone_no  VARCHAR2(11),

 email     VARCHAR2(40),

 join_date DATE);

DESC member;

INSERT INTO member

VALUES(1001, 'Jisang', '01012345678', 'wona23@gmail.com', sysdate);

SELECT * FROM member;

* 오라클에서 사용 가능한 데이터 타입

※ 이진 데이터(RAW, LONGRAW)는 용량 때문에 사용 시 주의

※ Bfile은 응용 프로그램에서 많이 쓰이는 형태

※ LOB 타입 이전에 사용된 LONG 타입(LONG, LONGRAW)의 데이터는 용량이 커서 테이블을 따로 저장해서 사용했었음.

- 테이블을 따로 만들고, 필요할 때 Join해서 사용.

※ LOB 타입은 링크 정보만 저장, 실제 데이터는 별도의 저장소에 저장하는 방식.

- Indexing 기법

* ROWID

ex)

SELECT employee_id, last_name, rowid

FROM employees;

>> ROWID 의미

-  6        3          6           3

  객체 | File No | Block 번호  | 행 번호

- ROWID는 일종의 Pseudo Column

- Primary Key보다 더 정확하게 중복 데이터를 식별할 수 있음

* DEFAULT Option : 기본 옵션

ex)

CREATE TABLE dept

       (deptno      NUMBER(2),

        dname       VARCHAR2(14),

        loc         VARCHAR2(13),

        create_date DATE DEFAULT sysdate);

INSERT INTO dept(deptno, dname)

VALUES(10, '관리부');

SELECT * FROM dept;

* Including Constraints : 제약 조건

- NOT NULL

- UNIQUE

>> NULL도 가능(NULL은 각각 UNIQUE하므로)

- PRIMARY KEY(PK)

>> 비교적 짧고, 숫자만으로 된 것이 가장 좋음. (주민등록번호보다는 학번)

- FOREIGN KEY(FK)

- CHECK

>> Foreign Key와 Check는 남발하면 그 자체로 제약 조건이 될 수 있어서, 최근에는 응용 프로그램 코드로 대체되는 경우가 많음. (리스트에서 선택하는 방식)

※ Bold는 많이 사용

실습)

CREATE TABLE member

(id NUMBER(4) Primary Key,   ← Column Level 제약 조건

name VARCHAR2(20) not null,  ← Column Level 제약 조건

phone_no VARCHAR2(11) CONSTRAINT member_phone_uk UNIQUE,  ← Column Level 제약 조건

email VARCHAR2(40),

join_date DATE,

CONSTRAINT member_email_uk UNIQUE(email)  ← Table Level 제약 조건

);

DESC member;

=> 제약 조건이 나타나지 않는다.

※ 제약 조건 확인 방법 >>

실습)

SELECT table_name, constraint_name, constraint_type

FROM user_constraints

WHERE table_name = 'MEMBER';  ← table_name은 반드시 대문자로 작성해야 함.