단 1%의 가능성이라도 도전하자!!

첫번째 실습 -인트로->WS1-1-ch10.FGA LAB

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900      <--900초동안 유지한다.
undo_tablespace                      string      UNDOTBS1 <--지정된 놈만 사용가능

SQL> ed
Wrote file afiedt.buf

  1  create undo tablespace undotbs2  --undo테이블스페이스를 생성한다.
  2* datafile '/u01/oradata/orcl/undotbs02.dbf' size 50m
SQL> /

Tablespace created.

> ed
Wrote file afiedt.buf

  1  select a.name, b.status
  2  from v$rollname a, v$rollstat b
  3  where a.name in (select segment_name
  4  from dba_segments
  5  where tablespace_name = 'UNDOTBS1')
  6* AND a.usn = b.usn
  7  /
  --활성화된 트랜잭션 조회
NAME                           STATUS
------------------------------ ---------------
_SYSSMU1$                      ONLINE
_SYSSMU2$                      ONLINE
_SYSSMU3$                      ONLINE
_SYSSMU4$                      ONLINE
_SYSSMU5$                      ONLINE
_SYSSMU6$                      ONLINE
_SYSSMU7$                      ONLINE
_SYSSMU8$                      ONLINE
_SYSSMU9$                      ONLINE
_SYSSMU10$                     ONLINE

10 rows selected.

9. Managing Undo Data - 언제 생성되는가?  DML실행시 생성되며, Undo Tablespace 안에 Undo

Segment에
저장된다.  Undo Data- 읽기 일관성을 보장한다.

DML실행시 Undo data 파일이 생성된다.

Undo Data
 - 변경되기전의 data 원본데이터의 복사본이다.
 - 모든 트랜잭션의 변경을 캡쳐(스냅샷)한다.
 - 롤백, 읽기 일관성, 복구 시 사용
 - flshback queries를 이용해 commit된 데이터를 살릴수있다.(단 900초안에)

‌instance recover - Undo data Rollback
   - Redo data 복구

Transactions and Undo Data
트랜잭션 - 논리적인 작업의 단위
각각의 트랜잭션 하나의 세그먼트에 저장될 수 있다.
Undo segment가 가득찰경우 동적으로 확장이 가능하다. (최대 테이블스페이스 범위까지)

Undo 저장정보는 undo Tablespace에 저장된다.
-  일반세그먼트 에서는 사용될수 없다.(Are used only for undo segements)
-  특정한 복구 수행시 고려되어야 함
-  하나의 인스턴스와 연관되어 있다.
-  하나의 Undo tablespace만 쓸 수 있다.

Undo segment는 Sys가 소유하고있다.

   Undo Data     VS    Redo Data
 
   Undo   Redo
-----------------------------------------------
Record of     데이터를 다시 돌림 복구하는 정보 저장
-----------------------------------------------
Used   for    롤백   DB변경사항을 재적용함
-----------------------------------------------
Stored in     Undo Segments  Redo log files
-----------------------------------------------
Protects      읽기일관성을  사용자와 환경적인 원인에서
against  보호한다.  발견된 loss를 막기위해서
-----------------------------------------------

Monitoring Undo
- Undo tablespace 충분한 공간이 있는지 확인하라
  만약 50GB 테이블을 Delete 구문으로 삭제할 경우 어떤 현상이 발생할 것인가?
  SQL>Delete from reallybigtable ;

 ORA-01650 : unable to extend rollback segment
 50GB의 여유공간을 가진 테이블 스페이스가 존재하지 않으므로 에러가 발생한다.

Snapshot - 과거 특정한 시점의 data

특정한 시점에서 Undo Segment에 새로운 DML이 무한정 유입될때의
2가지 방법으로 대처할수 있는데 한가지는 DML의 유입을 막는것이고 다른 한가지는 기존의 영역

에 덮어쓰게된다. 이때 활성화된 트랜잭션이 존재하는 위치에 덮어쓰기가 될경우 Snapshot to 

olderror 가 발생하게된다.

Undo Data의 관리

-적정한 크기의 테이블스페이스를 유지한다.
- 큰 트랜잭션은 주지적으로 commit을 보증해라
 Snapshot too old errors
 - 적정한 주기마다 처리를 한다.
 - undo retention 보증하는것을 고려해라

Automatic undo management :
 UNDO_MANAGEMENT=AUTO
 UNDO_TABLESPACE=UNDOTBS1

-  Snapshot too old errors를 방지하라.

Guaranteeing Undo Retention

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

일반 테이블 스페이스에서는 RETENTION GUARANTEE;구문은 사용이 불가능하다.

Chpater 10. Implementing Oracle Database Security

Separation of Responsibilities  책임의분리(견제와 균형의 원리를 지키기 위한 하나의 수단)

- 사용자가 부여한 DBA 권한은 신뢰되어야 한다.
- DBA의 권한이 공유되어야 한다.
- 계정을 절대 공유해서 쓰지말것
- DBA와 SYSTEM 관리자는 서로다른 사람의 부여해라.
- 운영자와 DBA도 책임을 분리할 수 있다.

Database security

confidentiality (기밀성)
기밀성을 보장해야 한다
 -데이터와 서비의스 접근을 제한을 둔다.
 - 인증받은 사용자많이 접속할 수 있다.
 - 의심가는 사용자를 모니터링 한다.

Principle of Least Privilege
최소 권한의 원칙

- 요구되는 소프트웨어만 설치한다.
- 요구되는 서비스이외의 것은 설치하지 않는다.
- 액세스가 필요한 사람만 권한을 준다.
- root계정이나 administrator 계정을 사용을 제한한다.
- sysdba나 sysoper 같은 최고관리자의 사용을 적당하게 제한한다.
- 필요한 부분에만 접근해서 사용할 수 있게한다.

Applying the Principle of Least Privilege

07_DICTIONARY_ACCESSIBILITY=FALSE -Data Dictionary 에 접근 제한두기

Revoke unnecessary privileges from PUBLIC:

REVOKE EXECUTE ON UTL_SMTP, UTL_TCP, UTL_HTTP, UTL_FILE FROM PUBLIC;
- 필요한 사람에게만 관리자 권한을 준다.
- 디렉토리에 제한을 둔다.

Monitoring for Suspicious Activity

 - Mandatory auditing - 필수적인감시 , alert.log ?/rdbms/audit
 - 표준 검사 - 문장검사, 권한검사, 객체검사 (AUDIT_TRAIL)
 - Value-based auditing - App., Triggers - 값이 변하는 검사
 - Fine - grained auditing( FGA) - 상세검사, 조건과 정책을 세울 수 있다.
 - DBA auditing - SYSDBA 검사(권한에 대한에 대한남용을 방지)

 
 AUDIT_TRAIL은 $ORACLE_HOME/rdbms/audit 에 저장되있다.
 잘못관리된 AUDIT_TRAIL은 성능이 저하에 영향을 준다. 반드시 감사로그는 감사 담당자 만 접근이 허용되어야 한다.

AUDIT Statement [문장|권한|객체] 검사
[BY USER [.user ] ]  미지정시  모든 유저 (ALL)
[BY {SESSION | ACESSION}] - 미지정지 BOTH
[WHENEVER [NOT] SUCCESSFULL] - BOTH

성공한 경우 에만 감사 , 실패한경우에만 감사, 생략하면 성공, 실패 모두 감사

‌

Index -  검색속도의 향상, 조건으로 자주 사용되는 컬럼에 사용된다.
 -  생성시간이 길다. 자동으로 생성된다. (PK, UK 기능을 강화)
 -  인덱스는 RowID를 가지고있다.(오라클은 자동으로 Rowid(의사결정컬럼)가 부여된다.)
 -  인덱스를 사용할 경우 직접 경로를 찾아서 액세스 하므로 검색속도가 빨라진다.

associated - 인덱스는 독립적 저장공간이며 테이블과는 종속적인 관계이다.

인덱스에 Type에는 2가지가 있다. B-tree ?index, bitmap index

   B트리   Bitmap
---------------------------
분포도   High    Row (중복 Data가 많으므로 유리하다.분포도가 높다.)
---------------------------
OR   불리    유리
---------------------------
Update   유리    불리
---------------------------
환경   OLTP    OSS(D/W)
---------------------------

DML 사용시 테이블과 인덱스의 비교

 Table   index
------------------------------
insert  Random저장 위치를 찾아서 저장
------------------------------
delete  삭제  delete Marking(비효율적 데이터)을 함
------------------------------
update 삭제->추가 delete Marking

인덱스의 Options

인덱스의 값은 unique하다. 중복을 배제한다.
인덱스의 값은 정렬 되어있다.. default ASC, 인덱스 생성시 nosort 옵션을 사용할수 있다.
값이 지속적으로 커지는 경우 역방향으로 쓴다.
하나이상의 컬럼을 대상으로하는 조합(composite)인덱스를 생성할 수 있다.
Function 인덱스 특정값을 리턴한다.
인덱스는 중복된 키값을 제거 한다.

CREATE [BITMAP] INDEX name_ix ON employess (last_name [ASC], first_name[ASC]);
조합인덱스의 형식
조합인덱스는 Not unique 인덱스 이다.
조합인덱스를 사용할경우 실행시간을 줄일수 있다.(중복된 값을 제거하므로)

뷰View (가상테이블)

View를 생성하는 서브쿼리의 실행순서
View access -> Server Process-> Dictionary Cache -> user_views.text -> view를 생성한

subquery > 실행

CREATE [OR REPLACE] VIEW V AS SELECT LOCATION_ID, COUNTRY_NAME FROM locations l, countries

c WHERE l.country_id=c.country_id AND c.country_id in ('AU', 'BR');

시퀸스(Sequences)

유일한 값(Unique number)을 생성해주는 오라클 객체이다.
시퀸스를 생성하게 되면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성하게 된다.
기본키를 생성하기 위해 쓰는것이 일반적이다. 메모리에 cache 되었을때 sequence값의 액세스 효

율이 증가한다. 시퀸스는 테이블과는 독립적으로 저장되고 생성된다.
따라서 하나의 시퀸스를 여러 테이블에 쓸 수 있다.

CREATE SEQUENCE seq_test(시퀸스명)
start with 시퀸스의 시작값
increment by 증가값
maxvalue 증가가능한 최대수 | nomaxvlue
minvalue 시퀸스의 최소값   | nominvalue

CYCLE | NO CYCLE
CACHE | NOCACHE

시퀸스를 작성하면 자동으로 정수값이 생성되며 빠른 액세스 가능하다.
시퀸스는 독립적인 공유 개체이다.

seq_test.NEXTVAL
seq_test.CURVAL

Temporary Table - 값을임시로 저장하는 테이블

세션이나 트랜잭션이 끝났을때 자동적으로 사라진다.
각각의 세션에 독립적인 저장공간이다.

Data Dictionary - 객체에 대한 정보의 집합, 정보조회용뷰

SELECT * FROM dictionary; 전체 사전 조회

SELECT table_name, tablespace_name from user_tables;
select sequence_name, min_value, max_value, increment_by from all_sequences
select username, account_status from dba_users;
describe dba_indexes;

8장 Managing Data and Concurrency

COMMIT의 구조적인 정의 ->  Redo log buffer 에 내용을 Redo log File에 저장한다.            

         
       Redo log buffer
       |
             LGWR
       |
       Redo   log File

PL/SQL
SQL의 확장한 4세데 언어있다. (자바,C++은 3세대 언어이다.)
서로 다른 OS간의 사용이 가능하다.(이식이 가능하다.Portability)
PL/SQL을 사용할 경우 대량의 데이터와 네트워크 상의 트래픽이 감소된다.

실습예제)
// 특정 사원의 급여를 일정 %센트 인상/인하하는 프로시저를 작성하시오

SQL> conn scott/tiger 에서만 실습할것
Connected.
SQL> create or replace procedure Update_test
  2  (v_empno IN emp.empno%TYPE, --급여를 수정한 사원의 사번
  3  v_rate IN NUMBER)   --급여 인상/인하율
  4  is
  5  v_emp emp%ROWTYPE;
  6  begin
  7     dbms_OUTPUT.ENABLE;
  8     UPDATE emp
  9  SET sal=sal+(sal * (v_rate/100)) --급여를 계산
 10     where empno = v_empno;
 11  DBMS_OUTPUT.PUT_LINE('*** Data Modify Successful ***');
 12     SELECT empno, ename, sal
 13     INTO v_emp.empno, v_emp.ename, v_emp.sal
 14     FROM emp WHERE empno = v_empno;
 15  DBMS_OUTPUT.PUT_LINE(' *** Modify Check ***');
 16  DBMS_OUTPUT.PUT_LINE('1.Emp_no: ' || v_emp.empno );
 17  DBMS_OUTPUT.PUT_LINE('2.Emp_name :' || v_emp.ename);
 18  DBMS_OUTPUT.PUT_LINE('3.Emp_salary:' || v_emp.sal);
 19  END;
 20
 21  /

Procedure created.

SQL> SET SERVEROUTPUT ON; --  해줘야 DBMS_OUTPUT.PUT_LINE사용할 수 있다.
SQL> execute Update_Test(7900, -10);
*** Data Modify Successful ***
*** Modify Check ***
1.Emp_no: 7900
2.Emp_name :JAMES
3.Emp_salary:855

PL/SQL procedure successfully completed.

SQL>

변경 이전 급여를 출력하게 PL/SQL을 수정해보자
create or replace procedure Update_test
( v_empno in emp.empno%TYPE,
  v_rate in number)
is
v_emp emp%ROWTYPE;
v_sal emp.sal%TYPE;
begin
        DBMS_OUTPUT.ENABLE;
        SELECT  sal into v_sal from emp where empno=v_empno;
UPDATE emp
SET sal=sal+(sal * (v_rate/100))
where empno = v_empno;
DBMS_OUTPUT.PUT_LINE('*** Data Modify Successful ***');
SELECT empno, ename, sal INTO v_emp.empno, v_emp.ename, v_emp.sal FROM
emp WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE(' *** Modify Check ***');
DBMS_OUTPUT.PUT_LINE('1. Emp_no:' || v_emp.empno);
DBMS_OUTPUT.PUT_LINE('2. Emp_name:' || v_emp.ename);
DBMS_OUTPUT.PUT_LINE('3. PreviewEmp_Salary:' || v_sal);
DBMS_OUTPUT.PUT_LINE('4. Emp_salary:' || v_emp.sal);
END;
/
SQL> execute Update_Test(7900, -10);
*** Data Modify Successful ***
*** Modify Check ***
1. Emp_no:7900
2. Emp_name:JAMES
3. PreviewEmp_Salary:855
4. Emp_salary:769.5

관리자로서 PL/SQL 사용할수 있는 능력

- 문제가있는 PL/SQL 식별해라
- 문제발견시 수정해라
- PL/SQL 객체를 불러 들일수 있어야 한다.
- 조낸 공부해라 -ㅇ-

Triggers - 자동이벤트 실행객체

Triggering Event  - 모든 명령어들은 트리거에 의해 처리된다.

------------------------------------------
Event Type  Examples of Events
------------------------------------------
DML    - INSERT, UPDATE, DELETE
------------------------------------------
DDL    - CREATE, DROP, ALTER, GRANT, REVOKE, RENAME
------------------------------------------------------------
Database - LOGON, LOGOFF, STARTUP, SHUTDOWN, SERVERERROR
---------------------------------------------------------------

Locks -잠금 ★★★★★

Lock은 일관성을 보장한다. 동시성을 제어한다.

7.Managing Schema Object

스키마란 ? 특정 유저가 소유하는 객체의 모음이다.
스키마 객체에는 테이블, 뷰, 인덱스와 같은 것들이 있다.

30바이트 까지 길이 제한이 있으며 예외적인 상황은 다음과 같다.
 - unique name 8byte로 제한되어 있다.
 - database links  128byte로 제한됨 (select * from hr.employees@oracl.com)

Nonquoted name 오라클 예약어를 지정할 수 없다.

Nonquoted name은 알파벳이으로 시작해야된다.
qoted name은 사용하지 말것을 권장한다. Quoted name은 대소문자를 구분한다.
Oracle에서는 대소문자를 구분하지 않는것이 원칙이다.

오라클에 기본적인 데이터 Type

CHAR 고정길이
VARCHAR2 가변길이
DATE
NUMBER(P,S)  - P전체자리수, S소수점 이하 자리수

Create and Modify Table

em->관리-> 스키마-테이블

Understanding Data Integrity  (데이터 무결성)
Constraint (제약조건=업무규칙) - 부적합한 데이터의 입력/ 수정을 방지한다.
     제약조건은 무결성을 보장한다.

Not null - 디폴트로 테이블에 있는 모든 컬럼은 null 값을 허용한다. Not null 제약조건은 컬

럼라의 null 값을 허용하지 않는다.

Unique key - 유일한값을 보장한다. Unique 중복을 배제하지만 null은 허용한다.

PRIMARY key - 중복을 배제 , not null (널값을 허용하지 않는다.)

   
Constraint Violations 제약조건의 위반

Primary key 값은 중복될수 없다.
부모와 자식의 행이 존재하느경우 자식행을 삭제하는 경우 부모행의 제약조건 값이 위배된다.
데이터의 값을 변경할경우 체크제약조건에 위배된다.(범위이내에 값이 아닐경우 제약조건을 위

반한다.)

invaild ==> insert Disable(부적합한 데이터 허용)  Disable    
Enable (방지)  Enable(방지)   기본값
-----------------------------------------
     update  Novalidate (검증허용)   Validate (검증/부적합한 데이터

방지) Novalidate(허용) validate(검증/방지) 기본값

제약조건 생성하기

1) ALTER TABLE countries       insert방지, update 허용
   ADD  (UNIQUE(country_name) ENABLE      NOVALIDATE);

2) ALTER TABLE employees ADD CONSTRAINT pk PRIMARY KEY(employee_id);

3) CREATE TABLE t1 (pk NUMBER PRIMARY KEY, fk NUMBER, c1 NUMBER, c2 NUMBER,
   CONSTRAINT ri FOREIGN KEY(fk) REFERENCES t1, CONSTRAINT ck1

CHECK(pk>0 and c1>0));

인덱스는 테이블과 독립적으로 저장되지만, 테이블에 종속된다.
인덱스는 독립된 저장소 이다.

 구조(컬럼)  data(행)  task
Drop    삭제      삭제 공간삭제
-------------------------------------
Truncate         삭제      공간삭제(High Water mark 재설정)
-------------------------------------
Delete        삭제 delete Trigger를 통해서 Row 삭제 작업만 한다.
-------------------------------------

Dropping a Table

Drop TABLE hr.employyes PURGE;

Drop Table 조건절
CASCADE CONSTRAINT : Dependent referential integrity constraints
제약조건은 끊고 삭제한다.
PURGE : No flashback possible - 놀리적복구가 불가능하다.

Truncating a Table - 데이터 삭제가 되지않고 사용된 공간만 해제된다.

TRUNCATE TABLE hr.employees;