퐁숑퐁숑

오늘은 예전에 OCP 자격증을 취득하기위해 공부했던 내용 중 TABLESPACE에 관한 내용을 정리해보고자 한다. Tablespace 실습 및 내용을 정리하기 전에 간단히 테이블 스페이스 개념을 집고 넘어가려 한다. 테이블 스페이스는 테이블을 생성할 수 있는 물리적인 파일이다. 데이터베이스는 데이터를  테이블에 담는다. 그 테이블들은 어디에 담기게 되는것일까? 바로  물리적 파일인 테이블 스페이스에 담기게 되는 것이다. 데이터베이스를 만드는 것은 테이블스페이스를 만드는 것이라 보아도 되겠다.

Tablespace 실습 및 내용 정리.

[Tablespace Syntax]

CREATE[undo|tempoary|smallfile] TABLESPACE테이블스페이스 이름
DATAFILE[tempfile]'/경로/파일 이름1.dbf' SIZE integer [T/G/M/K] [reuse]
[autoextend on next n [M] maxsize n [[M]| unlimnited]
,'파일 이름2' SIZE integer [M/K]...
[ENCRYPTION USING '3DES168' DEFAULT STORAGE (ENCRYPTION) ->11gR1~
[MINIMUM EXTENT integer [M/K]]
[BLOCK integer [k]]
[DEFAULT STORAGE(
INITIAL integer [M/K]
NEXT integer [M/K]
MAXEXTENTS integer
PCTINCREASE integer)]
[ONLINE|OFFLINE]
[LOGGING|NOLOGGING]
[PERMANENT|TEMPORARY]
[EXTENT MANAGEMENT
DICTIONARY|LOCAL
AUTOALLOCATE|UNIFORM SIZE integer [M/K]]
[SEGMENT SPACE MANAGEMENT (MANUAL/AUTO)]

- 테이블스페이스 생성하기

SYS> create tablespace sales_tbs
2 datafile '/u01/oradata/orcl/sales_tbs01.dbf' size 10m
3 autoextend on next 1m maxsize 100m
4 online logging
5 extent management local uniform size 128k
6 segment space management auto;

Tablespace created.

- sales_tbs 테이블 스페이스에 table생성
SYS> create table hr.sales00
2 (order# number(10) primary key, order_date date default sysdate)
3 tablespace sales_tbs;

Table created.

- 데이터 입력

SYS> begin
2 for i in 1..1000 loop
3 insert into hr.sales00 (order#) values (i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

-- EM을 통해 Sales_tbs의 tablespace용량이 늘어나는 것을 확인할 수 있다.

SYS> ed
Wrote file afiedt.buf

1 begin
2 for i in 1001..10000 loop
3 insert into hr.sales00 (order#) values (i);
4 end loop;
5 commit;
6* end;
SYS> /

PL/SQL procedure successfully completed.

--Viewing Tablespace Information(사용현황조회)

SYS> select * from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
EXAMPLE 8952 4194302 .213432414
SALES_TBS 64 12800 .5
SYSAUX 32632 4194302 .778007878
SYSTEM 60976 4194302 1.45378182
TEMP 0 4194302 0
UNDOTBS1 296 4194302 .007057193
USERS 840 4194302 .02002717

7 rows selected.

SYS>select tablespace_name from DBA_TABLESPACES;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
INVENTORY
RMAN_TBS
UTEST02
UTEST03
UTEST04

TABLESPACE_NAME
------------------------------
UTEST05

12 rows selected.

-- tablespace 공간 변경 3가지 방법

SYS> --1. add datafile
SYS> alter tablespace inventory
2 add datafile '/u01/oradata/orcl/inventory02.dbf' size 10m;

Tablespace altered.

SYS> --2. resize
SYS> alter database datafile '/u01/oradata/orcl/inventory02.dbf' resize 20m;
Database altered.

SYS> --3. autoextend
SYS> alter database datafile '/u01/oradata/orcl/inventory02.dbf'
2 autoextend on next 1m maxsize 50m;

Database altered.

--tablespace 이동(datafile)

1. System TBS (offline 설정 안됨)

1) shutdown immediate
2) cp를 통해 복사(이동) - 물리적
3) startup mount
4) alter database rename file '원래' to '새로운'; - 논리적
5) alter database open;
6) 이전 file 삭제

2. Non-System TBS

1) 해당 TBS. offline 설정
2) cp를 통해 복사(이동) - 물리적
3) alter tablespace XXXX rename datafile '원래' to '새로운' ; - 논리적
4) 해당 TBS online설정
5) 이전 file삭제

-- tablespace삭제하기

1. TBS가 비어있는 경우
SQL> drop tablespace XXXXX;

2. TBS에 Object가 존재하는 경우 포함 삭제
SQL> drop tablespace XXXX including contents;

3. OSfile 동반삭제
SQL> drop tablespace XXXX including contents and datafiles;

4. 다른 TBS의 Object와 참조무결성 제약조건에 연결되어 있는 경우.
SQL> drop tablespace XXXX including contents and datafiles cascade constraints;

--close backup

ocp@orcl : /home/oracle> ls
20100823-3.sql Desktop insert.sql lob_04_04.sql sqlnet.log
afiedt.buf dict.sql inst.sql lock_trace.sql test1.sql
backup emp10000.sql jazn.sh lock_trace_user.sql v_scan.sql
const.sql hidden.sql lab_01_07.sql orcl_start.sh xbh.sql
creuser.sql holding.sql labs practice01_02.sql
ocp@orcl : /home/oracle> cd backup
ocp@orcl : /home/oracle/backup> ls
pfile_backup
ocp@orcl : /home/oracle/backup> mkdir close open rman rman_con archive
ocp@orcl : /home/oracle/backup> ls
archive close open pfile_backup rman rman_con
ocp@orcl : /home/oracle/backup> cd close/
ocp@orcl : /home/oracle/backup/close> cp /u01/oradata/orcl/* ./
cp: omitting directory `/u01/oradata/orcl/arch1'
ocp@orcl : /home/oracle/backup/close>

################################
############실습################
################################

1. WS1 Tablespace 생성 관리 실습

########################################
#####실습 1 데이터베이스의 저장구조##########
########################################

SYS>!t_scan.sql
/bin/bash: t_scan.sql: command not found

--자주 사용함으로 스크립트 생성

SYS>!vi t_scan.sql

SYS>!cat t_scan.sql
col tablespace_name format a15;
col file_name format a45;
select tablespace_name,status,contents,
extent_management,segment_space_management
from dba_tablespaces;

SYS>@t_scan

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN
--------------- --------- --------- ---------- ------
SYSTEM onLINE PERMANENT LOCAL MANUAL
UNDOTBS1 onLINE UNDO LOCAL MANUAL
SYSAUX onLINE PERMANENT LOCAL AUTO
TEMP onLINE TEMPORARY LOCAL MANUAL
USERS onLINE PERMANENT LOCAL AUTO
EXAMPLE onLINE PERMANENT LOCAL AUTO
INVENTORY onLINE PERMANENT LOCAL AUTO
INSA onLINE PERMANENT LOCAL AUTO
RMAN_TBS onLINE PERMANENT LOCAL AUTO

9 rows selected.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa03.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /home/oracle/backup/insa02.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

SYS>select tablespace_name,bytes,file_name from dba_temp_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
TEMP 41943040 /u01/oradata/orcl/temp01.dbf

########################################
실습 2 사용자용 테이블스페이스 생성#####
########################################

SYS>drop tablespace insa including contents and datafiles cascade constraints;

Tablespace dropped.

SYS>create tablespace insa
2 datafile '/u01/oradata/orcl/insa01.dbf' size 1M
3 segment space management auto;

Tablespace created.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 1048576 /u01/oradata/orcl/insa01.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

8 rows selected.

SYS>@t_scan.sql

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN
--------------- --------- --------- ---------- ------
SYSTEM onLINE PERMANENT LOCAL MANUAL
UNDOTBS1 onLINE UNDO LOCAL MANUAL
SYSAUX onLINE PERMANENT LOCAL AUTO
TEMP onLINE TEMPORARY LOCAL MANUAL
USERS onLINE PERMANENT LOCAL AUTO
EXAMPLE onLINE PERMANENT LOCAL AUTO
INVENTORY onLINE PERMANENT LOCAL AUTO
INSA onLINE PERMANENT LOCAL AUTO
RMAN_TBS onLINE PERMANENT LOCAL AUTO

9 rows selected.

##########################################
실습 3 : 사용자용 테이블스페이스 확장#####
##########################################

SYS>alter database datafile
2 '/u01/oradata/orcl/insa01.dbf' resize 2M;

Database altered.

SYS>select tablespace_name, bytes/1024/1024,
2 file_name from dba_data_files;

TABLESPACE_NAME BYTES/1024/1024 FILE_NAME
--------------- --------------- ---------------------------------------------
USERS 6.25 /u01/oradata/orcl/users01.dbf
SYSAUX 260 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 35 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 490 /u01/oradata/orcl/system01.dbf
EXAMPLE 100 /u01/oradata/orcl/example01.dbf
INSA 2 /u01/oradata/orcl/insa01.dbf
INVENTORY 100 /u01/oradata/orcl/inventory
RMAN_TBS 100 /u01/oradata/orcl/rman01.dbf

8 rows selected.

SYS>alter tablespace insa
2 add datafile '/u01/oradata/orcl/insa02.dbf' size 2M;

Tablespace altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

9 rows selected.

SYS>alter tablespace insa add datafile
2 '/u01/oradata/orcl/insa03.dbf' size 2M
3 Autoextend on Next 1M Maxsize 10M;

Tablespace altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

##########################################
실습 4 : 사용자용(일반) 데이터 파일 이동##
##########################################

SYS>alter tablespace insa offline;

Tablespace altered.

SYS>!cp /u01/oradata/orcl/insa02.dbf /home/oracle/backup/

SYS>alter tablespace insa rename
2 datafile '/u01/oradata/orcl/insa02.dbf'
3 to '/home/oracle/backup/insa02.dbf';

Tablespace altered.

SYS>alter tablespace insa online;

Tablespace altered.

SYS>!rm /u01/oradata/orcl/insa02.dbf

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

#################################################
실습 5 : System 테이블스페이스 데이터 파일 이동##
#################################################

SYS>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>!cp /u01/oradata/orcl/system01.dbf /home/oracle/backup/system01.dbf

SYS>startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.
SYS>alter database rename
2 file '/u01/oradata/orcl/system01.dbf'
3 to '/home/oracle/backup/system01.dbf';

Database altered.

SYS>!rm /u01/oradata/orcl/system01.dbf

SYS>alter database open;

Database altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /home/oracle/backup/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

==> System Tablespace를 다시 원래 경로로 바꾸기!

SYS>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>!cp /home/oracle/backup/system01.dbf /u01/oradata/orcl/system01.dbf

SYS>startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.

SYS>alter database rename
2 file '/home/oracle/backup/system01.dbf'
3 to '/u01/oradata/orcl/system01.dbf';

Database altered.

SYS>!rm /home/oracle/backup/system01.dbf

SYS>alter database open;

Database altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

#################################################
실습 6. 테이블 스페이스의 삭제###################
#################################################

SYS>drop tablespace insa
2 including contents and datafiles cascade constraints;

Tablespace dropped.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

7 rows selected.

2. 1552 WS1-1-ch05. Creation Tablespace LAB (OCM-1)

문제1. dictionary를 생성할 수 없음 local로 생성 가능.

문제2.

SYS>create tablespace utest02
2 datafile '/home/oracle/backup/tstest/utest02.dbf' size 1m reuse
3 autoextend on next 1m maxsize 10m
4 extent management local uniform size 100k;

Tablespace created.

문제3.

SYS>create tablespace utest03
2 datafile '/home/oracle/backup/tstest/utest03.dbf' size 1m reuse
3 extent management local uniform size 100k;

Tablespace created.

문제4.

SYS>create tablespace utest04
2 datafile '/home/oracle/backup/tstest/utest04.dbf' size 2m reuse
3 extent management local autoallocate
4 segment space management auto;

Tablespace created.

문제5.

SYS>create temporary tablespace utest05
2 tempfile '/home/oracle/backup/tstest/utest05.dbf' size 10m reuse
3 extent management local uniform size 1m;

Tablespace created.

--tablespace 생성 확인

SYS>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ocp@orcl : /home/oracle> cd backup/tstest
ocp@orcl : /home/oracle/backup/tstest> ls
utest02.dbf utest03.dbf utest04.dbf utest05.dbf 

1. USER

Database 내에서 Username은 Unique(중복되지 않아야)해야 한다.

User create syntax

CREATE USER user_name

IDENTIFIED {BY password | EXTERNALLY | GLOBALLLY} => 인증

(OS인증방식) (제3의 App을 이용한 인증 ex: Directory/LDAP)

[DEFAULT TABLESPACE tablespace] - 지정 안하면 USER TABLESPACE로 지정

[TEMPORARY TABLESPACE tablespace] - default temporary tablespace : temp

[QUOTA {integer[K|M] | UNLIMITED} on tablespace -Tablespace에서 사용할 수 있는 할당값

[QUOTA {integer[K|M] | UNLIMITED} on tablespace]...]

[ACCOUNT LOCK | UNLOCK] - default UNLOCK

[PASSWORD EXPIRE] - 사용자가 로그인시 passwd만료를 표시해주고 바꾸도록 해주는 옵션

[PROFILE {Profile | DEFAULT}]

- default로 DEFAULT라는 이름의 profile이 있음 자원제한 9개 패스워드제한 6개

CREATE문을 ALTER로 바구면 수정문이 됨

EM에서 사용자 생성시 CONNECT권한 자동 부여

데스알고리즘(DES)으로 오라클의 password를 복구 가능 11g부터는 password컬럼에 내용이 보안상 안보임

사용자 삭제시 사용자가 로그인 시에는 DROP이 안됨!!

사용자가 object 소유시에는 cascade 옵션을 사용해서 DROP시켜야 함!!

SQL> alter database default tablespace 테이블 스페이스 명; - 해주면 Default tablespace가 바뀜

SQL> alter database default temporary tablespace 테이블 스페이스 명;

2. ROLE

CREATE ROLE rolename

role은 일반 사용자에게 권한ㅇ르 부여 및 회수 하듯이 롤에 권한을 부여, 회수할 수 있다.

시스템 권한처럼 사용자나 롤로부터 부여 및 회수가 가능하다.

시스템 권한(System Privilege)과 객체 권한(Object Privilege)으로 구성 될 수 있다.

각 사용자에게 부여된 롤은 활성화 되거나 비 활성화 될 수 있따.

role은 활성화하기 위해 암호가 필요 할 수 있다.

role은 특정 사용자가 소유하는 것이 아니기 때문에 어떤 스키마에도 저장되지 않는다.

권한부여

GRANT privilege on [user.]object TO USER[or ORLENAME] = object privilege

GRANT privilege TO USER[orPROLENAME] = system privilege

ALTER user HR default role {role_name | none};

SET ROLE {vacationdba | all | none};

CREATE ROLE secure_application_role

IDENTIFIED USING <security_procedure_name>; pl/sql로 만든 프로그램을 이용한 롤사용

3. profiles(자원제한 설정)

why?? 한정된 자원을 효과적으로 사용하기 위해

SQL>select * from dba_profiles;

session level = limit(한계) 초과시 session이 강제로 log out 당함 (error message 뜸)

call level = limit초과시 해당 작업이 중단됨(error message 뜸)

자원 제한이 설정되어 있을 때 실행 계획에 의해서 실행과 동시에 작업을 안하고 error를 발생시킴

현재 등록 되어 있는 profiles 조회

SQL> select * from dba_profiles;

PROFILE 만들기

CREATE PROFILE profilename LIMIT

CPU_PER_SESSION - 1/100 초 단위로 측정한 총 CPU이용시간

SESSIONS_PER_USER - 각 사용자마다 허용된 동시 세션의 수

CONNECT_TIME - 분 단위로 측정한, 경과된 접속시간

IDLE_TIME -분 단위로 측정한 비활동 시간
: 서버 프로세스에 대해서만 IDLE_TIME이 계산됩니다.
응용프로그램 작업은 포함되지 않습니다.
IDLE_TIME은 오랜시간 수행되는 질의나 다른 작업들은 포함하지 않습니다.

LOGICAL_READS_PER_SESSION - 데이터 블록 수 (물리적:디스크로부터,논리적:메모리로부터 읽은)
: LOGICAL_READS_PER_SESSION은 메모리와 디스크 모두 에게서
읽는 총 횟수를 제한 합니다.

PRIVATE_SGA - 바이트 단위로 측정한 SGA 내의 전용(private) 공간(MTS만)
: PRIVATE_SGA는 다중 스레드 서버(MTS) 구성 일 때만 적용됩니다.

CPU_PER_SEESION - 하나의 Call 당 CPU 사용시간

SESSIONS_PER_USER - 한 Call 당 I/O를 할 수 있는 데이터 블록 수

COMPOSITE_LIMIT - cpu_per_session, read_per_session, connect_time, private_sga의 합이 설정한 수치를 넘어가면 제한

패스워드 제한설정

권장 : 30일 마다 변경 권장

PASSWORD history : 이전 암호를 기억해 놓았다가 다음에 변경시 동일한 암호사용을 금지함

password_reuse_time : 동일한 password를 적용한 기간동안 사용금지

password_reuse_max : 입력된 value값만큼만 사용가능한 횟수를 제한 ex: 3이라고 입력하면 3번만 사용가능

password_life_time : password 생명주기 ex : 30 -> 30일마다 변경해야함

password_grace_time : password 변경 만료 알림을 value일 전부터 알림

failed_login_attempts : password 입력실패시 재시도 가능횟수 최종 실패시 계정 lock걸림

password_lock_time : lock걸렸을때 value값만큼 잠겨있음 1일단위임 1/24는 1시간 1/1440은 1분

password complexity verification(패스워드 복잡성)password설정시 제약조건

VERIFY_FUNCTION password 복잡도 함수

rdbms/admin/utlpwdmg.sql (이경로에 관리용 스크립트가 700여개 존재)-수정가능

scripts 실행

해제시

SQL>alter profile default limit password_verify_function null

password_reuse_time unlimited

password_reuse_max unlimited;

SQL>drop fuction verify_function;

ORACLE error코드중 20001~21000은 비어있는데 사용자정의 오류코드로 사용자가 만들어 쓴다

지금까지 개발 중에 마스터성 테이블의 데이터를 건들기전에 엑셀파일로 export 해서 백업해놓고 변경해서 사용하곤 했다. 근데 더 간단하게 테이블을 생성해서 백업해놓고 데이터 변경한 후 필요없으면 테이블 삭제하면 좀더 간단한 것 같아 정리해 본다.

1. 백업하고 싶은 테이블을 다른 명으로 해서 만든다

==> create table ppp_role_management_bak as select * from ppp_role_management

    요렇게 하면 ppp_role_management_bak으로 원본 테이블이 복사되어 백업되어진다.

    이렇게 만들어 놓고 ppp_role_management를 수정 하다 혹시 문제가 생기면 ppp_role_management_bak의

    테이블 명을 바꾸어서 사용하면 된다.

2. 백업해 놓은 테이블이 필요 없어지면 삭제한다.

==> drop table ppp_role_management_bak

요기서 DB 테이블 명 및 테이블 컬럼 이름 바꾸는 명령어를 정리해 보고자 한다.

1. DB 테이블 변경하기

ALTER TABLE 현재 테이블 이름 RENAME TO 바꾸고자 하는 테이블 이름

예를 들면

ALTER TABLE ppp_role_management(현재 사용 테이블) RENAME TO ppp_role_management_bak(바꾸고자 하는 테이블 명)

2. DB 테이블 컬럼 수정하기

ALTER TABLE 바꾸고자 하는 테이블 명 RENAME COLUNM 현재 테이블의 컬럼(필드명) TO 바꾸고자 하는 컬럼(필드명)

예를 들면

ALTER TABLE ppp_role_management(컬럼명을 바꾸고자 하는 테이블) RENAME COLUNM ppp_role_id(현재 사용되고 있는 테이블 컬럼명) TO  role_id(현재 사용되고 있는 테이블에 바꾸고자 하는 커럼명)

근데 오렌지나 ,TOAD 혹은 SQL DEVELOPER를 사용한다면 명령어 알 필요 없을 듯 하다 !

Oracle 10g를 리눅스에서 설치하던 기억이 난다. 오라클 양성반 과정에서 오라클 설치를 위한 커널 설정부터

필요한 패키지 설정까지 다 외우고 시험보던 기억들이 새록새록 하다. 오랫만에 리눅스 기반에서 Oracle 10g 설치 방법을 정리해 보았다. 

1. Hosts File

# vi /etc/hosts

127.0.0.1 expert.oracle.com~

192.168.100.100 expert.oracle.com~

2. Kernel Parameters 설정

# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 2147483648

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

3. Kernel Parameters를 변경 적용

[root@ocm52 ~]# /sbin/sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 2147483648
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144 

4. /etc/security/limits.conf 파일에 아래의 내용을 추가.

[root@ocm52 ~]# vi /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

5. /etc/pam.d/login 파일에 아래의 내용이 없다면 추가

[root@ocm52 ~]# vi /etc/pam.d/login 

session required pam_selinux.so open
session optional pam_keyinit.so force revoke
session required /lib/security/pam_limits.so

6. /etc/selinux/config 파일에 아래의 내용을 변경.

[root@ocm52 ~]# vi /etc/selinux/config

SELINUX=disabled (enforce를 disable로 변경)

7. RedHat Enterprise linux AS에 오라클 설치시 필요한 package

(oracle 설치 전 반드시 조회한 후 각 CD를 이용하여 설치되지 않은 패키지를 설치해야 함.)

=> 대부분의 패키지가 전부 설치 되어있으니 확인만 하면 됨.

=> RPM 조회 : rpm -qa | grep 패키지명

#From RedHat AS4 Disk 2

cd /media/media/Enterprise linux cd | 20071115/server

[root@ocm52 Server]# rpm -Uvh compat-db-4.2.52-5.1.i386.rpm
warning: compat-db-4.2.52-5.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:compat-db ########################################### [100%]
[root@ocm52 Server]# cd
[root@ocm52 ~]# cd /mnt/hgfs/shared
[root@ocm52 shared]# ls
10201_database_linux32.zip libaio-devel-0.3.107-2.i386.rpm
[root@ocm52 shared]# rpm -Uvh libaio-devel-0.3.107-2.i386.rpm
Preparing... ########################################### [100%]
1:libaio-devel ########################################### [100%]

8. Create the new groups and user(새로운 사용자 및 그룹 추가)

[root@ocm52 shared]# groupadd dba
[root@ocm52 shared]# groupadd oinstall
[root@ocm52 shared]# groupadd oper
[root@ocm52 shared]# groupadd asmadmin
[root@ocm52 shared]# useradd -g oinstall -G dba,oper,asmadmin oracle
[root@ocm52 shared]# passwd oracle
Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@ocm52 shared]# mkdir -p /u01/app/oracle/product/10.2.0/db_1
[root@ocm52 shared]# chown -R oracle.oinstall /u01
[root@ocm52 shared]# more /etc/passwd | grep oracle
oracle:x:502:503::/home/oracle:/bin/bash

9. 오라클 설치할 폴더 생성

[root@ocm52 shared]# su - oracle
[oracle@ocm52 ~]$ mkdir -p /u01/app/oracle/product/10.2.0
[oracle@ocm52 ~]$ mkdir -p /u01/app/oracle/tmp
[oracle@ocm52 ~]$ mkdir -p /u01/app/oracle/java

10. DISPLAY 설정 (*root 계정으로 설정)

#xhost+<machine-name | ip>

ex) #xhost +192.168.100.100 or xhost+expert.oracle.com

[root@ocm52 ~]# xhost +192.168.100.100
192.168.100.100 being added to access control list

11. .bash_profile에다가 아래의 내용을 추가 해준다.

[root@ocm52 ~]# su - oracle
[oracle@ocm52 ~]$ vi .bash_profile (or$vi.bashrc)

#User specific envirnment and startup programs

PATH=$PATH:$HOME/bin:.:/u01/app/oracle/product/10.2.0/bin
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_OWNER=oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_TERM=vt100
export TMPDIR=$ORACLE_BASE/tmp
export TEMP=$ORACLE_BASE/tmp
export NLS_LANG=AMERICAN_AMERICA.KO16KSC5601
export ORACLE_SID=DB01
export JAVA_HOME=$ORACLE_HOME/jdk
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
umask 022

:wq

[oracle@ocm52 ~]$ . .bash_profile ==>적용 ($source.bash_profile)

12. profile 변경

[oracle@ocm52 ~]$ su - (root로 접속 변경)

Password:

[root@ocm52 ~]# vi /etc/profile 

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
13. X-win 변경

[root@ocm52 ~]# vi /etc/inittab (runlever)

id:5 =============================>id:3으로 변경

[root@ocm52 ~]# reboot

 - 관리자로써의 의무

- Fail의 종류

• 구문(문장) 오류
• 사용자 실수의 오류
• 네트워크 오류
• 세션 오류
• 인스턴스 오류(껐다 켜라..!)
  • SMON의 복구 과정을 거쳐라!
• 매체 오류(Storage, Device, File)

- User Process Failure

• 비정상적 종료
• 세션을 종료시키는 프로그램에 오류발생
• Solution : DBA의 개입이 필요없다. PMON이 알아서 처리해준다. 문제를 발생한 Trend를 보고 판단한다
• 계속해서 연결 끊김현상(비정상적인 종료)가 발생하면
  • 사용자의 교육이 필요
  • Network Fail을 의심해본다
  • 비정상적으로 종료되는 Application도 확인해본다

- Network Failure

• Listener오류
  • 백업 리스너를 구성해서 재빠르게 대응한다
  • Failover(계속적인 접근시도) 기능을 이용한다
• 네트워크 카드 오류
  • 백업 랜카드를 준비해둔다
• 네트워크 장비 오류
  • 백업 네트워크 장비를 준비해둔다

- User Error

• 데이터를 실수로 삭제하거나 수정했을경우(DML실수)
• 실수로 드랍을 할경우(DDL 실수)
• Solution : 휴지통을 이용해서 복구 & 롤백, Purge로 삭제했을 경우는 Backup(불완전복구) 정보로 복구한다
• LogMiner : FlashBack 보다는 기능이 떨어지지만, 예전부터 사용해왔던 복구도구이다.

- Instance Failure

• 정전
• 하드웨어 오류
• 필수 백그라운드 프로세서중 오류
• 비상종료
• Solution : Startup 명령을 사용해서 Restart(SMON이 복구)
• 복구된후 Alert!로그와 Trace File & EM을 보고 오류를 분석한다
• ※ Commit된 정보를 DataFile로 적용시키는것 : Roll Fowarding

- CKPT

• DBWr Process에게 Signal을 보낸다
• 신호받은 DBWr는 Dirty Block(Buffer Cache에 있다)를 Data File로 기록한다
  • Check Point Queue에 Dirty Block의 정보가 있다.
• DB FIle Header와 Contorl 파일의 정보를 Update한다.
• 무결성 체크!
• Check Point Position : Check Point Position과 현재 DB 상태의 거리가 가까울수록 Recovery 시간이 짧다
• Check Point가 존재하는 이유
  • Dirty Block을 Data FIle로 주기적으로 내려쓰기 위해
  • Recovery Time을 줄일수있다.
  • Shutdown시 Data 기록을 확실히 보장한다

- LGWr

• Redo Log FIle
  • DB의 모든 변경사항
  • 데이터 손실로부터 보호하기 위해 다중화를 하고 다중화된 멤버는 서로다른 Disk에 위치시킨다!
    • 단점 : I/O를 많이 발생하고, Disk 공간을 차지한다
• 언제?
  • Commit 할때마다
  • 3초마다
  • DBWn Wirte가 쓰기전

- ARCn

• 옵션 프로세서이다
• 아카이브로그 모드가 활성되어 있는 경우 online Redo Log 파일을 자동으로 아카이브한다
• DB의 모둔 변경 사항을 영원히 보존한다.
• EM & CMD로 할수있다.

- Instance Recovery

• 종료될때 제대로 종료되지 않은 DB가 있으면 복구후 OPEN한다
• 자동설정되어있다
• Rego Log Group에 있는 내용으로 재적용 해준다
  • Roll Fowarding -> Commit된 정보와 DataFile이 일치가 안되면 적용
  • Roll Back -> Commit 되지 않은 값을 Undo Segment에 있는 값(Orinigal) 과 DataFIle값을 비교해서 롤백
• OPEN이 되면 DataFile과 Redo Log이 열린다. 이때 복구작업이 진행된다(SMON)
• Check Point Position의 마지막 정보와 Redo Log File의 마지막 정보를 비교해서 동기화시킨다

- RBA(Redo Log File의 전체 Address)

- fast-start-mttr-target = 900 -> Instance Recovery 하는데 걸리는 최대 시간

• 900초 분량의 Dirty Blcok의 개수
• 시간이 짧을경우 : 복구시간은 짧아지지만 DBWr I/O는 많이발생
• 시간이 길경우 : 복구시간은 길어지지만 DBWr I/O는 적게발생

- Media Failure

• Disk Driver 오류
• Disk Controller 오류
• DB 파일 오류
• Solution
  • 백업한 데이터로 부터 복구
  • 필요에 따라서 Redo(Redo Log FIle & Archive File) 정보 적용
  • 새로운 위치를 지정(Disk Fail일 경우)

- 복구능력 향상을 위한 구성

• 최대한 일반적인 백업 스케쥴을 구성(증분백업정책)
• Control 파일과, Redo Log Group의 다중화
• Redo Log의 Archive를 잘 보관

- Control Files

• 최소한 2개이상의 Control 파일은 다중화 시켜라
• 서로다른 디스크에 각각 저장
• Control File 백업
  • alter database backup controlfile to trace -> udump/*.trc
  • alter database backup controlfile to trace as '경로/파일"
  • alter database backup controlfile to '경로/파일' -> Image Backup(File로 생성)
• 실습
  • WS1-2-ch14. Managed Control File LAB

- Redo Log File

• 그룹이 2개 이상이여야 하는 이유 : Log Switch가 가능!
• 다중화는 성능의 심각한 영향을 준다(I/O 발생)
• v$log_stats
  • Current (LGWr)
  • Active -> Checkpoint (ARCn이 작업중일경우)
  • Inactive -> 삭제가능
  • Unused -> 초기화(Clear), 생성직후
  • Clearing -> Alter database clear logfile group n; 그룹 초기화
  • Clearing_Current -> I/O 오류가 발생한 상황
• 실습
  • WS1-2-ch14. Managed Redolog File LAB

- Archive Log

• Redo 정보를 유지 & 보존
• 아카이브 과정
  1. 이름에 대한 규칙성을 정하고
  2. 하나이상의 로그파일 위치를 지정한다
  3. 아카이브 로그모드 설정(마운트에가서)
• 이름의 규칙성을 정하는 파라미터 : log_archive_format
  

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

- Performing Database Backup

• Secure Backup
  • End-to-End 기능
  • 중앙화된 백업 관리를 수행할수 있다
  • RMAN Backup의 한창 강화된 기능을 포함
  • 네트워크상의 백업이 가능
  • Resource 문제를 해결
  • 저렴한 비용과 신뢰할수 있는 데이터보호 정책등이 보장된다
    
    
• User Managed Backup
• Backup이 필요한건지 Backup상태가 어떤지 사용자가 직접 체크한다(스크립트 이용 권고)
• 일반 TBS 백업시
  • 정보조회를 먼저한다
    • v$datafole
    • v$logfile
    • v$controlfile
  • alter tablespace xxx begin backup -> 백업모드로 전환(Check Point 발생)
  • v$backup으로 backup모드인지 확인
  • cp
  • alter tablespace xxx end backup

with grant option 과 with admin option에 대해 테스트를 하기 전에

쉽게 이해하실수 있도록 기본적인 셋팅을 하도록 하겠다.

C:\> sqlplus "/as sysdba"

1. 사용자 생성(USER_1, USER_2)

SQL> create user user_1 identified by user_1 default tablespace users;

사용자가 생성되었습니다.

SQL> create user user_2 identified by user_2 default tablespace users;

사용자가 생성되었습니다.

2. 접속 권한 및 간단한 권한 주기

SQL> grant create session, create table, create view to user_1;

권한이 부여되었습니다.

SQL> grant create session, create table to user_2;

권한이 부여되었습니다.

이제 with grant option 과 with admin option 을 알아보자.

(1) with grant option

기본적으로 scott 계정은 다들 가지고 계시니 scott 계정으로 접속한다.

SQL> conn scott/tiger

연결되었습니다.

scott이 user_1에게 emp 테이블을 select 할수 있는 권한을 with grant option을 써서 부여 하도록 한다.

SQL> grant select on emp to user_1 with grant option;

권한이 부여되었습니다.

SQL> select * from user_tab_privs
2 where table_name='EMP';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- --------------- --------------- --------------- ---- ---
USER_1 SCOTT EMP SCOTT SELECT YES NO

scott에게 받은 권한을 똑같이 user_2에게 부여한다.

SQL> conn user_1/user_1
연결되었습니다.
SQL> grant select on scott.emp to user_2 with grant option;

권한이 부여되었습니다.

SQL> select * from user_tab_privs
2 where table_name='EMP';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- --------------- --------------- --------------- ---- ---
USER_1 SCOTT EMP SCOTT SELECT YES NO
USER_2 SCOTT EMP USER_1 SELECT YES NO

USER_TAB_PRIVS 테이블은 누구의 소유이며 권한자 및 권한을 부여 받은 자를 확인 할 수 있다.

이제 부여한 권한을 다시 회수 할 것이다. 어떻게 회수가 되는지 확인이 필요하다.

SQL> conn scott/tiger
연결되었습니다.
SQL> revoke select on emp from user_1;

권한이 취소되었습니다.

SQL> conn user_1/user_1;
연결되었습니다.
SQL> select * from user_tab_privs
2 where table_name='EMP';

선택된 레코드가 없습니다.

SQL> conn user_2/user_2;
연결되었습니다.
SQL> select * from user_tab_privs
2 where table_name='EMP';

선택된 레코드가 없습니다.

※ scott이 user_1의 권한 revoke 하였지만, user_1이 user_2에게 부여한 권한도 같이 회수가 되는 것을 볼 수 있다.

(2) with admin option

SQL> conn /as sysdba

연결되었습니다.

SQL> grant alter session to user_1 with admin option;

권한이 부여되었습니다.

SQL> select * from dba_sys_privs where grantee like 'USER%';

GRANTEE PRIVILEGE ADM
---------- ------------------- ---
USER_1 CREATE VIEW NO
USER_1 CREATE TABLE NO
USER_1 ALTER SESSION YES
USER_1 CREATE SESSION NO
USER_2 CREATE TABLE NO
USER_2 CREATE SESSION NO

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

SQL> conn user_1/user_1
연결되었습니다.
SQL> grant alter session to user_2 with admin option;

권한이 부여되었습니다.

SQL> select * from user_sys_privs where username like 'USER%';

USERNAME PRIVILEGE ADM
----------------------- ------------------ ---
USER_1 CREATE VIEW NO
USER_1 CREATE TABLE NO
USER_1 ALTER SESSION YES
USER_1 CREATE SESSION NO

SQL> conn user_2/user_2
연결되었습니다.
SQL> select * from user_sys_privs where username like 'USER%';

USERNAME PRIVILEGE ADM
----------------------- ------------------ ---
USER_2 CREATE TABLE NO
USER_2 ALTER SESSION YES
USER_2 CREATE SESSION NO

SQL>

위 테이블을 보시면 ADM 컬럼에 YES로 나와있는 것을 볼수 있다.

이제 GRANT 권한을 회수한 것처럼 똑같이 해보겠다.

SQL> conn /as sysdba

SQL> revoke alter session from user_1;

권한이 취소되었습니다.

SQL> select * from dba_sys_privs where grantee like 'USER%';

GRANTEE PRIVILEGE ADM
---------- --------------- ---
USER_1 CREATE VIEW NO
USER_1 CREATE TABLE NO
USER_1 CREATE SESSION NO
USER_2 CREATE TABLE NO
USER_2 ALTER SESSION YES
USER_2 CREATE SESSION NO

※ dba_sys_privs를 보시면 user_2의 권한은 그대로 남아있고 user_1의 권한 회수 된것을 볼수 있다.

결론을 내리자면 with grant option 은 revoke 시 다른사용자에게도 부여한 권한을 같이 회수하지만 with admin option은 특정 사용자의 권한만 회수가 되고 나머지 다른사용자에게 부여된 권한은 회수가 되지 않는다.

사용된 테이블 : user_tab_privs

: dba_sys_privs

: user_sys_privs

시퀀스의 정의와 사용 방법에 대해 정리해보려 한다.

개발을 할때 자동으로 키(KEY) 생성을 해야 할 경우 주로 사용한다.?

시퀀스(Sequence)란?

• - 유일(UNIQUE)한 값을 생성해주는 오라클 객체이다.
• - 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다.
• - 보통 PRIMARY KEY 값을 생성하기 위해 사용 한다.
• - 메모리에 Cache되었을 때 SEQUENCE 값의 액세스 효율이 증가 한다.
• - SEQUENCE는 테이블과는 독립적으로 저장되고 생성됩니다. 따라서 하나의 SEQUENCE를 여러 테이블에서 쓸 수 있다.

시퀀스 문법

시퀀스 예제

-- 시퀀스 생성

SQL> CREATE SEQUENCE emp_seq

START WITH 1

INCREMENT BY 1

MAXVALUE 100000 ;



--  NEXTVAL을 사용하여  시퀀스 값을 자동으로 입력할 수 있다.

SQL> INSERT INTO emp(empno, ename, hiredate )

VALUES(emp_seq.NEXTVAL, 'julia' , sysdate);

-- NEXTVAL은 현재 시퀀스 값의 다음 값을 반환 한다.

SQL> SELECT emp_seq.NEXTVAL FROM DUAL ;

-- CURRVAL은 현재 시퀀스 값을 반환 한다.

SQL> SELECT emp_seq.CURRVAL FROM DUAL ;

시퀀스의 수정 및 삭제

START WITH는 수정 할 수 없다. START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE 문장과 같다.

시퀀스의 수정 및 삭제 예제

-- 2씩 증가하고, 최대값을 넘으면 다시 처음부터 순환하도록 수정

SQL> ALTER SEQUENCE emp_seq

INCREMENT BY 2

CYCLE;

-- 시퀀스 삭제

 SQL>DROP SEQUENCE emp_seq;

데이터베이스에서 with check option이 무엇일까?

view를 통해 insert 가 되는데 with check option 을 사용 여부에 따라 뷰의 조건에 해당하는

데이터만 삽입,삭제,수정 할 수 있다.

--예제를 보시면 훨씬 쉽게 이해 하실 수 있습니다.

ex)

create table testtable
(empno number(10) ,
deptno number(10),
name varchar2(20))

tablespace users;

insert into testtable values(10,20,'abc');

insert into testtable values(20,30,'ccc');

이런식으로 대충 몇개 쭈-욱 데이터를 넣어요!

이제 view를 만들어봅시다.

1. with check option을 사용하지 않은 view

create view testview2
as
select * from testtable where empno in (10,20,30,40);

2. with check option을 사용한 view

create view testview
as
select * from testtable where empno in (10,20,30,40)

with check option;

※ with check option 이 어떤 기능을 하는지 여기서 알수 있다.

view를 통해서 insert가 가능 하다.

먼저 with check option 을 사용하지 않은 view 를 통해서 insert를 해보자.

insert into testview2 values(10,20,'view_com');

select 문을 실행시키면 데이터가 입력된 것을 확인 할 수 있습니다.

select * from testview2;

(이것은 view는 테이블이 아니기 때문에 testview에 입력된 것이 아니라 testtable에 입력된 것입니다.)

insert into testview2 values(80,90,'view_com2');

이것도 역시 테이블에서 확인해 보면 정상적으로 삽입 된 것을 볼 수 있다.

(view에 제약조건이 걸려 있기 때문에 view에는 나타나지 않고 testtable에는 나타난 것을 확인합니다.)

여기까지 정상적으로 모든것이 insert가 된 것을 확인 할수 있을 겁니다.

이제, with check option을 사용한 view 를 통해서 insert를 해봅시다.

위에서 insert한 값과 똑같이 넣어 보겠습니다.

insert into testview values(10,20,'view_com'); 아무 이상없이 실행 될것입니다.

하지만

insert into testview values(80,90,'view_com2');

ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다.

이런 오류가 나오시는 것을 볼 수 있을 것입니다.

결론을 내리자면 with check option은

view를 만들 때 해당 제약조건 안에서만 삽입,삭제,수정이 가능하다는 것입니다.

위 예제는 view를 만들 때 empno가 10,20,30,40인 것들만 가져오는 것이였습니다.

그런데 with check option을 쓴 view에

empno에 80인 데이터를 넣을려고 하니 오류메세지가 나오는 것입니다.

select acos(0.1) from dual;

acos(0.1)

1.47062890563333682288579851218705812354

select asin(0.1) from dual;

asin(0.1)

0.10016742116155979634552317945269331856

select atan(0.1) from dual;

atan(0.1)

0.0996686524911620273784461198780206049018

select atan2(2,5) from dual;

atan2(2,5)

0.3805063771123648863035879168104337074528

select bitand(7,5) from dual;

bitand(6,5)

4

6 = 110(2)

5 = 101(2)

and값 = 100(2) = 4

select ceil(6.01) from dual;

ceil(6.01)

7

select cos(3) from dual;

cos(3)

-0.98999249660044545727157279473126130238

select cosh(3) from dual;

cosh(3)

10.06766199577776584195393603511588983682

select Exp(5) from dual;

Exp(5)

148.413159102576603421115580040552279624

select floor(6.5) from dual;

floor(6.5)

6

B, F, P, V = 1 C, G, J, K, Q, S, X, Z = 2 D, T = 3 L = 4 M, N = 5 R = 6