Transcript 초보자를 위한 오라클 10g

초보자를 위한 오라클 10g
0
목차
Chapter1.
Chapter2.
Chapter3.
Chapter4.
Chapter5.
Chapter6.
Chapter7.
Chapter8.
Chapter9.
Chapter10.
Chapter11.
Chapter12.
Chapter13.
Chapter14.
Chapter15.
데이터베이스
오라클 설치
오라클 메모리
오라클 프로세스
오라클 엑세스 및 시스템 뷰
오라클 필수 파일
오라클 시작과 종료
리두 로그 파일 및 아카이브 로그 파일
오라클 스토리지
테이블스페이스
오라클 오브젝트
언두 데이터
제약 조건
유저 및 권한
플래쉬백 및 데이터 펌프
1
Chapter1. 데이터베이스
2
1. DBMS의 개요
프로그램
1. 데이터베이스
……
프로그램
프로그램
• 중복성 통제
DBMS
• 메타 데이터 관리
• 사용자 중심의 데이터 처리
데이터베이스
• 데이터 일관성 유지
• 데이터 정합성 보장
• 백업 및 복구 기능
• 질의 처리
• 보안 기능
3
2. DBMS 언어
1. 데이터베이스
• 데이터 정의어
Create, Alter
• 데이터 제어어
Grant, Revoke
데이터베이스
• 데이터 조작어
Insert, Update 및 Delete
• 데이터 검색어
쿼리(Query)
4
3. 오라클 종류
1. 데이터베이스
• 오라클 엔터프라이즈 에디
션
• 오라클 퍼스널 에디션
일반 기업용 버전
개발용 버전
• 오라클 스탠다드 에디션
• 오라클 라이트 에디션
추가 기능이 없음
모바일 데이터베이스 구축
5
Chapter2. 오라클 설치
6
1. 오라클 설치를 위한 사전 절차
2. 오라클 설치
시스템 요구 사항
운영 체제 요구 사항
운영 체제 커널 파라메터
필요한 디렉토리 생성
운영 체제 유저/그룹 생성
7
2. 오라클 설치 작업 절차
2. 오라클 설치
X 윈도우 설정
인스톨러 실행
인스톨러 설치
기본 정보 설정
설치 타입 선정
사전 필요 작업 확인
설치
설치 목록 선택 설정
링크 및 기타 설정
설치 종료
8
3. 오라클 설치 절차
2. 오라클 설치
설치 초기 화면  Deinstall Product 선택
9
3. 오라클 설치 절차
2. 오라클 설치
설치 목록 확인  Close 선택
10
3. 오라클 설치 절차
2. 오라클 설치
오라클 인스톨러 정보 기입  Next 선택
11
3. 오라클 설치 절차
2. 오라클 설치
orainstRoot.sh 수행  Continue 선택
12
3. 오라클 설치 절차
2. 오라클 설치
설치 파일이 존재하는 Source 및 오라클을 설치할 파일 시스템 지정  Next 선택
13
3. 오라클 설치 절차
2. 오라클 설치
인스톨 타입 설정  Next 선택
14
3. 오라클 설치 절차
2. 오라클 설치
인스톨 사전 작업 확인  Next 선택
15
3. 오라클 설치 절차
2. 오라클 설치
인스톨 사전 작업 내용 확인  Next 선택
16
3. 오라클 설치 절차
2. 오라클 설치
설치할 Product Components 선택  Next 선택
17
3. 오라클 설치 절차
2. 오라클 설치
운영 체제 그룹 설정  Next 선택
18
3. 오라클 설치 절차
2. 오라클 설치
데이터베이스 생성 설정  Next 선택
19
3. 오라클 설치 절차
2. 오라클 설치
설치 정보 확인  Install 선택
20
3. 오라클 설치 절차
2. 오라클 설치
인스톨 시작(파일 복사)
21
3. 오라클 설치 절차
2. 오라클 설치
인스톨 시작(링크 단계)
22
3. 오라클 설치 절차
2. 오라클 설치
인스톨 시작(환경 설정)
23
3. 오라클 설치 절차
2. 오라클 설치
Root.sh 수행  OK 선택
24
3. 오라클 설치 절차
2. 오라클 설치
오라클 넷 설정  Next 선택
25
3. 오라클 설치 절차
2. 오라클 설치
인스톨 종료  Exit 선택
26
4. 데이터베이스 구조
2. 오라클 설치
오라클 데이터베이스
데이터 파일
기타 파일
컨트롤 파일
리두로그 파일
데이터베이스 필수 요소
27
5. 수동 데이터베이스 생성
2. 오라클 설치
> cat /oracle/app/oracle/product/10.1/dbs/initORATEST.ora
파라메터 파일 생성
데이터베이스 생성
필요 스크립트 수행
데이터베이스 생성 완료
compatible='10.1.0.2'
# 데이터베이스 버전
db_name=ORATEST
# 데이터베이스 이름
instance_name=ORATEST
# 데이터베이스 인스턴스 이름
control_files='/data1/control01.ctl' # 데이터베이스 컨트롤 파일 이름
db_block_size=8192
# 데이터베이스 블록 크기 지정
db_cache_size = 80M
# 버퍼 캐쉬 크기
large_pool_size = 3M
# Large Pool 크기
log_buffer = 1048576
# 로그 버퍼 크기
shared_pool_size = 50M
# 공유 풀(Shard Pool) 크기
sort_area_size = 1048576
# 메모리 정렬 영역 크기
undo_management=AUTO
# 언두 세그먼트 관리 방식
undo_tablespace=UNDOTBS1
# 언두 테이블 스페이스 이름
28
5. 수동 데이터베이스 생성
2. 오라클 설치
SQL> CREATE DATABASE ORATEST
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/data1/redo01a.log') SIZE 5M,
파라메터 파일 생성
GROUP 3 ('/data3/redo03a.log') SIZE 5M7
MAXLOGFILES 50
MAXLOGMEMBERS 5
데이터베이스 생성
MAXDATAFILES 1000
MAXINSTANCES 1
CHARACTER SET KO16KSC5601
NATIONAL CHARACTER SET AL16UTF16
필요 스크립트 수행
DATAFILE '/data1/system_01.dbf' SIZE 200M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/data2/sysaux_01.dbf' SIZE 325M REUSE
데이터베이스 생성 완료
DEFAULT TABLESPACE tbs_1 datafile '/data1/tbs_01.dbf' size 10M
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/data3/temp_01.dbf' SIZE 200M REUSE
노마운트 단계에서 수행
UNDO TABLESPACE undotbs1
DATAFILE '/data2/undotbs1_01.dbf' SIZE 100M REUSE
AUTOEXTEND ON MAXSIZE UNLIMITED;
29
5. 수동 데이터베이스 생성
2. 오라클 설치
파라메터 파일 생성
데이터 딕셔너리 뷰 생성
데이터베이스 생성
> sqlplus '/as sysdba'
필요 스크립트 수행
SQL> @$ORALCE_HOME/rdbms/admin/catalog.sql
SQL> @$ORALCE_HOME/rdbms/admin/catproc.sql
데이터베이스 생성 완료
패키지 및 프로시져 생성
30
5. 자동 데이터베이스 생성
2. 오라클 설치
DBCA 수행
31
5. 자동 데이터베이스 생성
2. 오라클 설치
DBCA 시작 화면  다음 선택
32
5. 자동 데이터베이스 생성
2. 오라클 설치
데이터베이스 생성 선택  다음 선택
33
5. 자동 데이터베이스 생성
2. 오라클 설치
데이터베이스 생성 템플리트 선택  다음 선택
34
5. 자동 데이터베이스 생성
2. 오라클 설치
환경 설정  다음 선택
35
5. 자동 데이터베이스 생성
2. 오라클 설치
오라클 엔터프라이즈 매니저 설정  다음 선택
36
5. 자동 데이터베이스 생성
2. 오라클 설치
암호지정  다음 선택
37
5. 자동 데이터베이스 생성
2. 오라클 설치
저장 방식 설정  다음 선택
38
5. 자동 데이터베이스 생성
2. 오라클 설치
데이터베이스 파일 위치 설정  다음 선택
39
5. 자동 데이터베이스 생성
2. 오라클 설치
플래시백 및 아카이브 설정  다음 선택
40
5. 자동 데이터베이스 생성
2. 오라클 설치
샘플 스키마 설정  다음 선택
41
5. 자동 데이터베이스 생성
2. 오라클 설치
데이터베이스 환경 설정  다음 선택
42
5. 자동 데이터베이스 생성
2. 오라클 설치
데이터베이스 환경 확인 및 변경  다음 선택
43
5. 자동 데이터베이스 생성
2. 오라클 설치
데이터베이스 생성 선택  완료 선택
44
5. 자동 데이터베이스 생성
2. 오라클 설치
데이터베이스 생성
45
5. 자동 데이터베이스 생성
2. 오라클 설치
데이터베이스 생성 완료
46
6. 오라클 넷
2. 오라클 설치
오라클 넷
데이터베이스
• 호스트 이름 방식
일반 유저
호스트 이름 도는 IP
• 디렉토리 이름 지정
디렉토리 서버 이용
• 로컬 이름 방식
Tnsnames.ora
• Oracle Names
오라클 자체 디렉토리
서버 이용
47
6. 오라클 넷
2. 오라클 설치
Ldap.ora
Names.ora
오라클 넷
tnsnames.ora
listener.ora
48
sqlnet.ora
6. 오라클 넷
로컬 이름 지정 방식
2. 오라클 설치
LISTENER=
(DESCRIPTION=
Listener.ora
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))
)
Listener 기동
)
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC=
Tnsnames.ora
(GLOBAL_DBNAME=sales.us.acme.com)
(ORACLE_HOME=/oracle9i)
(SID_NAME=sales)
접속
)
)
LOG_DIRECTORY_listener=/oracle/network/admin/log
LOG_FILE_listener=list.log
49
6. 오라클 넷
로컬 이름 지정 방식
2. 오라클 설치
> lsnrctl start 리스너명
> lsnrctl status 리스너명
Listener.ora
Connecting to
DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the 리스너명
Alias
Listener 기동
Tnsnames.ora
LISTENER
Start Date
23-SEP-2005 05:15:30
Uptime
84 days 10 hr. 39 min. 38 sec
Trace Level
OFF
Security
OFF
Listener Parameter File /oracle/product/10.1/network/admin/listener.ora
Listener Log File
접속
/oracle/product/10.1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TEST)(PORT=1521)
))
> lsnrctl stop 리스너명
50
6. 오라클 넷
2. 오라클 설치
로컬 이름 지정 방식
Listener.ora
Listener 기동
ORA8 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=203.235.207.233)(PORT=1521))
Tnsnames.ora
)
(CONNECT_DATA =
(SERVER = DEDICATED)
접속
(SERVICE_NAME = OCBSTEST)
)
)
51
6. 오라클 넷
2. 오라클 설치
로컬 이름 지정 방식
Listener.ora
Listener 기동
Tnsnames.ora
접속
> sqlplus scott/tiger@ora8
52
Chapter3. 오라클 메모리
53
1. 오라클 메모리 종류
3. 오라클 메모리
SGA
서버 프로세스
PGA
서버 프로세스
PGA
서버 프로세스
PGA
54
2. PGA
3. 오라클 메모리
PGA
정렬 공간
(Sort Area)
세션 정보
(Session
Information)
커서 상태 정보
(Cursor State)
서버 프로세스
55
변수 저장 공간
(Stack Space)
3. SGA
3. 오라클 메모리
SGA
공유 풀(Shard Pool)
데이터 버퍼 캐쉬
(Data Buffer Cache)
리두 로그 버퍼
(Redo Log Buffer)
라이브러리 캐쉬
(Library Cache)
데이터 딕셔너리 캐쉬
(Data Dictionary
Cache)
자바 풀(Java Pool)
대형 풀(Large Pool)
V$SGA
V$PARAMETER
V$SPPARAMETER
V$SGA_DYNAMIC_COMPONENTS
show parameter 명령어
56
4. 공유 풀
3. 오라클 메모리
공유 풀 (Shard Pool)
고정 영역
(Permanent Area)
동적 영역
(Dynamic Area)
session=100
process=150
…
라이브러리 캐쉬
데이터 딕셔너리 캐쉬
57
5. 공유 풀 개념
3. 오라클 메모리
라이브러리 캐쉬
SELECT * FROM 사원
공유 풀
데이터 딕셔너리 캐쉬
SELECT * FROM 사원
사원 테이블 정보
58
6. 데이터 버퍼 캐쉬
3. 오라클 메모리
SGA
공유 풀
데이터 버퍼 캐쉬
리두 로그 버퍼
서버 프로세스
DBWR
디스크로부터 데이터를 읽음
디스크로 데이터 저장
59
7. 데이터 버퍼 캐쉬 응용
3. 오라클 메모리
SGA
공유 풀
데이터 버퍼 캐쉬
재활용
기본
고정
60
리두 로그 버퍼
8. 리두 로그 버퍼
3. 오라클 메모리
INSERT INTO TAB1 (ID,이름)
VALUES(‘A123’,’황오현’)
리두 로그 버퍼
DBA,
INSERT,TAB1,
DELETE,TAB1
서버
프로세스
디스크
로그 정보
LGWR
61
9. 대형 풀
3. 오라클 메모리
SGA
UGA
공유 풀
데이터 버퍼 캐쉬
병렬 프로세싱
대형 풀
RMAN
I/O 서버 프로세스
62
리두 로그 버퍼
10. 자바 풀
3. 오라클 메모리
SGA
공유 풀
데이터 버퍼 캐쉬
리두 로그 버퍼
자바 풀
자바 명령 파싱
63
11. 공유 메모리 자동 관리
3. 오라클 메모리
공유 메모리 자동 관리 설정
STATISTICS_LEVEL 파라메터
SGA_TARGET=1024M
SGA
TYPICAL 또는 ALL로 설정
데이터 버퍼 캐시
SGA_TARGET 파라메터
0이 아닌 값으로 설정
MMAN
공유 풀
효과
* 업무 부하 파악
* 자동 크기 조정
대형 풀
메모리 관리 요소 감소
자바 풀
메모리 사용 효율 증가
메모리 부족으로 인한
에러 감소
64
Chapter4. 오라클 프로세스
65
1. 오라클 프로세스
4. 오라클 프로세스
필수 백그라운드 프로세스
PMON
SMON
DBWR
ARCH
Pnnn
Snnn
LGWR
CKPT
MMNL
SGA
공유 풀
(Shard Pool)
라이브러리 캐쉬
데이터 버퍼 리두 로그
캐쉬
버퍼
DMnn
MMAN
데이터 딕셔너리
캐쉬
MMON
Dnnn
자바 풀
대형 풀
RVWR
기타
선택 백그라운드 프로세스
오라클 10g 추가 백그라운드 프로세스
서버 프로세스
서버 프로세스
서버 프로세스
유저 프로세스
유저 프로세스
유저 프로세스
66
2. 데이터베이스 기록자 백그라운드 프로세스(DBWR)
SGA
공유 풀
데이터 버퍼 캐쉬
리두 로그 버퍼
AB
*
*
*
*
*
*
*
체크포인트 발생
더티 버퍼 임계치 도달
프리 버퍼 검색 임계치 도달
테이블스페이스 변경 시
테이블 변경 시(Read Only)
테이블 스페이스 백업 상태
Time Out 발생 시
UPDATE TEST
SET A=‘B’
WHERE 이름 = ‘이창구’;
지연 쓰기(Deferred Write)
A
TEST 테이블
DBWR
변경된 데이터 저장
67
빠른 커밋(Faster Commit)
4. 오라클 프로세스
3. 로그 기록자 백그라운드 프로세스(LGWR)
4. 오라클 프로세스
SGA
공유 풀
데이터 버퍼 캐쉬
리두 로그 버퍼
AB
AB
UPDATE TEST
SET A=‘B’
WHERE 이름 = ‘이창구’;
LGWR
DBWR
A
* 커밋을 수행할 경우
* DBWR이 변경된 데이터 블록을
저장하기 전
* 리두 로그 버퍼의 1/3 이상 사용
* 1MB이상의 리두 로그 생성 시
* 3초 마다(Time Out)
A B
TEST 테이블
리두 로그 파일
68
4. 프로세스 모니터 백그라운드 프로세스(PMON)
SGA
공유 풀
데이터 버퍼 캐쉬
리두 로그 버퍼
작업 롤백
리소스 할당
락(Lock) 해소 및
리소스 할당 해제
PMON
작업 실패
서버 프로세스
사원 테이블
테이블에 락(Lock) 발생
69
4. 오라클 프로세스
5. 시스템 모니터 백그라운드 프로세스(SMON)
장애 발생
4. 오라클 프로세스
인스턴스 복구 수행
테이블의 연속된 공간 통합
테이블
OPTIMAL 크기 유지
임시 세그먼트 제거
롤백 세그먼트
임시 세그먼트
70
SMON
6. 체크포인트 백그라운드 프로세스(CKPT)
4. 오라클 프로세스
SGA
공유 풀
데이터 버퍼 캐쉬
DBWR
리두 로그 버퍼
CKPT
LGWR
디스크에 기록을
제어
리두 로그 파일
테이블
71
7. 기타 백그라운드 프로세스
4. 오라클 프로세스
SGA
공유 풀
대용량 작업
데이터 버퍼 캐쉬
리두 로그 버퍼
Pnnn
Jnnn
리두 로그 파일
CJQ0
테이블
정기적인 작업
ARCH
백업 리두 로그 파일
Dnnn
Snnn
공유 서버(MTS) 환경
RECO
분산 시스템
72
8. 오라클 10g에 추가된 백그라운드 프로세스
4. 오라클 프로세스
SGA
공유 풀
데이터 버퍼 캐쉬
리두 로그 버퍼
RVWR
플래쉬백
(Flash Back)
성능 데이터 수집
MMON
DMnn
MMAN
테이블
데이터 펌프
(Data Pump)
자기 진단
MMNL
디스크로 저장
73
공유 메모리
자동 관리 기능
Chapter5. 오라클 엑세스와 시스템 뷰
74
1. 엑세스
유저 프로세스
5. 오라클 프로세스
UPDATE 급여
SET 상여 = 연봉*0.1
WHERE 고과 = ‘A’
세션 정보
SGA
공유 풀
데이터 버퍼 캐쉬
SQL
리두 로그 버퍼
로그
2750
2500
라이브러리 데이터
캐쉬 딕셔너리 캐쉬
서버 프로세스
고과 연봉
A
C
시스템
테이블스페이스
2500
3500
일반
테이블스페이스
75
언두
테이블스페이스
리두 로그 파일
2. 구문분석
5. 오라클 프로세스
구문 분석
쿼리 수행
기존 동일 SQL에 대한
정보 존재 유무 검색
서버 프로세스
예
존재
아니요
SELECT * FROM EMP
기존 구문 분석
정보 이용
실행 계획 및
구문 분석 트리 생성
구문 분석
수행
문장 확인 및
데이터베이스 분석
락(Lock)
76
단순화 및
권한 확인
3. 데이터 딕셔너리 뷰
5. 오라클 프로세스
오브젝트 관련
유저 및 권한 관련
데이터 딕셔너리 뷰
스토리지 관련
기타
77
4. 데이터 딕셔너리 뷰
5. 오라클 프로세스
세션 관련
대기 이벤트 관련
동적 성능 뷰
기타
78
Chapter6. 오라클 필수 파일
79
1. 오라클 필수 파일의 종류
6. 오라클 필수 파일
SGA
공유 풀
패스워드 파일
경고 로그 파일
데이터 버퍼 캐쉬 리두 로그 버퍼
추적 파일
유저 추적 파일
80
파라메터
파일
컨트롤 파일
Core 추적 파일
2. 파라메터 파일의 개념
6. 오라클 필수 파일
SGA
공유 풀
데이터 버퍼 캐쉬 리두 로그 버퍼
40 MB
200 MB
5MB
접속 불가
적용
SHARED_POOL_SIZE = 40M
DB_CACHE_SIZE = 200M
LOG_BUFFER = 5242880
유저1
유저200
SESSSIONS = 200
…
유저2
유저201
환경 설정 파일
81
3. 파라메터 파일의 종류
6. 오라클 필수 파일
구분
정적 파라메터 파일
동적 파라메터 파일
파일 형식
텍스트 형식
바이너리 형식
관리
사용자가 관리
오라클이 관리
파일 수정
텍스트 편집기로 수행
일반 편집기로 수정하면 사용할 수
없음
(정적 파라메터 파일로 변경 후 수
정 또는 SQL 명령어로 수정)
관련 뷰
V$PARAMETER
V$SPPARAMETER
파일 명
initSID.ora
spfileSID.ora
동적 파라메터 종류
적음
많음
사용
오라클 모든 버전에서 사용 가
능
오라클 9i 이상에서만 사용 가능
82
4. 파라메터 파일 생성
6. 오라클 필수 파일
$ORACLE_HOME/dbs/init.ora 파일을 참고
해서 텍스트 편집기로 생성 후 저장
정적 파라메터 파일
SQL> CREATE PFILE = ‘pfile_name’
FROM SPFILE = ‘spfile_name’;
생성
SQL> CREATE SPFILE = ‘spfile_name’
동적 파라메터 파일
FROM PFILE = ‘pfile_name’;
83
5. 파라메터 변경
6. 오라클 필수 파일
SQL> ALTER SYSTEM SET parameter_name = parameter_value
[COMMENT= ‘text’]
수정
[SCOP= MEMORY | SPFILE | BOTH]
[SID= ‘sid’ | ‘*’ ];
SQL> ALTER SESSION SET parameter_file = parameter_value;
파라메터
파일
동적 파라메터
재기동 없이 변경 가능
84
정적 파라메터
변경하기 위해 반드시
재기동 필요
6. 파라메터 확인
6. 오라클 필수 파일
SQL> SELECT * FROM V$PARAMETER;
확인
SQL> SELECT * FROM V$SPPARAMETER;
SQL> SHOW PARAMETER parameter_name
파라메터 값
조회
V$PARAMETER
85
V$SPARAMETER
7. 컨트롤 파일의 개념
Database 상태
정보 저장
6. 오라클 필수 파일
데이터베이스 이름 : ORCL
IDX1 테이블스페이스 : ONLINE
IDX2 테이블스페이스 : OFFLINE
현재 리두 로그 번호 : 322
데이터 파일 : /data/IDX1.dbf
…
참조
적용
컨트롤 파일
ORCL 데이터베이스
/data/IDX1.dbf
IDX1
테이블스페이스
리두 로그
321
IDX2
테이블스페이스
리두 로그
322
86
8. 컨트롤 파일의 내용
• 생성시 데이터
베이스 상태 정보 기록
• 주기적으로 변경되는
데이터베이스 정보 기록
6. 오라클 필수 파일
컨트롤 파일
■ 데이터베이스 이름
■ 데이터베이스 생성 시 타임스탬프
■ 현재 리두 로그 파일 번호
■ 체크포인트 정보
■ 테이블스페이스 정보
데이터베이스
■ 데이터 파일과 리두 로그 파일 정보
■ 데이터베이스 생성 시 생성되는 데이터베이스 구분자
■ 아카이브 로그 위치와 상태정보
■ RMAN(Recovery Manager) 사용 시 백업 위치와 백업파일 상태
87
9. 컨트롤 파일 다중화의 개념
6. 오라클 필수 파일
컨트롤 파일 다중화
컨트롤 파일1 컨트롤 파일2
디스크
장애
• 데이터베이스 변경 시
모든 컨트롤파일에 기록
ORCL 데이터베이스
• 한 개의 컨트로 파일 장애
시 서비스 중단
테이블스페이스
리두 로그
테이블스페이스
리두 로그
88
• 남아 있는 컨트롤 파일로
쉽게 복구 가능 (Copy)
10. 컨트롤 파일의 다중화 방법
6. 오라클 필수 파일
정적 파라메터 파일 사용
① 데이터베이스 종료
② 편집기로 CONTROL_FILES 파라메터 수정
③ 운영 체제 명령으로 컨트롤 파일 복사
④ 데이터베이스 시작
동적 파라메터 파일 사용
① 명령어로 CONTROL_FILES 파라메터 수정
② 데이터베이스 종료
③ 운영 체제 명령으로 컨트롤 파일 복사
④ 데이터베이스 시작
89
11. 패스워드 파일의 개념
운영체제 인증
6. 오라클 필수 파일
운영 체제 그룹에 등록된
운영 체제 유저에게 인증
SYSDBA
데이터베이스
SYSOPER
패스
워드
파일
패스워드 파일 인증
시스템 (운영체제)
패스워드 파일에 등록된
오라클 유저에게 인증
90
10. 운영체제 인증의 개념
6. 오라클 필수 파일
SYSDBA
SYSOPER
권한부여
운영 체제 그룹 : dba
가능
데이터베이스 기동
데이터베이스 종료,
백업, 복구 등
운영 체제 유저 : oracle, dbadmin
운영 체제 그룹 : was
불가능
운영 체제 유저 : wasadmin
91
11. 패스워드 파일 인증의 개념
SYSDBA
권한부여
SYSOPER
6. 오라클 필수 파일
운영 체제 그룹: dba
운영 체제 유저 : oracle, dbadmin
권한부여
가능
패스워드 파일
데이터베이스 유저 : SYS, SYSTEM,HR
데이터베이스 유저 : SCOTT
가능
불가능
92
데이터베이스 기동
데이터베이스 종료,
백업, 복구 등
12. 패스워드 파일의 관리
생성
삭제
6. 오라클 필수 파일
> orspwd file=file_name password=password entries=max_users
운영 체제 명령으로 파일 삭제
① 데이터베이스 종료
수정
② 패스워드 파일 삭제 후 재생성
③ 데이터베이스 시작
93
13. 추적(Trace) 파일의 개념
6. 오라클 필수 파일
이벤트 발생
백그라운드 프로세스
관련 추적파일
운영 체제 관련 추적파일
데이터베이스
유저 오류 추적 파일
백그라운드 덤프
코아 덤프
유저 덤프
94
14. 유저 추적 파일
유저
6. 오라클 필수 파일
유저
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
임의로 유저 추적 활성화
오류 발생
SQL> EXEC SYS.DBMS_SYSTEM.SET_SQL
_TRACE_IN_SESSION (7, 18, TRUE);
데이터베이스
유저 오류 추적 파일
유저 덤프
Parameter File:
user_dump_dest = /oracle/app/admin/ORCL/udump
95
15. 백그라운드 추적 파일
백그라운드
프로세스
6. 오라클 필수 파일
alertSID.log
로그
데이터베이스의 전체 로그 기록
오류 발생
데이터베이스
백그라운드 프로세스
관련 추적파일
백그라운드 덤프
SID_processf_PID.trc
Parameter File:
background_dump_dest = ‘/oracle/app/admin/ORCL/bdump’
96
16. 코어 추적 파일
6. 오라클 필수 파일
운영 체제
오류 발생
데이터베이스
운영 체제 관련 추적파일
코어 덤프
97
Chapter7. 오라클 시작과 종료
98
1. 오라클 시작과 종료의 개념
7. 오라클 시작과 종료
시작(STARTUP)
오픈
마운트
노마운트
종료
종료(SHUTDOWN)
99
2. 노마운트 단계
7. 오라클 시작과 종료
파라메터 파일 읽기
SGA 할당
노마운트
alertSID.log 파일과 추적 파일 시작
백그라운드 프로세스 기동
■ 컨트롤 파일 재생성 작업 가능
100
3. 마운트 및 오픈 단계
7. 오라클 시작과 종료
컨트롤 파일 확인
마운트
컨트롤 파일내의 데이터 파일
및 리두 로그 파일 인지
■ 데이터베이스 복구
■ 아카이브 로그 모드 적용 및 해제
온라인 데이터 파일 확인
오픈
온라인 리두 로그 파일 확인
101
4. 오라클 시작 방법
7. 오라클 시작과 종료
SQL> STARTUP {pfile|spfile = 파라메터 파일 위치}
일반 시작
SQL> ALTER DATABASE OPEN {RESETLOGS};
SQL> STARTUP RESTRICT
시작
제한된 모드로 시작
SQL>
ALTER SYSTEM
SESSION (설정);
ENABLE
RESTRICTED
SQL> ALTER SYSTEM DISABLE RESTRICTED
SESSION (해제);
SQL> STARTUP MOUNT
읽기 전용으로 시작
SQL> ALTER DATABASE OPEN READ ONLY;
102
5. 오라클 종료 방법
NORMAL
7. 오라클 시작과 종료
SQL> SHUTDOWN
• 모든 접속 유저가 접속 종료
후 DB 종료
• 새로운 접속 불가
TRANSACTIONAL
SQL> SHUTDOWN TRANSACTIONAL
• 수행 중인 모든 SQL 완료
후 DB 종료
• 새로운 접속 불가
• 새로운 SQL 수행 불가
종료
IMMEDIATE
SQL> SHUTDOWN IMMEDATE
• 수행 중인 모든 SQL
Rollback 후 DB 종료
• 새로운 접속 불가
• 새로운 SQL 수행 불가
• 수행 중인 모든 SQL 취소,
Rollback 후 DB 종료
ABORT
SQL> SHUTDOWN ABORT
•새로운 접속 불가
• 새로운 SQL 수행 불가
비정상 종료
• 인스턴스 복구 필요
103
Chapter8. 리두 로그 파일과 아카이브
로그 파일
104
1. 리두 로그 파일의 개념
8. 리두로그 파일과 아카이브 로그 파일
SGA
공유 풀
데이터 버퍼 캐쉬
리두 로그 버퍼
LGWR
리두 로그 파일
■ 리두 로그 버퍼 - 데이터베이스 장애 시 복구를 수행하기 위해 모든 DML에 대한 로그를 기록
■ 리두 로그 파일 - 리두 로그 버퍼의 내용을 디스크에 기록하는 데이터베이스 구성요소
105
2. 리두 로그 파일의 구성 요소
로그 시퀀스 번호 9
8. 리두로그 파일과 아카이브 로그 파일
로그 시퀀스 번호 10
멤버1
RedoB1.log
멤버1
디스크1
RedoA1.log
LGWR
디스크 2
멤버2
RedoB2.log
멤버2
RedoA2.log
그룹B
그룹A
■ 리두 로그 멤버 - 리두 로그 버퍼의 내용을 기록하는 파일이며 하나의 리두 로그 멤버는
하나의 리두 로그 파일
- LGWR 백그라운드 프로세스는 리두 로그 버퍼의 내용을 동일 그룹에 속해
있는 모든 리두 로그 멤버에 동일한 내용들을 기록
■ 리두 로그 그룹 - 동일한 로그 기록을 저장하고 있는 리두 로그 멤버들의 집합을 의미
106
3. 현재 리두 로그 그룹
8. 리두로그 파일과 아카이브 로그 파일
SGA
공유 풀
멤버1
데이터 버퍼 캐쉬
리두 로그 버퍼
멤버1
LGWR
멤버2
멤버2
현재 리두 로그 그룹
그룹B
그룹A
■ 현재 리두 로그 그룹(Current Redo Log Group) :
여러 개의 리두 로그 그룹 중 LGWR 백그라운드 프로세스가 리두 로그 버퍼의 내용을 기록하는
리두 로그 그룹
107
4. 로그 스위치 개념
8. 리두로그 파일과 아카이브 로그 파일
SGA
공유 풀
데이터 버퍼 캐쉬
리두 로그 버퍼
로그 시퀀스 번호 10 로그 시퀀스 번호 9
멤버1
멤버1
LGWR
로그 스위치 :
- 현재 리두 로그 그룹 공간 부족
- ALTER SYSTEM SWITCH LOGFILE;
멤버2
멤버2
그룹B
그룹A
현재 리두 로그 그룹
■ 로그 스위치(Log Switch) 현상 :
현재 리두 로그 그룹이 순서에 의해 다음 리두 로그 그룹으로 변경되는 현상
108
5. 로그 스위치 절차
8. 리두로그 파일과 아카이브 로그 파일
① 컨트롤 파일 확인:
- 다음에 사용될 리두 로그 번호 확인
- 다음에 사용될 리두 로그 그룸의 체크포인트, 아카이브 완료 여부 확인
② 로그 스위치 직전의 SCN을 리두 로그 파일 헤더에 기록
로그 스위치 발생
③ 리두 로그 파일에 대한 작업 수행 :
- 현재 로그 그룹을 CURRENT, 이전 로그 그룹을 ACTIVE로 표시
- 이전 리두 로그 그룹에 대해 체크포인트 및 아카이브 실행
- 체크 포인트가 완료되면 INACTIVE로 표시
④ 로그 시퀀스 번호 증가 :
- 로그 스위치에 의해 현재 리두 로그 그룹이 변경될 때마다 1씩
증가하므로 가장 높은 값이 가장 최근 로그임.
109
4. 리두 로그 파일의 장애
8. 리두로그 파일과 아카이브 로그 파일
단일 리두 로그 파일
다중 리두 로그 파일
장애발생
장애발생
멤버1
멤버1
멤버2
멤버2
그룹B
그룹A
디스크1
디스크1
멤버1
멤버1
그룹B
그룹A
장애발생
유저
C.F
리두 로그 파일 장애 발행 유형 :
- 사용자 실수로 리두 로그 파일 삭제
- 디스크 장애로 리두 로그 파일 손상
110
디스크2
유저
5. 리두 로그 파일 관리(V$LOG)
SELECT * FROM V$LOG;
검색
SELECT * FROM V$LOGFILE;
8. 리두로그 파일과 아카이브 로그 파일
SQL> SELECT GROUP#, SEQUENCE#, MEMBERS,
BYTES/1024/1024 “SIZE”, STATUS
FROM V$LOG;
GROUP# SEQUENCE# MEMBERS SIZE STATUS
----- -------- ------- --- ------ --------1
46
2
10 CURRENT
2
44
1
10 INACTIVE
3
45
1
5
INACTIVE
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SELECT *
FROM V$LOG_HISTORY;
•
리두 로그 그룹 정보 검색
SQL> SELECT GROUP#, SEQUENCE#, MEMBERS,
BYTES/1024/1024 “SIZE”, STATUS
FROM V$LOG;
GROUP#
SEQUENCE# MEMBERS SIZE STATUS
----- -------- ------- --- ------ ------1
46
2
10
ACTIVE
2
47
1
10 CURRENT
3
45
1
5
INACTIVE
111
6. 리두 로그 파일 관리(V$LOGFILE)
SQL> SLEECT GROUP$, MEMBER
FROM V$LOGFILE;
SELECT * FROM V$LOG;
검색
8. 리두로그 파일과 아카이브 로그 파일
GROUP#
-----1
1
2
3
SELECT * FROM V$LOGFILE;
SELECT *
•
FROM V$LOG_HISTORY;
112
MEMBER
---------------/data1/redo1_1.log
/data2/redo1_2.log
/data1/redo2_1.log
/data2/redo2_1.log
리두 로그 파일 정보 검색
7. 리두 로그 파일 관리(V$LOG_HISTORY)
SELECT * FROM V$LOG;
검색
8. 리두로그 파일과 아카이브 로그 파일
SQL> SELECT TO_CHAR(FIRST_TIME,'YYYYMM') “DATE”
, COUNT(*) cnt
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYYMM');
DATE
-----200508
200509
200510
200512
200601
SELECT * FROM V$LOGFILE;
SELECT *
CNT
---------------2143
2154
2208
2011
716
FROM V$LOG_HISTORY;
•
113
리두 로그 사용 추이 조회
8. 리두 로그 파일 관리 관련 명령어
8. 리두로그 파일과 아카이브 로그 파일
SQL> ALTER DATABASE ADD LOGFILE [GROUP n]
리두 로그 그룹 추가
(‘file_name’,[‘file_name’]...) SIZE n
[, [GROUP n] (file_name’,[‘file_name’]...) SIZE n ...];
리두 로그 그룹 삭제
SQL> ALTER DATABASE DROP LOGFILE GROUP n;
SQL> ALTER DATABASE ADD LOGFILE MEMBER
‘file_name’ [REUSE]
관리
리두 로그 멤버 추가
[,file_name’ [REUSE] ...] TO GROUP n
[,‘file_name’ [REUSE]
[,file_name’ [REUSE] ...] TO GROUP n...];
SQL> ALTER DATABASE DROP LOGFILE MEMBER
리두 로그 멤버 삭제
‘file_name’ [,’file_name’ ...];
SQL> ALTER DATABASE CLEAR [UNARCHIVED]
리두 로그 그룹 초기화
LOGFILE GROUP n;
114
9. 아카이브 로그의 개념
8. 리두로그 파일과 아카이브 로그 파일
노아카이브 로그 모드(Noarchivelog Mode)
로그 스위치
로그 스위치
22
22
21
23
리두 로그 파일
리두 로그 파일
아카이브 로그 모드(Archivelog Mode)
로그 스위치
로그 스위치
22
21
22
21
22
115
23
아카이브 로그
10. 아카이브 로그의 장/단점
8. 리두로그 파일과 아카이브 로그 파일
노아카이브 로그 모드
아카이브 로그 모드
장점
단점
백업 및 복구 용이
백업 및 복구 복잡
디스크 I/O 감소
디스크 I/O 증가
단점
장점
복구 시점 조절
불가
복구 시점 조절
가능
온라인 복구 불가
온라인 복구 가능
온라인 백업 불가
온라인 백업 가능
116
11. 아카이브 로그 모드 설정/해제
8. 리두로그 파일과 아카이브 로그 파일
① 데이터베이스 종료
아카이브 로그 모드 설정
② 관련 파라메터 설정
③ STARTUP MOUNT
④ ALTER DATABASE ARCHIVELOG;
⑤ ALTER DATABASE OPEN;
① 데이터베이스 종료
아카이브 로그 모드 해제
② STARTUP MOUNT
③ ALTER DATABASE NOARCHIVELOG;
④ ALTER DATABASE OPEN;
117
12. 아카이브 로그 모드 관련 파라메터
3. 오라클 시작과 종료
아카이브 로그 관련 파라메터:
■ LOG_ARCHIVE_DEST
■ LOG_ARCHIVE_DEST_n
■ LOG_ARCHIVE_DEST_STATE_n
■ LOG_ARCHIVE_DUPLEX_DEST
■ LOG_ARCHIVE_FORMAT
■ LOG_ARCHIVE_MAX_PROCESSES
■ LOG_ARCHIVE_MIN_SUCCEED_DEST
아카이브 로그 관련 파라메터 설정 예 :
■ LOG_ARCHIVE_TRACE
LOG_ARCHVIE_DEST = /data1/ARCH
LOG_ARCHIVE_DUPLEX_DEST = /data2/ARCH
LOG_ARCHIVE_DEST_1 = "LOCATION=/data1/ARCH/ MANDATORY
REOPEN = 600"
LOG_ARCHIVE_DEST_2 = "LOCATION=/data2/ARCH/ OPTIONAL"
LOG_ARCHIVE_DEST_3 = "SERVICE=arch_back OPTIONAL"
LOG_ARCHIVE_FORMAT = orcl_%s_%t_%r.arc
118
13. 정보 조회(Archive Log List)
ARCHIVE LOG LIST
SQL> ARCHIVE LOG LIST
Database log mode
Automatic archival
Archive destination
Oldest online log sequence
Next log sequence to archive
Current log sequence
SELECT *
검색
8. 리두로그 파일과 아카이브 로그 파일
FROM V$ARCHIVE_DEST A,
V$ARCHIVED_LOG B
WHERE A.DEST_ID = B.DEST_ID;
Archive Mode
Enabled
/data1/ARCH/
35
37
37
SELECT *
• 아카이브 로그 모드 정보 제공
FROM V$ARCHIVE_PROCESSES
관리
ALTER SYSTEM ARCHIVE LOG
CURRENT;
119
14. 정보 조회(V$ARCHIVE_LOG)
8. 리두로그 파일과 아카이브 로그 파일
ARCHIVE LOG LIST
SELECT *
검색
SQL> SELECT DEST_NAME, NAME, B.STATUS
FROM V$ARCHIVE_DEST A,
V$ARCHIVED_LOG B
WHERE A.DEST_ID = B.DEST_ID;
FROM V$ARCHIVE_DEST A,
V$ARCHIVED_LOG B
WHERE A.DEST_ID = B.DEST_ID;
DEST_NAME
NAME
------------ -----------------------LOG_ARCHIVE_DEST /data1/orcl_1_1_55820.arc
LOG_ARCHIVE_DEST /data1/orcl_2_1_55820.arc
LOG_ARCHIVE_DEST /data1/orcl_3_1_55820.arc
SELECT *
FROM V$ARCHIVE_PROCESSES
관리
ALTER SYSTEM ARCHIVE LOG
CURRENT;
• 아카이브 로그 파일 정보 검색
120
S
A
A
A
15. 정보 조회(V$ARCHIVE_PROCESS)
ARCHIVE LOG LIST
SELECT *
검색
FROM V$ARCHIVE_DEST A,
V$ARCHIVED_LOG B
WHERE A.DEST_ID = B.DEST_ID;
SELECT *
FROM V$ARCHIVE_PROCESSES
관리
8. 리두로그 파일과 아카이브 로그 파일
SQL> SELECT * FROM V$ARCHIVE_PROCESSES;
PROCESS
------- -0
1
2
3
4
5
6
7
8
9
10
ALTER SYSTEM ARCHIVE LOG
CURRENT;
STATUS
LOG_SEQUENCE STAT
----- ---- -------------- --ACTIVE
0 IDLE
ACTIVE
0 IDLE
STOPPED
0 IDLE
STOPPED
0 IDLE
STOPPED
0 IDLE
STOPPED
0 IDLE
STOPPED
0 IDLE
STOPPED
0 IDLE
STOPPED
0 IDLE
STOPPED
0 IDLE
STOPPED
0 IDLE
• 아카이브 로그 프로세스 정보 검색
121
16. 현재 리두 로그 아카이브 수행
8. 리두로그 파일과 아카이브 로그 파일
ARCHIVE LOG LIST
SELECT *
검색
FROM V$ARCHIVE_DEST A,
V$ARCHIVED_LOG B
WHERE A.DEST_ID = B.DEST_ID;
SELECT *
FROM V$ARCHIVE_PROCESSES
관리
ALTER SYSTEM ARCHIVE LOG
CURRENT;
• 현재 리두 로그 아카이브
122
Chapter9. 오라클 스토리지
123
1. 오라클 스토리지 구조
9. 오라클 스토리지
오라클
데이터베이스
테이블스페이스
테이블스페이스
세그먼트
세그먼트
익스텐트
익스텐트
세그먼트
익스텐트
테이블스페이스
세그먼트
익스텐트
익스텐트
오브젝트
세그먼트
테이블
인덱스
파티션
파티션 인덱스
뷰
시퀀스
동의어
SQL> SELECT FILE_NAME,
BYTES/1024/1024 “MB”
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME
= 'USERS';
데이터 블록
시스템
운영 체제 블록
디스크
데이터 파일
124
FILE_NAME
MB
---------------- ---/data1/users_01.dbf
10
/data2/users_02.dbf
20
2. 데이터 블록의 개요
9. 오라클 스토리지
SGA
공유 풀
SELECT *
FROM ‘사원’
WHERE 사원ID=’111’
데이터 버퍼 캐쉬
리두 로그 버퍼
111 이창구
112 최수련
113 장우형
• 오라클의 I/O 단위
• DB_BLOCK_SIZE 파라메터로 크기 설정
• 하나 이상의 운영 체제 블록으로 구성
125
3. 데이터 블록의 구조
데이터
블록 헤더
9. 오라클 스토리지
캐시 계층
트랙젝션 계층
테이블 딕렉토리
데이터 헤더
행 디렉토리
데이터 계층
사용 가능한 공간
데이터
126
4. 데이터 블록 크기에 따른 장/단점
데이터 블록 크기를 크게 설정
9. 오라클 스토리지
데이터 블록 크기를 작게 설정
장점
단점
블록 사용 율이 높음
블록 사용 율이 낮음
한 번 의 I/O 로 많 은
데이터 추출
한 번 의 I/O 로 적 은
데이터 추출
단점
장점
블록에 대한 경합
가능성 높음
블록에 대한
가능성 낮음
127
경합
5. 데이터 블록에 설정 가능한 옵션
설정 항목
INITRANS
MAXTRANS
9. 오라클 스토리지
설명
- 블록의 초기 트랜잭션 슬롯의 개수를 지정
- 테이블에 데이터 블록의 경우 기본 값 : 1
- 인덱스에 대한 데이터 블록의 기본 값 : 2
- 데이터 블록 안에 생성할 수 있는 최대 트랜잭션 슬롯의 개수 지정
- 오라클 10g부터는 해당 값은 무조건 255개로 설정된다
PCTFREE
- 행에 변경 발생시 행의 크기가 증가하는 것에 대비해 지정하는 여유
공간
- 기본 값은 데이터 블록 크기의 10%
PCTUSED
- 수동 세그먼트 공간 관리 방식 사용 시 블록 재사용을 여부를 결정
- 기본 값은 데이터 블록 크기의 40%
128
6. PCTUSED 및 PCTFREE
9. 오라클 스토리지
사용된 공간
PCTFREE 10
PCTUSED 40
미사용 공간
①
②
90%
90%
40%
40%
INSERT
INSERT
③
④
90%
DELETE
90%
DELETE
40%
INSERT
INSERT
129
40%
7. PCTFREE
9. 오라클 스토리지
PCTFREE 10
PCTFREE 0
고객ID
이름
100201 정은진
주소(100)
서울 강동구
100202 최세진
서울 중구
고객ID
이름
주소(100)
100201 정은진
100202 최세진
90%
서울 중구
고객 테이블
블록 #1
UPDATE
UPDATE
고객 테이블 블록 #1
고객ID(1 이름(10
0)
)
100201
정은진
100202 최세진
고객ID(1 이름
0)
100202
정은진
주소(100)
서울 중구 장미 아파트
고객ID(1 이름(10
0)
)
100201
최세진
90%
서울 강동구
주소(100)
서울 강동구
90%
서울 강동구
주소(100)
고객 테이블
블록 #1
서울 중구 장미 아파트
10%
100
%
행정보
고객 테이블 블록 #2
고객 테이블 블록 #1
130
8. Transaction Slot
9. 오라클 스토리지
캐시계층
변경 프로세스 1
트랜잭션 계층
작업 수행
테이블 디렉토리
행 디렉토리
트랜잭션 슬롯
변경 프로세스 2
대기
데이터
데이터 블록
• INITRANS의 수치가 높을 경우 :
- 트랜잭션 슬롯 하나 당 24Bytes의 공간을 차지하므로 사용할 수 있는 블록 공간이 감소.
- 데이터 블록에 대한 동시 유저 작업을 설정한 값만큼 지원
• INITRANS의 수치가 낮을 경우 :
- 공간 사용 효율은 좋아지나 동시에 여러 트랜잭션이 수행될 경우 해당 데이터 블록에
사용 가능 공간이 존재하지 않는다면 이미 할당된 트랜잭션 슬롯 개수만큼만 동시
유저의 작업이 가능하다.
131
9. 행 이전과 행 연결
9. 오라클 스토리지
행 이전
행 연결
4KB
PCTFREE 중
여유 공간
A
INSERT
A
6KB
UPDATE
21번 블록
17번 블록
행 연결
행 이전
위치 정보
A
17번 블록
A
21번 블록
35번 블록
132
22번 블록
10. 프리리스트
9. 오라클 스토리지
세그먼트 헤더
데이터 블록
익스텐트 2
익스텐트 1
고수위
A
익스텐트 맵
프리블록
프리리스트
고수위
133
11. 익스텐트의 개요
9. 오라클 스토리지
사원 테이블
INSERT
공간 부족
새로운 익스텐트 할당
134
12. 익스텐트의 할당 및 해제
9. 오라클 스토리지
Extent 할당
• 세그먼트 생성
- MINEXTENTS 옵션에서 설정한 값 만큼 할당
• 세그먼트 확장
- 1순위 : 데이터가 저장될 수 있는 여유 공간을 가지는 데이터 블록을 할당.
- 2순위 : 1순위에서 언급한 데이터 블록이 존재하지 않다면 해당 세그먼트의 고수위를
뒤로 이동시켜 빈 데이터 블록을 확보한 후 할당
- 3순위 : 2순위에서 언급한 고수위를 뒤로 이동시킬 공간이 없을 경우 익스텐트를 할당
• 수동으로 익스텐트를 할당
Extent 할당 해제
• 세그먼트 삭제
• 세그먼트 Truncate
• 수동으로 익스텐트를 할당 해제
135
13. 익스텐트 조회(DBA_EXTENTS)
SELECT * FROM DBA_EXTENTS
검색
SELECT SELECT * FROM
DBA_FREE_SPACE
9. 오라클 스토리지
SQL> SELECT A.FILE_NAME, B.EXTENT_ID,
B.BLOCKS, B.BYTES/1024 MB
FROM DBA_DATA_FILES A, DBA_EXTENTS B
WHERE A.FILE_ID = B.FILE_ID
AND B.SEGMENT_NAME='고객';
FILE_NAME EXTENT_ID BLOCKS MB
------------ -- ---- ------ --/data1/users01.dbf
0
8
64
/data1/users02.dbf
1
8
64
ALTER TABLE table_name ALLOCATE EXTENT
[ ([SIZE n [K|M] ] [DATAFILE ‘datafile_name’]) ];
관리
ALTER TABLE table_name DEALLOCATE UNUSED
[ KEEP n [ K|M ] ] ;
136
14.익스텐트 조회(DBA_FREE_SPACE)
9. 오라클 스토리지
SQL> SELECT TABLESPACE_NAME TS, FILE_ID,
BLOCK_ID, BYTES, BLOCKS CNT
FROM
DBA_FREE_SPACE
WHERE
TABLESPACE_NAME = 'USERS';
SELECT * FROM DBA_EXTENTS
검색
TS
----USERS
USERS
USERS
SELECT SELECT * FROM
DBA_FREE_SPACE
ALTER TABLE table_name ALLOCATE EXTENT
[ ([SIZE n [K|M] ] [DATAFILE ‘datafile_name’]) ];
관리
ALTER TABLE table_name DEALLOCATE UNUSED
[ KEEP n [ K|M ] ] ;
137
FILE_ID BLOCK_ID
----------5
754953
5
755977
5
757001
BYTES CNT
------ ---8388608
1024
8388608
1024
8388608
1024
15. 오브젝트와 세그먼트
9. 오라클 스토리지
오브젝트
뷰
시퀀스
동의어
세그먼트
테이블
인덱스
파티션
파티션 인덱스
• 오브젝트 - 뷰, 인덱스, 테이블 등
• 세그먼트 - 테이블, 인덱스, 클러스터 등 실제 물리적 공간을 가지는 오브젝트
138
16. 고수위 (High Water Mark – HWM) 개념
사원 테이블
익스텐트
고수위
부서 테이블
급여 테이블
익스텐트
익스텐트
사용 데이터 블록
익스텐트
미사용 데이터 블록
139
9. 오라클 스토리지
17. 고수위 (High Water Mark – HWM) 특징
• 고수위(High Water Mark, HWM) :
- 세그먼트 생성 이후 최대 사용량을 표시하는 세그먼트 구성요소
• 고수위의 특징:
-
모든 세그먼트에는 고수위 존재
테이블 전체 스캔(Full Scan) 시 고수위 앞에 존재하는 모든 데이터 블록을 엑세스
고수위 뒤에 존재하는 데이터 블록에는 데이터 저장 불가
고수위는 테이블 Truncate 또는 Drop에 의해서 앞으로 이동 및 제거 가능
오라클 10g에서는 세그먼트 축소 명령어로 고수위 이동이 가능
140
9. 오라클 스토리지
18. 고수위 (High Water Mark – HWM)와 테이블 스캔
9. 오라클 스토리지
사원 테이블
익스텐트
전체 스캔 범위
미사용 데이터 블록
부서 테이블
익스텐트
사용 데이터 블록
전체 스캔 범위
141
19. 고수위 (High Water Mark – HWM) 이동
사원 테이블
익스텐트
Insert
고수위
①
익스텐트
Insert
고수위
② 고수위 이동
익스텐트
③ 새로운 익스텐트 할당
고수위
미사용 데이터 블록
사용 데이터 블록
142
9. 오라클 스토리지
20. 세그먼트의 관리
검색
9. 오라클 스토리지
SELECT * FROM DBA_SEGMENTS
SQL> SELECT OWNER, SEGMENT_NAME, EXTENTS, BYTES/1024/1024 MB
FROM DBA_SEGMENTS;
OWNER SEGMENT_NAME
EXTENTS
MB
----- --------------- --------- ----SCOTT
EMP
8
16
SCOTT
DEPT
8
16
SCOTT
SALARY
8
16
SCOTT
GRADE
8
16
SCOTT
PROJECT
8
16
…………..
143
21. 세그먼트 축소의 개념
9. 오라클 스토리지
블록 단편화
테이블 생성
초기상태
고수위
DML
테이블 사용 후 상태
사용 데이터 블록
미사용 데이터 블록
• 테이블의 경우 테이블 전체 조회 소요 시간 증가
• 인덱스의 경우 인덱스 레벨이 깊어지므로 조회 소요 시간 증가
• 하나의 데이터 블록 조회로 적은 로우가 추출되므로 디스크 I/O 증가 가능
• 빈 데이터 블록의 증가로 디스크 공간 낭비
144
22. 세그먼트 축소의 종류
9. 오라클 스토리지
테이블 재구성
사원 테이블
사원 테이블
테이블 MOVE
불록 단편화 해결 방법
사원 테이블
온라인 세그먼트 축소
사원 테이블
145
사원 테이블
23. 세그먼트 축소 종류별 특징
9. 오라클 스토리지
세그먼트 축소 방법 특징 비교
항목
테이블 재구성
테이블 Move
온라인 세그먼트
재구성
절차
복잡함
단순함
던순함
작업 중 서비스
불가능
불가능
가능
수행 시간
빠름
빠름
보통
추가 필요 공간
대상 테이블 크기
만큼 필요
대상 테이블
크기만큼 필요
공간 필요 없음
인덱스 재구성 필요
유무
재생성 필요
재구성 필요
재구성 필요 없음
행 이전 및 행 연결
해결
해결
부분 해결
146
24. 온라인 세그먼트 축소 방법
9. 오라클 스토리지
사용 데이터 블록
고수위
미사용 데이터 블록
ALTER TABLE 사원 SHRINK SPACE;
할당 해제
고수위
1. 전체가 채워지지 않은 데이터 블록의 데이터를 다른 여유 공간이 있는 데이터 블록에 저장
2. 1단계의 저장이 완료되면 옮겨진 데이터 블록의 기존 데이터를 삭제한다.
3. 고수위를 이동시키며 빈 데이터 블록은 할당 해제를 한
147
25. 온라인 세그먼트 축소의 특징
9. 오라클 스토리지
• 온라인으로 실행 가능
• 별도의 추가 공간 없이 실행 가능
• 테이블에 세그먼트 축소 명령을 적용하는 경우 관련된
인덱스도 같이 세그먼트 축소 적용 가능
• 온라인 세그먼트 축소가 실행으로 인한 내부적 Delete, Insert는
Trigger를 실행 시키지 않음
고수위
위치 변경
ALTER TABLE 사원 SHRINK SPACE;
148
26. 온라인 세그먼트 축소 시 고려사항
• 테이블의 경우 ROW MOVEMENT 옵션 ENABLE 필요
•자동 세그먼트 공간 관리(ASSM) 테이블스페이스에 저장된 세그먼트만 실행 가능
• 온라인 세그먼트 축소 가능 세그먼트
- 파티션 또는 서브파티션을 포함하는 테이블 및 인덱스
- 인덱스 구조 테이블 (Index Organized Table, IOT)
- 구체화된 뷰(Materialized View)와 구체화된 뷰 로그
• 온라인 세그먼트 축소 불가능 세그먼트
- 클러스터 테이블
- LONG 컬럼을 포함한 테이블
- ON COMMIT 옵션을 사용한 구체화된 뷰
- ROWID 기반 구체화된 뷰
- LOB 세그먼트
- 함수 기반 인덱스를 가지는 테이블
149
9. 오라클 스토리지
27. 온라인 세그먼트 축소
수행
9. 오라클 스토리지
행 이전 활성화
SQL> ALTER TABLE table_name ENABLE ROW MOVEMENT
행 이전 비활성화
SQL> ALTER TABLE table_name DISABLE ROW MOVEMENT
SQL> ALTER segment_type segment_name SHRINK SPACE
온라인 세그먼트 축소
[ COMPACT ] [ CASCADE ];
150
Chapter10. 테이블 스페이스
151
1. 테이블스페이스 개요
10. 테이블 스페이스
용도
● 시스템 테이블스페이스
시스템용 테이블스페이스
비시스템용 테이블스페이스
● SYSAUX 테이블스페이스
● 일반 테이블스페이스
● 임시 테이블스페이스
● 언두 테이블스페이스
익스텐트 관리 방법
딕셔너리 관리 테이블스페이스
지역 관리 테이블스페이스
세그먼트 공간 관리
자동 세그먼트 공간 관리 테이블스페이스
수동 세그먼트 공간 관리 테이블스페이스
152
2. 용도에 따른 테이블스페이스 구분
10. 테이블 스페이스
- 데이터베이스 운영에 필요한 데이터 저장
- 데이터베이스 생성 시 반드시 생성해야 하며 생성 후 삭제 불가
- 테이블스페이스 상태 변경 불가
시스템
- 데이터베이스 정보 및 유저 오브젝트 정보를 저장
- 시스템 언두 세그먼트 저장
- 시스템 테이블스페이스가 지역 관리 방식으로 생성되면 이 후
시스템용
테이블스페이스
- 모든 테이슬스페이스는 지역 관리 방식으로만 생성 가능
- 데이터베이스 운영에 필요한 데이터 저장
SYSAUX
- 데이터베이스 생성 시 반드시 생성해야 하며 생성 후 삭제 불가
- 시스템 테이블스페이스의 사용량과 부하 감소시키는 역할
- 시스템 테이블스페이스 단편화 현상 감소시키는 역할
153
3. 용도에 따른 테이블스페이스 구분(비시스템용)
10. 테이블 스페이스
- 필요에 따라 하나 이상 생성하여 사용하며, 생성,
수정 및 삭제 에 대한 제약이 거의 없음
일반 테이블스페이스
- 정렬에 사용되는 임시 세그먼트를 저장하는 공간
- ORDER BY, GROUP BY , 인덱스 생성시 사용
비 시스템용
테이블스페이스
- 임시 세그먼트만 저장 가능
임시 테이블스페이스
- 기본 임시 테이블스페이스는 데이터베이스 생성
시 지정 됨
- 기본 임시 테이블스페이스는 DROP, OFFLINE
불가
- 롤백을 위한 이전 이미지를 저장하는 공간
- 언두 세그먼트만 저장 가능
임시 테이블스페이스
- 자동 관리 언두 세그먼트를 사용하기 위해서는
지역 관리 테이블스페이스로 생성해야 함
154
4. 용도에 따른 테이블스페이스 구분(임시 테이블스페이스)
10. 테이블 스페이스
임시 테이블스페이스의 종류
항목
익스텐트 할당 해제
엑세스 방식
복구
일반 테이블스페이스
임시 테이블스페이스
지역적 관리
임시 테이블스페이스
SMON에 의해 수시로
발생
발생 안함
발생 안한
일반 엑세스
일반 엑세스
다이렉트 엑세스
복구 필요
복구 필요
복구 불필요
155
5. 임시 테이블스페이스의 임시 세그먼트 할당
10. 테이블 스페이스
임시 테이블스페이스의 임시 세그먼트 할당방식의 비교
임시
테이블 스페이스
일반
테이블스페이스
유저 A
임시 세그먼트
유저 A
유저 B
임시 세그먼트
유저 C
유저 B
유저 C
테이터 파일
테이터 파일
156
6. 임시 테이블스페이스의 익스텐트 할당
10. 테이블 스페이스
임시 테이블스페이스의 익스텐트 할당 방식의 비교
익스텐트
익스텐트
임시 세그먼트
B
A
익스텐트 헤더
유저 A
세그먼트 헤더
유저 B
임시 테이블스페이스의 익스텐트 할당 특징 :
• 익스텐트 단위로 요청 유저에게 할당
• 익스텐트 헤더에 사용중인 유저 표시
• 세그먼트 헤더에서 사용하지 않는 익스텐트 관리
157
7. 임시 테이블스페이스의 엑세스
10. 테이블 스페이스
임시 테이블스페이스의 엑세스 방식의 비교
SGA
공유 풀
데이터 버퍼 캐쉬
리두 로그 버퍼
정렬 작업을
수행 중인 유저
유저
다이렉트 엑세스
일반
테이블스페이스
임시
테이블스페이스
158
지역 관리 임시
테이블스페이스
8. 익스텐트 관리 방법에 따른 테이블스페이스 구분
지역 관리
테이블스페이스
딕셔너리 관리
테이블스페이스
익스텐트
비트 맵
익스텐트 정보 기록
테이터 파일
테이터 파일
시스템
테이블스페이스
익스텐트 정보 기록
데이터 딕셔너리 테이블
익스텐트 정보를 기록하지 않음
테이터 파일
159
10. 테이블 스페이스
9. 딕셔너리 관리 테이블스페이스 구분
딕셔너리 관리
테이블스페이스
익스텐트
10. 테이블 스페이스
시스템
테이블스페이스
사원 테이블
익스텐트 정보 기록
데이터 딕셔너리 테이블
테이터 파일
테이터 파일
• 해당 테이블스페이스에 존재하는 세그먼트가 사용하는 익스텐트의 정보가 변경될 때마다
관련 데이터 딕셔너리 테이블에 해당 익스텐트 정보를 갱신
• 세그먼트마다 각기 다른 익스텐트 크기 설정 가능
• 데이터 딕셔너리 테이블에 대한 언두 정보 발생
160
10. 딕셔너리 관리 테이블스페이스 구분
지역 관리
테이블스페이스
10. 테이블 스페이스
시스템
테이블스페이스
데이터 딕셔너리 테이블
익스텐트 정보 기록
익스텐트 정보를
기록하지 않음
테이터 파일
테이터 파일
• 데이터 딕셔너리 테이블을 갱신하거나 참조하지 않음
• 데이터 딕셔너리 테이블 갱신에 따른 언두 정보 불필요
• 익스텐트 통합 불필요
• UNIFORM 옵션을 사용하여 테이블스페이스를 생성한 경우 모든 세그먼트의 스토리지 옵션이 동일
• UNIFORM 옵션을 사용하지 않은 경우 오라클이 세그먼트 크기를 참조하여 자동으로 익스텐트 크기
를 결정
161
11. 세그먼트 공간 관리 방법에 따른 테이블스페이스 구분
자동 세그먼트 공간 관리
익스텐트
10. 테이블 스페이스
수동 세그먼트 공간 관리
비트맵
프리리스트
첫번째 익스텐트
• 자동 세그먼트 공간 관리 방식(Automatic Segment Space Management, ASSM) :
- 세그먼트를 구성하는 각 익스텐트의 첫 번째 데이터 블록인 익스텐트 헤더에서 비트맵으로
여유 공간을 가지는 데이터 블록을 관리하는 방식
- PCTUSED, FREELISTS관련 옵션 사용 불필요
- 공간 사용 효율성 및 동시 Insert 작업에 대한 성능 향상
• 수동 세그먼트 공간 관리 방식 :
- 세그먼트를 구성하는 익스텐트 중 첫 번째 익스텐트의 첫 번째 데이터 블록인 세그먼트 헤더에서
프리리스트로 여유 공간을 가지는 데이터 블록을 관리하는 방식
162
12. 테이블스페이스 생성
10. 테이블 스페이스
SQL> CREATE TABLESPACE tablespace_name
[DATAFILE datafile_clause]
[BLOCKSIZE n [K]]
일반 테이블스페이스 생성
[LOGGING | NOLOGGING]
[DEFAULT storage_clause]
[extent_management_clause]
[segment_management_clause];
SQL> CREATE UNDO TABLESPACE tablespace_name
생성
언두 테이블스페이스 생성
[DATAFILE clause]
[extent_management_clause];
SQL> CREATE TEMPORARY TABLESPACE tablespace_name
임시 테이블스페이스 생성
[TEMPFILE clause]
[extent_management_clause];
163
13. 테이블스페이스 변경
10. 테이블 스페이스
SQL> ALTER TABLESPACE tablespace_name
크기 변경
[DATAFILE datafile_clause]
SQL> ALTER DATABAE ‘file_name’ resize n [G|M|K]
SQL> ALTER TABLESPACE tablespace_name READ ONLY
읽기 전용으로 변경
변경
스토리지 옵션 변경
SQL> ALTER TABLESPACE tablespace_name READ WRITE
SQL> ALTER TABLESPACE tablespace_name
[DEFAULT storage_clause]
Online / Offline
변경
SQL> ALTER TABLESPACE tablespace_name { ONLINE |
OFFLINE [NORMAL|TEMPORARY|IMMEDATE] };
이름 변경
SQL> ALTER
new_name;
데이터파일 위치 변경
164
TABLESPACE
old_name
rename
to
14. 일반 테이블스페이스의 데이터 파일 이동
10. 테이블 스페이스
비 시스템테이블 스페이스 데이터파일 위치 이동
/oradata1/
Tbs_a_01.dbf
② 복사
/oradata3/
Tbs_a_01.dbf
테이블스페이스 오프라인
④
데이터베이스
정보 변경
테이블스페이스 온라인
⑤
이전 데이터 파일 삭제
rm /oradata1/tbs_a_01.dbf
165
15. 시스템 테이블스페이스의 데이터 파일 이동
시스템테이블 스페이스 데이터파일 위치 이동
①
/oradata1/
system.dbf
데이터베이스 마운트 상태
② 복사
/oradata3/
system.dbf
④
③
데이터베이스
정보 변경
데이터베이스 오픈
⑤
이전 데이터 파일 삭제
rm /oradata1/system.dbf
166
10. 테이블 스페이스
16. 테이블스페이스 관련 조회
10. 테이블 스페이스
SQL> SELECT * FROM V$TABLESPACE;
테이블스페이스 정보 검색
SQL> SELECT * FROM DBA_TABLESPACES;
검색
SQL> SELECT FILE_ID, FILE_NAME,
TABLESPACE_NAME,
BYTES, STATUS, AUTOEXTENSIBLE
데이터파일 정보 검색
FROM DBA_DATA_FILES;
SQL> SELECT FILE_ID, FILE_NAME,
TABLESPACE_NAME,
BYTES, STATUS, AUTOEXTENSIBLE
FROM DBA_TEMP_FILES;
167
Chapter11. 오라클 오브젝트
168
1. 테이블의 구성요소
11. 오라클 오브젝트
컬럼
행
사원번호
사원이름
근무부서
0010
0020
0030
…
최세진
조경민
이창구
…
개발팀
개발팀
컨설팅팀
…
사원 테이블
• 컬럼 : 테이블에 저장될 데이터의 특성을 지정하는 테이블의 구성요소
• 행 : 컬럼에 정의된 형식으로 저장된 데이터 한 건 한 건을 의미
169
2. 컬럼 타입의 종류
11. 오라클 오브젝트
컬럼 타입의 종류
• Blob - 4GB 이내의 바이너리 데이터 저장
• Clob - 4GB 이하 크기의 문자열 저장
• Date - 날짜 및 시간 저장
• Long - 2GB 이하 크기의 문자열 저장
• Number(a,b) - 숫자를 저장하는 컬럼 타입으로 a는 소수점 왼쪽 자릿수이며 b는 소수점
이후 자릿수를 표현
• Raw, Long Raw - 이미지 파일 및 비디오 파일과 같은 로우 바이너리 파일 저장
• Rowid - 로우 아이디 값 저장
• Timestamp(a) - 날짜 및 시간을 저장하며 a는 초 이하의 자릿수를 지정
• Varchar2(Size) - 문자열을 실제 문자열의 크기로 저장
• Char(Size) - 문자열의 크기를 Size 값으로 고정해서 저장
170
3. 테이블의 종류
11. 오라클 오브젝트
일반 테이블
파티션 테이블
인덱스 구조의 테이블
171
4. 테이블의 사용
11. 오라클 오브젝트
데이터 저장
일반 테이블
데이터 변경
데이터 제거
데이터 조회
172
5. 테이블 생성
11. 오라클 오브젝트
SQL> CREATE TABLE 사원
(
사원번호 CHAR(4) NOT NULL,
사원이름 VARCHAR2(20)
)
TABLESPACE USERS
PCTFREE 10 PCTUSED 80 INITRANS 3 MAXTRANS 255
STORAGE ( INITIAL 10M NEXT 10M PCTINCREASE 0
MINEXTENTS 1 MAXEXTENTS UNLIMITED)
;
173
6. 테이블 삭제, 재구성, 수정 및 절단
테이블 재구성
SQL> ALTER TABLE 사원
MOVE TABLESPACE TEST;
테이블 절단
SQL> TRUNCATE TABLE 사원;
테이블 삭제
SQL> DROP TABLE 사원;
컬럼 수정
SQL> ALTER TABLE table_name
MODIFY (column_name data_type);
174
11. 오라클 오브젝트
7. 테이블 컬럼 추가, 삭제 및 변경
컬럼 추가
SQL> ALTER TABLE table_name
ADD (column_name data_type);
컬럼 삭제
SQL> ALTER TABLE 사원 DROP COLUMN column_name
CASCADE CONSTRAINTS CHECKPOINT 1000;
비사용 컬럼으로 변경
SQL> ALTER TABLE 사원
SET UNUSED COLUMN column_name CASCADE CONSTRAINT;
테이블 정보 확인
SQL> SELECT * FROM DBA_TABLES;
SQL> SELECT * FROM DBA_OBJECTS;
175
11. 오라클 오브젝트
8. 인덱스 개념
11. 오라클 오브젝트
데이터베이스
테이블
테이블 조회 SQL
빠른 조회 성능 보장
인덱스
176
9. 인덱스 구조
11. 오라클 오브젝트
사원이름
등급
윤진이
권기흥
박경남
이슬기
580
600
550
530
ROWID
AACCBTABCAAAAB5AAA
AACCBTABCAAAAB5AAB
AACCBTABCAAAAB5AAC
AACCBTABCAAAAB5AAD
사원 테이블
create index emp_idx
on emp(ename);
검색
사원이름
권기흥
박경남
윤진이
이슬기
ROWID
AACCBTABCAAAAB5AAB
AACCBTABCAAAAB5AAC
AACCBTABCAAAAB5AAA
AACCBTABCAAAAB5AAD
사원이름 인덱스
177
10. B*TREE 인덱스
11. 오라클 오브젝트
<02500, 블록 주소 >
루트(Root) 블록
<04000,블록 주소>
브랜치(Branch) 블록
<03991, ROWID>
<03992, ROWID>
<03993, ROWID>
…
리프(Leaf) 블록
• 루트 블록(Root Block) - 분기할 수 있는 키 값과 하위 레벨인 브랜치 블록들에 대한 주소 값을 저장
• 브랜치 블록(Branch Block) - 분기할 수 있는 키 값과 하위 레벨인 리프 블록들에 대한 주소 값 저장
• 리프 블록(Leaf Block) - 실제 인덱스 키 컬럼 값과 ROWID를 저장
178
11. B*TREE 인덱스 사용
루트
11. 오라클 오브젝트
블록
<100, 블록 주소>
①
<150, 블록 주소>
브랜치 블록
<100, 블록 주소 >
②
리프 블록
<160, ROWID>
<170, ROWID>
…
<110, ROWID>
<120, ROWID>
…
<80, ROWID>
<90, ROWID>
…
③
부서번호 인덱스
사번
이름
110
211
212
권기흥
김윤희
김미경
…
• 온라인 업무에서와 같이 적은 로우의 데이터 엑세스 시 유리
부서 테이블
• 분포도가 나쁜 컬럼에 대해서는 성능 저하 발생 가능
• 하나의 로우 엑세스 시 어느 로우나 동일한 양의 블록 엑세스
179
12. 비트맵 인덱스
11. 오라클 오브젝트
• 분포도가 나쁜 컬럼에 대해서 성능 보장
• OR 연산에 대해서 효율적
Root
• DML에 대한 데이터 변경 시 부하 급증 가능
Branch
Leaf
180
13. 역 전환 키 인덱스
11. 오라클 오브젝트
인덱스 Key
테이블
사번
사번
부서
1477
7002
DM
1537
7116
DM
3587
7126
DCS
6217
7349
CC
6117
7741
OSS
7417
7216
CSBS
…
…
…
…
…
…
• 좌측 리프 블록에 대한 경합 해소
• 범위 스캔 시 인덱스 엑세스 불가
181
14. 함수 기반 인덱스
11. 오라클 오브젝트
SQL> select * from emp where upper(ename)=‘JAMES’;
SQL> select * from emp where abc(sal) > 1000;
 SQL> Create Index emp_idx on emp(upper(ename));
• 사용하는 이유: 위와 같이 인덱스를 계산된 column을 기준으로 만들 때 사용
• 사용할 수 있는 조건: compatible이 8.1.7 이상일 경우, query_rewrite_enable=true,
user에 대한 query rewrite 권한
SQL> select * from emp where sal > ‘1000’ ;
(X)
• 가공(계산)된 컬럼에 대한 인덱스 엑세스 가능 - 함수 등에 의해 Where 조건 절의 인덱스 컬럼
변경 시에도 인덱스 사용 가능
• DML 부하 증가 - 실제 인덱스에 데이터 저장 시 함수를 적용하여 저장해야 하므로 함수 수행에
의한 부하 증가
• 인덱스의 유연성 감소 - 인덱스로 만들어진 해당 함수가 Where 조건에 반드시 존재해야만 인덱
스 사용이 가능하므로 다른 조건들에 대해 해당 인덱스 사용 불가
182
15. 인덱스 생성
11. 오라클 오브젝트
B*트리 인덱스 생성
SQL> CREATE [UNIQUE] INDEX INDEX_NAME
ON TABLE_NAME(COLUMN_NAME)
PCTFREE n
INITRANS n
TABLESPACE TABLESPACE_NAME
STORAGE (INITIAL nM NEXT nM
PCTINCREASE n MAXEXTENTS n);
비트맵 인덱스 생성
SQL> CREATE BITMAP INDEX INDEX_NAME
ON TABLE_NAME(COLUMN_NAME);
역 전환 인덱스 생성
SQL> CREATE INDEX INDEX_NAME
ON TABLE_NAME(COLUMN_NAME) REVERSE;
함수 기반 인덱스 생성
SQL> CREATE INDEX INDEX_NAME
ON TABLE_NAME(함수(COLUMN_NAME));
183
16. 인덱스 제거 및 재구성
11. 오라클 오브젝트
인덱스 제거
SQL> DROP INDEX INDEX_NAME;
인덱스 재구성
SQL> ALTER INDEX INDEX_NAME REBUILD
TABLESPACE TABLESPACE_NAME
ONLINE PARALLEL n;
184
17. 뷰 개념 및 특징
11. 오라클 오브젝트
일부 컬럼에 대해 뷰 생성
뷰
사원 테이블
유저1
유저2
유저3
• 뷰는 실제 데이터를 저장하지 않음
• 뷰에는 인덱스를 생성할 수 없음
• 보안 및 성능 향상을 위해 제공
185
유저4
18. 뷰 관리
11. 오라클 오브젝트
뷰 생성
SQL> CREATE OR REPLACE NOFORCE VIEW 사원_VIEW
AS 서브쿼리;
뷰 재컴파일
SQL> ALTER VIEW 사원_VIEW COMFILE;
뷰 제거
SQL> DROP VIEW 사원_VIEW;
뷰 조회
SQL> SELECT * FROM DBA_VIEWS
186
19. 동의어 개념
11. 오라클 오브젝트
부서 테이블
사원 테이블
사원 동의어
관리자 유저
개발자 유저
187
부서 동의어
20. 동의어 관리
11. 오라클 오브젝트
동의어 생성
SQL> CREATE [PUBLIC] SYNONYM synonym_name
FOR object;
동의어 제거
SQL> DROP [PUBLIC] SYNONYM synonym_name;;
동의어 확인
SQL> SELECT * FROM DBA_SYNONYMS;
188
21. 시퀀스 개념
11. 오라클 오브젝트
사원번호 사원이름 부서번호
0001
0002
0003
0004
..
.
DB팀
개발팀
지원팀
인력팀
..
.
10
40
20
10
..
.
사원 테이블
시퀀스
중복되지 않은
유일한 사번 할당 필요
189
신입 사원 채용
22. 시퀀스 관리
11. 오라클 오브젝트
시퀀스 생성
SQL> CREATE SEQUENCE sequence_name
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
시퀀스 수정
SQL> ALTER SEQUENCE sequence_name
[INCREMENT BY n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
시퀀스 제거
SQL> DROP SEQUENCE sequence_name;;
시퀀스 확인
SQL> SELECT * FROM DBA_SEQUENCES;
190
23. 시퀀스 사용
11. 오라클 오브젝트
사원번호 사원이름 부서번호
0001
10
김윤희
0002
40
김희진
0003
20
이훈주
0004
10
김미경
..
..
..
.
.
.
시퀀스
NEXTVAL
사원 테이블
NEXTVAL 할당
CURRVAL
신입 사원 채용
191
Chapter12. 언두 데이터
192
1. 언두 데이터 개념
12. 언두 데이터
언두 테이블스페이스
부서 테이블
번호 팀명 위치
10
DM팀 서울
언두 데이터(서울)
언두 세그먼트
분당으로 변경
UPDATE
193
2. 언두 데이터의 목적
12. 언두 데이터
언두 테이블스페이스
작업 롤백
읽기 일관성
복구
언두 세그먼트
194
3. 언두 데이터에 의한 롤백
12. 언두 데이터
언두 테이블스페이스
부서 테이블
번호 팀명 위치
10
② 언두 데이터(서울)
DM팀 서울
⑤ 데이터 복구(서울)
③
분당으로 변경
언두 세그먼트
④
① UPDATE
롤백
195
4. 언두 데이터에 의한 읽기 일관성
12. 언두 데이터
언두 테이블스페이스
부서 테이블
번호 팀명 위치
④
10
DM팀 서울
③
②분당으로 갱신
①
조회
⑤
Commit 여부
UPDATE
결과
⑥예
ORA-1555 또는 10 DM팀 서울
⑦ 아니오
10 DM팀 서울
196
5. 복구(인스턴스 복구)
오라클
12. 언두 데이터
오라클
오라클
장애 발생
③
리두 로그 파일
②
리두 로그
언두 세그먼트
① 장애 시점 이후의
리두 로그 파일 적용
Commit 안된 데이터
Commit된 데이터
197
5. 언두 세그먼트의 특징
12. 언두 데이터
동일한 구조
테이블
언두 세그먼트
트랜잭션1
트랜잭션2
트랜잭션1
트랜잭션2
데이터 블럭
언두 세그먼트
198
6. 언두 세그먼트의 확장 및 축소
12. 언두 데이터
언두 세그먼트 확장
①
EX4
EX1
②
EX4
Ex3
EX2
EX3
언두 세그먼트 축소
EX1
EX2
언두 세그먼트 축소
④
EX5
③
EX1
EX4
EX2
EX3
EX4
EX3
EX1
1. 절단
2. 익스텐트 재할당
3. Optimal 크기
EX2
사용중인 익스텐트
사용하지 않는 익스텐트
199
7. 언두 세그먼트의 관리 방식
12. 언두 데이터
자동 관리
수동 관리
언두 테이블스페이스
오라클
언두 테이블스페이스
언두 관리 주체
200
데이터베이스
관리자
8. 언두 테이블스페이스 관리
12. 언두 데이터
언두 테이블스페이스 생성(데이터베이스 생성 시)
SQL> CREATE DATABASE test
……
UNDO TABLESPACE undo_tbs
DATAFILE ‘/oradata/test/undo01.dbf’ SIZE 1000M;
언두 테이블스페이스 생성(데이터베이스 생성과 별도로 생성 시)
SQL> CREATE UNDO TABLESPACE undo_tbs
DATAFILE ‘/oradata/test/undo01.dbf’ SIZE 1000M;
언두 테이블스페이스 변경
SQL> ALTER TABLESPACE undo_tbs
ADD DATAFILE ‘/oradata/test/undo02.dbf’ SIZE 1000M;
언두 테이블스페이스 제거
SQL> DROP TABLESPACE undo_tbs;
언두 테이블스페이스 교체
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undo_tbs;
201
Chapter13. 제약 조건
202
1. 제약 조건의 종류
NOT NULL
UNIQUE
13. 제약 조건
해당 컬럼에 NULL을 허용하지 않음
해당 컬럼의 값이 유일함을 만족
PRIMARY KEY
해당 컬럼의 값이 NOT NULL이며 UNIQUE를 만족
FOREIGN KEY
해당 컬럼에는 부모 테이블에 존재하는 값으로만 저장
CHECK
조건을 만족하는 값만 해당 컬럼에 저장
203
2. NOT NULL 제약 조건
13. 제약 조건
사원번호 사원이름 부서번호
0001
0002
0003
0004
..
.
김윤희
김희진
이훈주
김미경
..
.
10
40
20
10
..
.
사원 테이블
NOT NULL 컬럼
아니요
예
사원번호
컬럼이
NULL?
사원번호 컬럼
NOT NULL 확인
Insert 불가
데이터 Insert
204
3. UNIQUE 제약 조건
13. 제약 조건
사원번호 사원이름 부서번호
0001
0002
0003
0004
..
.
김윤희
김희진
이훈주
김미경
..
.
10
40
20
10
..
.
사원 테이블
UNIQUE 컬럼
예
사원번호+사원이
름이 UNIQUE?
사원
번호+사원
UNIQUE 확인
아니
요
Insert 불가
데이터 Insert
205
4. PRIMARY KEY 제약 조건
13. 제약 조건
사원번호 사원이름 부서번호
0001
0002
0003
0004
..
.
김윤희
김희진
이훈주
김미경
..
.
10
40
20
10
..
.
PRIMARY KEY 컬럼
예
아니요
사원번호+사원이
름이 UNIQUE?
사원번호+사원이름
UNIQUE 확인
예
아니요
사원번호+사원이
름이 NOT NULL?
Insert 불가
사원번호+사원이름
NOT NULL 확인
데이터 Insert
206
5. FOREIGN KEY 제약 조건
13. 제약 조건
부서 테이블
부서번호 부서이름
10
관리팀
20
인력팀
30
총무팀
40
기획팀
..
..
.
.
지역
서울
서울
서울
부산
..
.
사원 테이블
PRIMARY KEY
사원번호 사원이름 부서번호
Insert 수행
부서번호 존재
여부 확인
존재
부서 테이블에 해당
부서번호 존재 확인
0001
0002
0003
0004
..
.
김윤희
김희진
이훈주
김미경
..
.
10
40
20
10
..
.
FOREIGN KEY
존재하지 않음
사원 테이블에
데이터 Insert
Insert 불가
207
6. CHECK 제약 조건
13. 제약 조건
사원 테이블
사원번호 사원이름 부서번호
0001
0002
0003
0004
..
.
김윤희
김희진
이훈주
김미경
..
.
10
40
20
10
..
.
예
급여 > ‘200’을
만족하는가?
급여 >‘200’
아니요
Insert 불가
데이터 Insert
208
7. CHECK 제약 조건 변경 및 확인
제약 조건 생성
13. 제약 조건
제약 조건 활성화 및 비활성화
SQL> CREATE TABLE table_name
(column_name datatype [column_constraint],
…
[table_constraint]…);
SQL> ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;
SQL> ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;
제약 조건 추가
SQL> ALTER TABLE table_name
ADD [CONSTRAINT constraint_name] type (column);
제약 조건 확인
SQL> SELECT CONSTRAINT_NAME,
CONSTRAINT_TYPE,
SEARCH_CONDITION
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = ‘table_name’;
제약 조건 삭제
SQL> ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
SQL> ALTER TABLE table_name
DROP PRIMARY KEY;
209
Chapter14. 유저와 권한
210
1. 유저와 권한의 개념
14. 유저와 권한
사용자
시스템
데이터베이스로 접속
데이터베이스 조작
사용자
시스템
텔넷으로 접속
데이터베이스 유저와
권한
시스템 정보 파악
사용자
시스템
시스템으로 접속
시스템 접속 후 데이터베이스 접속
유저와 권한
데이터베이스 접속 후
데이터베이스 조작
■ 운영 체제 유저 - 해당 운영 체제에 접속하기 위한 사용자를 의미
■ 운영 체제 권한 - 해당 운영 체제 유저로 접속하여 작업을 수행하기
위해 필요한 요소
211
운영 체제 유저, 데이터베이스
유저 및 권한
2. 데이터베이스 유저 생성 및 변경
데이터베이스 유저 생성
SQL> CREATE USER user_name
IDENTIFIED BY password
DEFAULT TABLESPACE tablespace_name
TEMPORARY TABLESPACE temp_tablespace_name
QUOTA { integer [K|M] | UNLIMITED } ON tablespace_name;
데이터베이스 유저 변경
SQL> ALTER USER user_name
IDENTIFIED BY password
DEFAULT TABLESPACE tablespace_name
TEMPORARY TABLESPACE temp_tablespace_name
QUOTA { integer [K|M] | UNLIMITED } ON tablespace_name;
데이터베이스 유저 제거
SQL> DROP USER user_name [CASCADE];
212
14. 유저와 권한
3. 데이터베이스 권한의 종류
14. 유저와 권한
시스템 권한
오브젝트 권한
시스템 권한 종류
내용
오브젝트 권한 종류
CREATE USER
유저 생성 권한
SELECT ANY TABLE
모든 유저의 테이블 조회 권한
CREATE ANY TABLE
모든 유저의 테이블 생성 권한
CREATE SESSION
유저에게 접속 권한
CREATE TABLE
유저의 테이블 생성 권한
CREATE VIEW
뷰 생성 권한
CREATE PROCEDURE 프로시져 생성 권한
SYSDBA
데이터베이스 최고 관리 권한
SYSOPER
데이터베이스 관리 권한
213
테이블
뷰
ALTER
O
X
DELETE
O
O
INSERT
O
O
UPDATE
O
O
INDEX
O
X
SELECT
O
O
4. 데이터베이스 권한 부여 및 제거
시스템 권한 부여
SQL> GRANT 권한 TO user;
오브젝트 권한 부여
SQL> GRANT 권한 ON object_name TO user;
시스템 권한 제거
SQL> REVOKE 권한 ON object_name FROM user;
오브젝트 권한 제거
SQL> REVOKE 권한 ON object_name FROM user;
기타
SQL> GRANT 권한 TO user WITH ADMIN OPTION;
SQL> GRNAT 권한 ON object_name TO user
WITH GRANT OPTION;
SQL> GRANT 권한 ON object_name TO PUBLIC;
SQL> REVOKE 권한 ON object_name FROM PUBLIC;
214
14. 유저와 권한
5. 데이터베이스 롤 개념 및 관리
CREATE
SESSION
CREATE
TABLE
14. 유저와 권한
사용자1
롤(Role) 생성
롤1
사용자2
SQL> CREATE ROLE role_name;
CREATE
INDEX
사용자3
롤(Role)에 권한 부여 및 제거
CREATE
SESSION
사용자4
SQL> GRANT CREATE TABLE, CREATE SESSION
TO role_name;
SQL> REVOKE CREATE TABLE FROM role_name;
롤2
SELECT ANY
Dictionary
유저에 롤(Role) 부여
SQL> GRANT role_name TO user_name;
사용자5
215
6. 데이터베이스 권한 조회
14. 유저와 권한
권한 확인
SQL> SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
FROM DBA_SYS_PRIVS;
SQL> SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR
FROM DBA_TAB_PRIVS;
롤(Role) 확인
SQL> SELECT ROLE
FROM DBA_ROLES;
SQL> SELECT ROLE, PRIVILEGE
FROM ROLE_SYS_PRIVS;
SQL> SELECT ROLE, OWNER, TABLE_NAME,
COLUMN_NAME, PRIVILEGE
FROM ROLE_TAB_PRIVS;
216
Chapter15. 플래쉬백과 데이터 펌프
217
1. 플래쉬백 개념
15. 플래쉬백과 데이터 펌프
데이터베이스
테이블
커밋이 수행된 DML 복원
이전 시점으로
데이터 베이스 복구
삭제된 테이블 복구
플래쉬백을 이용하여 가능
218
2. 플래쉬백 종류
15. 플래쉬백과 데이터 펌프
플래쉬 백
데이터베이스 레벨
플래쉬백
데이터베이스
테이블 레벨
로우 레벨
플래쉬백 테이블
버전 쿼리
플래쉬백 삭제
트랜잭션 쿼리
219
3. 플래쉬백 데이터베이스
15. 플래쉬백과 데이터 펌프
SGA
공유 풀
데이터 버퍼 캐쉬
리두 로그 버퍼
플래쉬백 버퍼
① 과거 시점으로 복구
RVWR
LGWR
② 원하는 시점으로 복구
플래쉬백 로그 파일
리두 로그 파일
220
4. 플래쉬백 삭제
15. 플래쉬백과 데이터 펌프
플래쉬백 삭제
SQL> FLASHBACK TABLE 사원
TO BEFORE DROP;
SQL> DROP TABLE 사원;
휴지통
사원 테이블
BIN$aGfsdf=$0
사원_PK 인덱스
BIN$bdfrgW=$0
SQL> DROP TABLE 사원;
221
5. 플래쉬백 테이블
15. 플래쉬백과 데이터 펌프
변경된 데이터
10:30
SQL> DELETE 사원
WHERE ……
11:00
SQL> FLASHBACK TABLE TO TIMESTAMP …;
플래쉬백 테이블
222
5. 버전 쿼리 및 트랜잭션 쿼리
버전 쿼리
15. 플래쉬백과 데이터 펌프
SQL> SELECT VERSION_STARTTIME, VERSION_ENDTIME, VERSION_XID
VERSION_OPERATION, ENAME
FROM 사원
VERSIONS BETWEEN
TIMESTAMP TO_DATE('06/11/2005 03:07:14','mm/dd/yyyy hh24:mi:ss')
AND TO_DATE('06/11/2005 03:07:35','mm/dd/yyyy hh24:mi:ss')
ORDER BY VERSIONS_STARTTIME;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID
트랜잭션 쿼리
V
ENAME
11-JUN-05 03.07.22
11-JUN-05 03.07.24 0002101A00500C61 I
이슬기
11-JUN-05 03.07.30
11-JUN-05 03.07.32 000A101C00700029 U
김지한
11-JUN-05 03.07.33
11-JUN-05 03.07.35 000A101B00700029 U
황오현
SQL> SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = HEXTORAW('000A101C00700029');
UNDO_SQL
UPDATE scott.emp SET ENAME='김지한'
WHERE ROWID= 'AAAB43ABBAAABBBAAA';
223
6. 데이터 추출 및 적재
15. 플래쉬백과 데이터 펌프
데이터베이스1
데이터베이스2
사원
테이블
사원
테이블
① 데이터 이동
② 데이터 추출
③ 데이터 적재
사원 테이블
데이터
사원 테이블
데이터
항목
Export/Import
Data Pump
SQL Loader
수행 속도
느림
빠름
빠름
이동
가능
가능
불가능
추출
가능
가능
가능
적재
가능
가능
불가능
224
7. Export
15. 플래쉬백과 데이터 펌프
>exp scott/tiger FILE=emp.dmp LOG=emp.log TABLES=emp DIRECTt=y
>exp system/manager FILE=full.dmp FULL=y INDEXES=n TRIGGERS=n
>exp system/manager FILE=scott.dmp onwer=scott ROWS=n
■ scott/tiger, system/manager - 데이터베이스 유저 및 비밀번호
■ FULL - 해당 데이터베이스의 전체 데이터 추출 여부(기본 값은 N)
■ BUFFER - 작업 단위의 크기 설정
■ OWNER - 데이터베이스 유저별 오브젝트 추출 설정
■ FILE - 추출한 데이터를 저장할 파일 이름 설정
■ TABLES - 데이터를 추출할 대상 테이블 설정
■ COMPRESS - 익스텐트 통합 여부를 지정(기본 값은 Y)
■ GRANTS - 오브젝트 권한 설정에 대한 정보 추출 여부(기본값은 Y)
■ INDEXES - 인덱스 스크립트 추출 여부(기본 값은 Y)
■ DIRECT - 직접 경로로 Export 수행 여부(기본 값은 N)
■ TRIGGERS - 트리거 정보 추출 여부(기본 값은 Y)
■ LOG - 로그를 저장할 파일 지정
■ ROWS - 테이블의 데이터 추출 여부(기본 값은 Y)
■ CONSISTENT - 대상 테이블의 읽기 일관성 지정(기본 값은 N)
■ CONSTRAINTS - 제약 조건의 추출 여부(기본 값은 Y)
■ PARFILE - 필요한 옵션을 파라메터 파일에 설정한 후 해당 파라메터 파일을 Export시 적용
225
8. Import
15. 플래쉬백과 데이터 펌프
> imp scott/tiger FILE=emp.dmp LOG=emp.log TABLES=emp
> imp system/manager FILE=full.dmp LOG=full.log FULL=y
> imp system/manager FILE=scott.dmp FROMUSER=scott TOUSER=mike
■ system/manager, scott/tiger - 데이터베이스 유저 및 비밀번호
■ FULL - 전체 데이터에 대해 Import 여부(기본 값은 N)
■ BUFFER - 작업 단위의 크기 지정
■ FROMUSER - 적재하는 테이블의 소유자 지정
■ TOUSER - 적재되는 테이블의 소유자 지정
■ TABLES - 적재 대상 테이블 지정
■ IGNORE - 적재 대상 테이블이 존재할 경우 에러 발생 여부(기본 값은 N)
■ GRANTS - 권한 적재 여부 지정(기본 값은 Y)
■ INDEXES - 인덱스 생성 여부(기본 값은 Y)
■ COMMIT - 적재 작업 수행 중 커밋 수행 여부이며 Y로 지정한 경우 BUFFER 옵션 단위로
커밋 수행(기본 값은 N)
■ ROWS - 테이블의 데이터 적재 여부(기본 값은 Y)
■ LOG - 로그를 저장할 파일 지정
■ CONSTRAINSTS - 제약 조건 적재 여부(기본 값은 Y)
■ PARFILE - 적재 작업의 옵션을 설정한 파라메터 파일을 지정하여 적용
226
9. SQL Loader
15. 플래쉬백과 데이터 펌프
컨트롤 파일 생성(controlfile.ctl)
LOAD DATA
INFILE '/oracle/DBA/적재.txt' BADFILE '/oracle/DBA/적재.bad‘ DISCARDFILE '/oracle/DBA/적재.dis'
INTO TABLE 사원
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
("번호"
CHAR(30),
"이름"
CHAR(10),
"급여"
CHAR(10))
SQL Loader 수행
> sqlldr system/manager controlfile = controlfile.ctl log = /oracle/DBA/적재.log
■ USERID - 데이터베이스 유저 및 비밀번호
■ CONTROLFILE - 설정 값을 저장하고 있는 컨트롤 파일 이름
■ LOGFILE - 로그 파일 이름
■ DATA - 데이터 파일 이름
■ DIRECT - 직접 경로로 적재 수행 여부(기본 값은 N)
■ PARALLEL - 병렬 프로세싱 사용 옵션
■ INFILE - 적재할 파일 이름
■ BADFILE - 입력 형식이 부적합한 로우에 대해 로그를 기록하는 파일
■ DISCARDFILE - 컨트롤 파일에 지정된 컬럼 선택 기준과 일치하지 않는 로우에 대해 기록
227
10. 데이터 펌프
15. 플래쉬백과 데이터 펌프
테이터 펌프 Export
> expdp system/oracle DIRECTORY=pump_dir2 DUMPFILE=full.dmp \
LOGFILE=full.log FULL=y
> expdp system/oracle DIRECTORY=pump_dir2 DUMPFILE=scott_hr.dmp \
LOGFILE=scott_hr.log SCHEMAS=scott,hr
> expdp scott/tiger DIRECTORY=pump_dir1 DUMPFILE=scott_emp.dmp \
LOGFILE=scott_emp.log TABLES=emp
> expdp system/oracle DIRECTORY=pump_dir1 DUMPFILE=users_ts.dmp \
logfile=users_ts.log TABLESPACES=users
테이터 펌프 Import
> impdp system/oracle DIRECTORY=pump_dir2 DUMPFILE=full.dmp \
LOGFILE=full.log FULL=y
> impdp system/oracle DIRECTORY=PUMP_DIR2 DUMPFILE=scott_hr.dmp \
LOGFILE=scott_hr.log SCHEMAS=scott,hr
> impdp scott/tiger DIRECTORY=pump_dir1 DUMPFILE=scott_emp.dmp \
LOGFILE=scott_emp.log TABLES=emp
> impdp system/oracle DIRECTORY=pump_dir1 DUMPFILE=users_ts.dmp \
LOGFILE=users_ts.log TABLESPACES=users
228
11. 데이터 펌프 옵션
15. 플래쉬백과 데이터 펌프
■ system/oracle, scott/tiger - 데이터베이스 유저 및 비밀번호
■ DIRECTORY - 데이터 펌프 파일을 저장하거나 또는 저장되어 있는 디렉토리
■ DUMPFILE - 데이터 펌프에 의한 추출 파일 또는 적재 파일 이름
■ LOGFILE - 로그가 저장될 파일 이름
■ FULL - 데이터베이스 전체에 데이터 펌프 적용
■ SCHEMAS - 설정된 데이터베이스 유저가 소유한 오브젝트에 대해 데이터 추출
■ TABLES - 명시된 테이블에 대해서만 데이터 추출
■ TABLESPACES - 명시된 테이블스페이스에 저장된 오브젝트에 대해서만 데이터 추출
229
12. 데이터 펌프 고급 옵션
병렬 프로세싱
15. 플래쉬백과 데이터 펌프
여러 개의 프로세스 사용
> expdp system/oracle DIRECTORY=pump_dir2 \
LOGFILE=full.log FULL=y PARALLEL=4 \
DUMPFILE=full01.dmp,full02.dmp,full03.dmp,full04.dmp \
필터링 기법
원하는 오브젝트만 추출
> expdp scott/tiger DIRECTORY=pump_test \ DUMPFILE=test.dmp
EXCLUDE=TABLE: "= 'EMP'"
선택 추출
데이터만 또는
오브젝트 정의만 추출
(Data_Only : 데이터만, METADATA_ONLY : 정의만, ALL : 모두)
> expdp scott/tiger DIRECTORY=pump_test dumpfile=test.dmp \
LOGFILE=test.log CONTENT=DATA_ONLY
오브젝트 정의 변경
소유자 또는
테이블스페이스등을 변경
> impdp system/manager DIRECTORY=pump_test \
DUMPFILE=scott.dmp REMAP_SCHEMA='SCOTT':'MIKE'
> impdp system/manager DIRECTORY=pump_test \
DUMPFILE=scott.dmp REMAP_TABLESPACE='USERS':'TOOLS'
> impdp system/manager DIRECTORY=pump_test \
230