Admin I

Sql processing

# Soft parsing step
1. 사용자가 새로운 SQL 문장 수행 요청
2. 기본적인 문법체크와 권한체크 등을 수행
3. 해쉬 버켓을 관리하는 라이브러리 캐시 래치를 획득
4. 라이브러리 캐시 영역에 동일한 SQL문장, 즉 동일한 LCO가 존재하는지 확인
5. 라이브러리 캐시 래치를 획득하는 과정에서 경합이 발생하면 latch:library cache 이벤트를 대기
6. 동일한 LCO가 존재하는 경우에는 SQL 커서에 대해 library cache Lock과 라이브러리 캐시 Pin을 shared mode로 획득하고 SQL 문장을 실행
cf) SQL 커서가 참조하는 LCO에 대해서는 기본적으로 SQL 커서와 동일한 모드로 라이브러리 캐시 Lock과 library cache Pin을
shared mode로 획득한다. 하지만, DDL문장과 같이 객체 변경작업을 수행하는 경우에는 해당 객체에 해당하는
LCO에 대해서 라이브러리 캐시 Pin을 excusive mode로 획득한다.

# Hard parsing (soft parsing 과정에서 검색 실패 즉 동일한 LCO검색이 실행한 경우 진행된다.)

1. 동일한 SQL문장이 존재하지 않는다면 shared pool latch를 획득하고 가장 적절한 크기의 Free chunk를 free list에서 찾음.

2. shared pool latch를 획득하는 과정에서 경합이 발생하면 latch:shared pool 이벤트를 대기

3. Free chunk가 확보될 때까지 계속해서 shared pool latch를 획득.
   a. 최적 크기의 프리 청크가 존재하지 않으면 조금 더 큰 크기의 프리 청크를 찾아서
      이를 split하여 사용하며
      남은 청크는 다시 프리 리스트로 등록시킴.
   b. 모드 프리 리스트를 탐색하고도 적절한 크기의 프리 청크를 찾지 못하면
      LRU list를 탐색함.
      LRU list의 청크들은 현재 Pin 되지 않은 recreatable 상태의 청크들임.
   c. LRU list를 탐색하고도 적절한 크기의 청크를 확보하지 못하면 shared pool 내의
      여유 메모리 공감을 추가적으로 할당.
   d. 이것마저도 실패한다면 ORA-4031 에러가 발생.

4. 위의 과정을 통해 적절한 프리 청크를 찾으면 SQL 문장에 해당하는 라이브러리 캐시 Handle에 대해 라이브러리 캐시 Lock을 exclusive mode로 획득.

5. LCO 정보를 생성

6. LCO가 생성되면 라이브러리 캐시 Lock을 Null mode로 변환하고, 라이브러리 캐시 Pin을 exclusive mode로 획득한 후 실행 계획을 생성.

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

CR Snapshot

# CR Snapshot 확인

> 블록의 상태를 보는 가장 좋은 방법은 Fixed View 중 하나인 X$BH 뷰 확인.
> 이 중에서 CR 작업의 정체를 파악하려면 다음과 같은 컬럼들의 의미를 알아야 한다.

SQL> desc X$BH

-DBARFIL : 데이터파일 번호
-DBABLK : 블록 번호
-STATE : 블록 상태. 3 인 경우가 CR이다. 0 = Free, 1 또는 2 가 Current 상태의
블록이다(즉 CR의 반대)
-OBJ : 오브젝트 번호. dba_all_objects.data_object_id 또는 object_id와 조인된다.
-CR_SCN_BAS, CR_SCN_WRP : CR 블록인 경우 SCN 정보
-CR_UBA_FIL, CR_UBA_BLK : CR 블록인 경우 롤백에 사용된 언두 정보

SQL> !vi xbh.sql
select obj, dbarfil, dbablk, state, cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk
from sys.x$bh where obj in (&object_id)
order by obj
/

session A-----------------------------------------------------------

SQL> CREATE TABLE cr_test(id INT PRIMARY KEY, name VARCHAR2(10));

SQL> INSERT INTO cr_test VALUES(1, 'name1');

SQL> COMMIT;

SQL> SELECT data_object_id FROM all_objects WHERE object_name = 'CR_TEST'

DATA_OBJECT_ID
--------------
53053 -- object_id

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid)
2 FROM cr_test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
1 61050
-- 파일번호=1, 블럭번호=61050

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; -- buffer cache flush

--이 상태에서 세 개의 세션 Session A, B, C 에서 다음과 작업을 수행하면
--xbh.sql 스크립트를 이용해 1/61050 블록의 상태를 확인해보자.

Session A: SELECT * FROM cr_test;

Session C: @xbh

-- 1/61050 블록이 1번(Current) 상태로 존재함을 알 수 있다. 아직 Dirty 상태가 아니기 때문이다.

-- Update를 수행해서 Dirty 블록을 만든다.

Session A:
SQL> UPDATE cr_test SET name = 'name2';

Session C :
SQL> @xbh.sql

-- 1/61050 블록에 대해 CR 블록(State=3)이 생겼음을 알 수 있다.
-- 즉 Update에 의해 Dirty 블록을 만들어지는 시점에 오라클은 하나의 CR snapshot을 만든다.

-- 이제 Session B에서 Dirty 블록에 대해 읽기를 수행한다.

Session B :

SQL> select * from cr_test;
SQL> @xbh.sql

-- CR 블록이 하나 더 생겼음을 알 수 있다(맨위).
-- 이 CR 블록은 기존의 CR블록(SCN_BAS가 896220인)의 카피본이며
-- 실제 롤백시 수행되었음(CR_UBA_FIL = 2)을 알 수 있다.

-- Session B 에서 십여 차례 select * from cr_test 쿼리를 수행한다.

SQL> @xbh.sql

       OBJ    DBARFIL     DBABLK      STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     53053          1      61050          3     896277          0          2       2193
     53053          1      61050          1          0          0          0          0
     53053          1      61050          3     896276          0          2       2193
     53053          1      61050          3     896275          0          2       2193
     53053          1      61050          3     896274          0          2       2193
     53053          1      61050          3     896273          0          2       2193
     53053          1      61049          1          0          0          0          0
-- CR 블록이 최대 5개까지만 만들어지는 것을 확인할 수 있다.
-- Select 요청이 있을 때마다 가장 가까운 CR 블록을 이용해 새로운 CR 카피본을 만들고
-- 필요한 경우 롤백을 수행한다. 5개를 초과하면 가장 오래된 CR 블록은 밀려난다.
-- 이 값은 _DB_BLOCK_MAX_CR_DBA 파마리터에 의해 결정된다.

-- 위 결과는 Session B에서 계속해서 Select를 수행한 결과인데 CR_SCN_BAS가
-- 최근 값을 모두 바뀐 것을 확인할 수 있다.

***********************************************************************************
Time Session1 Session2

T0 …

T1 Update (Dirty block)

T2 Select CR 1(T2) 생성

T3 Select CR 2(T3) 생성

T4 Select CR 3(T4) 생성

T5 Select CR 4(T5) 생성

T6 Select CR 6(T6) 생성

T7 Select CR 7(T7) 생성 --> CR1 제거

T8 Select CR 8(T8) 생성 --> CR2 제거
***************************************************************************************

[정리]---------------------------------------------------------------------------------------

1. 왜 오라클은 최근 5개의 CR 블록을 계속 갱신하면서 유지하는가?
: 이에 대한 대답은 CR 작업을 좀 더 효율적으로 하기 위해서이다. 즉, 최대 5개까지 가장 최근의
CR 블록을 유지함으로써 과거 버전 읽기 작업에 드는 부하를 최소화하기 위함이다. 오라클은 Select
요청에 맞는 CR 블록을 생성하기 위해 CR 카피를 만들고, Undo로부터 롤백을 수행하는 일련의 작업을
수행하는데, 이 작업의 부하를 최소화하기 위해 버퍼 캐시에 최근의 CR 블록을 여러 개 유지하는 것이다.

2. 하나의 블록당 여러 개의 CR 블록을 유지함으로써 메모리의 낭비를 유발하지 않는가?
: 실제로 그렇다. 오라클의 특정 버전에서는 CR 블록 수가 너무 많아져서 버퍼 캐시의 효율성이 떨어지는
현상이 실제로 있었다. 하지만 최신 버전의 오라클은 CR 블록의 위치를 LRU 리스트의 꼬리쪽으로 위치
시킴으로써 메모리 부족 현상이 생길때 되도록 빨리 밀려나도록 해준다.

3.CR, CR, CR, ...
: 오라클의 CR 블록과 Consistent Read는 다른 DB에서는 잘 지원하지 못하는
MVRC(Multi Versioning Read Consistency)를 가능하게 하는 핵심 메커니즘이다.

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

isqlplus sysdba access

$ vi .bash_profile
export JAVA_HOME=$ORACLE_HOME/jdk

:wq

$ . .bash_profile
----------------------------------------
$ vi jazn.sh
#!/bin/sh

pushd .

cd $ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus/
$JAVA_HOME/bin/java \
-Djava.security.properties=$ORACLE_HOME/oc4j/j2ee/home

i/config/jazn.security.props \
        -jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar \
        -user "iSQL*Plus DBA/admin" \
        -password welcome -shell

popd

:wq
---------------------------------------------------------

$ chmod 744 jazn.sh
$ ./jazn.sh

JAZN> adduser "iSQL*Plus DBA" expert expert
JAZN> grantrole webDba "iSQL*Plus DBA" expert
JAZN> listusers "iSQL*Plus DBA"

JAZN> exit

http://192.168.100.100:5560/isqlplus/dba > expert/expert

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

restricted database

# 제한모드 데이터베이스란?

restricted session 권한을 가진자만이 DB에 접근 할수 있도록 설정하는것을 말한다.

일반적으로 DB에 대한 유지보수 및 유지관리 업무를 관리자가 수행할 때 작업에 일관성을 유지하기 위한 수단으로 사용된다.

1. 제한모드로 DB startup

SQL> startup restrict

-- 해제

SQL> shutdown >>>>> startup
or
-- open상태에서 설정.

SQL> alter system enable restricted session; 헤제 > disable

2. 제한모드 실습

## 사용자 세션의 확인과 제한 모드를 위한 사용자의 강제 종료

오라클 관리자 세션***************************************************************************

SQL> startup
ORACLE instance started.
Total System Global Area  131142092 bytes
Fixed Size   451020 bytes
Variable Size   83886080 bytes
Database Buffers   46137344 bytes
Redo Buffers   667648 bytes
Database mounted.
Database opened.

SQL> alter user hr identified by hr account unlock;

일반 유저 세션*******************************************************************************
$ sqlplus hr/hr

SQL> select count(*) from tab;

COUNT(*)
-----------
8

오라클 관리자 세션***************************************************************************

SQL> alter system enable restricted session; >>>제한모드 설정.
System altered.

SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED -----------> disable = ALLOWED

일반 유저 세션*******************************************************************************
===> 기존 세션에 대해서는 제한모드를 설정하지 않는다.

SQL> select count(*) from tab;
COUNT(*)
-----------
8

일반 유저 세션 2*****************************************************************************

[oracle@oracle oracle]$ sqlplus hr/hr

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 14 18:25:59 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

오라클 관리자 세션***************************************************************************
SQL> select sid, serial#, username, status from v$session;

SID SERIAL#   USERNAME STATUS
-------- --------- -------------- --------- ---------
   1       1     ACTIVE
   2       1     ACTIVE
   3       1     ACTIVE
   4       1     ACTIVE
   5       1     ACTIVE
   6       1     ACTIVE
   7       1     ACTIVE
   8       1     ACTIVE
   9       1     ACTIVE
10       1     ACTIVE
11       3    SYS   ACTIVE
12       4      HR  INACTIVE
12 rows selected.

SQL> alter system kill session '12,4';
System altered.

SQL> select sid,serial#,username,status from v$session;
SID SERIAL#   USERNAME STATUS
-------- --------- -------------- --------- ---------
   1       1     ACTIVE
   2       1     ACTIVE
   3       1     ACTIVE
   4       1     ACTIVE
   5       1     ACTIVE
   6       1     ACTIVE
   7       1     ACTIVE
   8       1     ACTIVE
   9       1     ACTIVE
10       1     ACTIVE
11       3    SYS   ACTIVE
12       4      HR  KILLED
12 rows selected.

일반 유저 세션******************************************************************************

SQL> select count(*) from tab;
*
ERROR at line 1:
ORA-00028: your session has been killed

오라클 관리자 세션***************************************************************************

SQL> select * from dba_sys_privs
2 where privilege like '%RESTRICT%'; ←현재 restricted session 권한을 가진 사용자를 출력

GRANTEE   PRIVILEGE          ADM
---------------------- -------------------------- -----
DBA   RESTRICTED SESSION YES
SYS   RESTRICTED SESSION NO
MDSYS   RESTRICTED SESSION YES
WKSYS   RESTRICTED SESSION NO
CTXSYS   RESTRICTED SESSION NO

SQL> grant restricted session to HR;        ← HR에게 restricted session 권한 부여
Grant succeeded.
SQL> select * from dba_sys_privs
   2 where privilege like '%RESTRICT%';

GRANTEE   PRIVILEGE  ADM
---------------------- -------------------------- -----
HR   RESTRICTED SESSION NO
DBA   RESTRICTED SESSION YES    ← 차이?
SYS   RESTRICTED SESSION NO
MDSYS   RESTRICTED SESSION YES
WKSYS   RESTRICTED SESSION NO
CTXSYS   RESTRICTED SESSION NO

일반 유저 세션*******************************************************************************

SQL> conn scott/tiger
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

SQL> conn hr/hr
Connected.
← restricted session 권한을 가진 HR은 접속이 가능하지만 권한이 없는 사용자는 접속이 불가능함

*******************************************************************************************
<개인 실습>

⇒ SYS 사용자가 HR 사용자에게 RESTRICTED SESSION 권한과 상속 가능 권한 부여.

⇒ HR 사용자로 접속.

⇒ SCOTT 사용자에게 RESTRICTED SESSION 권한 부여하여 제한 모드 상태로 SCOTT 사용자
접속 가능하게 해주시오.

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

Creation Tablespace LAB

CREATE TABLESPACE 테이블스페이스이름

DATAFILE '파일 이름1‘ SIZE integer [M/K] reuse,

'파일 이름2‘ SIZE integer [M/K] ......

[AUTOEXTEND ON [NEXT n[M|K] MAXSIZE [M|K|UNLIMITED]]]

[MINIMUM EXTENT integer [M/K]]

[BLOCKSIZE integer [K]]

[DEFAULT STORAGE(

INITIAL integer [M/K]

NEXT integer [M/K]

MAXEXTENTS integer

PCTINCREASE integer)]

[ONLINE | OFFLINE]

[LOGGING | NOLOGGING]

[PERMANENT | TEMPORARY]

[EXTENT MANAGEMENT

DICTIONARY | LOCAL

AUTOALLOCATE | UNIFORM SIZE integer [M/K]]

[SEGMENT SPACE MANAGEMENT (MANUAL/AUTO)]

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

1. image backup
SQL> alter database backup controlfile to '/경로/파일명';
2. script backup1
SQL> alter database backup controlfile to trace;
>>> user_dump_dest 경로에 *.trc 파일로 생성되고 해당 파일을 열게 되면,
>>> startup nomount 절 이하가 controlfile을 재생성하는 script 임.
3. script backup2
SQL> alter database backup controlfile to trace as '/경로/파일명';
>>> user_dump_dest가 아닌 본인이 원하는 경로에 script 형식으로 backup.
4. 2,3번에서 발생하는 trace의 이름 식별하기.
SQL> alter system set tracefile_identifier = '식별자';
SQL> alter database backup controlfile to trace;
>>> 실행 시 해당 경로에 ora_12345_식별자.trc 형식으로 생성됨.
When?
: 물리적 구조변경이 발생할때마다...
------------------------------------------------------------------------------
critical vs noncritical
# non-critical (file lost) ===========================================
1. alter tablespace xxx offline immediate;
2. restore from backup (cp | restore)
if device fail ? restore new Location + rename
3. recover 명령. {database (mount) | datafile | tablespace (open)};
> 과거시점의 백업파일을 복원한 후 현재 시점으로 동기화하기위해서
> archivelog + redolog 를 적용하는 명령.
4. alter tablespace xxx online;
-LAB (file lost)-----------------------------------------------------
SQL> alter tablespace users offline immediate;
Tablespace altered.
SQL> !cp /home/oracle/backup/close/users01.dbf /u01/oradata/orcl
SQL> recover tablespace users;
Media recovery complete.
SQL> alter tablespace users online;
Tablespace altered.
-LAB (disk fail)------------------------------------------------------
SQL> alter tablespace users offline immediate;
Tablespace altered.
SQL> !cp /home/oracle/backup/close/users01.dbf /u01
SQL> alter tablespace users rename
2 datafile '/u01/oradata/orcl/users01.dbf' to '/u01/users01.dbf';
SQL> recover tablespace users;
Media recovery complete.
SQL> alter tablespace users online;
-----------------------------------------------------------------
# critical (file lost) - system or undo =================================
1. shutdown immediate
2. restore from backup
3. startup mount
if device fail ? 2. restore new Location + 3. rename
4. recover 명령. {database (mount) | datafile | tablespace (open)};
> 과거시점의 백업파일을 복원한 후 현재 시점으로 동기화하기위해서
> archivelog + redolog 를 적용하는 명령.
5. alter database open;
--------------------------------------------------------------------------

datapump에 관한 정리

Ch03. Data Pump, External Tables
export / import > expdp / impdp
> p.3-6 Data Pump: Benefits
      - DB Link를 이용한 Network Mode (NETWORK_LINK 옵션)
      - Directory Object가 반드시 필요(DIRECTORY 옵션)
      - expdp 옵션에 의한 객체 단위 포함/제외 가능(EXCLUDE/INCLUDE 옵션 및 향상된 QUERY 옵션)
      - 제한적이긴 하지만 Parallel 가능(PARALLEL 옵션)
      - Master Table에 의한 중지 작업 재시작 및 작업 변경(추가 및 삭제)이 가능
      - REMAP_SCHEMA, REMAP_DATAFILE, REMAP_TABLESPACE 등 새로운 옵션
> p.3-8
- client가 oracle db에 logon 하면 shadow process 가 생성된다.
shadow process는 client의 Data Pump API 요청을 처리한다(A).
일단 job이 실행되면 shadow process(B)의 주업무는 client로부터 GET_STATUS 요청에 대해 서비스하는 것이다.
Client(A)가 detach되면 해당 shadow process는 없어진다.
          A. DBMS_DATAPUMP.OPEN 요청
             - job을 생성
              - master table 생성
              - AQ queue 생성 : process간 통신
              - master control process(MCP) 생성
          B. MCP는 Data pump job의 실행과 순서를 control한다
             - job status, job description, restart, master table에 있는 정보를 dump한는 것을 maintain
             - MCP는 DMnn 이다.
          C. START_JOB 요정에 따라 MCP(master control process)는 PARALLEL parameter에 의거 몇개의 work process를
             만든다. work process(DWnn)는 MPC의 요청을 수행한다. 주로 Data와 Metadata의 load/unload를 담당.
- Loading/unloading을 위한 data access method로 external table path가 선택되면 work process는
load/unload assignment에 따라서 여러개의 parallel execution servers의 작업을 coordinate한다.

                                                                                       Dump File Sets <--|
          Client A Shaow Process --> Q                           AQ                     ^              |
                                       U    (DMnn)                 Q                      |              |
          Client B Shaow Process --> E <-- MCP --> Master Table U <--->Work process1 --|              |
                     |                 U     ^ |                   E <--->Work Process2 --|              |
                     |                 E     | ------------------>U <--->Work Process3 --               |
                     ----------------------->JOB                   E <--->Work Process4 ---- PX Server1--|
                                                                          (Ext Table Path) |_PX Server2--|

          1. Client Process -> DBMS_DATAPUMP.OPEN
             1) Create JOB : Master Table, AQ Queue, Master Control Process
             2) Service    : Get_Status
          2. Master Control Process(MCP)
             - Pump Job의 실행과 순서 관리
             - DMnn
          3. START_JOB 요청으로
             - PARALLEL Patameter에 의거 Work Process를 실행(DWnn:01~04)
> p.3-9
   * Direct Path 제약조건
       - Data pump 는 Direct path loading(using direct path API) 또는 External Tables을 사용하여
         table row data에 access한다.
       - Data Pump 가 자동으로 둘 중에 적절한 방법을 선택한다.
         Table structure가 허용하거나 Maximum single-stream performance가 요구될때 direct path가
         사용된다. 그러나 아래의 경우 또는 table이 encrypted column을 포함하거나 load시간과 unload시간
         에 partition이 다를 경우 data pump는 external table을 사용한다.

   * direct path가 안되고 external table이 되는 경우
       - fine-grained access control을 가진 table이 insert/select mode에서 enable된 경우
       - LOB 컬럼에 대해 domain index가 존재할때
       - Clustered table 이 존재할 경우
       - Table에 active trigger가 define 된 경우
       - Partition table에 global index가 있을 때 single-partition load를 할 경우
       - BFILE 또는 opaque type 컬럼
       - FK
       - VARRAY columns with an embedded opaque type
> p.3-10 Data Pump File Locations
     - Data pump file 종류
       - DUMP file : data와 metadata를 포함.
       - LOG file : operation과 관련된 message를 기록.
       - SQL file : impdp에서 SQLFILE operation의 결과를 기록
     - Data Pump는 server-based 이므로 Oracle directory path를 통해서 Data Pump file에 access한다.
       Absolute path는 보안상 지원되지 않는다.

     - Order of precedence of file Locations

       1) per-file directory
          - dump file, log file, sql file 마다 지정될수 있다. 콜론(:)으로 deirecoty 와 file name 을 구분한다.

            EX) dumpfile=AA:A.dmp

       2) DIRECTORY parameter
          - directory object를 사용.
          EX)
            Create Directory DIR_PJH as '/home/oracle10g/test/';
            Ggrant read, write On Directory DIR_PJH to SCOTT;

            Directory=AA
            Dumpfile=A.dmp

       3) DATA_PUMP_DIR 환경변수
          - DIRECTORY Parameter를 대신하여 directory object name을 설정.
          EX)
            export DATA_PUMP_DIR=AA
            Dumpfile=A.dmp

     - 위의 모든 경우에 시도하려는 operation에 대해 directory object에 대해 적절한 access privs가 있어야 한다.
       Export할 경우 모든 file에 대해 write access가 필요.
       Import할 경우 dump file에 대해 read access, log file과 sql file에 대해 write access가 필요.
> p.3-12 Data Pump File Naming and Size
     1) DUMPFILE
         - file list는 , 로 분리
         - %U template --> 결과 파일명에서 고정너비2자로 확장되면 01에서 시작하여 정수 단위로 일정하게 증가.
         - DUMPFILE 이 지정되어 있지 않으면 expdat.dmp 가 default로 사용된다. default로 autoextensible.
     2) FILESIZE
         - FILESIZE 가 지정되어 있으면 각 file은 FILESIZE안으로 만들어지고 늘어날수 없다. dump 공간이 더 필요하고
           template %U가 지정되었다면, 새로운 파일이 생성된다. 그렇치 않으면 사용자는 new file을 add하라는
           메세지를 받는다.
     3) PARALLEL
         - %U가 지정되면 PARALLEL patameter의 갯수만큼 초기에 file이 생성된다.
     4) 기존에 존재하는 file과 이름이 중복될 경우 overwrite하지 않고 에러를 발생시키고 job이 abort된다.
     5) 복수개의 dump file template가 제공되면 round-robin fashion으로 dump file을 생성하는 데 사용.
> p.3-14 Data Pump and Filtering
     (1) Find-grained object selection
         - 기존의 exp/imp는 index, trigger, grant, constriant를 포함하거나 제외할 수 만 있으나
           data pump는 virtually any type of object를 포함하거나 제외할 수 있다.
         - EXCLUDE 와 INCLUDE는 상호배타적이다.

           - INCLUDE = object_type[:"name_expr"]
           - EXCLUDE = object_type[:"name_expt"]

         EX) 모든 view, 모든 package, EMP로 시작하는 Index만 제외
           EXCLUDE=view
           EXCLUDE=package
           EXCLUDE=INDEX:"LIKE 'EMP%' "

     (2) Data Selection
           - CONTENT = ALL(def) | METADATA_ONLY | DATA_ONLY
           - QUERY = [Schema.][table_name:]"query_clause"
         - CONTENT에 data_only가 사용되면 EXCLUDE 와 INCLUDE를 사용할 수 없다.
         - QUERY=hr.employees:"WHERE department_id in (10,20) and salary < 1600 ORDER BY department_id"
            <--특정 table을 지정해서 해당 table로 한정. imp시에도 적용.
> p.3-15 Data Pump Import Transformations(변경)
     - Dump file의 metadata는 XML로 저장되어있기 때문에 import시 DDL이 적용될 때 쉽게 변경할 수 있다.
     1) Remap
         A. REMAP_DATAFILE
            - REMAP_DATAFILE='c:\oradata\tbs6.f':'/u1/tbs6.f'
            - 서로 다른 file-system 형식을 가지는 platform으로 옮기는 경우. (DB migration시 유용)
         B. REMAP_TABLESPACE :export 받은 데이터 속한 tablespace 에서 다른 tablespace로 Remapping하고자 하는 경우
         C. REMAP_SCHEMA : 유저 스키마로 export 받은 데이터를 B 유저 스키마로 import 하고자 할때
            - 기존의 fromuser, touser를 제공.
> p.3-16 TRANSFORM
         - TRANSFORM= SEGMENT_ATTRIBUTES | STORAGE:{y|n}[:TABLE | INDEX]
         - TRANSFORM 으로 storage와 tablespace 또는 storage에 대한 DDL이 생성되지 않게 할 수 있다.
           Target DB의 storage가 Source DB의 storage와 많이 다를 경우 유용하다.
> p.3-20 Parallel Full Export and Import
        - 4개의 work process를 가진 full export,
          Pump file은 DATADIR1, DATADIR2, DATADIR3, DATADIR4 네곳에 라운드로빈 방식으로 생성된다.
          2G를 넘지 않으면서... 최소4개 생성.
          Job 과 master table 이름은 default로 SYSTEM_EXPORT_FULL_01 를 가진다.

        - expdp로 받은 dump file을 network를 통해 NET_STORAGE_1 이라는 directory object위치로
          보내졌다. Default import는 dump set 전체를 import하는 것이므로 Full=y 는 필요없다.
          Job 과 master table 이름은 default로 SYSTEM_IMPORT_FULL_01 를 가진다.

> p.3-21 Limited Schema Export (fine-grained)
        - HR, OE schema에서 모든 func, prod, pkg, user-defined type, PRODUCT로 시작하는 view를 export한다.
           Schema definition과 system priv graints는 export되지 않는다.
        - 실제 import는 하지 않고 dmp file에서 DDL 문장만 뽑아낸다.

> p.3.22 Network Mode Import (DB Link)
       - Source DB에 dblink로 붙어서 hr, sh, payroll schema를 가져온다음 imp 한다.
          이때 payroll schema로 finacne schema로 만들어 진다.
          SYSTEM은 IMPORT_FULL_DATABASE rol을 가지고 있고 Source DB에 대해서는 EXPORT_FULL_DATABASE role
          을 가지므로...Target DB에 해당 schema definition이 없으면 만들어진다.
          flashback_time은 예전의 CONSISTENT와 동일.

> p.3-23 Attaching to Existing Job
       - 중지.
         Attach session은 terminate 되고 실행되던 job은 controlled fashion으로 run down 된다.
         해당 Job은 dump file과 SYSREM.EXP_TS1 table이 disturbed 되지 않는한 startable 하다.


> p.3-24 Restarting Stopped Job
       > expdp system/manager attach=exp_ts1   <--같은 schema안에 여러개의 outstanding job이 있으면 job name지정
         ...
         Export> parallel=4
         Export> start_job
         Export> status =600 <--10분. detailed per-work process가 10분 단위로 regualr status message를 보여준다.
         ...
         KILL_JOB로 job을 kill

         <--status, status=600(초)
            stop_job,
            start_job,
            continue_client:attach한 session이 계속 받아서 expdp 실행.(logging mode로 전환)
            exit_client: Attach를 빠져 나옴. expdp는 background로 실행.
            parallel=4,

> p.3-27 External Table Population
      - Create Table As select ... 로 External Flat File을 만들수 있다.(10g)
      - External Flat File로 External Table을 통하여 Insert ....Select로 타 Table로 insert 한다. (기존)
      - External Table은 복잡한 ETL 에 적합.
      - external flat file에 대한 insert/update/delete 는 불가능하다. ORA-30675 error
      - external flat file은 이동될수 있고 같은 DB나 다른 DB의 다른 External Table에 대해 사용될수 있으나
        오직 ORACLE_DATAPUMP access driver에 의해서만 사용될 수 있다.
      - ORACLE_DATAPUMP driver 를 사용.
        ~~~~~~~~~~~~~~~ External Table API <> Direct Path API

> p.3-29 External Table Parallel Population Operation
      - External Table은 일반적으로 크기 때문에 Parallel operation으로 unload한다(복수개의 file이 생성).
        Parallel operation은 External Table이 parallel로 define된 경우 가능.
      - Exactly one parallel execution server per file.
      - 각각의 I/O server process는 자신의 file을 요구하므로 Location 절에 있는 file의 갯수가 지정된
        parallel degree 와 같아야 한다. 지정된 parallelism degree 보다 작은 files인 경우 Location에 있는 file
        갯수와 맞추기 위해 degree of parallelism은 낮아진다. 반대로 files의 갯수가 많은 경우 여분의 file은
        무시된다.

                                      Coordinator
                                          |
                    |-------------------------------------------|
             Parallel Execution    Parallel Execution    Parallel Execution
                 Server 1             Server 2                Server 3
                    |                     |                     |
                    V                     V                     V
              generated file 1    generated file 2       generated file 3


      - 기존 9i 의 external table

            CREATE TABLE empxt (empno       NUMBER(4),
                                ename       VARCHAR2(10),
                                job         VARCHAR2(9),
                                mgr         NUMBER(4),
                                hiredate    DATE,
                                sal         NUMBER(7,2),
                                comm        NUMBER(7,2),
                                deptno      NUMBER(2)
                                 )
            ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER    <---기존
                                     DEFAULT DIRECTORY emp_dir_sqlt#
                                     ACCESS PARAMETERS ( records delimited by newline
                                                          badfile emp_dir:'empxt.bad'
                                                          logfile emp_dir:'empxt.log'
                                                          fields terminated by ','
                                                          missing field values are null
                                                          ( empno, ename, job, mgr,
                                                            hiredate char date_format date mask "dd-mon-yy",
                                                            sal, comm, deptno
                                                          )
                                                        )
                                     Location ('emp.dat')
                                  );

      - 10g 에서 External Flat File을 만드는 예제          <--그럼 external table은 언제/어떻게 만들어 지나?

           Create Table emp_ext(first_name,
                                last_name,
                                department_name
                                )
           Organization External ( Type    ORACLE_DATAPUMP   <---변경
                                    Default Directory ext_dir
                                    Location ('emp1.exp','emp2.exp','emp3.exp') <---|
                                 )                                                   |
           Parallel 3 <--병렬 ----------------file하나당 하나의 parallel process가 --|
            +
           As
           Select e.first_name, e.last_name, d.department_name
             From employee e, department d
            Where e.department_id = d.department_id
              and d.department_name in ('Marketing','Purchasing');
> p.3-31 External Table Projected Columns
      - External Flat file의 data format error로 row가 reject 될 수 있는데 projected column feature
        은 External Table에 access하는 SQL의 column에 상관없이 일관된 consistent result를 보장.
      - 예전(9i)에서는 참조하는 컬럼만 access driver에 의해 projected out 되었다. 즉 REFERENCED(참조).
        10g 부터는 ALL(Default)을 추가하였다.

        Alter table order_item_ext PROJECT COLUMN {(ALL) | REFERENCED};
                                                           ----------<--select하는 컬럼만 검사하여..9i
      - Default는 ALL 임.
      - REFERENCED 는 data가 safe하다고 예상될 때 성능향상을 위해서 유용.

         order_id line_id
         -------- -------
         2311,     1,...
         2312,     A,...

         Alter table order_item_ext PROJECT COLUMN REFERENCED;

         select count(order_id ) from order_item_ext; --> 2
         select count(line_id ) from order_item_ext;   --> 1

         Alter table order_item_ext PROJECT COLUMN ALL;

         select count(order_id ) from order_item_ext; --> 1
         select count(line_id ) from order_item_ext;   --> 1
----------------------------------------------------------------------------------------------------------

emp_imp(9i버젼)

$ sqlplus / as sysdba
SQL> alter user scott identified by tiger account unlock;
SQL> conn scott/tiger
SQL> select * from tab;
TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
[~/backup/dump]$ exp scott/tiger file=/home/oracle/backup/dump/exp_test.dmp tables=(emp, dept)
=====장애발생===================================
SQL> conn scott/tiger
SQL> drop table dept cascade constraints;
SQL> drop table emp;
SQL> purge recyclebin;
SQL> select * from tab;
TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
SALGRADE                       TABLE
===============================================
[~/backup/dump]$ imp scott/tiger file=/home/oracle/backup/dump/exp_test.dmp tables=(emp, dept)
SQL> select * from tab;
TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

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

External Table unloader

SYS> create directory dump1 as '/home/oracle/dp';
Directory created.
SYS> grant read, write on directory dump1 to public;
Grant succeeded.
1)
HR> CREATE TABLE extdp_test_tb1
2 ORGANIZATION EXTERNAL
3 (TYPE    ORACLE_DATAPUMP
4   DEFAULT DIRECTORY dump1
5   Location('ext_emp01.dmp','ext_emp02.dmp','ext_emp03.dmp')
6 )
7 PARALLEL 3
8 AS
9*   SELECT * FROM HR.employees
HR> /
Table created.
HR> desc extdp_test_tb1
Name                                      Null?    Type
----------------------------------------- -------- --------------
EMPLOYEE_ID                                        NUMBER(6)
FIRST_NAME                                         VARCHAR2(20)
LAST_NAME                                 NOT NULL VARCHAR2(25)
EMAIL                                     NOT NULL VARCHAR2(25)
PHONE_NUMBER                                       VARCHAR2(20)
HIRE_DATE                                 NOT NULL DATE
JOB_ID                                    NOT NULL VARCHAR2(10)
SALARY                                             NUMBER(8,2)
COMMISSION_PCT                                     NUMBER(2,2)
MANAGER_ID                                         NUMBER(6)
DEPARTMENT_ID                                      NUMBER(4)
HR> select * from extdp_test_tb1;
EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE           JOB_ID
------------------------- -------------------- ------------------- ----------
    SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
        100 Steven               King
SKING                     515.123.4567         1987-06-17 00:00:00 AD_PRES
     24000                                      90
        101 Neena                Kochhar
NKOCHHAR                  515.123.4568         1989-09-21 00:00:00 AD_VP
     17000                       100            90
...

[ocp@orcl : /home/oracle/dp]$ ls -alh
total 73M
drwxr-xr-x   2 oracle oinstall 4.0K Mar 30 12:53 .
drwxr-xr-x 17 oracle dba      4.0K Mar 30 12:53 ..
...
-rw-r--r--   1 oracle oinstall   82 Mar 30 12:53 EXTDP_TEST_TB1_16611.log
-rw-r-----   1 oracle oinstall   82 Mar 30 12:53 EXTDP_TEST_TB1_16627.log
-rw-r-----   1 oracle oinstall   82 Mar 30 12:53 EXTDP_TEST_TB1_16629.log
-rw-r-----   1 oracle oinstall   82 Mar 30 12:53 EXTDP_TEST_TB1_16631.log
-rw-r-----   1 oracle oinstall 16K Mar 30 12:53 ext_emp01.dmp
-rw-r-----   1 oracle oinstall 16K Mar 30 12:53 ext_emp02.dmp
-rw-r-----   1 oracle oinstall 20K Mar 30 12:53 ext_emp03.dmp
...
2)
HR> CREATE TABLE extdp_test_tb2
2 ORGANIZATION EXTERNAL
3 (TYPE    ORACLE_DATAPUMP
4   DEFAULT DIRECTORY dump1
5   Location('emp_dept_join01.dmp')
6 )
7 PARALLEL 3
8 AS
9    SELECT d.department_name, e.last_name, e.salary, e.department_id
10    FROM   hr.employees e, hr.departments d
11    where e.department_id = d.department_id
12*   and    e.department_id < 70
Table created.
HR> desc extdp_test_tb2
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
LAST_NAME                                 NOT NULL VARCHAR2(25)
SALARY                                             NUMBER(8,2)
DEPARTMENT_ID                                      NUMBER(4)
HR> select * from extdp_test_tb2;
DEPARTMENT_NAME                LAST_NAME                     SALARY
------------------------------ ------------------------- ----------
DEPARTMENT_ID
-------------
Shipping                       OConnell                        3000
           50
Shipping                       Grant                           2600
           50
...
3)
생성된 파일을 target db 로 복사
$ mkdir /home/oracle/dp2
$ sqlplus system/oracle
SYSTEM> CREATE OR REPLACE DIRECTORY dump2 AS '/home/oracle/dp2';
SYSTEM> GRANT read, write on directory dump2 TO hr ;
SYSTEM> !cp /home/oracle/dp/EMP_DEPT_JOIN01.DMP   /home/oracle/dp2/
SYSTEM> conn hr/hr
SYSTEM> CREATE TABLE extdp_test_tb3
      2    (DEPARTMENT_NAME   VARCHAR2(30)
      3    ,LAST_NAME         VARCHAR2(25)
      4    ,SALARY            NUMBER(8,2)
      5    ,DEPARTMENT_ID     NUMBER(4)
      6    )
      7   ORGANIZATION EXTERNAL
      8   (TYPE              ORACLE_DATAPUMP
      9   DEFAULT DIRECTORY dump2
     10   Location ('EMP_DEPT_JOIN01.DMP') ) ;
select * from extdp_test_tb3 ;

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

datapump full mode
[ocp@orcl : /home/oracle]$ sqlplus sys/oracle as sysdba
SYS> create directory dump1 as '/home/oracle/dp';
Directory created.
SYS> grant read, write on directory dump1 to public;
Grant succeeded.
SYS> exit
[ocp@orcl : /home/oracle]$ mkdir dp
[ocp@orcl : /home/oracle]$ expdp system/oracle dumpfile=full.dmp directory=dump1 full=y job_name=Lucie
Export: Release 10.2.0.1.0 - Production on Monday, 30 March, 2009 9:57:09
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."LUCIE": system/******** dumpfile=full.dmp directory=dump1 full=y job_name=Lucie
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39139: Data Pump does not support XMLSchema objects. TABLE_DATA:"OE"."PURCHASEORDER" will be skipped.
Total estimation using BLOCKS method: 61 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
-- Ctrl + C 로 강제 중지 시킨다!!!
Export> status
Job: LUCIE
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /home/oracle/dp/full.dmp
bytes written: 4,096
Worker 1 Status:
State: EXECUTING
Object Type: DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Completed Objects: 3
Total Objects: 3
Worker Parallelism: 1
Export> stop_job
Are you sure you wish to stop this job ([yes]/no): yes
[ocp@orcl : /home/oracle]$ ss
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 30 09:59:23 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SYS> select owner_name,job_name,operation,job_mode,state from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
------------------------------------------------------------------------
SYSTEM LUCIE EXPORT FULL NOT RUNNING

SYS> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[ocp@orcl : /home/oracle]$ expdp system/oracle attach=lucie
Export: Release 10.2.0.1.0 - Production on Monday, 30 March, 2009 9:59:40
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Job: LUCIE
Owner: SYSTEM
Operation: EXPORT
Creator Privs: FALSE
GUID: 664B8FEE4B8286B7E040007F01001E87
Start Time: Monday, 30 March, 2009 9:59:45
Mode: FULL
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** dumpfile=full.dmp directory=dump1 full=y job_name=Lucie
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /home/oracle/dp/full.dmp
    bytes written: 573,440
Worker 1 Status:
State: UNDEFINED
Export> start_job
----다른 SYS 세션 --------------------------------------------------------
SYS> select owner_name,job_name,operation,job_mode,state from dba_datapump_jobs;
OWNER_NAME     JOB_NAME OPERATION JOB_MODE STATE
------------------------------------------------------------------------
SYSTEM            LUCIE EXPORT FULL IDLING
----다른 SYS 세션 --------------------------------------------------------
Export> status
Job: LUCIE
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /home/oracle/dp/full.dmp
    bytes written: 577,536
Worker 1 Status:
State: EXECUTING
Export> status
Job: LUCIE
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 18,075,304
Percent Done: 30
Current Parallelism: 1
Job Error Count: 0
Dump File: /home/oracle/dp/full.dmp
    bytes written: 36,999,168
Worker 1 Status:
State: EXECUTING
Object Schema: SH
Object Name: SALES
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 12
Total Objects: 676
Worker Parallelism: 1
Export> status
Job: LUCIE
Operation: EXPORT
Mode: FULL
State: COMPLETING
Bytes Processed: 54,644,921
Percent Done: 100
Current Parallelism: 1
Job Error Count: 0
Dump File: /home/oracle/dp/full.dmp
    bytes written: 75,874,304
Worker 1 Status:
State: WORK WAITING
[ocp@orcl : /home/oracle]$ cd dp/
[ocp@orcl : /home/oracle/dp]$ ls
export.log full.dmp
[ocp@orcl : /home/oracle/dp]$ vi export.log
[ocp@orcl : /home/oracle/dp]$ ls -alh
total 73M
drwxr-xr-x   2 oracle oinstall 4.0K Mar 30 10:01 .
drwxr-xr-x 16 oracle dba      4.0K Mar 30 10:01 ..
-rw-r--r--   1 oracle oinstall 58K Mar 30 10:01 export.log
-rw-r-----   1 oracle oinstall 73M Mar 30 10:01 full.dmp
[ocp@orcl : /home/oracle/dp]$

######################################
특정 스키마 DDL 스크립트 생성 실습
######################################
[ocp@orcl : /home/oracle]$ impdp system/oracle directory=dump1 dumpfile=full.dmp schemas=scott sqlfile=ddl_scott.sql
Import: Release 10.2.0.1.0 - Production on Monday, 30 March, 2009 12:36:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/******** directory=dump1 dumpfile=full.dmp schemas=scott sqlfile=ddl_scott.sql
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 12:36:37
[ocp@orcl : /home/oracle]$ cd dp/
[ocp@orcl : /home/oracle/dp]$ ls
ddl_scott.sql export.log full.dmp import.log
[ocp@orcl : /home/oracle/dp]$ vi ddl_scott.sql

##########################################
table import
##########################################
1) content=data_only 포함하지 않은 경우 (해당 object가 존재하지 않는 경우)
[ocp@orcl : /home/oracle/dp]$ impdp system/oracle dumpfile=full.dmp directory=dump1 job_name=data_import logfile=table_log tables=scott.dept
2) 특정 사용자 (schema) import
SQL> drop user scott cascade;
[ocp@orcl : /home/oracle/dp]$ impdp system/oracle dumpfile=full.dmp directory=dump1 job_name=data_import logfile=table_log schemas=scott

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

External table

오라클에서 flat file을 테이블처럼 처리하도록 할 수 있게 해주는 것이 external table이다.
flat file이란 그냥 raw data만 있다고 생각하면 되겠다. CSV(Comma Separated Values)파일도 이에 해당한다.
External Table은 9i부터 지원되므로 9i이전에서는 organization external에서 에러가 발생한다.

특징:
-virtual read-only table
-인덱스 사용 못함(파일처리가 빠르기때문에 필요없음)
-OS명령을 통해 insert, update, delete수행
-external table간의 조인 가능, external-regular table간의 조인 가능

[ c:\oracle\labs_XX\emp.dat or /home/oracle/labs/emp.dat]
7369,SMITH,CLERK,20
7499,ALLEN,SALESMAN,30
7521,WARD,SALESMAN,30
7566,JONES,MANAGER,20
7654,MARTIN,SALESMAN,30
7698,BLAKE,MANAGER,30
7782,CLARK,MANAGER,10
7788,SCOTT,ANALYST,20
7839,KING,PRESIDENT,10
7844,TURNER,SALESMAN,30
7876,ADAMS,CLERK,20
7900,JAMES,CLERK,30
7902,FORD,ANALYST,20
7934,MILLER,CLERK,10

SQL> conn system/oracle >>> create any directory 권한을 가진자
연결되었습니다.

SQL> create directory test_dir as
2 '/home/oracle/labs' ;
디렉토리가 생성되었습니다.

SQL> create table emp_external
2 (
3   empno char(4),
4   ename char(10),
5   job char(9),
6   deptno char(2)
7 )
8 organization external
9 (
10   type oracle_loader
11   default directory test_dir
12   access parameters
13   (
14    fields terminated by ','
15    (empno,ename,job,deptno)
16   )
17   Location ('emp.dat')
18 )
19   reject limit 100
20 ;

테이블이 생성되었습니다.

SQL> desc emp_external
이름                                      널?      유형
----------------------------------------- -------- ----------------------------
EMPNO                                              CHAR(4)
ENAME                                              CHAR(10)
JOB                                                CHAR(9)
DEPTNO                                             CHAR(2)

SQL> select * from emp_external;

EMPN ENAME      JOB       DE
---- ---------- --------- --
7369 SMITH      CLERK     20
7499 ALLEN      SALESMAN 30
7521 WARD       SALESMAN 30
7566 JONES      MANAGER   20
7654 MARTIN     SALESMAN 30
7698 BLAKE      MANAGER   30
7782 CLARK      MANAGER   10
7788 SCOTT      ANALYST   20
7839 KING       PRESIDENT 10
7844 TURNER     SALESMAN 30
7876 ADAMS      CLERK     20
7900 JAMES      CLERK     30
7902 FORD       ANALYST   20
7934 MILLER     CLERK     10

14 개의 행이 선택되었습니다.

SQL> select count(*) from emp_external;

COUNT(*)
----------
14

SQL> select * from emp_external
2 where empno=7521;

EMPN ENAME JOB DE
---- ---------- --------- --
7521 WARD SALESMAN 30

===========================================================================================================

[조인시의 분석]
SQL> create table emp2_external
2 (
3   empno char(4),
4   ename char(10),
5   job char(9),
6   deptno char(2)
7 )
8 organization external
9 (
10   type oracle_loader
11   default directory test_dir
12   access parameters
13   (
14    fields terminated by ','
15    (empno, ename, job, deptno)
16   )
17   Location ('emp2.dat')
18 );
테이블이 생성되었습니다.

SQL> select a.empno, b.job, a.job
2 from emp_external a, emp_external b
3 where a.empno = b.empno
4 and a.empno = 7900
5 and b.empno = 7900;

EMPN JOB JOB
---- --------- ---------
7900 CLERK CLERK

[external table - regular table간의 조인]

SQL> create table emp_regular
2 (
3   empno char(4),
4   ename char(10),
5   job char(9),
6   deptno char(2)
7 );

테이블이 생성되었습니다.

SQL> insert into emp_regular values(7900, '길동', 'DBA', 50);

1 개의 행이 만들어졌습니다.

SQL> select a.empno, b.job, a.job
2 from emp_external a, emp_regular b
3 where a.empno = b.empno
4 and a.empno = 7900
5 and b.empno = 7900;

EMPN JOB JOB
---- --------- ---------
7900 DBA CLERK

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

datapump : export(expdp)---import(impdp)

---sh schema에 sales(or schemas 즉 sh외에 유저들) expdp / impdp---
/u01/oradata의 하위에 datapump라는 폴더를 하나 생성한다. 생성된 폴더로 이동한 후에
system 계정으로 접속한다.
잠겨있는 sh유저에 대해서 계정을 풀어주고 생성한 datapump에 대해서 다음과
같은 권한을 준다.

create directory dirpump as '/u01/oradata/datapump';
grant read, write on directory dirpump to sh;

sh 유저로 접속한다.
select count(*) from sales;
백업대상인 sales의 테이블 용량을 알아본다.
select sum(bytes)/1024/1024 from user_segments
where segment_name='SALES';

SUM(BYTES)/1024/1024
-----------------------
15.875

expdp_check.par라는 파일을 vi편집기로 생성한후에 다음과 같이 입력한다.

userid=system/oracle       : 해당 데이터를 가지고 있는 사용자
directory=dirpump            : 논리적 경로를 담고 있는 디렉토리
job_name=datapump        : 작업 프로세스명
estimate=statistics           : 예상 작업 통계 생성
estimate_only=Y              : 오로지 예상측정량만 계산
logfile=expdp_pump.log    : 출력에 대한 로그 기록
filesize=20M                    : 실제 데이터를 10M씩 분할 생성
tables=sales                   : 테이블은 sales를 선택
4번째와 5번째 옵션을 보면 측정만 할뿐 실제로 백업을 수행하지
않으므로 수행하기 위해서는 estimate 두 라인을 삭제해준다.

마지막라인에는 sales 라는 테이블을 백업하는 라인이고 스키마인 sh
를 백업할려면 다음과 같이 수정해준다.

schemas=sh
sh대신에 sys, hr, oe등등 모두 가능하다

$ expdp parfile=expdp_check.par

[-----$ expdp help=y-----옵션 확인]

Starting "SH"."DATAPUMP": parfile=expdp_check.par
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type -----------------내용 확인

estimated "SH"."sales"                            85.04 MB
Total estimation using STATISTICS method: 85.04 MB
Job "SH"."DATAPUMP" successfully completed at 11:41:00(완료)

expdp_check.par expdp_pump.log expdat.dmp(생성을 확인할수 있다)

$ cp expdp_check.par expdp_pump.par
$ vi expdp_pump.par

userid=sh/sh
directory=dirpump
job_name=datapump
logfile=expdp_pump.log
dumpfile=expdp_pump%U.dmp : 출력할 덤프파일명(파일 분할)
filesize=20M
tables=sales

$ expdp parfile=expdp_pump.par

Export: Release 10.2.0.1.0 - Production on Sunday, 11 November, 2007 11:42:40
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Starting "SH"."DATAPUMP": parfile=expdp_pump.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 151 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SH"."sales" 124.1 MB 8192000 rows
Master table "SH"."DATAPUMP" successfully loaded/unloaded
******************************************************************************
Dump file set for SH.DATAPUMP is:
/app/oracle/oradata/datapump/expdp_pump01.dmp
/app/oracle/oradata/datapump/expdp_pump02.dmp
/app/oracle/oradata/datapump/expdp_pump03.dmp
/app/oracle/oradata/datapump/expdp_pump04.dmp
/app/oracle/oradata/datapump/expdp_pump05.dmp
/app/oracle/oradata/datapump/expdp_pump06.dmp
/app/oracle/oradata/datapump/expdp_pump07.dmp
Job "SH"."DATAPUMP" successfully completed at 11:43:14
$ ls (---파일이 분할된것을 확인할수 있다.---)
$ ll
-----파일 분할 된것 확인-----

$ vi impdp_pump.par
userid=sh/sh
directory=dirpump
job_name=datapump
logfile=impdp_pump.log
dumpfile=expdp_pump%U.dmp(파일이 분할되어있는경우만)
tables=sales
or
schemas=sh
table_exists_action=append

$ ls
expdp_check.par expdp_pump01.dmp expdp_pump04.dmp expdp_pump07.dmp
expdp_pump.log expdp_pump02.dmp expdp_pump05.dmp impdp_pump.par
expdp_pump.par expdp_pump03.dmp expdp_pump06.dmp

$ impdp parfile=impdp_pump.par

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

create user profile

프로파일은 사용자에게 부여하는 자원과 패스워드 관리 정책이다.
DB를 처음 생성할 때 자동으로 DEFAULT 프로파일이 생성되며,
프로파일을 지정하지 않고 생성된 유저는 이 프로파일 정책을 따르게 된다.
DEFAULT 프로파일은 최초에는 어떠한 제한도 가지고 있지 않는다.
1. 프로파일 생성
    SQL> CREATE PROFILE any_profile_name LIMIT
                CPU_PER_SESSION UNLIMITED -- 해당 세션에 허용된 CPU 시간(1/100초)
                CPU_PER_CALL UNLIMITED   -- 호출(구문분석, 실행, 추출)을 위해 허용되는 CPU 시간(1/100초)
                CONNECT_TIME UNLIMITED   -- 접속이 허용된 시간(분)
                IDLE_TIME UNLIMITED   -- 작업을 하지 않은 채로 접속이 허용된 시간(분)
                SESSIONS_PER_USER UNLIMITED -- 세션당 허용된 유저 수
                LOGICAL_READS_PER_SESSION UNLIMITED -- 세션이 디스크와 메모리에서 읽을 수 있는 블록 수(Block)
                LOGICAL_READS_PER_CALL UNLIMITED -- SQL문을 수행하기 위해 세션이 릭을 수 있는 블록 수(Block)
                PRIVATE_SGA UNLIMITED   -- ※ shared sever를 사용할 때만 가능. shared pool에서 전용으로
                                                           사용할 수 있는 공간(Bytes)
                COMPOSITE_LIMIT UNLIMITED -- 하나의 서비스 단위에서 사용할 수 있는 총 비용으로
                CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, PRIVATE_SGA에 가중치를 두어 계산(서비스)
                PASSWORD_LIFE_TIME UNLIMITED -- 같은 패스워드를 사용할 수 있는 기간 지정(日)
                PASSWORD_GRACE_TIME UNLIMITED -- 패스워드 라이프 타임이 지난 후 계정을 잠그기 전까지 경고 메세지
                                                           를 보여주는 기간(日)
                PASSWORD_REUSE_MAX UNLIMITED            -- 현재 사용하는 패스워드를 재사용하기 위해서는 패스워드를 몇 번
                                                           바꿔야 하는가
                PASSWORD_REUSE_TIME UNLIMITED           -- 현재 사용하는 패스워드를 보관하는 기간
                FAILED_LOGIN_ATTEMPTS UNLIMITED         -- 여기서 지정한 횟수를 초과한 로그인 실패가 있었을 경우 계정을
                                                           잠궈버린다
                PASSWORD_LOCK_TIME UNLIMITED            -- 로그인 실패로 계정이 잠겼을 때, 지정한 일자가 지나면 자동으로
                                                           잠금이 풀린다
                PASSWORD_VERIFY_FUNCTION DEFAULT;       -- 패스워드 복잡성 함수. $ORACLE_HOME/rdbms/admin/utlpwd.sql 이라는
                                                           스크립트를 수정하면 자신이 원하는 정책을 세울 수 있을 것이다.
              ※ 스크립트에는 펑션의 이름이 verify_function 로 지정되어 있다. 수정할 필요가 있다면 수정한다.
              스크립트 마지막에 디폴트 프로필을 수정하는 부분이 있다. 적용하고 싶다면 적용하고 아니라면 삭제한다.
2. 프로파일 부여
    SQL> ALTER USER any_user_name PROFILE any_profile_name;
    혹은 생성시에 부여할 수도 있다.
    SQL> CREATE USER any_user_name ... PROFILE any_profile_name ...;
3. 프로파일 수정
    SQL> ALTER PROFILE any_profile_name LIMIT
            ...
            any_profile_options
            ...;
수정된 프로파일은 현재 접속중인 세션에는 영향을 주지 않는다.
4. 프로파일 삭제
    SQL> DROP PROFILE any_profile_name[ CASCADE];
해당 프로파일을 사용하는 유저가 있다면 cascade 옵션을 준다. 그렇게 해서 삭제된 유저의 프로파일은 서버의 기본 프로파일로 변경된다.
5. 프로파일 확인
DBA_USERS 의 PROFILE 컬럼, DBA_PROFILES 테이블의 PROFILE 컬럼을 조인한다.

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

SQL*Loader

1. 임의의 컬럼에 문자열(값)을 입력한 경우
   [ 테이블 구조 ]
   create table sample
   (a number, b number,    c number,    d varchar2(10))

   [ 컨트롤 화일 ? sample.ctl]
   load data
   infile sample.dat
   replace
   into table sample
   fields terminated by ','
   (a integer external,
    b integer external,
    c CONSTANT '100',
    d char)

   [ 외부 데이타 파일 - sample.dat ]
   1,2,DATA
   3,4,DATA2
   5,6,DATA3

$ sqlldr hr/hr control=sample.ctl   data=sample.dat

   [ 검색결과 ]
   SQL> select * from sample;
       A       B       C    D
     ------- ----- ------ ------------
       1       2     100   DATA
       2       4     100   DATA2

2. 로드한 때의 날짜를 넣고 싶은 경우
   [ 테이블 구조 ]
   create table sysdate_table
   (a number,    b date,    c varchar(10))
   [ 컨트롤 화일 ]
   load data
   infile sysdate_table.dat
   replace
   into table sysdate_table
   fields terminated by ','
   (a integer external,
    b sysdate,
    c char(10))
   [ 외부 데이타 화일 ]
   111,STRINGS
   222,STRINGS2
   333,STRING3
   [ 검색결과 ]
   SQL> select * from sysdate_table
        A         B           C
     ------- --------- ----------
      111    13-MAY-94    STRING
      222    13-MAY-94    STRING2
SYSDATE는 Conventional Path의 경우는 실행시에 삽입된 각각의 레코드 배열마다, Direct Path의 경우는 로드된각각의 레코드의 블록마다 사용된다. 새로운 값으로 변경된다.

3. SEQUENCE를 임의의 수에서 임의의 수만큼 붙이고 싶은 경우
   [ 테이블 구조 ]
   create table seq_table
   (a varchar(10),    b number,    c varchar(10))
   [ 컨트롤 화일 - seq_table.ctl ]
   load data
   infile seq_table.dat
   replace
   into table seq_table
   fields terminated by ','
   (a char,
    b sequence(100,5),
    c char)
   [ 외부 데이타 파일 - seq_table.dat ]
    1,a
    2,b
    3,c
   [ 검색결과 ]
   SQL> select * from seq_table
     A    B     C
   ----- ---- --------
    1    100    a
    2    105    b
    3    110    c

4. 포지션 지정시 char형 전후의 블랭크도 로드하고 싶은 경우
   [ 테이블 구조 ]
   create table pretb
   (a varchar(10),    b varchar(10),    c varchar(10))
   [ 컨트롤 화일 ? pretb.ctl]
   load data
   infile pretb.dat
   preserve blanks
   into table pretb
   (a position(01:05) char,
    b position(06:10) char,
    c position(11:20) char)
   [ 외부 데이타 파일 ? pretb.dat ]
    12 4 67890 ab def hi
       2 67890 ab def hi
   [ 검색결과 ]
   SQL> select * from pretb;
        A       B         C
     ------- ------- ------------
      12 4    67890   ab def hi
         2    67890   ab def hi

   SQL> select length(a), length(c) from pretb;
   LENGTH(A) LENGTH(C)
   --------- ----------
        5        10
5 10

5. 데이타가 없는 경우 NULL 데이타를 넣고자 할 때
   [ 테이블 구조 ]
   create table null_table
   (a varchar(10),    b varchar(10),    c varchar(10))
   [ 컨트롤 화일 - null_table.ctl]
   load data
   infile null_table.dat
   into table null_table
   fields termintated by ','
   trailing nullcols
   (a char,
    b char,
    c char)
   [ 외부 데이타 파일 - null_table.dat ]
    1,aa,
    2,bb,FF
    3,cc,
   [ 검색결과 ]
   SQL> select * from null_table;
   A       B       C
   ----- ----- -------
     1    aa
     2    bb      FF
     3    cc
trailing nullcols를 사용하지 않으면 1 레코드째와 3 레코드째가 데이타 에러가 된다.

6. CHAR형 필드가 BLANK로 채워져 있을 때 NULL을 삽입하고 싶은 경우
   [ 테이블 구조 ]
   create table nulltb
   (a varchar(10),    b varchar(10),    c varchar(10))
   [ 컨트롤 파일 ? nulltb.ctl ]
   load data
   infile nulltb.dat
   replace
   into table nulltb
   fields terminated by ','
   (a char,
    b char,
    c char(10) nullif c = blanks)
   [ 외부 데이타 파일 ? nulltb.dat ]
   aa,bb, ,
   11,22, ,
   99,88,AA
   00,00,BB
   [ 검색결과 ]
   SQL> select * from nulltb;
   A       B       C
----- ------ ------
aa      bb
11      22
99      88      AA
00      00      BB

7. POSITION 지정시 BLANK를 그대로 로드하고 싶은 경우
   [ 테이블 구조 ]
   create table nulltb2 (a varchar(10),    b varchar(10),    c date)
   [ 컨트롤 화일 ]
   load data
   infile null3.dat
   replace
   preserve blanks
   into table nulltb2
   (a position(1:2) char,
    b position(3:4) char nullif b = blanks,
    c position(5:13) date "YY/MM/DD")
   [ 외부 데이타 화일 ]
   998892/11/11
      94/12/12
   [ 검색결과 ]
   SQL>select * from nulltb2;
    A       B       C
   ----- ----- ---------------
   99      88      04/04/20
                   04/04/20
SQL> select length(a), length(b) from nulltb2;
LENGTH(A) LENGTH(B)
--------- ----------
        2         2
        2

8. BLANK가 들어가 있을 때 0을 입력하고 싶은 경우
    [ 테이블 구조 ]
    create table def2
    (a varchar(10),     b varchar(10),     c number)
   [ 컨트롤 화일 ]
   load data
   infile def2.dat
   replace
   into table def2
   fields terminated by ','
   (a char,
    b char,
    c integer external defaultif c = blanks)
   [ 외부 데이타 화일 ]
   11,11,123
   22,22, ,
   33,33, ,
   44,44, ,
   [ 검색결과 ]
   SQL> select * from deft;
   A       B        C
----- -------- -------
    11      11       123
    22      22         0
    33      33         0
    44      44         0

9. 40070건의 DATA가 들어 있는 우편번호 정보를 Oracle Table로 Loading 하시오.

10.1000건의 데이터가 들어 있는 hotel_tol.dat 라는 파일이 있다. 이 데이터가 들어 있는 형식이 다음
과 같을 때 오라클 DB 속으로 1000건의 데이터를 Loading 하시오.

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

Alert.log 파일 sql로 확인하는 방법

SQL> create directory BDUMP as '/u01/ora920/admin/ORA920/bdump'

SQL> create table alertlog ( text varchar2(80) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alert_ORA920.log')
)
reject limit 1000;

SQL> select * from alertlog where text like '%ORA-%';

--------------------------------------------------------------------------------
ORA-00202: controlfile: '/u01/ora920/oradata/ora920/control01.ctl'
ORA-27037: unable to obtain file status
ORA-205 signalled during: ALTER DATABASE   MOUNT...
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

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

Admin I ch.01 정리

# database buffer cache
: 사용자가 요청한(select,DML 등) data block 복사본을 disk의 datafile 로 부터 읽어들여
저장하는 메모리 공간 (단위:block) >>> I/O성능향상을 위해, reuse(reduce disk I/O)
# redo log buffer
: server process에 의해 변경된 정보(redo information=redo entry)를 저장하는
메모리 공간 (1. PGA > 2. redo log buffer copy > 3. LGWR > 4. redo log file 기록)
목적-복구정보로 사용
# shared pool
: 사용자들에 의해 분석(parse)된 여러가지 정보를 보관하여 공유가능하게 만들어주는
메모리 공간 ( library(SQL분석정보) / dictionary cache )
# large pool
: 대용량 메모리 작업(Backup/recovery, batch load, parallel prcessing...)을 위해
설정하는 선택적 메모리 공간
(설정권장-미설정시 shared pool에서 발생 > 메모리부족현상, fragmentation 발생 가능..)
# CKPT
: checkpoint event(동기화 이벤트)가 발생하면
1) DBWn에 신호를 보내고 > dirty block을 datafile에 기록.
2) control file / datafile header 정보를 최신의 물리적 정보로 update 수행! (동기화정보)   > 무결성 보장
# SMON
: 비정상적 종료(instance fail)가 발생한 경우 (정전, 잘못된 주소참조로 인한 OS강제종료, Device fail..)
> 미쳐 기록되지 못한 buffer 정보들을
   - open중에 commit된정보는 recovery를(using redo log file) 수행하고(roll forward),
   - open후에 uncommit 된 정보는 rollback을(using undo tablespace > undo segment) 수행함.
# PMON
: 비정상적으로 종료된 세션을 정리하는 프로세스. (강제로그아웃, session kill...)
cleanup > 1) rollback 2) lock 해제 3) resource 해제
# DBWn (n=0~19) > CPU 수와 연관
: buffer에 저장된 dirty block을 주기적으로 disk의 datafile에 기록하는 역할.
when? 1) checkpoint event발생    2) free buffer 부족 시
          3) dirty block수가 임계치 도달 ...
# LGWR
: server process > redo log buffer > 주기적으로 LGWR 내려쓰기 > redo log file
when? commit, buffer 1/3 full, 매3초마다, 1MB이상의 redo발생, DBWn쓰기 직전
# ARCn (n=0~29)
# controlfile : 동기화정보 (for checkpoint) > 최신의 물리적 저장정보
> show parameter control_files, v$controlfile, v$controlfile_record_section
# data file : schema object repository (물리) vs tablespace(논리)
> dba_data_files, v$datafile   vs dba_tablespaces, v$tablespace > dba_temp_files, v$tempfile
# redo log file : 복구정보 (for LGWR(주기적으로..), server process(수동 log switch))
> v$log, v$logfile, v$log_history
# parameter file : DB parameter 값을 저장한 파일
> show parameter XXX, v$parameter, v$spparameter
# archive log file : ARCn, log switch event발생
> v$archived_log, archive log list
# password file : sysdba 권한자들에 대한 목록 보유 (remote 접근 시 인증용으로 사용)
> v$pwfile_users
select empno, ename, job, sal from empl;
client > user process > server process

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

extent

col segment_name for a25
col segment_type for a15
col tablespace_name for a15
set linesize 150
set pagesize 100
select segment_name, segment_type, tablespace_name
,count(extent_id), sum(blocks), sum(bytes)
from dba_extents
where owner=upper('&owner')
group by segment_name, segment_type, tablespace_name
/
set linesize 80

block_per_rows

select dbms_rowid.rowid_block_number(rowid), count(*)
from &tablename
group by dbms_rowid.rowid_block_number(rowid)
/

procedure-salary_inc_dec_lab

특정 사원의 급여를 일정비율 인상/인하하는 프로시져 (arg - 사원번호, 인상율/인하율)
CREATE OR REPLACE PROCEDURE Update_Test
       ( v_empno IN    emp.empno%TYPE,
         v_rate     IN    NUMBER )
         IS
          v_emp emp%ROWTYPE ;
         BEGIN
           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.Emp_salary : ' || v_emp.sal );
         END ;
         /
프로시저가 생성되었습니다.
===================================================================================
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE Update_Test(7900, -10);
데이터 수정 성공
*** Data Modify Successful ***
1.Emp_no : 7900
2.Emp_name : JAMES
3.Old_Emp_salary : 890 > 변경전 급여.
4.New_emp_salary : 855 > 변경후 급여.
-> 7900번 사원의 급여를 10% 인하했습니다.

WS1.ch10 standard auditing

----- 표준 감사 수행 -----
sqlplus / as sysdba 접속

startup 이후에 파라미터 조회

show parameter audit

NAME                                 TYPE                                             VALUE
--------------------- ----------- ------------------------------------
audit_file_dest                      string     /u01/app/oracle/admin/orcl/adump
audit_sys_operations         boolean     FALSE
audit_syslog_level                 string
audit_trail                             string      NONE

조회를 해보면 audit_trail의 값이 NONE다. 이 값을 DB로 바꿔줘야 한다.
spfile에 올려야 하기 때문에 DB를 내렸다 올려야 한다.
-static parameter입니다.-
alter system set audit_trail="DB" scope=spfile;
후에 내렸다 올린다.

startup이후 파라미터 조회를 해보면 DB로 수정되어있는 것을 볼수 있다.

1. Object 감사 수행

select owner, object_name, object_type, sel from dba_obj_audit_opts
where object_name='EMP' and owner='SCOTT';

스크립트로 저장한다.
save audit_obj.sql

audit select on scott.emp by session whenever successful;
Audit succeeded.<---이 메세지가 떠야 한다.
scott유저의 emp테이블에 성공적으로 select를 했을때 감사를 실시 한다.

audit_obj.sql를 실행하면 아래의 내용이 보인다.

OWNER OBJECT_NAME OBJECT_TYPE SEL
--------------- ------------------ ----------------- ------
SCOTT EMP TABLE S/-

select * from dba_obj_audit_opts
where object_name='EMP' and owner='SCOTT'

조회 해보면
SEL
----
S/-
요렇게 나온다.

* dba_obj_audit_opts 는 object에 설정할 수 있는 각 audit option을 column으로 하고 있으며, 내용은 [A/S]/[A/S]의 형식을 갖는다. / 의 앞부분은 successful일 경우, 뒷부분은 not successful일 경우를 표시한다.

* A는 by access, S는 by session을 의미한다. by access는 해당 명령이 내려질 때마다 정보를 기록하고, by sessoin은 접속된 세션에 대하여 하나의 레코드만 생성한다.

select count(*) from sys.aud$;
를 실행하면 0으로 나온다.

scott로 접속한다.

select * from emp where sal >3000;
조회를 한번 해보고~

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------- ---------- ----------- ---------- ------------------ -------- ---------- --------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

select count(*) from sys.aud$;
로 다시 조회해본다.

그럼 count(*)가 1로 변한것을 알 수가 있다.

select os_username, username, timestamp, owner, obj_name, action_name, ses_actions, returncode
from dba_audit_object

OS_USERNAM USERNAME TIMESTAMP OWNER OBJ_NAME ACTION_NAM SES_ACTIONS RETURNCODE
---------- ---------- ------------------- ----- ---------- ---------- ------------------- ----------
oracle SCOTT 2009-12-24 17:58:38 SCOTT EMP SESSION RE ---------S------ 0

save audit_obj2.sql
스크립트를 저장해둔다.

* returncode가 0인 경우는 SUCCESS 임을 의미한다.

* by session로 설정한 경우 audit를 설정한 경우 action_name 은 ‘SESSION REC’으로 표시된다.

* ses_actions columns은 총 13가지의 action(alter, audit, comment, delete, grant, index, insert,
lock, rename, select, update, reference, execute)에 대한 Audit 정보를 포함한다. 각 Action에 대해 모두 성공하였다면 ‘S’를 실패하였다면 ‘F’로 표현되며 성공과 실패가 모두 있었다면 ‘B’로 표현된다.

* by access로 설정한 경우 action_name 컬럼에 해당 action(예: delete, insert)등이 바로 기술된다.

----- Privilege Audit -----

select * from dba_priv_audit_opts;
조회를 하면 결과가 없다.

audit create session by scott;
성공했다는 메세지와 함께 다시 조회를 해보면 조회 내용이 나온다.

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------ ------------------ ----------------- ------------ ----------
SCOTT CREATE SESSION BY ACCESS BY ACCESS

그런 후 scott계정으로 접속을 하고 10초가 지난뒤에 접속을 끊는다.
그리고 나서 시간을 확인하고 관리자로 접속한다.

select os_username, username, timestamp, action_name, logoff_time, logoff_lread, logoff_pread
from dba_audit_session
where username='SCOTT';

OS_USERNAM USERNAME TIMESTAMP ACTION_NAM LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD
---------- ---------- ------------------- ---------- ------------------- ------------ ------------
oracle SCOTT 2009-12-25 12:58:22 LOGOFF 2009-12-25 12:58:55 305 3

위의 내용을 확인 후, 다른 터미널 창을 열고 scott로 접속한다.
다시 원래 터미널에서 재 조회를 해보면 된다.

----- Statement Audit -----

존재하지 않는 오브젝트에 대한 구문을 실행시에 Audit Trail 작성
audit not exists by scott;

성공했다는 메세지를 볼수 있다.
select * from dba_stmt_audit_opts;

USER_NAME PROXY_NAME   AUDIT_OPTION    SUCCESS    FAILURE
------------ --------------- ----------------- ---------- ----------
SCOTT                                 CREATE SESSION BY ACCESS BY ACCESS
SCOTT                                 NOT EXISTS           BY ACCESS BY ACCESS

조회를 한 후에 scott으로 접속해보자.

grant select on not_exist_table to tester;

관리자로 접속한 후에
select os_username,username,timestamp,obj_name,action_name,obj_privilege, grantee
from dba_audit_statement;

OS_USERNAM USERNAME   TIMESTAMP           OBJ_NAME   ACTION_NAM OBJ_PRIVILEGE    GRANTEE
---------- ---------- ------------------- ---------- ---------- ---------------- ------------------------------
oracle     SCOTT      2009-12-25 13:53:13 NOT_EXIST_ GRANT OBJE ---------Y------ TESTER
                                          TABLE      CT

-- 단점 --
위의 결과에서는 어떤 구문을 실행했을 때 Auditing 되었는지 정확히 알 수가 없고, Object Audit 에서 볼 수 있듯이 어떤 데이터를 조회했는지 알 수가 없다.

10g부터 추가된 DB_Extended 기능을 사용해 본다.

conn /as sysdba
alter system set audit_trail=db_extended scope=spfile;
System altered.
shutdown immediate
startup
show parameter audit_trail

NAME TYPE VALUE
---------------------- ----------- ---------------
audit_trail string DB_EXTENDED

audit insert on scott.emp by access;

select owner, object_type, object_name, ins from dba_obj_audit_opts
where owner = 'SCOTT' and object_name = 'EMP';

OWNER OBJECT_TYPE OBJECT_NAME INS
----- ----------------- ------------ -----
SCOTT TABLE EMP A/A

desc dba_audit_object

Name                                                              Null?    Type
----- -------- --------------------------------------------------------------------------
OS_USERNAME                                                        VARCHAR2(255)
USERNAME                                                              VARCHAR2(30)
USERHOST                                                               VARCHAR2(128)
TERMINAL                                                                VARCHAR2(255)
TIMESTAMP                                                              DATE
OWNER                                                                     VARCHAR2(30)
OBJ_NAME                                                                VARCHAR2(128)
ACTION_NAME                                                          VARCHAR2(28)
NEW_OWNER                                                             VARCHAR2(30)
NEW_NAME                                                               VARCHAR2(128)
SES_ACTIONS                                                           VARCHAR2(19)
COMMENT_TEXT                                                       VARCHAR2(4000)
SESSIONID                                                 NOT NULL NUMBER
ENTRYID                                                    NOT NULL NUMBER
STATEMENTID                                            NOT NULL NUMBER
RETURNCODE                                            NOT NULL NUMBER
PRIV_USED                                                                VARCHAR2(40)
CLIENT_ID                                                                  VARCHAR2(64)
ECONTEXT_ID                                                            VARCHAR2(64)
SESSION_CPU                                                            NUMBER
EXTENDED_TIMESTAM                                               TIMESTAMP(6) WITH TIME ZONE
PROXY_SESSIONI                                                       NUMBER
GLOBAL_UID                                                              VARCHAR2(32)
INSTANCE_NUMB                                                       NUMBER
OS_PROCESS                                                            VARCHAR2(16)
TRANSACTIONID                                                         RAW(8)
SCN                                                                           NUMBER
SQL_BIND                                                                   NVARCHAR2(2000)
SQL_TEXT                                                                  NVARCHAR2(2000)

select username, owner, obj_name, ACTION_NAME, SES_ACTIONS, RETURNCODE, TIMESTAMP, SQL_BIND, SQL_TEXT
from dba_audit_object;

USERNAME OWNER OBJ_NAME ACTION_NAME SES_ACTIONS RETURNCODE TIMESTAMP SQL_BIND SQL_TEXT
---------- ----- ---------- --------------- ------------------- ---------- ------------------- --------------- ---------------
SCOTT SCOTT EMP SESSION REC ---------S------ 0 2009-12-24 17:58:38

scott로 접속한 후에
variable empno number;
variable ename varchar2(8);

begin
:empno := 9999;
:ename := 'TESTER';
end;
/

insert into emp values (:empno, :ename, 'MANAGER', 7499, SYSDATE, 3000, '', 20);

commit후에 다시 조회를 해보면 SQL_BIND, SQL_TEXT의 내용을 볼 수가 있다.

WS1.ch10 audit trail control

Growth and Size of the Standard Audit 제어

Audit Trail이 쌓이는 SYS.AUD$ 테이블은 SYSTEM 테이블스페이스 생성된다.
그러므로 SYS.AUD$ 테이블에 데이터가 쌓임으로써 SYSTEM 테이블에 대하여 경합 등 부담을 줄 수 있다.

예를 들어, CREATE SESSION에 대한 AUDITING을 수행할 때 세션의 사용량에 따라 엄청난 데이터가 축척될 수 있다.
그러므로 불필요한 AUDIT의 사용은 데이터베이스의 성능을 저하시킬 수 있다.
그러므로 불필요한 Audit기능은 제한하고, Audit하는 기간 동안 SYS.AUD$ 테이블의 크기를 적절히 유지하도록 해야한다.

?? Purging Audit Record from the Audit Trail
?? Archiving Audit Trail Information
?? Reducing the Size of the Audit Trail

제어 방법은 다음과 같다.

-- Purging Audit Record from the Audit Trail
DELETE FROM SYS.AUD$;

DELETE FROM SYS.AUD$ WHERE obj$name = ‘EMP’;

TRUNCATE TABLE SYS.AUD$;

-- Archiving Audit Trail Information
INSERT INTO table SELECT … FROM SYS.AUD$ …

exp
sys/sys as sysdba
file=aud.dmp
tables=aud$;
차례대로 입력하면 백업을 시작한다.

-- Reducing the Size of the Audit Trail
: Archiving과 Purging을 이용한 관리
1. SYS.AUD$ 에 대한 백업을 수행한다. - Export 등
2. sys 유저로 접속한다. ? 관리자에 의한 수행
3. TRUNCATE TABLE SYS.AUD$; (HWM 의 이동으로 extent할당을 해제하여 테이블 내 공간을 줄임.)
4. Audit Trail Record가 많이 생성되면 1번부터 재수행한다.

WS1.ch10 Trigger Audit

sys로 접속

CREATE TABLE emp_audit (
old_empno NUMBER(4),
old_ename VARCHAR2(10),
old_job VARCHAR2(9),
old_mgr NUMBER(4),
old_hiredate DATE,
old_sal NUMBER(7,2),
old_comm NUMBER(7,2),
old_deptno NUMBER(2),
new_empno NUMBER(4),
new_ename VARCHAR2(10),
new_job VARCHAR2(9),
new_mgr NUMBER(4),
new_hiredate DATE,
new_sal NUMBER(7,2),
new_comm NUMBER(7,2),
new_deptno NUMBER(2),
changed_by VARCHAR2(8),
change_type CHAR(1),
timestamp DATE )

위의 테이블을 생성한다.

CREATE OR REPLACE TRIGGER LogEmpChanges
BEFORE INSERT OR DELETE OR UPDATE ON scott.emp
FOR EACH ROW
DECLARE
v_ChangeType CHAR(1);
BEGIN
/* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */
IF INSERTING THEN
v_ChangeType := 'I';
ELSIF UPDATING THEN
v_ChangeType := 'U';
ELSE
v_ChangeType := 'D';
END IF;
INSERT INTO emp_audit (
change_type, changed_by, timestamp, old_empno,
old_ename, old_job, old_mgr, old_hiredate, old_sal,
old_comm, old_deptno, new_empno, new_ename, new_job,
new_mgr, new_hiredate, new_sal, new_comm, new_deptno)
VALUES (
v_ChangeType, USER, SYSDATE, :old.empno, :old.ename,
:old.job, :old.mgr, :old.hiredate, :old.sal, :old.comm,
:old.deptno, :new.empno, :new.ename, :new.job, :new.mgr,
:new.hiredate, :new.sal, :new.comm, :new.deptno);
END LogEmpChanges;
트리거를 생성한다.

scott로 접속
INSERT INTO emp VALUES ( 9999, 'TESTER', 'CLERK', 7782, SYSDATE, 1000, 0, 10);

commit;

UPDATE emp SET comm = 300 WHERE empno = 9999;

commit;

DELETE FROM emp WHERE empno = 9999;

commit;

관리자로 접속

SELECT OLD_EMPNO, OLD_ENAME, OLD_COMM, NEW_EMPNO, NEW_ENAME, NEW_COMM,
CHANGED_BY, CHANGE_TYPE, TIMESTAMP
FROM EMP_AUDIT;

OLD_EMPNO OLD_ENAME    OLD_COMM NEW_EMPNO NEW_ENAME    NEW_COMM CHANGED_ C TIMESTAMP
---------- ---------- ---------- ---------- ---------- ---------- -------- - -------------------
                                       9999 TESTER              0 SCOTT    I 2008-12-26 13:22:19
      9999 TESTER              0       9999 TESTER            300 SCOTT    U 2008-12-26 13:22:25
      9999 TESTER            300                                  SCOTT    D 2008-12-26 13:22:31

위와 같은 소스를 자동 생성해주는 샘플 Package를 생성하여 응용할 수 있다. [첨부파일]
해당 테이블의 정보를 추출하여 audit trail record를 담을 테이블과 이벤트를 처리할 트리거를 자동생성하고 제거할 수 있는 패키지이다. 구문을 자동 생성하고, 실행시키는 루틴을 포함시켜 놓았다. 오라클의 제공하는 Audit 기능을 대체할 수 있도록 응용해 보기 바란다.

-----audit_package.sql-----

--Trigger 를 이용한 DML Auditing - Package Name : AUDIT_UTIL
CREATE OR REPLACE PACKAGE AUDIT_UTIL
IS
-- AUDIT 테이블 생성
PROCEDURE create_audit_table(
dest_table_owner IN VARCHAR2,
dest_table_name IN VARCHAR2,
audit_table_name IN VARCHAR2 DEFAULT '',
tablespace_name IN VARCHAR2 DEFAULT 'SYSTEM');
-- TRIGGER 생성
PROCEDURE create_audit_trigger(
audit_table_name IN VARCHAR2,
dest_table_owner IN VARCHAR2,
dest_table_name IN VARCHAR2);
-- DML AUDIT 를 위한 테이블 및 트리거 자동 생성
PROCEDURE create_dml_audit(
dest_table_owner IN VARCHAR2,
dest_table_name IN VARCHAR2,
audit_table_name IN VARCHAR2 DEFAULT '',
tablespace_name IN VARCHAR2 DEFAULT 'SYSTEM');
-- DML AUDIT 관련 테이블 및 트리거 제거
PROCEDURE remove_dml_audit(
audit_table_name IN VARCHAR2,
audit_trigger_name IN VARCHAR2 DEFAULT '');
-- ADUIT TABLE 내 데이터 삭제 (날짜별 이전 데이터 삭제)
PROCEDURE delete_auditing_values(
audit_table_name IN VARCHAR2,
start_date IN DATE,
end_date IN DATE DEFAULT NULL);
-- ADUIT TABLE 내 데이터 삭제 (지정 일수 이전 데이터 삭제)
PROCEDURE delete_auditing_values(
audit_table_name IN VARCHAR2,
days IN NUMBER);
END AUDIT_UTIL;
/

CREATE OR REPLACE PACKAGE BODY AUDIT_UTIL
IS
-- 0. DDL 실행 프로시져(PRIVATE)
PROCEDURE exec_ddl(statement IN VARCHAR2)
IS
v_statment VARCHAR2(4000);
v_cid INTEGER;
v_ret INTEGER;
BEGIN
v_statment := R-PLACE(statement, CHR(10), '');
v_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(C => v_cid,
STATEMENT => v_statment,
LANGUAGE_FLAG => DBMS_SQL.NATIVE);
--v_ret := DBMS_SQL.EXECUTE(v_cid);
DBMS_SQL.CLOSE_CURSOR(v_cid);
END exec_ddl;
-- 0. DML 실행 프로시져(PRIVATE)
PROCEDURE exec_dml(statement IN VARCHAR2)
IS
v_statment VARCHAR2(4000);
v_cid INTEGER;
v_ret INTEGER;
BEGIN
v_statment := R-PLACE(statement, CHR(10), '');
v_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(C => v_cid,
STATEMENT => v_statment,
LANGUAGE_FLAG => DBMS_SQL.NATIVE);
v_ret := DBMS_SQL.EXECUTE(v_cid);
DBMS_SQL.CLOSE_CURSOR(v_cid);
END exec_dml;
-- 1. AUDIT 테이블 생성 PROCEDURE : CREATE_AUDIT_TABLE (only SYS OR SYSTEM USER)
PROCEDURE create_audit_table (
dest_table_owner in VARCHAR2,
dest_table_name in VARCHAR2,
audit_table_name in VARCHAR2 DEFAULT '',
tablespace_name in VARCHAR2 DEFAULT 'SYSTEM')
IS
CURSOR c_table_columns IS
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM dba_tab_columns
WHERE owner = dest_table_owner
AND table_name = dest_table_name;
v_ddl VARCHAR2(4000);
v_audit_table_name VARCHAR2(100);
BEGIN
IF (dest_table_owner IS NULL ) OR (dest_table_name IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('No Values in Owner or Table_name');
END IF;
IF (audit_table_name IS NULL) THEN
v_audit_table_name := dest_table_name || '_AUDIT';
ELSE
v_audit_table_name := audit_table_name;
END IF;
-- Table DDL 문
v_ddl := 'CREATE TABLE ' || v_audit_table_name || ' (' || chr(10);
FOR v_col_rec IN c_table_columns LOOP
IF (c_table_columns%NOTFOUND) THEN
DBMS_OUTPUT.PUT_LINE('TABLE NOT FOUND');
END IF;
CASE
WHEN v_col_rec.data_type IN ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'NCHAR') THEN
v_ddl := v_ddl || ' old_' || v_col_rec.column_name || ' '
|| v_col_rec.data_type ||'(' || v_col_rec.data_length || ')';
WHEN v_col_rec.data_type = 'NUMBER' THEN
v_ddl := v_ddl || ' old_' || v_col_rec.column_name || ' ' || v_col_rec.data_type;
IF (v_col_rec.data_precision IS NOT NULL) THEN
v_ddl := v_ddl || '(' || v_col_rec.data_precision;
IF (v_col_rec.data_scale IS NOT NULL) THEN
v_ddl := v_ddl || ',' || v_col_rec.data_scale;
END IF;
v_ddl := v_ddl || ')';
END IF;
ELSE
v_ddl := v_ddl || ' old_' || v_col_rec.column_name || ' ' || v_col_rec.data_type;
END CASE;
v_ddl := v_ddl || ',' || CHR(10);
END LOOP;
FOR v_col_rec IN c_table_columns LOOP
IF (c_table_columns%NOTFOUND) THEN
DBMS_OUTPUT.PUT_LINE('TABLE NOT FOUND');
END IF;
CASE
WHEN v_col_rec.data_type IN ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'NCHAR') THEN
v_ddl := v_ddl || ' new_' || v_col_rec.column_name || ' '
|| v_col_rec.data_type ||'(' || v_col_rec.data_length || ')';
WHEN v_col_rec.data_type = 'NUMBER' THEN
v_ddl := v_ddl || ' new_' || v_col_rec.column_name || ' ' || v_col_rec.data_type;
IF (v_col_rec.data_precision IS NOT NULL) THEN
v_ddl := v_ddl || '(' || v_col_rec.data_precision;
IF (v_col_rec.data_scale IS NOT NULL) THEN
v_ddl := v_ddl || ',' || v_col_rec.data_scale;
END IF;
v_ddl := v_ddl || ')';
END IF;
ELSE
v_ddl := v_ddl || ' new_' || v_col_rec.column_name || ' ' || v_col_rec.data_type;
END CASE;
v_ddl := v_ddl || ',' || CHR(10);
END LOOP;
v_ddl := v_ddl || ' changed_by VARCHAR2(8),' || CHR(10);
v_ddl := v_ddl || ' change_type CHAR(1),' || CHR(10);
v_ddl := v_ddl || ' timestamp DATE' || CHR(10) || ') TABLESPACE ' || tablespace_name;
exec_ddl(v_ddl);
END create_audit_table;
-- 2. TRIGGER 생성 PROCEDURE : CREATE_AUDIT_TRIGGER
PROCEDURE create_audit_trigger(
audit_table_name IN VARCHAR2,
dest_table_owner IN VARCHAR2,
dest_table_name IN VARCHAR2)
IS
CURSOR c_table_columns IS
SELECT rownum, column_name FROM dba_tab_columns
WHERE owner = USER
AND table_name = audit_table_name;
v_audit_table_name VARCHAR2(100);
v_st VARCHAR2(4000);
BEGIN
v_audit_table_name := audit_table_name;
v_st := 'CREATE OR REPLACE TRIGGER TR_' || v_audit_table_name|| CHR(10)
|| ' BEFORE INSERT OR DELETE OR UPDATE ON ' || dest_table_owner || '.' || dest_table_name || CHR(10)
|| ' FOR EACH ROW '|| CHR(10)
|| 'DECLARE' || CHR(10)
|| ' v_ChangeType CHAR(1); ' || CHR(10)
|| 'BEGIN ' || CHR(10)
|| ' IF INSERTING THEN v_ChangeType := ''I''; ' || CHR(10)
|| ' ELSIF UPDATING THEN v_ChangeType := ''U''; ' || CHR(10)
|| ' ELSE v_ChangeType := ''D''; ' || CHR(10)
|| ' END IF; ' || CHR(10)
|| ' INSERT INTO ' || v_audit_table_name || '(';
FOR v_col_rec IN c_table_columns LOOP
IF (v_col_rec.rownum > 1) THEN
v_st := v_st || ', ';
END IF;
v_st := v_st || v_col_rec.column_name;
END LOOP;
v_st := v_st || ') ' || CHR(10) || ' VALUES (';
FOR v_col_rec IN c_table_columns LOOP
IF (v_col_rec.rownum > 1) THEN
v_st := v_st || ', ';
END IF;
CASE SUBSTR(v_col_rec.column_name, 1, 3)
WHEN 'OLD' THEN
v_st := v_st || REPLACE(v_col_rec.column_name, 'OLD_', ':OLD.');
WHEN 'NEW' THEN
v_st := v_st || REPLACE(v_col_rec.column_name, 'NEW_', ':NEW.');
ELSE
IF (v_col_rec.column_name = 'CHANGED_BY') THEN
v_st := v_st || 'USER';
ELSIF (v_col_rec.column_name = 'CHANGE_TYPE') THEN
v_st := v_st || 'v_ChangeType';
ELSIF (v_col_rec.column_name = 'TIMESTAMP') THEN
v_st := v_st || 'SYSDATE';
END IF;
END CASE;
END LOOP;
v_st := v_st || '); ' || CHR(10)
|| 'END TR_' || v_audit_table_name || ';';
exec_ddl(v_st);
END create_audit_trigger;
-- 3. DML AUDIT 를 위한 테이블 및 트리거 자동 생성 PROCEDURE : CREATE_DML_AUDIT
PROCEDURE create_dml_audit(
dest_table_owner in VARCHAR2,
dest_table_name in VARCHAR2,
audit_table_name in VARCHAR2 DEFAULT '',
tablespace_name in VARCHAR2 DEFAULT 'SYSTEM')
IS
v_audit_table_name VARCHAR2(100);
v_dest_table_owner VARCHAR2(100);
V_dest_table_name VARCHAR2(100);
BEGIN
v_dest_table_owner := UPPER(dest_table_owner);
v_dest_table_name := UPPER(dest_table_name);
IF (audit_table_name IS NULL) THEN
v_audit_table_name := UPPER(dest_table_name) || '_AUDIT';
ELSE
v_audit_table_name := UPPER(audit_table_name);
END IF;
CREATE_AUDIT_TABLE(v_dest_table_owner, v_dest_table_name, v_audit_table_name, tablespace_name);
CREATE_AUDIT_TRIGGER(v_audit_table_name, v_dest_table_owner, v_dest_table_name);
END create_dml_audit;
-- 4. DML AUDIT 관련 테이블 및 트리거 제거 PROCEDURE : MAKE_DML_AUDIT
PROCEDURE remove_dml_audit(
audit_table_name IN VARCHAR2,
audit_trigger_name IN VARCHAR2 DEFAULT '')
IS
v_c INTEGER;
v_st VARCHAR2(4000);
v_tr VARCHAR2(100);
BEGIN
v_st := 'DROP TABLE ' || audit_table_name;
exec_ddl(v_st);
IF (audit_trigger_name IS NULL) THEN
v_tr := 'TR_' || audit_table_name;
ELSE
v_tr := audit_trigger_name;
END IF;
v_st := 'DROP TRIGGER ' || v_tr;
exec_ddl(v_st);
END remove_dml_audit;
-- 5. ADUIT TABLE 내 데이터 삭제 PROCEDURE : DELETE_AUDITING_VALUES
PROCEDURE delete_auditing_values(
audit_table_name IN VARCHAR2,
start_date IN DATE,
end_date IN DATE DEFAULT NULL)
IS
v_st VARCHAR2(4000);
BEGIN
v_st := 'DELETE FROM' || audit_table_name;
IF (end_date IS NULL) THEN
v_st := v_st || ' WHERE TIMESTAMP < TO_DATE('''
|| TO_CHAR(start_date, 'YYYY-MM-DD HH24:MI:SS')
|| ''', ''fmYYYY-MM-DD HH24:MI:SS'')';
ELSE
v_st := v_st || ' WHERE TIMESTAMP BETWEEN TO_DATE('''
|| TO_CHAR(start_date, 'YYYY-MM-DD HH24:MI:SS')
|| ''', ''fmYYYY-MM-DD HH24:MI:SS'') AND TO_DATE('''
|| TO_CHAR(end_date, 'fmYYYY-MM-DD HH24:MI:SS')
|| ''', ''fmYYYY-MM-DD HH24:MI:SS'')';
END IF;
exec_dml(v_st);
COMMIT;
END delete_auditing_values;
PROCEDURE delete_auditing_values(
audit_table_name IN VARCHAR2,
days IN NUMBER)
IS
v_st VARCHAR2(4000);
v_last_date DATE;
BEGIN
v_last_date := SYSDATE - days;
v_st := 'DELETE FROM' || audit_table_name
|| ' WHERE TIMESTAMP < TO_DATE('''
|| TO_CHAR(v_last_date, 'YYYY-MM-DD HH24:MI:SS')
|| ''', ''fmYYYY-MM-DD HH24:MI:SS'')';
exec_dml(v_st);
COMMIT;
END delete_auditing_values;
END AUDIT_UTIL;
/
------------------------------------------------------------------------------------
1. 실행방법 ( AUDIT 용 TABLE 및 TRIGGER 생성)
exec make_dml_audit (dest_table_owner, dest_table_name, \
audit_table_name(default ''), tablespace_name(default 'SYSTEM');
EXAMPLE 1
- exec make_dml_audit('SCOTT', 'EMP');
EXAMPLE 2
- exec make_dml_audit('SCOTT', 'EMP', 'EMP_AUDIT', 'USERS');
확인 : DML 작업 이후 audit_table_name 조회
EXAMPLE - SELECT * FROM EMP_AUDIT;

2. AUDIT TABLE 내 데이터 삭제 PROCEDURE delete
EXAMPLE 1
- exec delete_auditing_values('EMP_AUDIT', TO_DATE('2007-08-01 18:00:00', 'fmYYYY-MM-DD HH24:MI:SS');
EXAMPLE 2
- exec delete_auditing_values('EMP_AUDIT', TO_DATE('2007-08-01 00:00:00', 'fmYYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-08-01 18:00:00', 'fmYYYY-MM-DD HH24:MI:SS'));
EXAMPLE 3
- exec delete_auditing_values('EMP_AUDIT', 1); -- 1일 이전 데이터 삭제

3. 제거방법 ( AUDIT 용 TABLE 및 TRIGGER 제거)
exec remove_dml_audit (audit_table_name, audit_trigger_name(default ''));
EXAMPLE - exec remove_dml_audit('EMP_AUDIT');
-- Test : SCOTT.EMP 에 대한 DML Auditing
exec make_dml_audit('SCOTT', 'EMP', 'EMP_AUDIT', 'USERS');
select table_name from dba_tables where trigger_name like '%_AUDIT'
select trigger_name from dba_triggers where trigger_name like 'TR_%'
connect scott/tiger
INSERT INTO emp VALUES ( 9999, 'TESTER', 'CLERK', 7782, SYSDATE, 1000, 0, 10);
COMMIT;
UPDATE emp SET comm = 300 WHERE empno = 9999;
COMMIT;
DELETE FROM emp WHERE empno = 9999;
COMMIT;
connect /as sysdba
SELECT OLD_EMPNO, OLD_ENAME, OLD_COMM, NEW_EMPNO, NEW_ENAME, NEW_COMM, CHANGED_BY, CHANGE_TYPE,
TIMESTAMP FROM EMP_AUDIT;
OLD_EMPNO OLD_ENAME OLD_COMM NEW_EMPNO NEW_ENAME NEW_COMM CHANGED_BY CH TIMESTAMP
---------- ---------- ---------- ---------- ---------- ---------- ---------------- -- ---------
9999 TESTER 0 SCOTT I 01-AUG-07
9999 TESTER 0 9999 TESTER 300 SCOTT U 01-AUG-07
9999 TESTER 300 SCOTT D 01-AUG-07
-- 일정 기간에 대한 Audit Trail Reocords 삭제
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
exec delete_auditing_values('EMP_AUDIT', TO_DATE('2007-08-01 18:00:00', 'fmYYYY-MM-DD HH24:MI:SS'));
exec delete_auditing_values('EMP_AUDIT', TO_DATE('2007-08-01 00:00:00', 'fmYYYY-MM-DD HH24:MI:SS'),
TO_DATE('2007-08-01 18:00:00', 'fmYYYY-MM-DD HH24:MI:SS'));
exec delete_auditing_values('EMP_AUDIT', 0); -- 현재 이전 삭제(전체 삭제)
-- dbms_job 을 이용하여 자동 삭제 스케줄링
VARIABLE job_no NUMBER;
VARIABLE inst_no NUMBER;
begin
select instance_number into :inst_no from v$instance;
dbms_job.submit(:job_no, 'AUDIT_UTIL(''EMP_AUDIT'', 0);', trunc(sysdate+1,'HH'), 'trunc(SYSDATE+1,''HH'')', TRUE, :inst_no);
commit;
end;
/
print :job_no;
exec dbms_job.broken(:job_no, TRUE);
exec dbms_job.remove(:job_no);
-- Audit Trail Table 제거
exec remove_dml_audit('EMP_AUDIT');

----- Value-Based Auditing -----

(터미널1: system)---------------------------------
$ sqlplus system/oracle
create table audit_employees
(username varchar2(150)
,mod_date date
,ipaddr varchar2(30)
,mod_conts varchar2(2000))
tablespace example ;

CREATE OR REPLACE TRIGGER system.hrsalary_audit
AFTER UPDATE OF salary
ON hr.employees
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :old.salary != :new.salary THEN
INSERT INTO system.audit_employees
VALUES (sys_context('userenv','os_user'), sysdate,
sys_context('userenv','ip_address'),
:new.employee_id ||
' salary changed from '||:old.salary||
' to '||:new.salary);
END IF;
END;
/
select * from system.audit_employees ;
(터미널2: hr)------------------------------------
update hr.employees
set salary= 3000
where employee_id= 198 ;
commit ;
(터미널1: system)---------------------------------
ALTER SESSION SET nls_date_format='YYYY/MM/DD HH24:MI:SS' ;
select * from system.audit_employees ;
select * from hr.employees
where employee_id = 198 ;

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

WS1.ch10 Advanced Audit FGA by 10g

show parameter audit_trail

NAME TYPE VALUE
----------------- ----------- ---------
audit_trail string NONE

BEGIN
    DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT',
    object_name => 'EMP',
    policy_name => 'POL_SCOTT_EMP',
    audit_condition => 'sal > 3000',
    enable => TRUE,
    statement_types => 'SELECT, INSERT',
    audit_trail => DBMS_FGA.DB_EXTENDED);
END;

SELECT object_schema, object_name, policy_name, policy_text, policy_column, enabled
FROM dba_audit_policies;

OBJECT_SCH OBJECT_NAM POLICY_NAME POLICY_TEXT POLICY_COLUMN ENA
------------- ------------- ------------------ --------------- --------------------- ----
SCOTT EMP POL_SCOTT_EMP sal > 3000 YES

SELECT timestamp, object_name, scn, sql_text, sql_bind
FROM dba_fga_audit_trail;

no rows selected

conn scott/tiger

var empno number;
var ename varchar2(10);
begin
     :empno := 1234;
     :ename := 'ocmkorea';
end;
/

insert into emp values (:empno, :ename, 'MANAGER', 7499, SYSDATE, 3100, '', 20);

select * from emp;
EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- -------
      8888 TESTER2    SALESMAN        7499 2008-12-26 13:41:40       3200         10         30
      1234 ocmkorea   MANAGER         7499 2008-12-26 13:45:36       3100                    20
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- -------      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

insert into emp values (7777, 'OCMKOREA2', 'SALESMAN', 7499, SYSDATE, 3200, 10, 30);

delete from emp where empno = 7777;     -- 정책에 포함되지 않은 구문이므로 기록되지 않음.

conn /as sysdba

select timestamp, object_name, scn, sql_bind, sql_text
from dba_fga_audit_trail;

TIMESTAMP           OBJECT_NAME
------------------- ----------------------------------------------------------------------
       SCN
----------
SQL_BIND
-----------------------------------------------------------------------------------------
SQL_TEXT
-----------------------------------------------------------------------------------------
2008-12-26 13:46:07 EMP
         0
insert into emp values (7777, 'OCMKOREA2', 'SALESMAN', 7499, SYSDATE, 3200, 10, 30)

* Oracle Database 10g 부터 statement_types과 audit_trail이 추가되었다. Oracle 9i Database에서 SELECT 문만 auditing이 가능했지만 Oracle Database 10g 에서는 INSERT, DELETE, UPDATE, MERGE 에 대해서도 가능하다. 또한 audit_trail 파라메터에 DBMS_FGA.DB_EXTENDED로 설정하므로 init parameter 의 audit_trail 파라메터와 무관하게 FGA 가 설정만으로 수행된 구문과 바인드 정보를 볼 수 있다.

* 위 예제에서 보았듯이 statement_types 가 ‘SELECT’, ‘INSERT’이므로 두가지의 구문형식에 대해서만 Auditing 되며, ‘DELETE’는 Auditing되지 않았다. 그리고 audit_trail 이 DB_EXTENEDED로 설정되어 sql_bind, 와 sql_text에 대해서도 audit trail이 작성되었다.

[Oracle Database 10g FGA 예제1 ? AUDIT_COLUMN_OPTS]

conn /as sysdba

truncate table fga_log$;

BEGIN
   DBMS_FGA.DROP_POLICY(object_schema => 'SCOTT',
   object_name => 'EMP',
   policy_name => 'POL_SCOTT_EMP');
END;

BEGIN
DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'POL_SCOTT_EMP1',
audit_column => 'empno, sal, comm',
enable => TRUE,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);
END;

select timestamp, object_name, scn, sql_bind, sql_text
from dba_fga_audit_trail;

conn scott/tiger

select empno, ename from emp where deptno = 20;
     EMPNO ENAME
---------- ----------
      1234 ocmkorea
      7369 SMITH
      7566 JONES
      7788 SCOTT
      7876 ADAMS
      7902 FORD

select empno, sal from emp where sal > 3000;
     EMPNO        SAL
---------- ----------
      8888       3200
      1234       3100
      7839       5000

select ename, job from emp where job > 'MANAGER';

ENAME      JOB
---------- ---------
TESTER2    SALESMAN
ALLEN      SALESMAN
WARD       SALESMAN
MARTIN     SALESMAN
KING       PRESIDENT
TURNER     SALESMAN

select empno, sal, comm from emp where deptno = 30;

     EMPNO        SAL       COMM
---------- ---------- ----------
      8888       3200         10
      7499       1600        300
      7521       1250        500
      7654       1250       1400
      7698       2850
      7844       1500          0
      7900        950

conn /as sysdba

select timestamp, object_name, scn, sql_bind, sql_text
from dba_fga_audit_trail;

TIMESTAMP           OBJECT_NAME                 SCN SQL_BIND             SQL_TEXT
------------------- -------------------- ---------- -------------------- ------------------2008-12-26 13:56:38 EMP                      599796                      select empno, ename from emp where deptno = 20
2008-12-26 13:56:42 EMP                      599800                      select empno, sal from emp where sal > 3000
2008-12-26 13:56:50 EMP                      599803                      select empno, sal, comm from emp where deptno = 30

SQL> -- audit_column_opts 값이 DBMS_FGA.ANY_COLUMNS으로 설정했을 경우 audit_column에 나열된 Column 중에 1개라도 포함이 되어 있다면 Audit Trail이 기록된다. 나열된 컬럼들 중에 없는 경우는 기록되지 않는다.

truncate table fga_log$;

BEGIN
   DBMS_FGA.DROP_POLICY(object_schema => 'SCOTT',
   object_name => 'EMP',
   policy_name => 'POL_SCOTT_EMP1');
END;

BEGIN
   DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT',
   object_name => 'EMP',
   policy_name => 'POL_SCOTT_EMP2',
   audit_column => 'empno, sal, comm',
   enable => TRUE,
   audit_column_opts => DBMS_FGA.ALL_COLUMNS);
END;

conn scott/tiger

select empno, ename from emp where deptno = 20;

     EMPNO ENAME
---------- ----------
      1234 ocmkorea
      7369 SMITH
      7566 JONES
      7788 SCOTT
      7876 ADAMS
      7902 FORD

select empno, sal from emp where sal > 3000;

     EMPNO        SAL
---------- ----------
      8888       3200
      1234       3100
      7839       5000

select ename, job from emp where job > 'MANAGER';

ENAME      JOB
---------- ---------
TESTER2    SALESMAN
ALLEN      SALESMAN
WARD       SALESMAN
MARTIN     SALESMAN
KING          PRESIDENT
TURNER     SALESMAN

select empno, sal, comm from emp where deptno = 30;             -- policy에 부합되는 조회

     EMPNO        SAL       COMM
---------- ---------- ----------
      8888       3200         10
      7499       1600        300
      7521       1250        500
      7654       1250       1400
      7698       2850
      7844       1500          0
      7900        950

conn /as sysdba

logminer 실습

접속 할 계정은 system/oracle 이다.

분석된 결과를 OS상의 파일로 저장
초기화 파라미터를 설정해야 한다.

show parameter utl_file_dir
alter system set utl_file_dir='/home/oracle/logminer' scope=spfile;

logminer라는 폴더는 미리 생성되어있어야 한다.

그리고 DB를 재시작해준다.

conn system/oracle
update scott.emp set sal=sal*1.5;
commit;

logminer를 활용하기 위해서는 패키지가 필요하다.
DBMS_LOGMNR, DBMS_LOGMNR_D

desc를 이용해서 설치되어있는지 확인하자.

+ DBMS_LOGMNR_D

로그마이너 딕셔너리 파일을 생성하기 위한 프로시저를 제공한다.

몇개의 프로시져가 있으나 Public인것은 Build 뿐이므로 우리는 Build프로시저만 사용하면 된다.

이 프로시저는 현재 DB의 딕셔너리 테이블에 질의하여 여기서 나온 정보를 가지고 텍스트기반의 파일을 생성한다.

이 외부 딕셔너리 파일은 차후 로그마이너가 로그파일 분석하기 위한 용도로 사용된다.

이 프로시저는 내부적으로 UTL_FILE패키지를 사용하기 때문에 파라미터파일에 'UTL_FILE_DIR'파라미터를 설정해야 한다.

'set outputserver on'명령을 사용하면 Build프로시저의 진행상황을 모니터링 할 수 있다.

+ DBMS_LOGMR

이 패키지는 3가지 프로시저를 제공한다.

- add_logfile(name varchar2, options number) 분석할 Redo Log의 추가/제거

- start_logmnr(start_scn number, end_scn number, start_time number, end_time number, dictfilename varchar2, options number)

분석한 시간대나 SCN범위를 지정, 분석에 사용할 Data Dictionary Extract를 지정

- end_logmnr() 마이닝 세션 종료. Redo 스트림을 분석하고 Dictionary Extract를 읽기 위해 사용한 메모리를 해제한다.

만약 설치 되어있지 않으면
SQL>@?/rdbms/admin/dbmslmd.sql

SQL>@?/rdbms/admin/dbmslm.sql

이 명령으로 설치 해준다. 어지간하면 다 설치 되어있을것이다.

패키지 설치 후에 다음 명령어를 실행한다.
exec DBMS_LOGMNR_D.BUILD('logminer.ora','/home/oracle/logminer');

options=>dbms_logmnr_d.store_in_flat_file--이것의 의미를 모르겠다.

LogMnr Dictionary Procedure started
LogMnr Dictionary File Opened
Procedure executed successfully - LogMnr Dictionary Created
PL/SQL procedure successfully completed.

그럼 생성 되었다는 메세지가 보일것이다.

그 다음으로 분석할 로그파일을 로그파일목록에 등록한다.

- 로그마이너 세션에서 'DBMS_LOGMNR.ADD_LOGFILE'프로시저를 사용하여 로그파일
목록에서 등록 또는 제거 할 수 있다.

- 주의 할 점은 처음 등록시에는 'DBMS_LOGMNR.NEW'를 사용하여 등록하고, 그 이후에는
'DBMS_LOGMNR.ADDFILE'으로

등록해야 한다는 점이다. 제거는 'DBMS_LOGMNR.REMOVEFILE'을 사용한다.

select * from v$logfile;

조회하면 경로와 파일명이 나온다.
나온것 모두 등록해 준다.

exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo01.log', dbms_logmnr.new);

exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo02.log', dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo03.log', dbms_logmnr.addfile);

등록된 로그파일은 다음 명령으로 알아 볼 수 있다.

select log_id, filename from v$logmnr_logs;

로그파일을 등록한뒤에는, DBMS_LOGMNR.START_LOGMNR 프로시저를 이용 해서
로그파일을 읽어들인다.

- 분석할 Redo 레코드를 필터링 하기위해 시작/종료 SCN과 Time파라메터를 지정할 수 도 있다.

desc v$logmnr_parameters;

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

dbms_logmnr.start_logmnr 패키지에 의해 리두로그 파일을 분석하고 그 결과를 지정된 경로와
파일에 저장해줌
exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/logminer01.ora');

저작자표시 비영리 변경금지 (새창열림)

OCM10gR2

Admin I - I

티스토리툴바