Transcript Oracle8i Administration

Oracle8i Administration
포스데이타 광양운영팀
권태삼
Lesson 1
Oracle Architectural Components
■ Overview
Instance
User
Process
Shared Pool
Server
Process
Data Dict
Cache
SGA
Library
Cache
Data buffer
Cache
Redo Log
Buffer
PGA
SMON
Parameter
file
DBW0
Data
file
PMON
CKPT
Control
file
Password
file
LGWR
Others
Redo log
file
Archived
Log file
Database
3
■ Overview
•
Oracle Server
– Oracle Instance and Oracle Database
•
Oracle Instance
– Memory structure: SGA(Shared Global Area)
• 데이터 베이스 프로세스에 의해 공유되는 데이터베이스 정보를 저장
– Background Process
•
Database file
– 데이터베이스 정보를 저장하기 위해 제공되는 OS File System
•
Server Process
– User Process로 부터 보내어진 SQL 문을 실행하는 프로세스
4
■ Oracle Database Files
Parameter
file
Data
file
Control
file
Redo log
file
Password
file
Archived
Log file
Database
•
Data File
– 데이터베이스 내에 실제 데이터
– 데이터는 사용자 정의 테이블에 저장
– 데이터 파일은 데이터 사전 포함
•
Redo Log File
– 데이터를 복구하기 위해 데이터베이스 변경 기록을 보관
– 데이터베이스는 최소 두개의 Redo log file이 필요
•
Control File
– 데이터베이스의 무결성을 유지하기 위해 필요한 정보를 포함
• 데이터베이스 구조정보
• 동기화정보
– 데이터베이스는 최소한 하나의 control file을 포함
5
■ Oracle Instance
Instance
Shared Pool
Memory
structures
SGA
Library
Cache
Data Dict
Cache
SMON
DBW0
Data buffer
Cache
PMON
CKPT
Redo Log
Buffer
LGWR
•
Oracle database에 접근하기 위한 수단
•
항상 하나의 데이터베이스만을 Open
•
Oracle Instance
Others
Background
Processes
– SGA Memory Structure
– Background Process
6
■ Oracle Instance
•
SGA
– 데이터베이스 프로세스에 의해 공유되는 데이터베이스 정보를 저장하기 위
해 사용되는 메모리 영역
– Shared Pool
• Library Cache: 최근에 실행된 SQL 문장을 보관
• Data Dictionary Cache: 데이터 사전으로부터 가장 최근에 사용된 데이터 보관
– Data Buffer Cache: 가장 최근에 사용된 데이터를 보관
– Redo log Buffer: 데이터베이스의 변경된 정보를 보관
•
Background Process
– 동시 접속 사용자의 서비스 요청에 필요한 기능을 수행
– Database Writer(DBW0): database buffer cache의 변경된 내용을 데이터
파일에 저장
– Log Writer(LGWR): redo log buffer에 등록된 변경사항을 redo log file에
보관
– System Monitor(SMON): 데이터베이스 일관성 체크
– Process Monitor
– Checkpoint Process(CKPT): control file에 데이터베이스 상태 정보를 갱
신, buffer cache내에 변경사항을 data file에 갱신
7
■ Processing a Query
•
Parse
–
–
–
–
•
Search for identical statement
Check syntax, object names, and privileges
Lock objects used during parse
Create and store execution plan
Execute
– Identify rows selected
•
Fetch
– Return rows to user process
8
■ The Shared Pool
Shared Pool
Library
Cache
Data Dict
Cache
•
Library Cache(Shared SQL Area)
–
–
–
–
•
Text of the SQL statement
Parse tree
Execution plan
Reduce parse time and memory requirement
Data Dictionary Cache(Dictionary Cache, Row cache)
– 최근에 사용된 테이블에 대한 정보 보관
– Tables, indexes, columns, users, privileges, other database objects
•
SHARED_POOL_SIZE 파라미터로 size를 정할 수 있음.
9
■ Database Buffer Cache
•
최근에 사용된 block을 보관
•
DB_BLOCK_SIZE 파라미터를 이용해 block size 변경
•
DB_BLOCK_BUFFES 파라미터를 이용해 buffer 수 변경
■ Program Global Area(PGA)
Server
Process
•
Server Process에 의해서만 쓰여짐
•
Sort area, session information, cursor state, stack space
PGA
10
■ Processing a DML Statement
SGA
User
Process
Data buffer
Cache
UPDATAE emp …
4
Server
Process
1
1
2
Data
file
Shared Pool
Redo Log
Buffer
3
Control
file
Redo log
file
Database
•
DML execute phase
1.
2.
3.
4.
필요로 하는 block이 buffer cache에 있는지 없는지 check
수정하기 위해 row를 locking
Data 변경사항을 redo log buffer에 기록
Server process가 rollback block에 before image를 기록, data block을
update
11
■ Redo Log Buffer
•
Size defined by LOG_BUFFER
•
Records changes made through the instance
•
Used sequentially
•
Is a circular buffer
■ Rollback Segment
•
Server process saves the old data value into a rollback segment
•
Undo the changes if the transaction is rolled back
•
Provide read consistency
•
Instance Recovery
12
■ COMMIT Processing
Instance
1
Server
Process
SGA
4
Data buffer
Cache
Shared Pool
Redo Log
Buffer
3
LGWR
User
Process
2
Data
file
Control
file
Redo log
file
Database
•
Step in Processing COMMIT
–
–
–
–
Server process가 SCN을 가진 commit record를 redo log buffer에 기록
LGWR이 redo log buffer의 내용을 redo log file에 기록
User에게 COMMIT 완료를 통보
Resource lock을 release
13
■ Log Writer(LGWR)
Instance
SGA
Data buffer
Cache
Shared Pool
Redo Log
Buffer
LGWR
Data
file
Control
file
Redo log
file
Database
•
LGWR writes when
– When a transaction commits
– When the redo log buffer is one-third full
– When there is more than a megabyte of changes recorded in the redo
log buffer
– Before DBW0 writes modified blocks to the data files
14
■ Database Writer(DBW0)
Instance
SGA
Data buffer
Cache
Shared Pool
Redo Log
Buffer
DBW0
Data
file
Control
file
Redo log
file
Database
•
DBW0 writes when
–
–
–
–
There are many dirty buffers
There are few free buffers
Timeout occurs(every three seconds)
Checkpoint occurs
15
■ SMON(System Monitor)
•
Automatically recovers the instance
– Roll forward changes in the redo logs
– Opens the database for user process
– Rolls back uncommitted transactions
•
Coalesces free space
•
Deallocates temporary segments
■ PMON(Process Monitor)
•
Cleans up after failed processes by
– Rolling back the transaction
– Releasing locks
– Releasing other resources
16
Lesson 2
Getting Started with the Oracle Server
■ Users SYS and SYSTEM
•
SYS
– Password: change_on_install
– Owner of the database data dictionary
•
SYSTEM
– Password: manager
– Owner of additional internal tables and views used by Oracle tools
■ Authentication
•
OS 인증
•
Password 인증
– Remote로 접속 시 password file을 인증 받아야 접속 가능
– OS의 DBA 그룹이 아니면서 sysdba, sysoper role을 가지고 있는 user
•
DB 인증
18
■ Operating System Authentication
•
Set up the user to be authenticated by the operating system
•
Set REMOTE_LOGIN_PASSWORDFILE to NONE
•
Use the following commands to connect to a database
CONNECT / AS SYSDBA
CONNECT / AS SYSOPER
sqlplus internal
19
■ Using Password File Authentication
•
Create the password file using the password utility
$orapwd file=$ORACLE_HOME/dbs/orapwSID
password=admin entries=10
•
Set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE or SHARED
•
Use the following command to connect to a database
CONNECT INTERNAL/ORACLE
20
Lesson 3
Managing an Oracle Instance
■ Overview of Start Up and Shut Down an Oracle Server
•
Start up
– Start an instance
– Mount the database
– Open the database
•
Shut down
– Close the database
– Dismount the database
– Shut down the instance
22
■ The Initialization Parameter File
Instance
SGA
Data buffer
Cache
initU15.ora
SMON
DBW0
Shared Pool
Redo Log
Buffer
PMON
CKPT
LGWR
ARCH
SQL> CONNECT / AS SYSDBA
SQL> STARTUP PFILE=/DISK1/initU15.ora
•
Size the System Global Area(SGA)
•
Set database and instance defaults
•
Set user or process limits
•
Set limits on database resources
•
Define various physical attributes of the database(database block size)
•
Specify control files, archived log files, the ALERT file, and trace file
locations
23
■ Stages in Startup and Shutdown
OPEN
Data file, Redo log
file 검증
All files opened as
STRATUP
described by the control
MOUNT
control file 읽음
file for this instance
Control file opened
NOMOUNT
for this instance
parameter file 읽음
Instance started
SHUTDOWN
SHUTDOWN
•
Starting Up in Stages
–
Starting the Instance
•
•
•
•
–
Mounting the Database
•
•
•
–
parameter file 읽음
SGA와 background process를 띄움
ALERT file, trace file을 open
Database와 control file을 생성
Renaming data file
Redo log archived option 설정
full database recovery 수행
Opening the Database
•
•
•
•
Open the data file
Open the redo log file
database 일관성 check
필요하다면 SMON이 instance recovery 수행
24
■ Stages in Startup and Shutdown
•
Shutting Down in Stages
– Closing the Database
• buffer cache의 변경사항을 data file에 보관
• redo log file 내의 entries를 redo log file에 보관
• data file, redo log file close
– Dismounting the Database
• Database dismount
• Control file close
– Shutting Down the Instance
•
•
•
•
Instance Shutting down
ALERT file, trace file close
SGA deallocate
background process terminate
– ALERT file
• DB start, shutdown 시간 기록
• startup, shutdown 시 error 기록
25
■ Starting Up the Instance
•
STARTUP Command
STARTUP PFILE=$ORACLEHOME/DISK1/initU15.ora
•
ALTER DATABASE Command
ALTER DATABASE database MOUNT;
•
Opening a Database in Read-Only Mode
ALTER DATABASE database OPEN READ ONLY;
–
–
–
–
Execute queries
Execute disk sorts using locally managed tablespaces
Take data files offline and online
Perform recovery of offline data files and tablespaces
26
■ Shutting Down
•
Shutdown Option
Shutdown Mode
•
A
I
T
N
Allow new connections
NO
NO
NO
NO
Wait until current sessions end
NO
NO
NO
YES
Wait until current transactions end
NO
NO
YES
YES
Force a checkpoint and close files
NO
YES
YES
YES
Shutdown mode
A Abort
I Immediate
T Transactional
N Normal
27
■ Getting and Setting Parameter Values
•
Dynamic Performance View
–
–
–
–
Oracle Server에 의해 관리되고, 계속적으로 update
디스크와 메모리 구조에 대한 데이터를 포함
performance tuning을 위해 사용되는 데이터를 포함
Have public synonyms with the prefix V$
SGA
Control
file
V$PARAMETER
V$SGA
V$OPTION
V$PROCESS
V$VERSION
V$INSTANCE
V$THREAD
V$CONTROLFILE
V$DATABASE
V$DATAFILE
V$DATAFILE_HEADER
V$LOGFILE
28
■ Getting and Setting Parameter Values
•
Displaying Current Parameter Values
– Command
SHOW PARAMETER control
– Dynamic performance view V$PARAMETER를 이용한 query
SELECT name FROM V$PARAMETER
•
Dynamic Initialization Parameters
– instance 실행동안에 initialization parameter를 수정
ALTER SYSTEM SET TIMED_STATISTICS=true;
ALTER SYSTEM SET SORT_AREA_SIZE=131072 DEFERRED;
29
■ Managing Sessions
•
Enable and Disable Restricted Session
– Command
STARTUP RESTRICT
– Mode 변경
ALTER SYSTEM DISABLE RESTRICTED SESSION;
•
Terminating Sessions
– Session 확인
SELECT id,serial# FROM V$SESSION WHERE
username=‘SCOTT’;
– Session Terminate
ALTER SYSTEM KILL SESSION ‘7,15’;
30
■ Managing Sessions
•
ALERT File and Trace File
–
–
–
–
Trace file은 서버와 background process에 의해 작성
Oracle Server는 trace file 내에 error 정보를 보관
ALERT file은 메시지와 에러로 구성
Server process tracing을 변경
ALTER SESSION SET sql_trace=TRUE;
31
Lesson 4
Creating a Database
■ Overview
•
Database System을 관리하고, 조직하는데 첫번째 단계
•
Database 생성은 많은 data file(OS file)이 필요함
•
Database 생성 후 변경할 수 없는 database block size와 database
character set과 같은 database 설정을 결정한 후 database를 생성
•
다음과 같은 방법으로 인증이 되어야 database를 생성할 수 있음
– operating system
– Using a password file
•
Instance allocate를 위한 Memory가 충분해야
•
Database 공간을 위한 충분한 디스크 공간이 있어야
33
■ Creating a Database Manually
•
유일한 instance, 데이터베이스 이름, 데이터베이스 character set을 결
정
•
OS 변수 설정
•
parameter file 준비
•
password file 생성
•
Instance 실행
•
데이터베이스 생성
•
Data Dictionary 생성을 위한 scripts 실행과 차후 단계 실행
34
■ Creating a Database Manually
•
OS 변수 설정
Variable
•
Description
ORACLE_HOME
Oracle Software가 인스톨될 디렉토리 설정
ORACLE_SID
Oracle Instance 지정
ORACLE_BASE
특별하게 필요하지 않음
ORA_NLS33
Database 생성 시 character set 설정
PATH
$ORACLE_HOME/bin이 포함된 path 설정
Parameter file 준비
– 새로운 initSID.ora 생성
$cp init.ora $ORACLE_HOME/dba/initU16.ora
– vi 에디터를 이용해 initSID.ora 파일 수정
• DB_NAME, CONTROL_FILES, DB_BLOCK_SIZE는 반드시 수정
35
■ Creating a Database Manually
•
Instance 실행
– SYSDBA로 접속
– NOMOUNT 단계에서 Instance 실행
SQL>STARTUP NOMOUNT PFILE=initU16.ora
•
데이터베이스 생성
CREATE DATABASE U16
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (‘/DISK3/log1a.rdo’, /DISK4/log1b.rdo’) SIZE 1 M,
GROUP 2 (‘/DISK3/log2a.rdo’, /DISK4/log2b.rdo’) SIZE 1 M
DATAFILE
‘/DISK1/system01.dbf’ size 50M autoextend on
CHARACTER SET WE8ISO8859P1;
36
Lesson 5
Creating Data Dictionary Views and
Standard Packages
■ Overview
•
Data Dictionary
– 데이터베이스 내에 객체들에 대한 설명을 포함
– Base tables
– Data Dictionary views
•
Dynamic Performance table
– 데이터베이스와 instance를 모니터하고 tuning 하기 위해 DBA에 의해 사용
되는 정보를 포함
•
Built-in PL/SQL packages
38
■ Data Dictionary Overview
•
Oracle Database 중에 가장 중요한 부분
•
데이터베이스와 데이터베이스의 object들에 대한 정보 제공
•
read-only table과 view를 포함
•
SQL Command에 의해 update
– DDL(Data Definition Language)
– Some DML(Data Manipulation Language)
•
SYS user가 소유
•
SYSTEM tablespace 내에 저장
•
SELECT 문만을 사용
39
■ Data Dictionary Contents
•
Logical and physical database structure
•
Definition and space allocations of objects
•
Integrity constraints
•
Users의 이름
•
각 user에게 할당된 Roles
•
각 user에게 할당된 Privileges
•
Auditing
•
Other Information
40
■ How the Data Dictionary is Used
•
Oracle server uses it to find information about
– Users
– Schema Objects(tables, views, indexes, clusters, synonyms,
procedures, and so on)
– Storage structures
•
Oracle Server는 DDL 문이 실행될 때 Data Dictionary를 수정
•
User와 DBA는 데이터베이스에 대한 정보를 얻고자 할 때 사용
•
Data Dictionary Example
– General Overview
• DICTIONARY
• DICT_COLUMNS
– Schema objects
•
•
•
•
DBA_TABLES
DBA_TAB_COLUMNS
DBA_OBJECTS
DBA_CONSTRAINTS
– Space allocation
• DBA_SEGMENTS
• DBA_EXTENTS
• DBA_FREE_SPACE
– Database Structure
• DBA_DATA_FILES
• DBA_ROLLBACK_SEGS
• DBA_TABLESPACES
41
■ Constructing the Data Dictionary
•
Creating Data Dictionary Views
Script
•
Purpose
catalog.sql
공통적으로 사용되는 data dictionary와 synonyms를 생성
catproc.sql
서버측 PL/SQL을 위해 요구되는 scripts
Administrative Scripts
Convention
Description
cat*.sql
Catalog와 data dictionary에 대한 정보
dbms*.sql
Database Package specifications
prvt*.plb
암호화된 database package code
utl*.sql
Database utilities를 위한 Views와 Tables
42
Lesson 6
Maintaining the Control File
■ The Use of the Control File
•
데이터베이스를 성공적으로 동작시키기 위해 필요한 작은 binary file
•
각 control file은 하나의 database와 link
•
데이터베이스가 사용되는 동안 Oracle Server에 의해 계속적으로
update
•
control file 내의 정보는 Oracle Server에 의해서만 수정
•
Should be multiplexed
•
Contents
–
–
–
–
–
–
–
–
–
Database name and identifier
Database creation date
Data file and redo log file locations
Tablespace names
Log history is recored during log switches
Location and status of archived logs
Backup information
Current log sequence number
Checkpoint information
44
■ How to Add a Control File
•
Shut down the database
•
Copy the current control file using an operating system command
•
Add the new control file names to the CONTROL_FILES parameter
•
Start up the database
■ Guidelines for Control Files
•
Multiplex the control file
•
Include the full pathname in CONTROL_FILES
•
Back up the control file after the database structure changes
45
■ Obtaining Information About the Control File
•
V$CONTROLFILE
SELECT name FROM v$controlfile;
•
V$CONTROLFILE_RECORD_SECTION
•
Control File로 부터 많은 Performance View에 대한 정보를 얻음
–
–
–
–
–
–
–
–
–
–
–
V$BACKUP
V$DATAFILE
V$TEMPFILE
V$TABLESPACE
V$ARCHIVE
V$LOG
V$LOGFILE
V$LOGHIST
V$ARCHIVED_LOG
V$DATABASE
Others
46
Lesson 7
Maintaining Redo Log Files
■ Overview
•
Oracle Server는 데이터베이스의 데이터 손실을 최소화하기 위해 online
redo log file을 관리
•
database buffer cache 내에 모든 데이터 변경 사항을 기록
•
instance failure와 같은 상황에서 data file에는 쓰여지지 않고, commit 된
데이터를 복구하기 위해 사용
•
recovery를 위해서만 사용
•
Redo Log Groups and Members
– Redo Log Groups
• online redo log file들의 집합을 online redo log group
• LGWR Process는 그룹 내 모든 online redo log files에 같은 정보를 동시에 작성
• Oracle Server에는 최소 두개의 online redo log file groups가 있어야
– Redo Log Members
• 그룹 내 각 online redo log file을 member라 함
• 그룹 내 각 member는 동일한 log sequence number를 가지고 있고, 같은 size
• 현재 log sequence number는 control file과 모든 data files의 헤더에 저장
48
■ LGWR, Log Switches, and Checkpoints
Instance
SGA
Data buffer
Cache
DBWR
Parameter
file
Password
file
Shared Pool
Redo Log
Buffer
CKPT
Data
file
LGWR
Control
file
ARCH
Redo log
file
Archived
log file
Database
49
■ LGWR, Log Switches, and Checkpoints
•
LGWR
–
–
–
–
–
•
transaction commit
redo log buffer가 1/3이상 full
redo log buffer에 기록된 변경사항이 1M 이상일때
timeout(every three second)
DBWn이 동작하기 전에
Log Switches
– LGWR이 하나의 online redo log group에 작성하는 것을 멈추고, 다른
online redo log group에 작성하는 event
– Log switch가 발생하면 checkpoint가 시작
•
Checkpoint
– CKPT는 모든 데이터파일의 header와 control file의 header를 update
– Checkpoint는 다음과 같은 상황에서 발생
• 모든 log switch
• instance가 normal, transactional, immediate 옵션을 가지고 shut down되었을 때
• LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT,
FAST_START_IO_TARGET 파라미터 설정에 따라
• DBA의 요청에 의해
• checkpoint에 대한 정보는 LOG_CHECKPOINTS_TO_ALERT 파리미터가 TRUE로
설정 되어있으면 ALERT 파일에 기록된다.
50
■ Obtaining Log and Archive Information
•
SQL Command
SQL>ARCHIVE LOG LIST;
•
V$DATABASE
SQL>SELECT name, log_mode FROM v$database;
•
V$INSTANCE
SQL>SELECT archive FROM v$instance;
51
■ Obtaining Information About Groups and Members
•
V$THREAD
SQL>SELECT groups, current_group#, sequence# FROM v$thread;
•
V$LOG
SQL>SELECT group#, sequence#, bytes, members, status FROM v$log;
•
V$INSTANCE
SQL>SELECT * FROM v$logfile;
52
■ Multiplexing and Maintaining Members and Groups
•
Adding Online Redo Log Groups
ALTER DATABASE ADD LOGFILE
(‘/DISK3/log3a.rdo’, ‘/DISK4/log3b.rdo’) size 1M;
•
Adding Online Redo Log Members
ALTER DATABASE ADD LOGFILE MEMBER
‘/DISK4/log1b.rdo’ TO GROUP 1
‘/DISK4/log2b.rdo’ TO GROUP 2
•
How to Relocate or Rename Online Redo Log Files
– 새로운 위치로 online redo log file 복사
– ALTER DATABASE RENAME FILE 명령어 실행
– 물리적으로 이름이나 OS 파일을 생성하는 것이 아니라 control file의 pointer
를 변경하는 것
53
■ Dropping Online Redo Log Groups and Members
•
Dropping Online Redo Log Groups
ALTER DATABASE DROP LOGFILE GROUP 3;
– 최소 2개의 online redo log group이 있어야
– active and current group은 drop 불가
– 데이터베이스가 ARCHIVELOG 모드이고, log file group이 archived가 아니
면 그룹 drop 불가
– drop 후 OS 파일 삭제
•
Dropping Online Redo Log Members
ALTER DATABASE ADD LOGFILE MEMBER ‘/DISK4/log2b.dbf’;
– group내에 member가 하나이면 그 member는 drop 불가
– group이 current이면 그 group에 속한 member는 drop 불가 -> log switch
– member drop 후 OS 파일 삭제
•
Clearing Online
ALTER DATABASE CLEAR LOGFILE ‘/DISK3/log2a.rdo’;
54
■ Using LogMiner
•
Redo log file을 처리하기 위한 procedure를 제공
•
Redo log file의 내용을 SQL문으로 변환
•
LogMiner 사용법
– Directory file 생성
EXECUTE DBMS_LOGMNR_D.BUILD
(‘v815dict.ora’, ‘/ora815/admin/v815/log’);
– Log File List 생성
EXECUTE DBMS_LOGMNR.ADD_LOGFILE (
‘/ora815/admin/v815/redo01a.log’, DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE (
‘/ora815/admin/v815/redo02a.log’, DBMS_LOGMNR.ADDFILE);
– LogMiner 실행
EXECUTE DBMS_LOGMNR.START_LOGMNR ( DICTFILENAME=>
‘/ora815/admin/v815/log/ v815dict.ora’);
55
■ Using LogMiner
•
LogMiner 사용법
– 조회
SELECT timestamp, username, sql_redo FROM v$logmnr_contents
WHERE seg_name =‘EMP’;
TIMESTAMP
---------14-APR-99
14-APR-99
USER
----SYS
SYS
SQL_REDO
---------------------------------update SCOTT.EMP set sal = ..
update SCOTT.EMP set sal = ..
– LogMiner 종료
EXECUTE DBMS_LOGMNR.END_LOGMNR;
•
Obtaining Information About Logs Being Analyzed
– V$LOGMNR_DICTIONARY
– V$LOGMNR_PARAMETERS
– V$LOGMNR_CONTENTS
56
Lesson 8
Maintaining Tablespaces and
Data Files
■ Database Storage Hierarchy
Database
Tablespace
Logical
Segment
Data File
Physical
Extent
Oracle
Block
OS Block
58
■ Database Storage Hierarchy
•
Database Architecture
– physical structure: control files, online redo log files, data files
– logical structure: tablespaces, segments, extents, data blocks
•
Tablespace
–
–
–
–
•
하나의 database에만 존재해야
각 tablespace는 하나 이상의 datafile로 구성
하나의 tablespace는 하나 이상의 segments로 구성
Tablespace는 read-write, read-only status 변경 가능
Data File
– 하나의 data file은 오직 하나의 tablespace내에 존재
– DBA가 data file의 크기를 변경
– Oracle Server는 디스크 공간을 할당함으로써 tablespace를 위한 data file을
생성
•
Segments=Object
–
–
–
–
table에 할당된 모든 저장 공간
segment는 다른 tablespace에 존재할 수 없음
하나의 tablespace 내에 존재하는 다른 data file 내에 존재 가능
각 segments는 하나 이상의 extent들로 구성
59
■ Database Storage Hierarchy
•
Extents
–
–
–
–
–
•
하나 이상의 extent들은 하나의 segment를 구성
segment를 생성할 때 최소 하나의 extent를 포함
DBA가 segment에 extent들을 추가할 수 있음
extent는 연속된 Oracle Block의 집합
하나의 extent는 하나의 data file에만 존재해야
Data Blocks
–
–
–
–
–
Oracle Server는 Oracle Block 단위로 data file 내에 저장공간을 관리
Oracle Server가 할당, 읽기, 쓰기할 수 있는 가장 작은 단위의 저장공간
하나의 Data block은 하나 이상의 OS block으로 구성
Data block의 크기는 DB_BLOCK_SIZE 파라미터에 따라 결정
최대 block 크기는 OS에 따라 다름
Tablespace
data file
data file
Segment
Extent
60
■ SYSTEM and Non-SYSTEM Tablespace
•
SYSTEM Tablespace
– Database 생성 시 만들어짐
– Data dictionary를 포함
– SYSTEM rollback segment를 포함
•
Non-SYSTEM Tablespace
– Space 관리가 쉽다
– rollback, temporary, application data, application index segment를 분리
– user 객체에 할당된 space를 제어
61
■ Creating Tablespace
CREATE TABLESPACE app_data
DATAFILE ‘/DISK4/app_data_01.dbf’ SIZE 100M,
‘/DISK5/app_data_02.dbf’ SIZE 100M
MINIMUM EXTENT 500K
DEFAULT STORAGE ( INITIAL
500K
NEXT
500K
MAXEXTENTS 500
MINEXTENTS
3
PCTINCREASE 0
);
■ Space Management in Tablespace
•
Dictionary-managed tablespaces
– Default technique
– Free extents recored in data directory tables
•
Locally managed tablespaces
– Free extents recorded in bitmap
– Each bit corresponds to a block or group of blocks
– Bit value indicates free or used
62
■ Locally Managed Tablespaces
CREATE TABLESPACE user_data
DATAFILE ‘/DISK2/user_data_01.dbf’ SIZE 500M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M;
•
recursive space management를 줄일 수 있음
•
data dictionary table의 내용을 줄일 수 있음
•
data dictionary내에 table을 update하지 않기 때문에 rollback 정보를 생
성하지 않는다.
•
locally managed tablespace 내에 모든 extent들의 크기는 같다
•
No coalescing required
63
■ Temporary Tablespace
•
sort 연산을 위해 사용
•
permanent object들을 포함할 수 없다
•
Locally managed extent
•
UNIFORM SIZE = SORT_AREA_SIZE * n
•
항상 NOLOGGINF 모드로 설정
•
tempfile을 read-only로 할 수 없다
•
tempfile을 rename 할 수 없다
•
ALTER DATABASE 명령어로 tempfile을 생성할 수 없다.
CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/DISK2/temp_01.dbf’
SIZE 500M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M;
64
■ Taking Tablespace Offline or Online
•
Offline tablespace인 data에 접근 불가능
•
항상 online이어야 하는 tablespace
– SYSTEM
– active rollback segment인 tablespace
•
offline tablespace backup을 수행
•
tablespace나 data file을 복구
•
data file의 이동
•
tablespace offline 명령어
ALTER TABLESPACE app_data OFFLINE;
•
tablespace online 전환
ALTER TABLESPACE app_data ONLINE;
65
■ Read-Only Tablespaces
ALTER TABLESPACE app_data READ ONLY;
•
Tablespace에 대한 read operation 만 수행 가능
•
Objects를 tablespace로부터 drop 가능 -> Data dictionary에서만 삭제
•
Tablespace가 online 상태에서만 read-only 상태로 변경 가능
•
active rollback segment를 포함하고 있으면 불가능
•
Tablespace가 online backup 상태이면 read-only 상태로 변경 불가능
■ Dropping Tablespaces
DROP TABLESPACE app_data INCLUDING CONTENTS;
•
Tablespace가 data dictionary로 부터 삭제
•
contents가 data dictionary로 부터 삭제
66
■ Resizing a Tablespace
•
Data file의 크기를 변경
– Automatically
ALTER TABLESPACE app_data
ADD DATAFILE ‘/DISK6/app_data_04.dbf’
SIZE 200M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 500M;
– Manually
ALTER DATABASE
DATAFILE ‘/DISK5/app_data_02.dbf’
RESIZE 200M;
•
Tablespace에 data file을 추가
ALTER TABLESPACE app_data
ADD DATAFILE ‘/DISK5/app_data_03.dbf’
SIZE 200M;
67
■ Moving Data Files
•
ALTER TABLESPACE
– Tablespace는 offline 상태이어야 함
– target data file이 존재해야 함
ALTER TABLESPACE app_data
RENAME
DATAFILE ‘/DISK4/app_data_01.dbf’
TO
‘/DISK5/app_data_01.dbf’;
•
ALTER DATABASE
– Database가 mount되어 있어야 함
– target data file이 있어야 함
ALTER DATABASE
RENAME FILE ‘/DISK1/system_01.dbf’
TO ‘/DISK2/system_01.dbf’;
– Tablespace가 offline될 수 없는 tablespace의 data file rename 방법
•
•
•
•
•
Shut down the database
data file move
Mount the database
ALTER DATABASE RENAME FILE 명령어 실행
Open the database
68
Lesson 9
Storage Structure and Relationships
■ Types of Segments
•
Segments
– Database 내에 object들이 차지하고 있는 공간
•
Table
– Database 내에 data를 보관하기 위한 가장 일반적인 방법
•
Table Partition
– High concurrent usage 시 Scalability와 availability를 향상
– Table 내에 data는 여러 partition에 저장될 수 있고, partition들은 다른
tablespace에 존재 가능
– hashing algorithm 이용
•
Cluster
–
–
–
–
•
data segment
cluster 내에 row는 key column value를 기반으로 저장
하나의 cluster는 하나 이상의 table을 포함
index와 hashing algorithm을 이용하여 cluster 내 row를 접근
Index
– 특정 key를 이용하여 row의 위치를 쉽게 검색할 수 있도록
•
Index-organized table, Index partition, Rollback segment, Temporary
segment, LOB segment, LOB index, Nested table, Bootstrap segment
70
■ Using Block Space Utilization Parameters
•
Database Block
–
–
–
–
•
I/O의 최소 단위
하나 이상의 OS Block으로 구성
DB_BLOCK_SIZE 파라미터로 크기 설정
Database 생성 시 설정
Database Block Contents
– Header: data block address, table directory, row directory, transaction slot
– Free space
– Data
Header
Free Space
Data
71
■ Using Block Space Utilization Parameters
•
Block Space Utilization Parameters
INITRANS
MAXTRANS
PCTFREE
PCTUSED
– Data와 index segment의 공간 활용을 제어하기 위해 사용
– INITRANS, MAXTRANS
• Data block이나 index block에 생성되는 transaction slot의 초기와 최대 수 설정
• transaction slot은 block을 변경하는 transaction에 대한 정보를 저장
– PCTFREE: Block의 update에 대비해 reserved space percentage
– PCTUSED: minimum percentage of used space
72
■ Obtaining Information About Storage Structures
•
Data Dictionary View
Used Extents
DBA_EXTENTS
Free Extents
DBA_FREE_SPACE
Segments
DBA_SEGMENTS
Data Files
DBA_DATA_FILES
Tablespaces
DBA_TABLESPACES
•
Querying DBA_SEGMENTS
– General Information
• OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME
– Storage settings
• INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE
– Size
• EXTENTS, BLOCKS, BYTES
– Other information
• Location, Tuning
73
■ Obtaining Information About Storage Structures
•
Querying DBA_EXTENTS
– Identification
• OWNER
• SEGMENT_NAME
• EXTENT_ID
– Location
•
•
•
•
TABLESPACE_NAME
RELATIVE_FNO
FILE_ID
BLOCK_ID
– Size
• BLOCKS
• BYTES
•
Querying DBA_FREE_SPACE
– Location
•
•
•
•
TABLESPACE_NAME
RELATIVE_FNO
FILE_ID
BLOCK_ID
– Size
• BYTES
• BLOCKS
74
Lesson 10
Managing Rollback Segments
■ Overview
•
Rollback Segment는 데이터 변경 시 변경 전 data와 data의 위치 저장
•
Rollback segment header에는 rollback segment를 사용하는 현재
transaction에 대한 정보가 저장된 transaction table을 포함
•
하나의 transaction은 하나의 rollback segment만을 사용
Old
Image
New
Image
Table
Rollback Segment
Update Transaction
76
■ Purpose
•
Transaction Rollback
– transaction이 table내에 row를 변경할 때, 변경 전에 데이터를 rollback
segment에 보관
•
Transaction Recovery
– instance failure 시 uncommitted 변경 사항을 roll back
•
Read Consistency
– 다른 user에게 변경되기 전에 데이터 값을 보여줌
Transaction Rollback
Transaction
recovery
Rollback Segment
Read Consistency
77
■ Types of Rollback Segments
•
SYSTEM
– Database 생성 시 SYSTEM tablespace 내에 생성
– SYSTEM tablespace 내에 objects를 위해 사용
•
Non-SYSTEM
– multiple tablespace를 가지고 있는 database는 최소 하나의 non-SYSTEM
rollback segment가 필요
– 다른 tablespace 내에 objects를 위해 사용
– private
– public
•
Deferred
– Tablespace가 immediate option으로 offline 되었을 때 사용
– Tablespace가 다시 online 되었을 때 transactions을 roll back
78
■ Using Rollback Segments with Transactions
•
Allocation of a Rollback Segment
– Transaction이 시작되면 하나의 rollback segment가 할당
•
Using extent
– Transactions은 sequential, circular하게 rollback segment의 extent를 사용
– 하나 이상의 transaction들이 같은 rollback segment의 extent를 사용 가능
– 각 rollback segment block은 하나의 transaction의 정보를 포함
•
Growth of Rollback Segments
– pointer는 다음 extent가 no active transaction일 때만 이동 가능
– next extent가 사용되고 있으면, transaction은 하나의 extent를 추가
1
1
2
2
5
4
3
3
4
Active extent
New extent
Inactive extent
79
■ Creating Rollback Segments
•
Guidelines
– 모든 extents의 크기가 같도록 INITIAL=NEXT를 사용
– Rollback segment extents의 allocation과 deallocation을 최소화하기 위해
OPTIMAL 값을 설정
– 불필요한 rollback segment의 확장을 피하기 위해 MAXEXTENTS 값을 설정
CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE (
INITIAL
100K
NEXT
100K
MINEXTENTS
20
MAXEXTENTS
100
OPTIMAL
2000K );
•
Bringing Rollback
ALTER ROLLBACK SEGMENT rbs01 ONLINE;
– Database startup 시 rollback segment를 online 상태로 설정
ROLLBACK_SEGMENTS=(rbs01, rbs02)
80
■ Maintaining Rollback Segments
•
Changing Rollback Segment Storage Settings
– OPTIMAL이나 MAXEXTENTS를 변경
ALTER ROLLBACK SEGMENT rbs01
STORAGE( MAXEXTENTS 200 );
•
Deallocating Space from Rollback Segments
ALTER ROLLBACK SEGMENT rbs01
SHRINK TO 4M;
•
Taking a Rollback Segment Offline
ALTER ROLLBACK SEGMENT rbs01
OFFLINE;
•
Dropping Rollback Segments
DROP ROLLBACK_SEGMENT rbs01;
81
■ Obtaining Rollback Segment Information
•
DBA_ROLLBACK_SEGS
– Identification
• SEGMENT_ID
• SEGMENT_NAME
– Location
• TABLESPACE_NAME
– Type
• OWNER(PUBLIC or SYS)
– Status
• STATUS(ONLINE or OFFLINE)
•
Rollback Segment Statistics
V$ROLLNAME
USN
NAME
V$ROLLSTAT
USN
EXTENTS
RSSIZE
XACTS
.
.
82
■ Obtaining Rollback Segment Information
•
Rollback Segment Statistics
V$ROLLNAME
USN
NAME
•
V$ROLLSTAT
USN
EXTENTS
RSSIZE
XACTS
OPTSIZE
HWMSIZE
AVEACTIVE
STATUS
CUREXT
CURBLK
Rollback Segment: Current Activity
V$SESSION
SADDR
USERNAME
SID
SERIAL#
V$TRANSACTION
SES_ADDR
XIDUSN
UBAFIL
STATUS
.
.
83
■ Troubleshooting Rollback Segment Problem
•
Transactions을 위한 공간 부족
– Tablespace에 data file 추가
– MAXEXTENTS 크기를 늘려줌
– 초기 extent 값을 크게 설정해서 segment를 재생성
•
Read-consistency error
– MINEXTENTS 값을 크게
– extent의 크기를 크게
– OPTIMAL 값을 크게
•
Blocking Session
– Blocking Session을 찾아서 kill
•
Error in taking tablespace offline
84
Lesson 11
Managing Tables
■ Overview
•
Regular table
•
Partitioned table
•
Index-organized table
•
Cluster
•
Structure of a Row
Row header
Column length
Column value
86
■ Maintaining Rollback Segments
•
Changing Rollback Segment Storage Settings
– OPTIMAL이나 MAXEXTENTS를 변경
ALTER ROLLBACK SEGMENT rbs01
STORAGE( MAXEXTENTS 200 );
•
Deallocating Space from Rollback Segments
ALTER ROLLBACK SEGMENT rbs01
SHRINK TO 4M;
•
Taking a Rollback Segment Offline
ALTER ROLLBACK SEGMENT rbs01
OFFLINE;
•
Dropping Rollback Segments
DROP ROLLBACK_SEGMENT rbs01;
87
■ Creating Table
CREATE TABLE employee (
id
NUMBER(7),
last_name VARCHAR2(25),
dept_id
NUMBER(7))
PCTFREE 20 PCTUSED 50
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50 )
TABLESPACE data;
•
Guidelines
– tablespace fragmentation을 줄이기 위해 표준 extent size를 사용
– fragmentation을 줄이기 위해 locally managed tablespaces를 사용
– 작고, 자주 사용되는 table에 대해서는 CACHE 모드로 설정
88
■ Controlling Space Used by Tables
•
High-Water Mark
–
–
–
–
–
•
Table에서 사용되고 있는 마지막 block를 가리키고 있다
insert 시 마지막 block으로 이동
row delete 시에는 reset되지 않음
Table의 segment header에 보관
Full table scan 시 high-water mark 까지 모든 block을 scan
Finding the High-Water Mark
– High-Water Mark의 위치와 unused block의 수를 찾기 위한 package 제공
– DBMS_SPACE
•
Deallocation of Unused Space
– release 된 공간은 table 내에 다른 segment에 의해 사용
ALTER TABLE summit.employee
DEALLOCATE UNUSED;
89
■ Controlling Space Used by Tables
•
Truncating a Table
–
–
–
–
Table내에 모든 row를 삭제
대응하는 모든 index를 truncate
foreign key에 의해 참조되고 있는 table은 truncate될 수 없음
삭제된 trigger는 fire
TRUNCATE TABLE summit.employee;
•
Dropping a Table
DROP TABLE summit.department
CASCADE CONSTRAINTS;
90
■ Controlling Space Used by Tables
•
Dropping a Column
– Table내에 하나의 column을 삭제
ALTER TABLE employee
DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000;
– column length, 각 row의 데이터를 삭제
•
Using the UNUSED Option
– Mark a column as unused
ALTER TABLE orders
SET UNUSED COLUMN commnets
CASCADE CONSTRAINTS
– Drop unused columns
ALTER TABLE orders
DROP UNUSED COLUMNS CHECKPOINTS 1000;
91
■ Retrieving Table Information
•
Table 정보 얻기
DBA_OBJECTS
OWNER
OBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
CREATED
DBA_SEGMENTS
OWNER
SEGMENT_NAME
TABLESPACE_NAME
HEADER_FILE
HEADER_BLOCK
•
DBA_TABLES
OWNER
TABLE_NAME
PCT_FREE
PCT_USED
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
CACHE
BLOCKS
EMPTY_BLOCKS
CHAIN_CNT
TEMPORARY
DURATION
Extent 정보 얻기
– DBA_EXTENTS
• OWNER, SEGMENT_NAME, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
92
Lesson 12
Managing Indexes
■ Overview
•
Classification of Indexes
– Logical
•
•
•
•
logical classification groups indexes from an application perspective
Single column or concatenated
Unique or nonunique
Function-based
– Physical
•
•
•
•
•
index를 어떻게 보관하느냐 따라 분류
Partitioned or nonpartitioned
B-tree
Normal or reverse key
Bitmap
94
■ Overview
•
B-Tree Index
Root
Branch
Leaf
– Index entry
• Index entry header: column의 수와 locking 정보를 포함
• Key column length-value pairs: key value에 따른 key 내에 column의 크기 정의
• ROWID: key value를 포함하는 row의 id
•
Reverse Key Index
EMPLOYEE table
Index on EMPLOYEE(ID)
KEY
ROWID
ID
----1257
2877
4567
6657
(BLOCK# ROW# FILE#)
-------------------0000000F.0002.0001
0000000F.0006.0001
0000000F.0004.0001
0000000F.0003.0001
ID
----7499
7566
7782
7369
FIRST_NAME
---------ALLEN
JONES
CLARK
SMITH
JOB
----------SALESMAN
MANAGER
MANAGER
CLERK
95
■ Overview
•
Creating Function-Based Indexed
– Query performance 개선
CREATE INDEX summit.item_quantity_to_deliver_idx
ON summit.item (quantity – quantity_shipped);
– Queries using expressions can use the index
SELECT ord_id, item_id
FROM ITEM
WHERE ( quantity – quantity_shipped) > 0;
•
Bitmap Index
– B-tree와 비슷하게 구성
– leaf node는 ROWIDs 대신에 각 key value에 대한 bitmap을 저장
– 구성요소
• bitmap entry header: columns의 수와 lock information
• Key values: 각 key column에 대한 length와 value의 쌍
96
■ Overview
•
Comparing B-Tree and Bitmap Indexes
B-tree
Bitmap
High cardinality columns에 적당
Low cardinality columns에 적당
key에 대한 update가 inexpensive
key에 대한 update가 expensive
OR 절을 사용하는 query는 비효율적 OR절을 사용하는 query에 효율적
OLTP에 사용
Data warehousing에 사용
97
■ Creating Indexes
•
Creating Normal B-Tree Indexes
CREATE INDEX summit.employee_last_name_idx
ON summit.employee(last_name)
PCTFREE 30
STORAGE (INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;
•
Guidelines
–
–
–
–
–
Query와 DML의 balance
Data나 rollback segment와 다른 tablespace에 존재하도록
Uniform extents size를 사용
large index에 대해서는 NOLOGING 모드를 사용
Table과 다르게 PCTFREE를 높게 설정
98
■ Creating Indexes
•
Creating Reverse Key Indexes
CREATE UNIQUE INDEX summit.orders_id_idx
ON summit.orders(id) REVERSE
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE INDX;
•
Creating Bitmap Indexes
CREATE BITMAP INDEX orders_region_id_idx
ON summit.orders(region_id)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;
99
■ Reorganizing Indexes
•
Changing Storage Parameters for Indexes
ALTER INDEX summit.employee.last_name_idx
STORAGE(NEXT 400K
MAXEXTENTS 100);
•
Allocating and Deallocating Index Space
ALTER INDEX summit.orders_region_id_idx
ALLOCATE EXTENT (SIZE 200K
DATAFILE ‘/DISK6/indx01.dbf’;
ALTER INDEX summit.orders_id_idx
DEALLOCATE UNUSED;
100
■ Reorganizing Indexes
•
Rebuilding Indexes
– index를 다른 tablespace로 이동
– 삭제된 entries를 제거함으로써 공간 활용도를 개선
– reverse key index를 normal B-Tree index로 변경 또는 그 반대로 변경
ALTER INDEX summit.orders_region_id_idx REBUILD
TABLESPACE indx02;
•
Coalescing Indexes
ALTER INDEX summit.orders_id_idx COALESCE;
■ Dropping Indexes
•
커다란 data를 load 하기전에 index를 drop 하고 load 후 index 재생성
•
자주 사용되지 않은 indexs를 제거하고, 필요할 때 생성
•
instance failure 시 INVALID 표시된 index를 제거 후 재생성
DROP INDEX summit.department_name_idx;
101
■ Obtaining Index Information
DBA_INDEXES
OWNER
INDEX_NAME
INDEX_TYPE
TABLE_OWNER
TABLE_NAME
UNIQUENESS
TABLESPACE_NAME
LOGGING
STATUS
DBA_ID_COLUMNS
INDEX_OWNER
INDEX_NAME
TABLE_OWNER
TABLE_NAME
COLUMN_NAME
COLUMN_POSITION
COMUMN_LENGTH
SELECT index_name, tablespace_name, index_type,
uniqueness, status
FROM dba_indexes
WHERE owner = ‘SUMMIT’;
102
Lesson 13
Managing Data Integrity
■ Integrity Constraints
•
Types of Constraints
Constraint
•
Description
NOT NULL
column이 null values를 가질 수 없음
UNIQUE
unique로 column이나 column의 조합을 설정
PRIMARY KEY
column이나 column의 조합을 table의 primary key로 설정
FOREIGN KEY
column이나 column의 조합을 table의 foreign key로 설정
CHECK
table의 row가 만족해야 하는 condition을 설정
Constraint States
–
–
–
–
Disabled novalidate
Disabled validate
Enabled novalidate
Enabled validate
104
■ Integrity Constraints
•
Deferred Constraints
– 하나의 transaction이 commit 되었을 때만 constraints를 check
– commit 시 constraints violation이 있다면 모든 transaction이 rollback
•
Defining Constraints Immediate or Deferred
ALTER SESSION
SET CONSTRAINT[S] =
{IMMEDIATE|DEFERRED|DEFAULT);
•
Foreign Key Considerations
Action
적절한 해결방법
Drop Parent Table
Cascade constraints
Truncate parent table
Disable or drop foreign key
Drop Tablespace containing
parent table
CASCADE CONSTRAINTS 를 사용
Avoid locks on child table
while performing DML o
parent table
foreign key에 대한 index를 생성
Perform DML o child table
Ensure tablespace containing parent key index
online
105
■ Implementing Constraints
•
Defining Constraints While Creating Table
CREATE TABLE summit.employee(
id NUMBER(7)
CONSTRAINTS employee_id_pk PRIMARY KEY
DEFERRABLE
USING INDEX
STORAGE(INITIAL 100K NEXT 100K)
TABLESPACE indx,
last_name VARCHAR2(25)
CONSTRAINTS employee_last_name_nn NOT NULL,
dept_id NUMBER(7))
TABLESPACE data;
•
Guidelines
– Primary and unique constraints
• table과 다른 tablespace에 위치
• bulk load가 자주 발생하면 nonunique indexes를 사용
– Self-referencing foreign key
• initial load 후 foreign key를 정의하거나 enable
• constraint를 deferrable constraint로 정의
106
■ Maintaining Constraints
•
Using the EXCEPTIONS Table
– EXCEPTION Table 생성(utlexcpt.sql)
SQL>@?/rdbms/admin/ttlexcpt
– EXCEPTIONS 절을 포함하여 ALTER TABLE 실행
ALTER TABLE summit.employee
ENABLE VALIDATE CONSTRAINT employee_dept_id_fk
EXCEPTIONS INTO system.exceptions
– invalid data를 가진 row 확인
SELECT rowid, id, last_name, dept_id
FROM summit.employee
WHERE ROWID in( SELECT row_id FROM exceptions)
FOR UPDATE;
– data 내에 error를 수정
– constraints를 enable하기 위해 ALTER TABLE 실행
ALTER TABLE summit.employee
ENABLE VALIDATE CONSTRAINT employee_dept_id_fk
EXCEPTIONS INTO system.exceptions
107
■ Obtaining Constraint Information
DBA_CONSTRAINTS
OWNER
CONSTRAINT_NAME
CONSTRAINT_TYPE
TABLE_NAME
SEARCH_CONDITION
R_OWNER
R_CONSTRAINT_NAME
DELETE_RULE
STATUS
DEFERABLE
DEFERRED
VALIDATED
GENERATED
BAD
RELY
LAST_CHANGE
DBA_CONS_COLUMNS
OWNER
CONSTRAINT_NAME
TABLE_NAME
COLUMN_NAME
POSITION
SELECT constraint_name, constraint_type, deferrable,
deferred, validated
FROM dba_constraints
WHERE owner= ‘ SUMMIT’ AND table_name = ‘EMPLOYEE’;
108
Lesson 14
Loading Data
■ Overview
Other Applications
Oracle
Database
SQL*Loader
Export
Import
Oracle
Database
Direct-load insert
•
Loading data
– Direct-Load Insert
– SQL*Loader
– Export and Import Utilities
110
■ Loading Data Using Direct-Load Insert
•
Using Direct-Load Inserts
INSERT /*_APPEND */ INTO scott.emp
NOLOGGING
SELECT * FROM SCOTT.OLD_emp;
EMP table
Used block
Free space after delete
Server
Process
High-water mark
Blocks used by inserted rows
111
■ Loading Data Using Direct-Load Insert
•
Parallel Direct-Load Insert
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLEL(scott.emp, 2) */
INTO scott.emp NOLOGGING
SELECT * FROM scott.old_emp;
EMP table
Used block
Free space after delete
Slave
Process
Slave
Process
High-water mark
Temporary segments
– 각 slave process에 의해 insert된 data를 보관하기 위해 temporary segments
가 할당
– commit을 실행해야만 temporary segment의 extents가 table에 포함
112
■ Loading Data Using Direct-Load Insert
•
Parallel Direct-Load Insert
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLEL(scott.emp, 2) */
INTO scott.emp NOLOGGING
SELECT * FROM scott.old_emp;
EMP table
Used block
Free space after delete
Slave
Process
Slave
Process
High-water mark
Temporary segments
– 각 slave process에 의해 insert된 data를 보관하기 위해 temporary segments
가 할당
– commit을 실행해야만 temporary segment의 extents가 table에 포함
113
■ Loading Data Using SQL*Loader
•
SQL*Loader
– 외부 파일들로부터 table로 데이터를 load
Control file
Data file
Parameter file
(optional)
SQL*Loader
Rejected
Field processing
Discarded
Discard file
(optional)
Accepted
Bad file
Record selection
Selected
Oracle Server
Rejected
Log file
Inserted
Database file
114
■ Loading Data Using SQL*Loader
•
Files Used by SQL*Loader
–
–
–
–
–
–
•
Control file: input format, output tables, optional condition
Data file: control file에 정의된 형태의 data를 포함
Parameter file: command line parameter를 정의
Log file: SQL*Loader에 의해 생성되며, load의 기록을 보관
Bad file: load 동안 reject 된 기록을 작성
Discard file: 조건에 맞이 않는 모든 data를 보관
SQL*Loader 특징
–
–
–
–
–
–
–
하나 이상의 input file을 사용
여러 input record들이 하나의 논리적인 record로 조합
데이터는 disk, tape, named pipe와 같은 media로 부터 load
데이터는 한번의 수행으로 여러 table에 load
table 내에 존재하는 데이터에 추가, 대체하는 option을 제공
SQL function을 input 데이터에 적용
데이터를 data buffer cache를 거치지 않고, 직접 table에 load
115
■ Loading Data Using SQL*Loader
•
Conventional Path Load
–
–
–
–
•
데이터 load 위해 SQL 문을 사용
field specification에 만족하지 않은 record는 reject
select 목록에 만족하지 않은 record는 discard
Redo log 생성은 logging attribute에 따라 결정
Direct Path Load
–
–
–
–
메모리 내에 data의 blocks을 만들고, 이 blocks을 직접 table의 extent에 저장
database가 archivelog mode이면 redo log 생성 안함
database buffer cache를 거치지 않고, 직접 데이터를 load
extent 관리 high-water mark 조정을 위해 SGA를 사용
Conventional Path
Direct Path
데이터 변경 후 COMMIT 실행
data save
Redo log entries 발생
특정 조건에 따라 발생
모든 constraints enforce
primary key, unique, NOT NULL
INSERT trigger fire
INSERT triggers do not fire
clustered table에 load 될 수 있음
clustered table에 load될 수 없음
다른 사용자가 table 변경 가능
변경 불가능
116
■ Loading Data Using SQL*Loader
•
Using SQL*Loader
$sqlldr scott/tiger ＼
> control=u1case6.ctl ＼
> log=u1case6.log direct=true
•
Guidelines
– 사용된 command line option을 지정하기 위해 parameter file을 사용
– data file이 작은 경우 control file에 위치
– loading performance를 향상
• 충분한 공간 할당
• sorting the data on the largest index
• parallel load시 temporary segments의 위치를 다르게 지정
117
Lesson 15
Reorganizing Data
■ Overview
•
Moving Data Using Export and Import
Export
OS file
Data files
Import
–
–
–
–
–
–
–
Administrator가 실행
database 들 사이에 data 이동
다른 tablespaces 로 data 이동
효율적인 storage와 performance를 위해 data를 reorganize
어떤 user 소유의 data를 다른 user로 이동
다른 OS 플랫폼이나 Oracle 버전에 다른 database를 migrate
logical backup을 수행
119
■ Overview
•
Export Modes
– Table
• table, indexes, triggers, constraints, grants 모두 export 가능
– User
• user가 소유하고 있는 모든 objects를 export 가능
• 다른 users가 소유하고 있는 indexes와 triggers은 제외
– Database
• database 내에 모든 objects를 export 가능
• SYS 소유의 objects는 제외
•
Conventional and Direct Path Export
Dump files
Direct
Conventional
Evaluating
buffer
Private buffer
or buffer cache
Export
SQL command
processing
Buffer cache
management
Read database
block
Database
120
■ Overview
•
Using Export
$exp scott/tiger tables=(dept, emp) ＼
> file=emp.dmp log=exp.log ＼
> compress=n direct=y
•
Using Import
$imp scott/tiger tables=(dept, emp) ＼
> file=emp.dmp log=imp.log ignore=y
121
■ Transporting a Tablespace
•
Steps for Transporting a Tablespace
1.
2.
3.
4.
5.
6.
•
Tablespace를 read-only로 만듬
Export metadata from source
data files을 대상 system으로 복사
대상 system으로 export file을 전송
metadata를 대상 system 내에 import
필요하다면 tablespace를 read-write로 변경
Transportable Tablespace Uses
–
–
–
모든 tablespace data를 이동
media recovery를 지원
source and target database
•
•
•
•
같은 OS 이어야 함
Oracle 8i, release 8.1 또는 그 이상이어야 함
같은 block size이어야 함
같은 character set이어야 함
122
■ Transporting a Tablespace
•
Example
SYSTEM 1
/u/d1
/u/d2
SYSTEM 2
/disk1
/disk2
s980501.dmp
SALES_TS
SALES_TS
/u/d1/sales1.dbf
/disk1/sales1.dbf
/u/d2/sales2.dbf
/disk2/sales2.dbf
read only
–
SYSTEM 1의 tablespace를 read-only로
SQL>ALTER TABLESPACE sales_ts READ ONLY;
–
metadata tablespace export
exp FILE=x980501.dmp TRANSPORT TABLESPACE=Y ＼
TABLESPACE=sales_ts TRIGGER=N CONSTRAINTS=N
123
■ Transporting a Tablespace
•
Example
SYSTEM 1
/u/d1
/u/d2
SYSTEM 2
/disk1
/disk2
s980501.dmp
SALES_TS
SALES_TS
/u/d1/sales1.dbf
/disk1/sales1.dbf
/u/d2/sales2.dbf
/disk2/sales2.dbf
read only
–
–
–
tablespace data file을 system 2로 복사
s980501.dmp export file을 system 2로 복사
tablespace metadata import
imp FILE=x980501.dmp TRANSPORT TABLESPACE=Y ＼
DATAFILES=(/disk1/sales01.dbf, /disk2/sales02.dbf)
–
tablespace read-write로 변환
SQL>ALTER TABLESPACE sales_ts READ WRITE;
124
Lesson 16
Managing Password Security and
Resources
■ Overview
•
Profiles
–
–
–
–
–
–
–
–
–
–
Password aging and expiration
Password history
Password complexity verification
Account locking
CPU time
I/O operations
Idle time
Connect time
Memory space
Concurrent sessions
126
■ Administering Passwords
•
password management
–
–
–
–
•
Enabling Password Management
–
–
–
–
•
Account locking
Password aging and expiration
Password history
Password complexity verification
profiles을 사용함으로써 password management를 설정
users에게 profile을 할당
CREATE USER 또는 ALTER USER를 사용하여 account를 lock, unlock,
expire
password limit은 항상 강요
Controlling Account Lock and Password
–
–
–
–
사용자가 password를 분실했을 때, password를 reset
사용자의 account를 unlock
account를 lock
수동으로 password를 expire
ALTER USER hanne
IDENTIFIED BY rue
ACCOUNT UNLOCK;
127
■ Administering Passwords
•
Creating a Profile: Password settings
CREATE PROFILE grace_5 LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 30
PASSWORD_VERIFY_FUNCTION verify_function
PASSWORD_GRACE_TIME 5;
•
Altering a Profile
ALTER PROFILE default
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10;
•
Dropping a Profile
DROP PROFILE developer_prof;
DROP PROFILE developer_prof CASCADE;
128
■ Controlling Usage of Resources
•
Managing Resources with Profiles
–
–
–
CREATE PROFILE 명령어를 이용하여 profile을 생성
CREATE 또는 ALTER USER 명령어를 이용하여 user에게 profile 할당
resource limit을 enable
•
•
•
RESOURCE_LIMIT 초기화 파라미터
ALTER SYSTEM 명령어
Setting Resource Limits at Session Level
Resource
Description
CPU_PER_SESSION
총 CPU 시간(100초 단위)
SESSIONS_PER_USER
각 user당 concurrent session 수
CONNECT_TIME
연결 시간(분)
IDLE_TIME
inactive time(분)
LOGICAL_READS_PER
_SESSION
PRIVATE_SGA
data block 수
SGA 내에 private space(byte)
129
■ Controlling Usage of Resources
•
Setting Resource Limits at Call Level
Resource
CPU_PER_CALL
Description
call 당 CPU 시간(100초)
LOGICAL_READS_PER_CALL call 당 읽을 수 있는 data blocks의 수
•
Creating a Profile: Resource Limit
CREATE PROFILE developer_prof LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
IDLE_TIME 60
CONNECT_TIME 480;
•
Enabling Resource Limits
–
–
RESOURCE_LIMIT 초기화 파라미터를 TRUE를 설정
ALTER SYSTEM 명령어를 이용하여 resource limit enable
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
130
■ Viewing Password and Resource Limits Information
•
DBA_USERS
–
–
–
–
–
•
profile
username
account_status
lock_date
expire_date
DBA_PROFILES
–
–
–
–
profile
resource_name
resource_type(PASSWORD, KERNEL)
limit
SELECT username, password, account_status, lock_date, expire_date
FROM dba_users;
SELECT * FROM dba_profiles WHERE resource_type=‘PASSWORD’;
SELECT p.profile, p.resource_name, p.limit
FROM dba_users u, dba_profiles p
WHERE p.profile=u.profile AND username=‘SCOTT’ AND
p.resource_type=‘KERNERL’;
131
Lesson 17
Managing Users
■ Overview
•
Users and Security
Account
locking
Default
tablepsace
Authentication
mechanism
Temporary
tablespace
Security
Domain
Role
privileges
Direct
privileges
•
Tablespace
quotas
Resource
limits
Database Schema
–
특정 user와 관련된 table, view, cluster, procedure, package와 같은 object
들의 집합
133
■ Creating New Database Users
•
Checklist for Creating Users
–
–
–
–
–
–
•
username과 authentication mechanism을 선택
user가 object들을 저장하기 위해 필요한 tablespace를 확인
각 tablespace를 위한 quotas를 결정
default tablespace와 temporary tablespace를 할당
user 생성
user에게 privileges와 roles를 grant
Creating a New User: Database Authentication
CREATE USER peter
IDENTIFIED BY mylstson
DEFAULT TABLESPACE data
TEMPORARY TABLESPACE temp
QUOTA 15m ON data
PASSWORD EXPIRE;
134
■ Creating New Database Users
•
Creating a New User: Operating System Authentication
–
–
OS_AUTHENT_PREFIX 파라미터를 사용
Example: os User = user15
OS_AUTHENT_PREFIX
•
Database User
Remote_Login Possible
OS_
OS_USER15
No
empty string “”
USER15
No
OPS$(default)
OPS$USER15
Yes
Guidelines
–
–
–
–
–
Choose a standard password initially
EXPIRE 키워드를 사용
항상 temporary tablespace를 사용
QUOTA UNLIMITED를 조심하여 사용
user에게 Oracle Server 연결 방법과 password 변경 방법을 교육
135
■ Altering and Dropping Database Users
•
Changing User Quota on Tablespace
ALTER USER peter
QUOTA 0 ON data;
•
Dropping Users
DROP USER peter;
DROP USER peter CASCADE;
136
Lesson 18
Managing Privileges
■ Overview
•
Managing Privileges
–
–
System: user가 database 내에서 특정 action을 수행할 수 있도록
Object: user가 특정 object에 접근과 조작할 수 있도록
■ System Privileges
•
126가지의 system privileges
•
ANY 키워드는 user가 모든 schema 내에 privileges를 가진다는 의미
•
GRANT 명령어는 user 또는 user의 그룹에 privileges를 추가
•
REVOKE 명령어는 privileges를 제거
Category
Examples
INDEX
CRAETE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX
TABLE
CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY
TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
SESSION
CREATE SESSION, ALTER SESSION, RESTRICTED SESSION
TABLESPACE
CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE,
UNLIMITED TABLESPACE
138
■ Granting System Privileges
•
Granting System Privileges
GRANT CREATE SESSION, CREATE TABLE TO manager;
GRANT CREATE SESSION TO scott WITH ADMIN OPTION;
•
Guidelines
–
–
PUBLIC은 모든 user에게 system privileges를 부여
WITH ADMIN OPTION으로 user가 system privileges를 부여 받게 됨
139
■ Password File Authentication
•
SYSDBA and SYSOPER Privileges
Category
Examples
STARTUP
SHUTDOWN
ALTER DATABASE OPEN | MOUNT
SYSOPER
ALTER DATABASE BACKUP CONTROLFILE
ALTER TABLESPACE BEGIN/END BACKUP
RECOVER DATABASE
ALTER DATABASE ARCHIVELOG
RESTRICTED SESSION
SYSOPER privileges WITH ADMIN OPTION
SYSDBA
CREATE DATABASE
RECOVER DATABASE UNTIL
140
■ Password File Authentication
•
Password File Authentication
–
–
–
–
–
•
생성된 password file을 check
password file이 없다면, ORAPWD를 이용하여 password file 생성
파라미터 file 내에 REMOTE_LOGIN_PASSWORD_FILE이 EXCLUSIVE로 설정
되어 있는지 check
user에게 SYSOPER, SYSDBA 권한을 부여
password file member를 확인하기 위해 V$PWFILE_USERS view를 확인
Displaying System Privileges
SELECT * FROM DBA_SYS_PRIVS;
•
Revoking System Privileges
REVOKE CREATE TABLE FROM karen;
141
■ Object Privileges
•
Object Privileges
Object Privilege
Table
ALTER

DELETE

View
Sequence



EXECUTE
•
Procedure
INDEX

INSERT

REFERENCES

SELECT


UPDATE




Granting Object Privileges
GRANT EXECUTE ON dbms_pipe TO public;
GRANT UPDATE(first_name, salary) ON employee
TO karen WITH GRANT OPTION;
142
■ Object Privileges
•
Displaying Object Privileges
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM DBA_COL_PRIVS;
•
Revoking Object Privileges
REVOKE execute ON dbms_pipe FROM scott;
143
■ Auditing Guidelines
•
Define your purpose of auditing
–
–
•
audit하고자 하는 것을 정의
–
–
–
•
users, statements, objects
By session
Successful or unsuccessful
audit trail 관리
–
–
•
의심스러운 database activity
historical information 수집
audit trail의 growth를 모니터
권한이 없는 접근으로부터 audit trail 보호
Auditing Categories
–
Auditing privileged operations
•
–
Database auditing
•
•
•
–
Instance startup, shutdown, SYSDBA connections
특정 database activities에 대한 data를 모니터하거나 수집하기 위해 사용
column values를 기록할 수 없다.
enabled by DBA
Value-based or application auditing
•
•
•
code를 통해 구현
column values를 기록
table에 대한 변경 사항을 track 하기 위해 사용
144
■ Using Database Auditing
•
Database Auditing
DBA
Enable database
auditing
Execute command
parameter
file
Specify
audit options
Review
audit
Information
Audit option
Database
User
Server
Process
Generate
audit trail
OS audit
trail
Audit trail
145
■ Using Database Auditing
•
Enabling Auditing Options
–
Statement auditing
AUDIT user;
–
Privilege auditing
AUDIT select any table BY summit BY ACCESS;
–
Schema object auditing
AUDIT LOCK ON summit.employee
BY ACCESS WHENEVER SUCCESSFUL;
•
Viewing Auditing Options
Data Dictionary view
Description
ALL_DEF_AUDIT_OPTS
Default audit options
DBA_STMT_AUDIT_OPTS
Statement auditing options
DBA_PRIV_AUDIT_OPTS
Privileges auditing options
DBA_OBJ_AUDIT_OPTS
Schema object auditing options
146
■ Viewing Auditing Results
•
Viewing Auditing Results
Audit Trail View
Description
DBA_AUDIT_TRAIL
모든 audit trail entries
DBA_AUDIT_EXISTS
AUDIT EXISTS/NOT EXISTS를 기록
DBA_AUDIT_OBJECT
연결된 schema objects를 기록
DBA_AUDIT_SESSION
모든 connect and disconnect entries
DBA_AUDIT_STATEMENT
Statement auditing records
147
Lesson 19
Managing Roles
■ Overview
•
What is a Role?
–
•
Role Characteristics
–
–
–
–
–
•
users or other roles에 할당된 related privileges의 집합
system privileges grant or revoke 명령어를 이용하여 role을 grant or revoke
system and object privileges로 구성될 수 있음
enable and disable
각 role의 이름은 unique
role에 대한 설명이 data dictionary에 저장
Role의 이점
–
–
–
–
–
–
privileges 관리를 간소화
role이 수정되면 role이 할당된 모든 users의 privileges가 수정(Dynamic
privilege management)
Selective availability of privileges
OS command or utilities를 이용하여 users에게 role을 할당
No cascading revoke
Improve performance
149
■ Creating and Modifying Roles
•
Creating Roles
CREATE ROLE sales_clerk;
CREATE ROLE hr_clerk IDENTIFIED BY bonus;
•
Using Predefined Roles
Role Name
Description
CONNECT, RESOURCE
backward compatibility를 위해 제공
DBA
WITH ADMIN OPTION 모든 system privileges
EXP_FULL_DATABASE
database export을 위한 privileges
IMP_FULL_DATABASE
database import를 위한 privileges
DELETE_CATALOG_ROLE
data dictionary tables에 대한 delete privileges
EXECUTE_CATALOG_ROLE
data dictionary tables에 대한 execute privileges
SELECT_CATALOG_ROLE
data dictionary tables에 대한 select privileges
150
■ Creating and Modifying Roles
•
Modifying Roles
ALTER ROLE sales_clerk
IDENTIFIED BY commission;
ALTER ROLE hr_clerk
IDENTIFIED EXTERNALLY;
ALTER ROLE hr_manager
NOT IDENTIFIED;
■ Assigning Roles
GRANT sales_clerk TO scott;
GRANT hr_manager TO scott
WITH ADMIN OPTION;
151
■ Controlling Availability of Roles
•
Establishing Default Roles
–
–
–
user는 많은 assigned roles을 가질 수 있다.
user가 log on 시 자동적으로 enabled된 roles를 default role이라 함
user에게 할당된 모든 roles은 user가 log on 시 enable
ALTER USER scott
DEFAULT ROLE hr_clerk, sales_clerk;
ALTER USER scott DEFAULT ROLE ALL;
ALTER USER scott DEFAULT ROLE NONE;
•
Enabling and Disabling Roles
–
–
–
–
–
user로 부터 role을 임시적으로 revoke하기 위해 role을 disable
role을 임시적으로 grant하기 위해 enable
SET ROLE command를 이용하여 enable and disable
Default role은 user가 log on 시 enable
password는 role을 enable하기 위해 요구될 수 있다.
152
■ Controlling Availability of Roles
•
Enabling and Disabling Roles: Examples
SET ROLE hr_clerk;
SET ROLE sales_clerk IDENTIFIED BY commission;
SET ROLE NONE;
•
Removing Roles from Users
REMOVE sales_clerk FROM scott;
REMOVE hr_manager FROM PUBLIC;
•
Removing Roles
DROP ROLE hr_manager;
153
■ Displaying Role Information
•
Displaying Role Information
Role View
Description
DBA_ROLES
database에 존재하는 모든 roles
DBA_ROLE_PRIVS
users and roles에 부여된 roles
ROLE_ROLE_PRIVS
roles에 부여된 roles
DBA_SYS_PRIVS
users and roles에 부여된 system privileges
ROLE_SYS_PRIVS
roles에 부여된 system privileges
ROLE_TAB_PRIVS
roles에 부여된 table privileges
SESSION_ROLES
concurrently enabled roles
SELECT role, password_required FROM dba_roles;
154
Lesson 20
Using National Language Support
■ Overview
•
NLS(National Language Support) Features
–
–
–
database utilities, error messages, sort order, date, time, monetary,
numeric, and calendar convention을 자동적으로 native language로 변환할
수 있도록 함
language-dependent operations은 client and server 측 환경 변수와 파라미
터에 의해 제어
client와 server가 다른 character set을 사용할 때, Oracle server가 자동적으로
변환
156
■ Choosing a Database and a National Character Set
•
different classes of character encoding schemes
–
single-byte character sets
•
•
–
–
–
•
7-bit
8-bit
Varying-width multibyte character set
Fixed-width multibyte character set
Unicode(UTF8, AL24UTFFSS)
Character Sets and National Character Sets of a Database
Database Character Sets
National Character Sets
creation 시 정의
creation 시 정의
변경 안됨
변경 안됨
CHAR, VARCHAR2, CLOB, LONG data NCHAR, NVARCHAR2, NCLOB data
type store
type store
fixed-width and varying-width
varying-width character sets
multibyte character sets
•
Guidelines
–
–
–
가장 연관된 database character set과 national character set을 선택
String operations은 fixed-width character set을 선택하면 빨라짐
공간 효율을 위해 variable-width character set을 사용
157
■ Specifying Language-Dependent Behavior
•
NLS parameter를 지정하기 위한 방법
–
–
–
•
서버 측 initialization parameter
client에 대한 environment variables
ALTER SESSION command
Specifying Language-Dependent Behavior for the Server
–
NLS_LANGUAGE
•
•
•
•
–
messages에 대한 language
Day and month names
A.D, B.C, A.M, P.M에 대한 symbol
default sorting mechanism
NLS_TERRITORY
•
•
•
•
•
•
default date format
decimal character and group separator
local currency symbol
ISO currency symbol
ISO week number calculation
week start day
158
■ Specifying Language-Dependent Behavior
•
Specifying Language-Dependent Behavior for the Session
–
Environment variable
NLS_LANG=<language>_<territory>.<character>
–
다른 환경 변수
•
•
•
•
•
•
•
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_SORT
NLS_NUMERIC_CHARACTERS
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_CALENDAR
ALTER SESSION SET
NLS_DATE_FORMAT=‘DD.MM.YYYY’;
DBMS_SESSION.SET_NLS
(‘NLS_DATE_FORMAT’, ’ ’ ’DD.MM.YYYY’ ’ ’);
159
■ NLS Parameters and SQL Functions
•
Sorting
–
–
–
linguistic sort 제공
NLS_SORT 파리미터는 sort의 유형을 지정
NLSSORT function은 linguistic comparison을 나타냄
ALTER SESSION SET NLS_SORT=GERMAN;
SELECT letter FROM letters ORDER BY letter;
•
Using NLS Parameters in SQL Functions
SELECT TO_CHAR(hiredate, ‘DD.MON.YYYY’,
‘NLS_DATE_LANGUAGE=GERMAN’) FROM emp;
SELECT ename, TO_CHAR(sal, ‘9G999D99’,
‘NLS_NUMERIC_CHARACTERS=‘ ‘,.’ ’’)
FROM emp;
160
■ Linguistic Index Support
•
Linguistic indexing
•
High performance with local sorting
CREATE INDEX nls_ename ON
emp (NLSSORT(ename, ‘NLS_SORT = GERMAN’));
•
NLS_COMP parameter for linguistic comparisons
■ Importing and Loading Data Using NLS
•
Data는 import 동안 NLS_LANG에서 database character set을 변환
•
Loader
–
Conventional: Data는 NLS_LANG에 지정된 session character set으로 변환
–
DIRECT: Data는 직접 database character set으로 변환
161
■ Obtaining Information About NLS Settings
•
Obtaining Information About Character Sets
–
NLS_DATABASE_PARAMETERS
•
•
PARAMETER(NLS_CHARACTERSET, NLS_NCHAR_CHARACTERSET)
VALUE
SELECT parameter, value FROM nls_database_parameters
WHERE parameter LIKE ‘%CHARACTERSET%’;
•
Obtaining Information About NLS Settings
–
NLS_INSTANCE_PARAMETERS
•
•
–
PARAEMETER
VALUE
NLS_SESSION_PARAMETERS
•
•
PARAMETER
VALUE
SELECT * FROM nls_instance_parameters;
SELECT * FROM v$nls_valid_values
WHERE parameter=‘LANGUAGE’;
SELECT * FROM v$nls_parameters;
162