Advanced Database

Contents

1. DBMS

- DBMS Administration: Oracle Server

- Query Processing, Transactions - Concurrency Control/Recovery

- Performance/Tuning

2. Web-DB Programming

- JDBC

- Servlet/JSP

- Middleware - CORBA & JavaRMI/EJB(Enterprise JavaBeans)

- XML

3.  New Applications

- OLAP and Data Warehouse

- Data Mining

New Enviornment and Applications

□Three-tier C/S Architecture on the Web

Ex)

c.f. Two-tier C/S Architecture

Ex) PowerBuilder, Delphi, Visual Basic, etc

□Data Warehousing and Data Mining

Schedule

1. Mid-Term

- Exam

2. Final

- Report

- Project

DBMS Administration - Oracle Server

Oracle Overview

□The most widely used commercial DBMS

- Oracle 7: OLTP(On-line Trans. Processing)

- Oracle 8: DW(Data Warehousing) 지원 기능 추가 - Parallel Processing 등

- Oracle 8i: Internet 지원 기능 추가 - JVM, JDBC 등

- Oracle 9i (2001): E-Business 지원 기능 추가 - Encryption, XML 등

Ex) DB2, Informix, Sybase, SQL Server 등

- Products:	http://otn.oracle.com/

http://otn.oracle.co.kr/

1. Oracle9i Database

Oracle9i Database

□Related Features & Products

Oracle9i Lite	Internet File System

Oracle8i and Oracle8i JVM	interMedia

Oracle8i Personal Edition	Spatial

Oracle8i Appliance	Gateways

Enterprise Manager and Management Packs	Software Packager

Internet Directory	eMail Server

Workspace Manager	Rdb Products

Dynamic Services	Ultra Search

* Gateway: Oracle의 SQL을 사용하여 다른 DBMS에 접속하기 위한 gateway S/W.

2. Oracle9i Application Server

Oracle9i Application Server

Oracle9i Application Server Wireless Edition

□Related Features & Products

Forms	Integration Server

Reports	Internet File System

Oracle9iAS Portal	Oracle8i JVM

3. Internet Developer Suite

Internet Developer Suite

□Related Features & Products

JDeveloper	Reports Developer

Oracle9iAS Portal	Discoverer

Repository	Designer

Forms Developer	BI Beans

Migration Workbench

4. Data Warehousing and Business Intelligence

Discoverer	Data Mining Suite

Reports Developer	Express Server

Warehouse Builder	Express Analyzer / Objects

Data Mart Suite

5. Oracle E-Business Suite

For Oracle Applications product information, please visit Oracle AppsNet.

6. Archived Products

Oracle8	OAS

WebDB

- DB Administration

1. Instance Management

2. Database Management

3. User Management

4. Object Management - Table, View, Index, Cluster 등

5. Performance/Tuning Management

Instance Management

□Memory Structures and Processes of Oracle

- Session: A specific connection from a user process to an Oracle instance.

Ex) When a user starts SQL*Plus with a valid username and password, then a session is established.

∀ A session lasts until the user disconnects or exits the DB application.

- Multiple sessions can be created concurrently for a single Oracle user using  the same username.

- A 'dedicated' server process is created for each user session.

A 'shared' server process can be shared among many user sessions.

- Dedicated/Shared Server Process

□It is better for a user process to be connected thru a 'dispatcher' and use a 'shared' server process.

Ex) For OLTP, the server process is not needed during 'most' of the transaction.

∀ Server process의 □공유□가 가능

∀ Monitoring the V$QUEUE, V$DISPATCHER, V$DISPATCHER_RATE views.

∀ The load on the dispatcher processes

□But, users and DBAs should 'explicitly' connect to an instance using a 'dedicated' server process in the following situations.

① To submit a 'batch' job: A job can allow 'little or no' idle time for the server process.

② To use Recovery Mgr to back up, restore, or recover a DB.

□To request a dedicated server connection, users must use a 'net service name' that is 'configured' to use a dedicated server.

□SGA (System Global Area)

- Database Buffer Cache: Disk에 보관되어 있는 '데이터'들은 dbw에 의해 이 Cache를 거쳐 User에게 보여지고, 이전에 사용된 데이터들은 Cache 영역이 부족하면 다시 Disk로 돌아감.

∀ MRU (Most Recently Used) 데이터를 보관하고 있음.

- Redo Log Buffer: 데이터 '변경'에 대한 내용을 담고 있는 Redo Log Entry들을 보관.

∀ 이는 다시 lgwr에 의해 Redo Log File에 보관되며, Recovery시 사용됨.

- Shared Pool: oracle에 의해 Parsing된 SQL과 'Execution Plan'들을 저장.

- Large Pool: Backup/Restore Operation, I/O Server Process, Multi-thread Server를 위한 'Session' Memory 영역.

- Cursor: oracle Program의 Statement Handler가 이용.

□Oracle Instance: Oracle server processes

- □하나의□ DB를 관리하기 위해서 기본적으로 필요한 Daemon process들을 묶어서 instance라고 함.

- Instance의 이름(SID)은 Oracle install시에 정해지거나, DB를 생성시 정해짐.

- SID (System IDentifier): Instance name in Oracle

Ex)	COMORA on comedudb (Solaris)

COMORA2 on comedudb2 (Linux)

$echo $ORACLE_SID	; display oracle SID

COMORA2

□OPS (Oracle Parallel Server): MTS(Multi-instance system)

- Oracle instance는 보통 '하나의' DB를 관리함.

- OPS의 경우, 여러 instance가 하나의 DB에 동시에 연결되어 여러 trans.들을 동시에 처리함.

∀ 많은 user들을 동시에 지원 가능.

- 하나의 table은 병렬처리를 위해 분할(partition)되어 저장

∀ range/hash/composit partitioning.

□Oracle Installation: Instance and DB

- Solaris, Linux, Win2000 등 (http://otn.oracle.co.kr)

- DBA accounts

Account	Role	Functions
SYS	DBA	Creates and manages all of the 'base' tables and views for the DB's data dictionary
SYSTEM	DBA	Creates and manages 'additional' tables and views

- System privileges

Privileges	Schema Accessiblility	Operations Authorized
SYSDBA	SYS	Performs DBA tasks
SYSOPER	PUBLIC	Performs basic operational tasks

□When you connect with SYSDBA/SYSOPER privileges, you can access a default schema with SYS/PUBLIC accessibility.

EX) $sqlplus /nolog

SQL> connect sys/***** as sysdba;

SQL> GRANT SYSDBA to scott;

SQL> connect scott/tiger as sysdba;	//scott의 사용 schema는 scott이 아니라	//SYS가 됨.

SQL> REVOKE sysdba from scott;

EX)	$sqlplus /nolog

SQL> CONNECT scott/tiger

SQL> CREATE TABLE scott_test(name VARCHAR2(20));

// Later, scott issues these statements...

SQL> CONNECT scott/tiger AS SYSDBA	// scott의 사용 schema는 scott이	// 아니라 SYS가 됨.

SQL> SELECT * FROM scott_test;

** ERROR !! **

Ex) SQL> select * from v$pwfile_users;	// View privileged users from the	// password file.

USERNAME	SYSDB	SYSOP

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

INTERNAL            TRUE	TRUE

SYS                  TRUE	TRUE

□SQL*Plus: Execute standard SQL commands

∀ DBA 작업 수행

Ex) $sqlplus scott/tiger[@comora2]	; client의 tnsnames.ora에 등록된 oracle server

SQL> conn[ect]	; connect a default DB

SQL> disc[onnect]

SQL> select * from tab;	; display tables in the DB

SQL> desc table_name	; display a table schema

SQL> start file_name.sql	; sql file 실행

SQL> spool file_name	; spool file 설정

SQL> spool off;

SQL> set heading off/on	; heading display 설정

SQL> host ls -al	; execute commands

SQL> exit

□Oracle Enterprise Manager

- Two methods

①Standalone: Two-tier C/S

□Connects 'directly' to the target DB

□Manages only DB

②Management Server: Three-tier

□Uses middle tier Management Server/Repository

□Manages DB, web server, application server, applications, etc.

- Overview

- DB connection

- Instance Manager

- Schema Manager

- Security Manager

- Storage Manager

- Replication Manager

- JVM Manager

- Work Space Manager

□MS SQL Server 7.0 Enterprise Manager

□Oracle instance 구동

1. Network

- Listener process를 구동

2. Startup

- instance process들을 구동.

- control file들을 참조하여 DB에서 사용하는 datafile들을 mount.

- process들이 데이터를 access할 수 있도록 datafile들을 open.

3. Shutdown

- datafile들을 close하고 dismount 시킴.

- instance process들을 모두 종료.

Ex) [oracle@comedudb2 oracle]$ lsnrctl start	; Listener 구동

[oracle@comedudb2 oracle]$ lsnrctl stop	; Listener 멈춤

[oracle@comedudb2 oracle]$ lsnrctl status	; Listener 상태

LSNRCTL for Linux: Version 9.0.1.0.0 - Production on 01-MAY-2002 01:46:29

Copyright (c) 1991, 2001, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=comedudb3)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 9.0.1.0.0 - Production

Start Date                30-APR-2002 02:46:06

Uptime                    0 days 23 hr. 0 min. 23 sec

Trace Level               off

Security                  OFF

SNMP                      OFF

Listener Parameter File   /oradb/u01/app/oracle/product/9.0.1/network/admin/lisa

Listener Log File         /oradb/u01/app/oracle/product/9.0.1/network/log/listeg

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=comedudb2)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "comdb2" has 1 instance(s).

Instance "COMORA2", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Ex)	[oracle@comedudb2 oracle]$ sqlplus /nolog

SQL*Plus: Release 9.0.1.0.0 - Production on Wed May 1 01:39:28 2002

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

SQL> connect system/***** as sysdba	; sysdba로 login

Connected.

SQL> startup [option]	; NOMOUNT/MOUNT/RESTRICT/FORCE/OPEN RECORVER

ORACLE instance started.

Total System Global Area  202138632 bytes

Fixed Size	279560 bytes

Variable Size	167772160 bytes

Database Buffers	33554432 bytes

Redo Buffers	532480 bytes

Database mounted.

Database opened.

SQL> shutdown [option]	; NORMAL/IMMEDIATE/TRANSACTIONAL/ABORT

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production

With the Partitioning option

JServer Release 9.0.1.0.0 - Production

Ex) Oracle8i 이하

$svrmgrl	; Server Mgr을 구동

SVRMGRL> connect internal

SVRMGRL> startup [options]

ORACLE instance started.

Total System Global Area	4754704 bytes

Fixed Size	48400 bytes

Variable Size	4222976 bytes

Database Buffers	409600 bytes

Redo Buffers	73728 bytes

Database mounted.

Database opened.

SVRMGRL> exit

$svrmgrl

SVRMGRL> connect internal

SVRMGRL> shutdown

SVRMGRL> exit

- Server Manager commands (Oracle 8i 이하)

SVRMGR> help

The following are SIMPLIFIED syntax descriptions. For complete syntax

descriptions, please refer to the Oracle Server Manager User's Guide.

STARTUP      [DBA] [FORCE] [PFILE=filespec] [EXCLUSIVE | SHARED]

[MOUNT dbname | OPEN dbname] [NOMOUNT]

- FORCE: 비정상적으로 shutdown되었을 경우에 instance들이 시작되지 않는 경우가 발생하므로, 이러한 경우에 사용.

- MOUNT: DB 복구, datafile 삭제 등 DBA가 일반 user들의 접근을 차단하여 홀로 작업을 하고 싶은 경우.

- NOMOUNT: instance만을 start시키고 DB는 mount되지 않는 경우.

SHUTDOWN     [NORMAL | IMMEDIATE | ABORT]

- ABORT: 데이터 buffer에 있는 변경된 데이터과 redo log buffer의 내용들을 disk에 쓰는 작업 없이 종료.

MONITOR      For graphical modes only, bring up a monitor

ARCHIVE LOG  [START] [STOP] [LIST] [NEXT] [<n>] [ALL] ['destination']

RECOVER     { [DATABASE [MANUAL] ] | [TABLESPACE ts-name [,tsname]] }

- ABORT 시켰을 경우에 DB를 복구시킴.

CONNECT      [username [/password] ] [INTERNAL] ['@'instance-spec]

DISCONNECT

SET options:INSTANCE, ECHO, TERMOUT, TIMING, NUMWIDTH, CHARWIDTH

SHOW   LONGWIDTH, DATEWIDTH, AUTOPRINT and for SHOW: ALL, SPOOL

EXIT

REM

SQL statements can also be executed.

□Oracle Processes

- Server/Daemon/User Processes

Ex) [oracle@comedudb2 oracle]$ ps -ef | grep ora

oracle     970     1  0 07:57 ?        00:00:00 /oradb/u01/app/oracle/product/8.

oracle     975     1  0 07:59 ?        00:00:00 ora_pmon_COMORA2

oracle     977     1  0 07:59 ?        00:00:00 ora_dbw0_COMORA2

oracle     979     1  0 07:59 ?        00:00:00 ora_lgwr_COMORA2

oracle     981     1  0 07:59 ?        00:00:00 ora_ckpt_COMORA2

oracle     983     1  0 07:59 ?        00:00:00 ora_smon_COMORA2

oracle     985     1  0 07:59 ?        00:00:00 ora_reco_COMORA2

root      9119  9118  0 08:19 pts/2    00:00:00 login -- oracle

sspark   9120  9119  0 08:19 pts/2    00:00:00 -bash

sspark   9163  9120  0 08:19 pts/2    00:00:00 sqlplus

root      9166  9165  0 08:19 pts/3    00:00:00 login -- tykim

tykim     9167  9166  0 08:19 pts/3    00:00:00 -bash

tykim     9210  9167  0 08:19 pts/3    00:00:00 sqlplus

oracle 9164  9163  0 08:19 ? 00:00:00 oracleCOMORA2 (DESCRIPTION=(LOCA

oracle 9211  9210  0 08:19 ? 00:00:00 oracleCOMORA2 (DESCRIPTION=(LOCA

□Configuration Files

Ex)	$ cd $ORACLE_BASE

$ find . -name *.ora -print	; find configuration files

./product/9.0.1/network/admin/samples/sqlnet.ora

./product/9.0.1/network/admin/samples/tnsnames.ora

./product/9.0.1/network/admin/samples/listener.ora

./product/9.0.1/network/admin/sqlnet.ora

./product/9.0.1/network/admin/listener.ora

./product/9.0.1/network/admin/tnsnames.ora

./product/9.0.1/network/agent/config/nmxw.ora

./product/9.0.1/network/agent/config/oapps.ora

./product/9.0.1/hs/admin/inithsodbc.ora

./product/9.0.1/dbs/init.ora

./product/9.0.1/dbs/initdw.ora

./product/9.0.1/dbs/spfileCOMORA2.ora

./product/9.0.1/dbs/initCOMORA2.ora

./admin/COMORA2/pfile/configCOMORA2.ora

./admin/COMORA2/pfile/initCOMORA2.ora

./admin/COMORA2/pfile/initCOMORA2_0.ora

./admin/comdb2/pfile/initCOMORA2.ora

□Listener Process

- Net8 protocol 사용

- Connect-time Failover 기능

□network service명에 '다수의' listener addr.를 지정. (TNSNAMES.ORA)

□FAILOVER=ON 설정

□client가 server의 접속에 실패할 경우, 다른 listener addr.로 다시 접속을 시도.

- Load Balancing 기능

① Network Load Balancing

□client에 다수의 listener addr.를 지정.

□LOAD_BALANCE=ON 설정

□client는 무작위로 각 listener를 사용하여 load balancing을 수행.

② Server Load Balancing

□OPS 환경에서 dispatcher가 같은 서비스를 하는 다른 instance에 작업을 넘겨줌.

Database Management

□Databases, Tablespaces, and Datafiles

- 하나의 file은 각각의 segment로 나누어져 있고, 하나의 segment에 하나의 table, 혹은 index가 저장되어 있음. (data segment, 혹은 index segment)

- 각각의 extent는 연속된 block의 묶음으로 구성.

CREATE TABLE emp ( … … )

STORAGE (INITIAL	84K  NEXT 28K

MAXEXTENTS 10 PCTFREE 5 PCTUSED 75)

∀ 처음에 84K, 필요시 24K씩 할당.

2K	…				2K	…
2K					…
…
Extent 84K					Extent 28K

Segment = 112K

□Database & Tablespaces (Oracle Install시/DB creation시)

① SYSTEM tablespace

□DB에 반드시 있어야 할 tablespace로서 DB를 관리하기 위한 table이 저장됨 - data dictionary 등.

② USERS tablespace

□사용자나 programmer들이 사용하는 table을 저장

□여러 datafile들로 구성될 수 있는데, 여러 disk에 분산되어 있으면 성능향상을 얻을 수 있음.

③ RBS tablespace

□RollBack Segment

□Trans.의 commit이 수행되기 전까지의 변경기록들을 보관.

□Trans.의 abort시 이전 data를 다시 DB에 적용시켜 rollback함.

④ TEMP tablespace

□join, group by, order by, index 생성 등의 임시 작업을 수행하는 곳.

Ex) startCOMORA2.sql

#  building a database from 'existing' datafiles (system, rbs, tools, temp, users).

#set termout on

#spool build.log

spool /oradb/u01/app/oracle/admin/COMORA2/create/crdbCOMORA2.lst

connect internal

startup nomount pfile=/oradb/u01/app/oracle/admin/COMORA2/pfile/initCOMORA2_0.ora

create controlfile reuse set database "COMORA2"

datafile	select * from SYSFILES;

'/oradb/u01/oradata/COMORA2/system01.dbf',	SYSTEM

'/oradb/u01/oradata/COMORA2/rbs01.dbf',	RBS	tablespace

'/oradb/u01/oradata/COMORA2/temp01.dbf',	TEMP	names

'/oradb/u01/oradata/COMORA2/tools01.dbf',	TOOLS

'/oradb/u01/oradata/COMORA2/users01.dbf'	USERS

logfile

'/oradb/u01/oradata/COMORA2/redoCOMORA201.log'   size 500k,

'/oradb/u02/oradata/COMORA2/redoCOMORA202.log'   size 500k,

'/oradb/u03/oradata/COMORA2/redoCOMORA203.log'   size 500k resetlogs;

alter database "COMORA2"  open resetlogs;

disconnect

spool off

- 반드시 하나 이상의 system file, 하나 이상의 data file, 두 개 이상의 redo log file이 있어야 함.

□Creation of a DB

- Procedures

□ Decide/set on your instance ID: Environment var. $ORACLE_SID 설정

Ex) Linux bash의 경우 (in /etc/profile)

export ORACLE_BASE="/oradb/u01/app/oracle"

export ORACLE_HOME="/oradb/u01/app/oracle/product/9.0.1"

export ORACLE_SID="COMORA2"

$ echo $ORACLE_SID

Decide global DB name.

Ex) comdb2.knue.ac.kr

① Establish the DBA authentication method: DBA accounts, system privileges 설정

Ex) SYS, SYSTEM

② Create the initialization parameter file: Instance/DB configuration file 작성

Ex) init_SID.ora, config_SID.ora

block size (db_block_size, default-2KB), DB name (db_name), 최대 datafile 수(db_files), data buffer size (db_block_buffers), shared pool size (shared_pool_size) 등을 결정

③ Connect to/Start the instance

Ex)	SQL> connect sys/***** as sysdba

SQL> startup nomount

④ DB/Tablespaces creation script 작성/실행: CREATE DATABASE ∼

Ex) Create DB (crdb.orc), create tablespaces (crdb2.orc) 등을 참고.

⑥ Create 'additional' tablespaces.

⑦ Run scripts to build Data Dictionary views, synonyms, and PL/SQL packages.

Ex)	SQL>connect sys/***** as sysdba

SQL>@/vobs/oracle/rdbms/admin/catalog.sql

SQL>@/vobs/oracle/rdbms/admin/catproc.sql

⑧ Run scripts to install additional options (Optional)

⑨ Create a server parameter file (Recommended): Maintain 'dynamically' initialization parameters using the ALTER SYSTEM statement.

∀ DB를 shutdown하지 않고도 parameter들을 수정할 수 있음.

Ex)	SQL> CONNECT SYS/ password AS SYSDBA

-- create the server parameter file

SQL> CREATE SPFILE='…/oracle/product/9.0.1/dbs/spfileCOMORA2.ora'

FROM PFILE='…/oracle/admin/comdb2/pfile/initCOMORA2.ora';

SQL> SHUTDOWN

-- this time you will start up using the server parameter file

SQL> CONNECT SYS/password AS SYSDBA

SQL> STARTUP

SQL> EXIT

⑩ Back up the DB (Backup and Recovery)

⑪ listener에 등록/client에서 DB를 연결:

Ex) listener.ora(server), tnsnames.ora(client)에 등록.

⑫ DB를 사용할 user들 등록.

- Two Methods

① Use the Oracle Database Configuration Assistant (DBCA) in the Oracle Universal Installer.

② Create the DB 'manually' from a script.

Ex) Oracle9i DB Creation (Oracle Universal Installer 사용)

①일반 옵션

옵션 이름	선택됨
Example Schemas	true
Oracle Intermedia	true
Oracle JVM	true
Oracle Label Security	false
Oracle OLAP Services	false
Oracle Spatial	true
Oracle Ultra Search	true

② Initialization       Parameters

이름	값
background_dump_dest	{ORACLE_BASE}/admin/{DB_NAME}/bdump
compatible	9.0.0
control_files	("{ORACLE_BASE}/oradata/{DB_NAME}/control01.ctl", "{ORACLE_BASE}/oradata/{DB_NAME}/control02.ctl", "{ORACLE_BASE}/oradata/{DB_NAME}/control03.ctl")
core_dump_dest	{ORACLE_BASE}/admin/{DB_NAME}/cdump
db_block_size	8192
db_cache_size	32MB
db_domain
db_name	comdb2
dispatchers	(PROTOCOL=TCP)(SER=MODOSE), (PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer), (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer), (PROTOCOL=TCP)
fast_start_mttr_target	300
instance_name	COMORA2
java_pool_size	51200KB
large_pool_size	6167KB
open_cursors	300
processes	150
remote_login_passwordfile	EXCLUSIVE
resource_manager_plan	SYSTEM_PLAN
shared_pool_size	64MB
sort_area_size	524288
timed_statistics	TRUE
undo_management	AUTO
undo_tablespace	UNDOTBS
user_dump_dest	{ORACLE_BASE}/admin/{DB_NAME}/udump

③Datafiles      (Tablespaces)

이름	테이블스페이스	크기(M)
{ORACLE_BASE}/oradata/{DB_NAME}/cwmlite01.dbf	CWMLITE	20
{ORACLE_BASE}/oradata/{DB_NAME}/drsys01.dbf	DRSYS	20
{ORACLE_BASE}/oradata/{DB_NAME}/example01.dbf	EXAMPLE	160
{ORACLE_BASE}/oradata/{DB_NAME}/indx01.dbf	INDX	25
{ORACLE_BASE}/oradata/{DB_NAME}/system01.dbf	SYSTEM	325
{ORACLE_BASE}/oradata/{DB_NAME}/tools01.dbf	TOOLS	10
{ORACLE_BASE}/oradata/{DB_NAME}/undotbs01.dbf	UNDOTBS	260
{ORACLE_BASE}/oradata/{DB_NAME}/users01.dbf	USERS	25
{ORACLE_BASE}/oradata/{DB_NAME}/temp01.dbf	TEMP	40

④Control         Files

제어 파일

{ORACLE_BASE}/oradata/{DB_NAME}/control01.ctl

{ORACLE_BASE}/oradata/{DB_NAME}/control02.ctl

{ORACLE_BASE}/oradata/{DB_NAME}/control03.ctl

⑤Redo     Log     Groups

그룹	크기(K)	파일
1	102400	/oradb/u01/app/oracle/oradata/comdb2/redo01.log
2	102400	/oradb/u01/app/oracle/oradata/comdb2/redo02.log
3	102400	/oradb/u01/app/oracle/oradata/comdb2/redo03.log

* Control file

- Every Oracle DB has a control file.

- Without the control file, the DB cannot be mounted.

- A small binary file that records the 'physical structure' of the DB.

① DB name

② Names and locations of associated datafiles and online redo log files

③ Timestamp of the DB creation

④ Current log sequence no.

⑤ Checkpoint info.

□Tablespace 영역

① 새로운 tablespace 추가

Ex) NEW_USERS라는 새로운 tablespace를 create.

CREATE TABLESPACE NEW_USERS

DATAFILE 'user02.dbf' SIZE 100M

Ex) Oracle9i

CREATE TABLESPACE "OEM_REPOSITORY"

LOGGING

DATAFILE '/oradb/u01/app/oracle/oradata/comdb2/OEM_REPOSITORY.dbf'

SIZE 5M REUSE

AUTOEXTEND ON NEXT  5M MAXSIZE  1000M	// or UNLIMITED

EXTENT MANAGEMENT LOCAL

② Tablespace 확장:

Ex) SYSTEM에 100MB인 system02.dbf를 add.

ALTER TABLESPACE SYSTEM

ADD DATAFILE 'system02.dbf' SIZE 100M

③ datafile 확장

Ex) user02.dbf의 크기를 확장

ALTER DATABASE

DATAFILE 'user02.dbf' AUTOEXTEND ON NEXT 100M

MAXSIZE 2000M

Ex) RBS의 경우

- Trans.이 수행되는 동안 RBS에 변경 전 데이터를 보관.

- 대량의 데이터를 insert/update/delete하는 경우, RBS의 영역이 꽉 차게되면 error msg가 발생.

- RBS를 확장시키고, trans.을 다시 수행해야 함.

□Tablespace On-line/Off-line

□On-line: User가 그 tablespace에 접근 가능한 상태임.

Ex) SYSTEM tablespace는 DB가 startup되어 open되어 있는 경우에는 항상 On-line 상태임.

□Off-line: Tablespace의 back-up 등의 특수한 목적상 일시적으로 user의 접근을 차단한 상태.

□어떤 table의 index가 tablespace A에 저장되어 있고, table은 tablespace B에 저장되어 있는 경우, A가 off-line되어 있어도 user들은 table의 access가 가능. 그러나, B가 off-line되어 있는 경우는 table access가 불가능함.

Ex)	SELECT * FROM sys.dba_tablespaces;

ALTER TABLESPACE USERS online;

ALTER TABLESPACE USERS offline;

□Table access를 읽기 전용 모드로 변경할 수 있음.

Ex) ALTER TABLESPACE USERS read only;

User Management

□Create a User for the DB

① 사용할 DB 결정.

② user ID/password 설정.

③ 권한 설정.

Ex) $sqlplus system/****

SQL> select * from all_users;

SQL> select * from dba_users;

SQL> select * from sys.dba_tablespaces;

SQL> CREATE USER	tykim

IDENTIFIED BY	*******

DEFAULT	TABLESPACE	users

TEMPORARY	TABLESPACE	temp

QUOTA	10M	ON	users

GRANT	RESOURCE, CONNECT TO tykim;   // role 부여...

□Authorization

- Role 정의

□Privilege: A right to execute a particular type of SQL statement, or a right to access another user's object.

□DBA는 role을 만들어 role에 privilege들을 부여함.

∀ 이 role을 사용하여 같은 부류의 user들끼리 grouping이 가능.

Ex)	CREATE ROLE new_employee

INDENDIFIED BY newone;

GRANT SELECT, INSERT, UPDATE ON CUSTOMER TO new_employee;

GRANT SELECT, INSERT, UPDATE ON ORDER     TO new_employee;

GRANT SELECT	ON PRODUCT  TO new_employee;

GRANT new_employee	TO tykim;

SQL> SET HEADING OFF

SQL> SELECT * FROM dba_roles;	// display roles...

Ex)	CREATE	USER	tykim

IDENTIFIED BY	*******

DEFAULT	TABLESPACE	users

TEMPORARY	TABLESPACE	temp	; sort 등의 연산시	사용하는 tablespace

QUOTA	10M	ON	users

DEFAULT ROLE	developer;

□Predefined Roles

CONNECT

Created by SQL.BSQ (scripts)

Included privileges:	ALTER SESSION,	CREATE CLUSTER

CREATE DATABASE LINK, CREATE SEQUENCE

CREATE SESSION,	CREATE SYNONYM

CREATE TABLE,	CREATE VIEW

RESOUCE

Created by SQL.BSQ (scripts)

Included privileges:	CREATE CLUSTER,	CREATE INDEXTYPE

CREATE OPERATOR,	CREATE PROCEDURE

CREATE SEQUENCE,	CREATE TABLE

CREATE TRIGGER,	CREATE TYPE

DBA

Created by SQL.BSQ (scripts)

All system privileges

- 권한 부여

□DBA는 user에게 권한 부여/취소를 수행할 수 있음.

∀ GRANT/REVOKE 사용

Ex)	GRANT RESOURCE, CONNECT TO	tykim;

GRANT DBA	TO	tykim;

SQL> SET HEADING OFF

SQL> SELECT * FROM dba_privs;

Ex)	REVOKE DBA	FROM	tykim;

REVOKE RESOURCE, CONNECT FROM	tykim;

Ex) Oracle9i

CREATE USER "TYKIM"  PROFILE "DEFAULT"

IDENTIFIED BY "********"

DEFAULT TABLESPACE "OEM_REPOSITORY"

TEMPORARY TABLESPACE "TEMP"

QUOTA UNLIMITED ON OEM_REPOSITORY

QUOTA UNLIMITED ON TEMP

ACCOUNT UNLOCK;

GRANT CREATE PROCEDURE	TO "TYKIM"

GRANT CREATE TRIGGER	TO "TYKIM"

GRANT CREATE TYPE	TO "TYKIM"

GRANT EXECUTE ANY PROCEDURE TO "TYKIM"

GRANT EXECUTE ANY TYPE	TO "TYKIM"

GRANT SELECT ANY DICTIONARY	TO "TYKIM"

GRANT SELECT ANY TABLE	TO "TYKIM"

GRANT "CONNECT"	TO "TYKIM";

GRANT "SELECT_CATALOG_ROLE"	TO "TYKIM";

□Resource 관리

- Profile: A named set of 'resource limits'.

∀ A user's profile limits 'DB usage' and 'instance resources' as defined in the profile.

- Oracle resource:

sessions_per_user,	cpu_per_session,	cpu_per_call,

logical_reads_per_session,	logical_reads_per_call,

idle_time,	connect_time,	private_sga

∀ DBA는 각 resource에 상대적인 값을 정의할 수 있음.

Ex) ALTER RESOURCE COST cpu_per_session 10

logical_reads_per_session 2;

- DBA는 Oracle resource들을 숙지하고, 이들을 user별로 관리하기 위해서 profile을 만들고, 이를 user별로 지정.

∀ user별로 할당된 resource관리가 가능함.

Ex) Resource limit 기능 enable 시키는 방법

① Oracle instance parameter 중에서 RESOURCE_LIMIT을 TRUE로 정의.

② 혹은, ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

- Profile 생성: 특정 user에 대한 resource의 제한을 profile로 정의함.

Ex)	CREATE PROFILE	tykim_profile	LIMIT

sessions_per_user	1

cpu_per_call	10000

connect_time	240;

ALTER USER tykim PROFILE tykim_profile;

Ex) DROP PROFILE tykim_profile CASCADE;  // profile 삭제

□User Monitoring

- 아래의 table/view를 참조

SYSTEM TABLE	설명
dba_users	user name, ID, 비밀번호 등
dba_profiles	profile, resource, resource type 등
dba_objects	DB내에 있는 모든 object들에 관한 정보
dba_roles	role과 password 유무
dba_role_privs	user와 user의 권한
dba_ts_quotas	user와 user가 사용하는 tablespace
user_resource_limits	user에게 주어진 resource에 대한 제한
v$session	user session에 관한 정보

Ex) SQL> desc v$session

SQL> SELECT sid, serial# FROM v$session;

- User session 끊기

Ex) SELECT sid, serial# FROM v$session

WHERE username='tykim';

ALTER SYSTEM KILL SESSION '10, 2133';

- User 정보 변경: ALTER USER 문 사용

Ex)	ALTER	USER	tykim

IDENTIFIED BY	*******;	; new password 입력

Ex)	ALTER	USER	tykim

DEFAULT	TABLESPACE	new_users;

Ex)	ALTER	USER	tykim

PROFILE	dba_profile;

Ex)	DROP	USER	tykim	CASCADE;

∀ user와 user가 만든 모든 object들을 모두 함께 삭제.

□Audit: 감사

- DB에 이상이 있거나 특정 user에 대한 정보가 필요할 때 audit를 수행.

- Oracle 초기화 parameter 설정

Ex)	AUDIT_TRAIL = DB/OS/NONE

AUDIT_FILE_DEST = 감사 파일이 저장될 directory

Ex) tykim의 table 작업에 대해 감사 실행

AUDIT CREATE TABLE, ALTER TABLE, DROP TABLE BY tykim

WHENEVER successful;

Ex) 작업 session에 대한 감사 실행

AUDIT INSERT ON tykim.회원;

AUDIT TABLE BY SESSION;

- Audit 정보 Dictionary Table

Table	정보
ALL_DEF_AUDIT_OPTS	감사의 기본 option list
AUDIT_ACTIONS	감사 가능 item
DBA_AUDIT_EXISTS	감사 명령어 option에 의해 만들어진 감사 entry list
DBA_AUDIT_OBJECT	감사 대상 object
DBA_AUDIT_SESSION	감사 대상 session
DBA_AUDIT_STATEMENT	감사 대상 SQL
DBA_AUDIT_TRAIL	AUD$ table list
DBA_OBJ_AUDIT_OPTS	view, table 등에 대한 감사 option
DBA_PRIV_AUDIT_OPTS	user 권한에 대한 감사 option
DBA_STMT_AUDIT_OPTS	SQL문에 대한 감사 option
STMT_AUDIT_OPTION_MAP	감사 option code를 위한 type 설명

Object Management - Table, View, Index, Cluster

□Table

- Create a table

Ex) emp_crt.sql script file 작성/실행

$sqlplus scott/tiger

set termout off

set feedback off

drop table dept;

create table dept

(deptno	number(2),

dname	char(14),

location	char(13) ) ;

create index ix_deptno on dept(deptno);

exit

- SQL*Loader: Loading data into a table

Ex) emp_data.ctl script file 작성

LOAD DATA

NFILE *

INTO TABLE dept

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(deptno, dname, location)

BEGINDATA

12,"RESEARCH","SARATOGA"

10,"ACCOUNTING","CLEVELAND"

11,"ART","SALEM"

13,"FINANCE","BOSTON"

21,"SALES","PHILA"

22,"SALES","ROCHESTER"

42,"INT'L","SAN FRAN"

$sqlldr scott/tiger emp_data

- Tablespace/storage 할당

Ex)	CREATE TABLE dept

(deptno	number(2),

dname	char(14),

location	char(13) )

TABLESPACE DEPT_TBS;

Ex)	CREATE TABLE dept

(deptno	number(2),

dname	char(14),

location	char(13) )

TABLESPACE DEPT_TBS

STORAGE (initial	10M,	; 처음 크기

next	5M,	; 다음 extent의 크기

minextents 2,	; 생성시 할당받는 확장 횟수 (10+5*2 M)

maxextents 10);	; 최대 확장 가능 횟수

- Parallel Processing

□Table partitioning

Ex) CREATE TABLE dept	(deptno	number(2) primary key,

dname	char(14),

location	char(13) ) PARALLEL 3

storage (initial 100 K next 50k)

PARTITION BY RANGE (deptno)

(PARTITION dept1 VALUES LESS THAN (100) TABLESPACE DEPT_TBS1,

PARTITION dept2 VALUES LESS THAN (200) TABLESPACE DEPT_TBS2,

PARTITION dept3 VALUES LESS THAN (MAXVALUE) TABLESPACE DEPT_TBS3)

AS SELECT * FROM old_dept;

∀ 이 경우 Oracle은 6개의 병렬처리 process들을 사용함.

3개는 old_dept를 읽는데, 3개는 dept를 만드는데 사용.

□PARALLEL: Table에서 수행되는 SQL문을 병렬로 처리.

Ex)	ALTER TABLE dept PARALLEL 3;

∀ SQL문을 처리하는데 3개의 process들을 동시에 사용.

Ex)	CREATE INDEX idx_dept PARALLEL 3

ON dept(deptno) LOCAL

STORE IN (DEPT_TBS1, DEPT_TBS2, DEPT_TBS3);

∀ 이 경우에도 Oracle은 6개의 병렬처리 process를 사용하여 index를 만듬.

3개는 dept를 읽는데, 3개는 index를 만드는데 사용.

□Temporary table

- Trans.이 수행 중이거나 user session(sqlplus, application 등)이 연결되어 있는 동안만 임시적으로 존재하는 table. (Session = T1 + T2 + … + Tn)

∀ user가 1명이므로, lock 관리나 redo log 생성 등이 필요하지 않음.

∀ trans.이나 session이 끝나면 자동으로 소멸.

Ex)① 'session'이 끝났을 경우에 table을 truncate.

CREATE GLOBAL TEMPORARY TABLE temp_dept	(deptno	number(2),

dname	char(14),

location	char(13) )

ON COMMIT PRESERVE ROWS;

② 'trans.'이 끝났을 경우에 table을 truncate.

CREATE GLOBAL TEMPORARY TABLE temp_dept	(deptno	number(2),

dname	char(14),

location	char(13) )

ON COMMIT DELETE ROWS;

- Data 삭제 방법

① DROP

□data, table, (integrity) constraint, trigger 등도 모두 삭제.

□stroage parameter(initial, next, minextents, maxextents) 등도 다시 정의해야 함.

② DELETE

□data만 삭제.

□recovery를 위해 RBS(RollBack Segment)에 DELETE가 끝나기 전까지의 내용들을 기록함.

③ TRUNCATE

□data를 삭제하면서 RBS에 기록하지 않음.

□stroage parameter 들을 초기값으로 되돌려 놓음.

□DELETE나 DROP에 비해 간편하고 빠름.

Ex) TRUNCATE TABLE dept;

□View

- Data들을 가지고 있으나, 물리적으로 저장되지 않는 table.

∀ View는 SQL에 의해서 사용될 때, shared SQL area (shared pool)에 저장

∀ 같은 SQL 문장을 사용하는 user가 많은 경우, 이 SQL문의 table을 view로 만들어 놓으면 성능향상을 기대할 수 있음.

Ex) CREATE VIEW 회원메일

AS SELECT 회원명, 메일주소 FROM 회원

- 사용목적

□복잡한 SQL 문을 단순하게 사용하고자 할 때.

Ex) CREATE VIEW 서울회원메일

AS SELECT 회원명, 메일주소 FROM 회원

WHERE 지역=□서울□

□user의 권한을 특정 col.이나 row로 한정시킬 때.

□하나의 table을 여러 목적으로 사용하고자 할 때.

□여러 table을 동시에 사용하고자 할 때.

- Materialized View

□자신의 물리적인 영역과 data를 가지고 있는 view.

□Oracle에서 이를 관리하고 자동으로 변경사항들을 update하여 줌.

∀ DW나 분산 DB에서 사용할 수 있음.

Ex) CREATE MATERIALIZED VIEW 지역별_년도별_회원수익

REFRESH FAST ON COMMIT

AS SELECT A.지역, SUM(B.1999년), SUM(B.2000년), SUM(B.2001년)

FROM   회원정보 A, 회원년수입 B

WHERE A.회원번호 = B.회원번호

GROUP BY A.지역

BUILD IMMEDIATE;

□Index

- B+-tree index

Ex) CREATE INDEX IDX_회원번호 ON 회원(회원번호);

- Composite index

□ 두 개 이상의 col.에 관한 index

Ex) CREATE INDEX IDX_회원명_주소 ON 회원(회원명, 주소);

□ 두 개 이상의 조건에 관한 SQL 속도 향상.

Ex) SELECT 회원번호 FROM 회원명 = □김*□ AND 주소 = □서울□;

- Key-compressed index

□ Col.에 중복되는 값이 많은 경우 사용.

Ex) CREATE INDEX IDX_회원 ON 회원(거주지, 회원명) COMPRESS 1;

∀ 회원의 거주지에 반복되는 데이터를 압축하고 index를 작성.

	서울, aaa		서울, bbb		서울, ccc

□Key compression breaks an index key into a prefix and a suffix entry.

∀ Compression is achieved by 'sharing' the prefix entries among all the suffix entries in an index block.

- Function-Based index

□ 자주 사용되는 function을 미리 index로 작성하여 SQL 속도 향상.

Ex) + function

CREATE INDEX idx_년도별_회원년수입

ON 회원년수입(1999년+2000년+2001년, 1999년, 2000년, 2001년);

SELECT * FROM 회원년수입

WHERE (1999년+2000년+2001년)>=100000000;

Ex) UPPER function: Uppercase evaluation

CREATE INDEX idx_employee

ON employee(UPPER(ename));

SELECT * FROM employee

WHERE UPPER(ename) LIKE 'JO%';

- Bitmap index

□일반index의 leaf node는 ROW_ID를 가리키고 있으나, bitmap index는 bit값을 가지고 있음.

□이 bit 값이 미리 정의된 function을 통하여 ROW_ID로 변환.

□bit 값을 사용하기 때문에 disk 사용을 최소화.

□DW와 같은 대용량 DB에서는 index의 크기가 커지므로 사용하면 효과적.

Ex) CREATE BITMAP INDEX idx_회원 ON 회원(회원명);

Ex)  customers

cust_id	cust_gender	cust_marital_status
70 80 90 100 110 120 130 140	F F M F F M M M	married single married single married

bitmap index on cust_gender & cust_marrital_status

	gender='M'	gender='F'	m_status='married'	m_status='single'	m_status=NULL
cust_id 70 cust_id 80 cust_id 90 cust_id 100 cust_id 110 cust_id 120 cust_id 130 cust_id 140	0 0 1 0 0 1 1 1	1 1 0 1 1 0 0 0	0 1 0 0 1 0 0 1	0 0 1 0 0 1 0 0	1 0 0 1 0 0 1 0

Query: "How many of our married customers are female?

SELECT COUNT(*)

FROM customers

WHERE cust_marrital_status = 'married' AND cust_gender ='F'

∀ Count the no. of 1s after the AND operation.

	gender='F'	m_status='married'	AND
cust_id 70 cust_id 80 cust_id 90 cust_id 100 cust_id 110 cust_id 120 cust_id 130 cust_id 140	1 1 0 1 1 0 0 0	0 1 0 0 1 0 0 1	0 1 0 0 1 0 0 0

∀ To identify additional attributes that satisfy the criteria, use the resulting bitmap to access the customer table after a 'bitmap-to-rowid' conversion.

□Index-Organized Table

- 일반 index와는 달리 □실제 row□들이 B+-tree 구조로 저장되어 있음.

∀ Index를 사용하는 것보다 access time이 빠르고, disk를 덜 차지함.

But, INSERT/UPDATE/DELETE가 자주 발생하면 overhead가 커짐.

Ex)	CREATE TABLE dept_ix_org

(deptno	number(2)	PRIMARY KEY,

dname	char(14),

location	char(13) )

ORGANIZATION INDEX

TABLESPACE DEPT_TBS

OVERFLOW TABLESPACE NEW_DEPT_TBS;

□Cluster

- 관련있는 두 개 이상의 table들을 물리적인 위치에 같이 저장.

∀ Search key 값을 interleaving함.

- 물리적인 cluster를 생성.

Ex)	CREATE CLUSTER 회원_cluster (회원번호 char(10))

SIZE 512	; the expected avg. row size (bytes)

STORAGE (initial 100K next 50k)

- Cluster에 두 개 이상의 table들을 함께 저장.

Ex)	CREATE TABLE 회원 (회원번호 char(10),

…	…    )

CLUSTER 회원_cluster(회원번호);

CREATE TABLE 회원년수입 (회원번호 char(10),

…	…    )

CLUSTER 회원_cluster(회원번호);

- Search key값에 index를 만들거나 'hash' cluster를 사용하여 access 속도 향상.

∀ Oracle에서는 내부적으로 hash function을 제공함.

∀ cluster의 생성시 hash key 값을 정의하면 바로 원하는 tuple의 access가 가능함.

Ex)	CREATE CLUSTER 회원_cluster (회원번호 char(10))

SIZE 512

HASHKEYS 1009	; the next highest prime no. to

the no. of search keys (=1000)

STORAGE (initial 100K next 50k)

- Data의 update와 insert가 빈번한 경우에는 cluster를 유지하기 위해서 많은 작업이 필요함.

□Data Type

이름	최대길이	Type	설명
CHAR	2000B	문자열	고정길이
NCHAR	2000B	문자열	다국적 언어 지원
VARCHAR2	4000B	문자열	가변길이
NVARCHAR2	4000B	문자열	가변길이, 다국적 언어 지원
LONG	2GB	문자열	가변길이
RAW	2GB	바이트 문자열	가변길이
LONG RAW	2GB	이진 문자열
NUMBER	10-38 ∼ 1038	숫자
DATE		날짜	BC 4712/1/1∼AD 4712/12/31, (DD/MM/YY)
BLOB	4GB	이진 데이터
CLOB	4GB	문자 데이터
NCLOB	4GB	문자 데이터	다국적 언어 지원
BFILE	4GB	이진 데이터	DB 외부에 데이터를 저장
ROWID	10B	이진 데이터	Row Address.

- LOB Type

□Large Binary Object 저장 (최대 4GB)

□① 내부 LOB: table내에 직접 저장.

BLOB - Binary LOB

CLOB - Char. LOB

② 외부 LOB: table에 외부 binary file을 가리키는 경로를 저장.

BFILE

□$ORACLE_HOME/rdbms/admin/dbmslob.sql에 있는 함수들을 사용하여 연산 수행.

Ex) CREATE TABLE 회원 (회원번호  CHAR(5),

회원명	VARCHAR2(10),

메일주소	VARCHAR2(20),

사진	BLOB,

자기소개서	CLOB);

- User-Defined Type

□Object-Oriented Programming

Ex) CREATE TYPE 회원_type AS OBJECT (회원명	VARCHAR2(10),

생년월일	CHAR(8),

남녀구분 CHAR(2),

MEMBER FUNCTION

get_value RETURN number);

(CREATE FUNCTION을 사용하여 작성된 함수)

CREATE TABLE 회원 (회원번호	CHAR(5),

회원_profile	회원_type,

메일주소	VARCHAR2(20) );

INSERT INTO 회원 VALUES( '10001',

회원_profile('홍길동□, □19781218□, □남□),

□kdhong@cc.knue.ac.kr' );

□REF type: object에서 object를 참조하는 type

Ex)  CREATE TYPE 회원명_type AS OBJECT (회원명	VARCHAR2(10),

생년월일	CHAR(8),

남녀구분	CHAR(2),

MEMBER FUNCTION

get_value RETURN number);

CREATE TYPE 회원_type  AS OBJECT (회원번호	CHAR(5),

회원_profile	REF	회원명_type,

메일주소	VARCHAR2(20) );

CREATE  TABLE 회원  OF 회원_type;

□(Integrity) Constraint

- 잘못된 데이터가 table에 입력되는 것을 방지.

- 미리 정의된 constaint에 따라서 input data를 검사.

∀ 잘못된 데이터가 입력되면 trans.을 rollback시키고 error msg.를 출력

□NOT NULL

□UNIQUE: Unique value (NULL is permitted)

□PRIMARY KEY

□REFERENCES: Foreign key

□CHECK: User가 원하는 입력값 정의

Ex) CREATE TABLE 회원 (회원번호  CHAR(5) CONSTRAINT check_id

CHECK (회원번호 BETWEEN '10000' AND '20000'),

회원명	VARCHAR2(10),

메일주소	VARCHAR2(20) CONSTRAINT check_mailaddr

CHECK (메일주소=LOWER(메일주소));

□Trigger

- PS/SQL, C, Java로 작성된 procedure를 event 발생시 자동으로 수행시킴.

Ex) Events - Insert/Update/Delete 시

DB Start-up/Shut-down 시

특정 시간 등.

- Trigger Types

① Row trigger

□미리 정의된 DML(INSERT/UPDATE/DELETE)이 table의 각 row에 수행될 때마다 수행.

② 문장 trigger

□미리 정의된 DML이 table에 수행될 때 수행.

∀ 하나의 DML 문장이 한 table의 여러 row를 변경시켰을 경우,

row trigger: 여러 번 수행

문장 trigger: 한번만 수행

③ BEFORE/AFTER trigger

□	BEFORE: DML 문장이 수행되기 전에 수행

AFTER : DML 문장이 수행된 후에 수행

④ INSTEAD-OF trigger

□View가 DML에 의해서 변경되면 물리적인 '실제 table'에 적용시킴.

EX) ROW-BEFORE trigger

dept.sql 작성;

drop table dept;

create table dept	(deptno number(2),

dname varchar2(14),

loc    varchar2(13) );

dept-inst.sql 작성;

insert into dept values (60, 'distribution', 'new york');

change.sql 작성;

create table  change (change_no integer);

insert into change values (0);

change-trg.sql 작성;

drop trigger change_trg;

create trigger change_trg

before update or delete or insert on dept	; event 부분

begin	; action 부분

update change set change_no=change_no+1;

end;

/

[comedudb2]$ sqlplus scott/tiger

SQL> start dept

SQL> start change

SQL> select * from change;

CHANGE_NO

----------

0

SQL> start change-trg

Trigger created.

SQL> start dept-inst

1 row created.

SQL> select * from change;

CHANGE_NO

----------

1

SQL> start dept-inst

1 row created.

SQL> select * from change;

CHANGE_NO

----------

2

EX) DB가 shut-down되기 전에 change-no를 0으로 setting하고 싶은 경우,

change-shutdown-trg.sql 작성;

create trigger change_shutdown_trg before shutdown on database

begin

update change set change_no=0;

end;

/