JayK

user Management

◆ Schema란?

- 특정 사용자가 생성한 object의 집합을 의미

- user가 생성될 때 관련 schema가 생성

- user와 schema를 혼용해서 사용하기도 함

Schema Object ( Table, Indexes, Views, Triggers, Constraints, Database links, Synonyms, Stored Procedures, Sequences )

* tablespace는 미리 생성 해놓아야 함!!!!!

 

◆ User 생성하기

1. 사용자를 생성하는 목적을 정리

2. 해당 사용자의 default tablespace를 결정하고 해당 테이블 스페이스를 먼저 생성

CREATE USER user_name IDENTIFIED BY password DEFAULT TABLESPACE taplespace_name TEMPORARY TABLESPACE temp_tablespace_name quota unlimited on tablespace_name quota 0m on system;

 

-- User 생성 실습

-- SMS 서비스용 user 생성

-- 계정명 : smsuser / passwd: smspwd

-- default tablespace : TS_SMS

-- temporary tablespace : temp_sms

-- quota : ts_sms : unlimited / system : 0m

--1. 테이블 스페이스 생성

CREATE TABLESPACE TS_SMS

datafile '/home/oracle/oradata/testdb/ts_sms.dbf' size 10M;

 

--2. temp 테이블 스페이스 생성

CREATE TEMPORARY TABLESPACE temp_sms

tempfile '/home/oracle/oradata/testdb/temp_sms.dbf' size 10M;

 

--3. user 생성

CREATE USER smsuser IDENTIFIED BY smspwd

DEFAULT TABLESPACE TS_SMS

TEMPORARY TABLESPACE temp_sms

quota unlimited on TS_SMS

quota 0m on system;

 

--선언된 link 확인

select * from dba_db_links;

 

--db link 생성

create public database LINK yang

CONNECT TO scott IDENTIFIED BY "imsi00"

using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)

(HOST=172.16.1.53)(PORT=1521))

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL2)))';

-- scott계정에 모두 다 들어갈 수 있어서 public으로 선언 잘 안함

 

--DB Link 삭제

drop public database link yang;

 

--yoon 계정의 emp 조회

select * from emp@yang;

 

select * from dba_tables

where table_name='TEST';

 

select * from dba_synonyms

where synonym_name='TEST';

 

create or replace public synonym test for test@yang; -- synonym을 등록

 

select * from test; -- synonym 설정 후 하면 출력됨

 

select * from test@yang;

 

◆ Profile 관리하기

●Password 관련

1) FAILED_LOGIN_ATTEMPTS : LOGIN 시도를 실패할 경우 계정을 잠그는데 여기 설정된 횟수

2) PASSWORD_LOCK_TIME : 위에서 계정이 잠기면 며칠 동안 잠글 것 인지 기간을 정하는 파라미터 (일 단위)

-일수와 무관하게 DBA가 UNLOCK 할 수 있음

3) PASSWORD_LIFE_TIME : 동일한 암호를 며칠간 사용하게 할 것인지 설정하는 파라미터. (일 단위)

이 기간이 지나도 안 바꾸면 다음 로그인 할 때 강제로 바꾸게 프롬프트를 보여줌

4) PASSWORD_GRACE_TIME : 위 3번 항목에서 만료되어도 이 파라미터에 지정된 값 만큼 더 암호를 변경할 기간을 허용

5) PASSWORD_REUSE_TIME : 동일한 암호를 다시 사용할 수 없도록 설정하는 기간 즉, 암호를 변경하라고 했는데 사용자가 동일한

암호를 다시 사용하려고 할 수 있기에 같은 암호를 다시 쓸 수 없게 만들어야 할 때 사용하는 파라미터

6) PASSWORD_REUSE_MAX : 동일한 암호를 위 5번 설정을 피해 재사용을 할 경우 최대 사용 가능한 횟수를 지정하는 파라미터

7) PASSWORD_VERIFY_FUNCTION : 암호를 보다 복잡하게 만들기 위해 특정 함수를 적용시켜 사용자의 암호를 점검 할 수 있음.

ORACLE에서 만들어 둔 기본 함수인 VERIFY_FUNCTION을 사용할 수 있고 사용자가 별도의

함수를 이용할 경우 어떤 조건을 점검하는 가

· 암호는 최소한 4글자 이상 되어야 한다

· 암호는 사용자 계정과 달라야 한다

· 암호는 하나의 특수문자나, 알파벳, 숫자가 포함되어야 한다

· 암호는 이전 암호와 3글자 이상 달라야 한다

-- password 관련 profile 생성하기

-- 다음의 profile을 만드시오. profile이름은 profile_sms

--smsuser에 profile_sms를 적용

--조건1 : 로그인 시도 3회 실패 시 계정을 5일 동안 사용 못하게 할 것

--조건2 : 계정의 암호는 15일에 한 번씩 변경하게 할 것

--조건3 : 동일한 암호는 15일 동안 사용 못하게 할 것

--profile 생성

CREATE PROFILE profile_sms LIMIT

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 5

PASSWORD_LIFE_TIME 15

PASSWORD_REUSE_TIME 15;

--user‘s profile 변경

ALTER USER SMSUSER PROFILE profile_sms;

 

◆ 리소스 관련 PROFILE

(1) CPU_PER_SESSION : 하나의 세션이 CPU를 연속적으로 사용할 수 없는 최대 시간을 설정합니다. 무한루프 같은 쿼리가 작동되면 혼자 서 CPU를 연속적으로 점유하기 때문에 이 설정을 사용해서 그런 일을 막고자 하는 것입니다. 1/100초 단위입니다.

(2) SESSIONS_PER_USERS : 하나의 사용자 계정으로 몇 명의 사용자가 동시에 접속 할 수 있는지를 설정하는 파라미터입니다

(3) CONNECT_TIME : 하루 동안 DB SERVER에 접속을 허락하는 총 시간을 설정합니다. (단위는 분)

(4) IDLE_TIME : 연속적으로 휴면 시간이 여기 값을 넘으면 접속을 해제 합니다. 예를 들어 IDLE_TIME 5 이렇게 하면 5분 동안 활동이 없는 세션은 강제로 접속이 끊어지게 됩니다 (분 단위)

(5) LOGICAL_READS_PER_SESSION : 한 SESSION에서 사용 가능한 최대 BLOCK 수를 지정합니다.

(6) PRIVATE_SGA : MTS / shared server 일 경우 해당 session의 SGA 사용량을 byte 단위로 설정

(7) CPU_PER_CALL : 하나의 Call 당 cpu 점유할 수 있는 시간 1/100초 단위

(8) LOGICAL_READS_PER_CALL : 하나의 call당 읽을 수 있는 block의 개수 지정

 

-- Resource 관련 profile 생성

--profile_sms에 다음의 resource 관련 parameter 값을 수정

-- SYS> alter system set resource_limit=true;

--조건1 : 1명당 연속적으로 CPU를 사용할 수 있는 시간을 10초로 제한할 것

--조건2 : 하루 중 8시간만 DB에 접속 가능하게 할 것

--조건3 : 10분 동안 사용하지 않으면 강제로 접속을 끊을 것

 

ALTER PROFILE PROFILE_SMS LIMIT

CPU_PER_SESSION 1000

CONNECT_TIME 480

IDLE_TIME 10 ;

 

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; -- 리소스 내용이 적용 되려면 TRUE로 해야함

 

SELECT * FROM DBA_PROFILES

WHERE PROFILE='PROFILE_SMS';

 

◆ Privilege (권한) 관리하기

· SYSTEM 관련 privilege

대 분 류	PRIVILEGE	설 명
INDEX	CREATE ANY INDEX	소유자에 상관없이 모든 테이블에 인덱스를 생성할 수 있는 권한
	DROP ANY INDEX	소유자에 상관없이 모든 인덱스를 삭제할 수 있는 권한
	ALTER ANY INDEX	소유자에 상관없이 모든 인덱스를 수정할 수 있는 권한
TABLE	CREATE TABLE	자신 소유의 테이블을 생성할 수 있는 권한
	CREATE ANY TABLE	소유자에 상관없이 다른 USER 이름으로 테이블을 생성할 수 있는 권한
	ALTER ANY TABLE	소유자에 상관없이 모든 테이블의 구조를 수정할 수 있는 권한
	DROP ANY TABLE	소유자에 상관 없이 모든 사용자의 테이블을 삭제할 수 있는 권한
	UPDATE ANY TABLE	소유자에 상관없이 모든 사용자의 테이블을 업데이트 할 수 있는 권한
	DELETE ANY TABLE	소유자에 상관없이 모든 사용자의 테이블의 데이터를 삭제 할 수 있는 권한
	INSERT ANY TABLE	소유자에 상관없이 모든 사용자의 테이블에 데이터를 삽입 할 수 있는 권한
SESSION	CREATE SESSION	서버에 접속할 수 있는 권한
	ALTER SESSION	접속 상태에서 환경값을 변경할 수 있는 권한
	RESTRICTED SESSION	Restricted 모드로 open 된 DB에 접속할 수 있는 권한
TABLESPACE	CREATE TABLESPACE	Tablespace를 만들 수 있는 권한
	ALTER TABLESPACE	Tablespace를 수정 할 수 있는 권한
	DROP TABLESPACE	Tablespace를 삭제 할 수 있는 권한
	UNLIMITED TABLESPACE	Tablesapce 사용용량을 무제한으로 허용하는 권한 즉 quota 옵션 적용을 받지 않게 됨

-- 권한 부여

-- SYSTEM privilege 부여

GRANT CREATE SESSION, CREATE ANY TABLE TO SMSUSER;

-- OBJECT privilege 부여

GRANT SELECT ON SCOTT.EMP TO SMSUSER;

 

--권한확인

--01. 계정별 권한 조회(role 제외)

select * from dba_sys_privs

where grantee='SMSUSER';

 

select * from dba_tab_privs

where 1=1

and grantee='SMSUSER'

and TABLE_NAME='EMP';

 

--02. 계정별 role 조회

select * from dba_role_privs

where grantee='SMSUSER';

 

--03. 그 role에 어떤 권한이 포함되어있는지

select * from dba_sys_privs

where grantee='CONNECT';

 

--04. role에 부여된 SYS 권한 조회

select * from dba_users;

 

◆ ROLE

-- ROLE 생성 하기

create role trole;

create role srole;

 

-- ROLE에 권한 할당 하기

grant create session, create table to trole;

grant select on scott.dept to srole;

 

-- ROLE 할당하기

grant trole, srole to smsuser;

 

1. smsuser에게 부여된 system privilege를 직접 부여된 권한과 role을 통해 부여된 권한을 한번에 조회하도록 쿼리 작성

select grantee, privilege

from dba_sys_privs

where 1=1

and grantee='SMSUSER'

union all

select ROLE, PRIVILEGE

from role_sys_privs

where ROLE IN (select GRANTED_ROLE

from dba_role_privs

where grantee='SMSUSER');

 

2. smsuser에게 부여된 table privilege를 직접 부여된 권한과 role을 통해 부여된 권한을 한 번에 조회하도록

select GRANTEE as "dd", OWNER, TABLE_NAME, PRIVILEGE

from DBA_TAB_PRIVS

where 1=1

and grantee='SMSUSER'

union all

select ROLE, OWNER, TABLE_NAME, PRIVILEGE

from role_tab_privs

where ROLE IN (select GRANTED_ROLE

from dba_role_privs

where grantee='SMSUSER');

 

3. table에 부여된 권한 확인

select * from DBA_TAB_PRIVS

where 1=1

and TABLE_NAME='DEPT';

 

4. table에 부여된 권한 부여 DDL

select 'grant '||PRIVILEGE||' on ' ||OWNER||'.'||TABLE_NAME||' to '||GRANTEE||';'

from DBA_TAB_PRIVS

where 1=1

and TABLE_NAME='DEPT';

 

5. table에 부여된 권한 회수 DDL

select 'grant '||PRIVILEGE||' on ' ||OWNER||'.'||TABLE_NAME||' from '||GRANTEE||';'

from DBA_TAB_PRIVS

where 1=1

and TABLE_NAME='DEPT';

Oracle Block

◆ Oracle Block 개요

- Oracle에서의 최소 작업 단위

- 사용자가 입력한 데이터를 하드디스크에 저장하거나 읽어들 일 때 1건씩 처리하는 구조가 아닌, 가장 작은 단위인 block 단위로 작업

- DB_BLOCK_SIZE 파라미터로 block 사이즈 조절 가능, default는 8K (BUT, 생성 시 지정하면 바꿀 수 없음)

- create database 할 때 한 번 지정되면 그 값은 database를 재 생성하기 전에는 변경할 수 없다.

- SQL> show parameter db_block_size로 조회

- 2KB부터 4KB,8KB,16KB,32KB가 제공

- 짝수 SIZE 권장

- block 사이즈가 작다면 한 번에 담을 수 있는 데이터양이 작아져 disk I/O가 많아짐

- block 사이즈가 크면 데이터가 작을 경우 공간이 낭비될 수 있고 wait time이 많이 생길 수 있음

- Tablespace 생성 시 block size를 기존과 다르게 설정할 수 있으나 먼저 database buffer cache에도

해당 block 사이즈 만큼 공간을 미리 할당해두어야 설정 가능(권고 X) * memory도 1:1로 matching 해야 함 (p.224)

 

◆ Oracle Data Block 상세구조

- 블록헤더(Header) : 일반적인 Block의 정보를 가지고 있습니다.

(Block의 위치, Segment의 형태), 85~100bytes정도

- Table Directory : 클러스터에 있는 테이블에 관한 정보를 가지고 있습니다.

- Row Directory : Block내의 Row관련 정보를 가지고 있습니다. 각 Row마다 2byte

- Free Space : New Row Insert나 Update시 사용 합니다. PCTFREE값과 PCTUSED에

의해 결정 됩니다.

* 로우 마이그레이션(ROW MIGRATION) 발생 --> 많은 디스크 I/O 발생

(인접한 공간으로 이주)

- Row Data : 테이블 데이터와 인덱스 데이터를 포함 합니다.

 

 

◆ PCTFREE

 

- 사용 가능한 Block 공간 중에서 데이터 행의 UPDATE시 block의 여유 공간이

없어 다른 block에 저장될 것에 대비하여 확보해 놓은 영역

ex) "PCTFREE 20" 으로 설정을 하면, 데이터 블록의 20%를 사용 가능한 빈 영역으로 유지하여 각 블록에 있는 행을 갱신하는데

      사용한다는 의미

- PCTFREE의 기본 값은 10%

- INDEX값은 수정이 자주 발생하지 않으므로 PCTFREE는 5%정도가 적당

- 이 공간은 데이터가 insert 되지 않고 오로지 update만을 위해 남겨놓은 공간

- PCTFREE값이 도달된 후 블록 내에 남은 공간은 기존의 ROW가 Update되어 공간이 추가로 필요할 때 사용됨 

- 이 공간을 제외한 나머지 공간이 가득 차게 되면 이 block은 더 이상 빈 공간이 없는 Dirty Block 상태로 변경

- PCTFREE와 PCTUSED의 합이 100을 초과하지 않는 범위 내에서 0에서 99까지 값을 PCTFREE값으로 사용할 수 있음

(PCTFREE+PCTUSED <= 100)

 

1) PCTFREE가 적을 경우

- 기존 테이블 행 갱신에 의한 확장을 위해 적은 공간을 확보

- update가 빈번히 일어나는 경우 Row Migration이 자주 발생

- 많은 row가 한 block에 입력 가능

- 수정이 적은 세그먼트에 적합

 

2) PCTFREE가 클 경우

- 블록 당 적은 row가 입력, 즉 같은 row를 입력하기 위해서 많은 block이 필요

- update시 다른 block으로 이동할 필요가 없으므로 수정 수행 속도가 증가

- 자주 수정되는 세그먼트에 적합

 

create table pct_test1

(a number, b varchar2(10));

 

create table pct_test2

(a number, b varchar2(10))

PCTFREE 50;

 

--테이블 별 storage 옵션 확인

select owner, table_name, pct_free, pct_used, num_rows,blocks,chain_cnt

from dba_tables

where table_name like 'PCT_TEST%';

 

◆ block내 빈 공간을 관리하는 과정

- 비어있는 block의 명단은 Free list에 다 기록되고, 빈 공간이 없는 block는 Dirty list에 기록되는데 데이터 1건 지울 때 마다

Free list와 Dirty list를 업데이트 하는 경우 부하가 매우 큼

- block에 있는 row가 삭제되어서 사용량이 줄어들어도 즉시 이 block을 재사용하지 않고 특정 양이 빌 때까지 기다렸다 재사용 함

- 지워진 후 남은 용량이 얼마가 되어야 Free block이 되는지를 결정하는 파라미터가 PCTUSED

 

◆ PCTUSED

 

- 계속해서 데이터가 지워져서 빈 공간이 생길 경우 Dirty block에서 Free block으로 변경되는 기준을 의미

- PCTUSED=40 : 데이터 블록의 사용영역이 40%보다 적어지지 않으면 새로운 행을 INSERT할 수 없음(PCTUSED의 기본 값은 40%)

- ASSM(Automatic Segment Space Management)일 경우 사용되지 않음 (default)

 

1) PCTUSED값이 적을 경우

- 블록이 재사용되는 일이 적어질 수 있으므로 처리 비용이 감소

- 데이터베이스에서 사용되지 않은 공간이 증가

2) PCTUSED값이 클 경우

- 블록이 재사용되는 일이 많아질 수 있으므로 처리 비용이 증가

- 공간 사용도를 향상

 

 

PCTFREE = 20 / PCTUSED = 40 일 경우

 

◆ Row Chaining

- Row Chaining은 DB_BLOCK_SIZE보다 너무 큰 데이터가 들어왔을 경우, 혹은 남아 있는 block size보다 큰 데이터가

들어올 경우 인접한 다른 블록까지 데이터가 쓰여지는 것

- 이 데이터 블록은 원래 블록과 연결 되어짐

- Row Chaining은 블록이 하나의 I/O 작업과 동일한 양을 수행하기 위해 두 개의 I/O를 사용해야 하므로 성능상의 부하를 줌

- Row Chaining 현상을 줄이기 위해서는 block size를 크게 생성하는 것이 좋음

 

◆ Row Migration

- UPDATE시 row에 저장된 Data의 양을 증가시켜 더 이상 그 블록에 저장될 수 없을 경우 새로운 Block으로

데이터를 완전히 옮기는데 이를 Row Migration 이라고 함

- Update시 공간이 부족할 때 발생하며, Data 행을 완전히 채울 수 있는 새로운 Block에 저장됨

- 원래 block에 신규 block의 포인터 주소를 남겨두어 이전 block으로 찾아오는 access도 신규 block을 찾아갈 수 있도록 함

- PCTFREE값을 많이 주거나 reorg 등의 작업을 통해 줄일 수 있음 ( 향후 )

* PCTFREE를 그대로 가져 갈 것이라면 해당 테이블스페이스에 move,

PCTFREE값을 바꾸려면 TableSpace를 새로 생성 후 move

 

-- PCT_FREE_TEST를 위한 TEST TABLE 생성

-- PCTFREE 10(DEFAULT)

create table pct_test1

(a number, b varchar2(10));

 

-- PCTFREE 50으로 생성

create table pct_test2

(a number, b varchar2(10))

PCTFREE 50;

 

-- table별 storage 옵션 확인

select owner, table_name, pct_free, pct_used, num_rows, -- 실제 data 건수

blocks, -- 실제 block 수

chain_cnt, -- row_chain 수

last_analyzed -- 마지막 통계정보 갱신 날짜

from dba_tables

where 1=1

and table_name like 'PCT_TEST%' ;

 

-- 대용량 data insert

begin for i in 1..100000 loop

insert

into pct_test1

values(i,'AAAA');

end loop;

commit;

end;

/

 

begin for i in 1..100000 loop

insert

into pct_test2

values(i,'AAAA');

end loop;

commit;

end;

/

 

 

-- 통계정보 갱신 후 block수 재 확인

exec dbms_stats.gather_table_stats('SCOTT','PCT_TEST1');

exec dbms_stats.gather_table_stats('SCOTT','PCT_TEST2');

 

-- 대용량의 update 발생

update pct_test1

set b='AAAAAAAA';

commit;

 

update pct_test2

set b='AAAAAAAA';

commit;

 

-- 통계정보 갱신 후 update 후 달라지는 block 수 확인

exec dbms_stats.gather_table_stats('SCOTT','PCT_TEST1');

exec dbms_stats.gather_table_stats('SCOTT','PCT_TEST2');

 

-- 결론

-- pctfree가 10%인 경우 대량 update 발생 후 block 수가 현저히 증가

-- pctfree가 50%인 경우 update를 pctfree로 할당한 공간에서

-- 충분히 수행했기때문에 block수가 그대로인 것을 확인할 수 있음

 

◆ Extent

- Extent는 일정한 수의 연속된 oracle block

- 일정한 수라는 의미는 사용자가 지정한 값을 의미

- create table 옵션에 INITIAL EXTENT로 설정

- INITIAL EXTENT = 64K이면 DB_BLOCK_SIZE=8K일 경우 연속해서 8개의 block이 1개의 extent를 구성

- 10g 버전부터는 ASSM(Automatic Segment Space Management)이 기본이며, 이때 extent size는 64K임

- Extent 값이 크면 대량 insert에 유용하며, 블록데이터들을 인접하게 생성할 수 있음 ( But, 공간을 많이 차지함 )

- create table로 생성시 64k로 생성됐으나 10g부터 default creation이 적용 되 0k로 생성됨

◆ Extent 특징

- Object는 현재 할당된 모든 Extent가 이미 채워진 경우에만 새로운 Extent를 할당

- 새로운 Extent는 Next Extent 크기만큼 할당(10g 이상부터는 64KB가 기본)

- Next Extent = Next * (1+Pctincrease/100)

- Free Extent를 할당하고 관리하는 방식은 DMT와 LMT로 나뉘게 된다

●DMT(DINCTIONARY MANAGED TABLESPACE)

- 전통적인 방법으로 STORAGE절을 사용하여 EXTENT를 할당

- Oracle 9i 버전 이후 DICTIONARY MANAGED 방식의 사용을 권장하지 않음

● (LOCALLY MANAGED TABLESPACE)

- CREATE TABLE절에 STORAGE를 모두 무시

- TABLESPACE 정한 UNIFORM SIZE 대로 EXTENT 할당

- Segment를 관리하는 방식에 따라 FLM과 ASSM으로 나뉨

◆ Extent 사용 이유

- 해당 테이블이 사용할 extent를 미리 할당함으로써 data file내 곳곳에 분산되어 저장되는 것을 피하기 위함

- 동일 table의 data가 곳곳의 block에 저장되어 있다면 이를 찾는 시간이 오래 걸려 전체적인 성능 저하 발생

 

create tablespace extent1

datafile '/home/oracle/oradata/testdb/extent01.dbf' SIZE 50M

EXTENT MANAGEMENT LOCAL -- 생략시 default

UNIFORM SIZE 1M; ====> LOCAL 이므로 테이블 생성시 STORAGE를 무시함

 

create table extent_test (a number, b varchar2(10)) tablespace extent1;

create table extent_test2 (a number, b varchar2(10)) tablespace extent1 storage ( INITIAL 64K NEXT 1M );

 

-- extent 할당 확인

select tablespace_name ,

segment_name ,

extent_id,

blocks,

bytes/1024 "extent_size(KB)"

from dba_extents

where segment_name like 'EXTENT%';

 

-- table 별 storage 옵션 확인(PCT_FREE, PCT_USED)

select table_name, initial_extent/1024 "initial_extent(KB)",next_extent/1024 "next_extent(KB)"

from dba_tables

where table_name like 'EXTENT%';

   

===> LMT일땐 next_extent의 의미는 무의미 ( dba_tables View만 보고 판단 X )

 

-- table별 reorg *CTAS로 reorg시

alter table emp move tablespace example;

 

-- table reorg시 index unusable 상태로 변경되므로 rebuild 필요 ==> Rowid구조가 바뀌므로

select 'alter index '||owner||'.'||index_name||' rebuild;'

from dba_indexes

where status='UNUSABLE';

-- 결론

-- LMT 환경에서는 uniform size로 지정한 크기만큼

-- extent가 할당되고 따라서, 생성한 test1과 2의 extent size가 똑같다

-- 또한 initial extent size와 next extent size가 동일하게 할당된다

 

◆ FLM(Free List Management)

- Oracle 8i까지의 Segment 관리 기법

- Segment의 Free Block들은 항상 freelist를 통해 관리 됨

- PCTUSED 아래로 채워진 block들이 freelist로 연결되어 있어서, insert가 필요하면

이 freelist를 segment header에서부터 뒤지면서 블록내의 빈 공간에 insert를 하게 됨

- Undo, Redo 같은 파일들은 제외

 

◆ ASSM(Automatic Segment Space Management)

- Oracle 9i부터는 PCTFREE, PCTUSED를 직접 지정하는 기존의 FLM 방법 대신 자동으로 관리하는 방법을 권장

- segment에 할당된 space를 freelist 대신 bitmap으로 관리

- ASSM 방법을 이용하여 space를 관리하게 되면 freelist를 타고 다음다음 block을 access하는 대신 Tree구조의

bitmap 정보를 참고로 적당한 block들을 선택하기 때문에 space에 관한 성능이 훨씬 좋아짐

- ASSM 방식을 이용하려면 반드시 LOCALLY MANAGED TABLESPACE여야 함

 

DMT - table별 extent 별도 관리

- extent size(initial, next) 다르게 지정 가능

LMT - table 별 동일 extent size

- initial,next extent size 동일 (FLM : PCTUSED O , ASSM : PCTUSED X)

 

◆ SEGMENT

- Oracle Object 중에서 독자적인 저장 공간을 가지는 것

- 데이터가 증가되면 실제 증가되는 영역

- Table,Index,Undo,Temp 등이 대표적인 Segment의 종류

- 여러 개의 Extent로 구성

-- nologging 테스트 시나리오

-- nologging_test1 : 일반테이블, 아무 옵션 없이 insert

-- nologging_test2 : table nologging 옵션

-- nologging_test3 : table은 그대로, insert append

 

create table nologging_test5

tablespace haksa

as

select * from nologging_test3;

 

create table nologging_test6

tablespace haksa

nologging

as

select * from nologging_test1;

 

-- data insert

begin for i in 1..5000000 loop

insert

into nologging_test1

values(i,'AAAA');

end loop;

commit;

end;

/

 

truncate table nologging_test2;

 

begin for i in 1..5000000 loop

insert

into nologging_test2

values(i,'AAAA');

end loop;

commit;

end;

/

 

truncate table nologging_test3

 

begin for i in 1..5000000 loop

insert /*+ append */

into nologging_test3

values(i,'AAAA');

end loop;

commit;

end;

/

 

begin for i in 1..5000000 loop

insert /*+ append */

into nologging_test4

values(i,'AAAA');

end loop;

commit;

end;

/

 

-- 날짜별 redo log 사용량

select to_char(first_time, 'YYYY-MM-DD') "Date"

, count(*)

from v$log_history

group by to_char(first_time

, 'YYYY-MM-DD')

order by 1;

-- 실제 redo log 사용 현황

SELECT name,

value

FROM v$sysstat

WHERE name in ('redo size',

'redo writes' ,

'redo blocks written');

-- 결론

-- nologging일 경우에는 변경된 내용을 redo, undo에 쌓지 않아서 빠르다.

-- 실제 nologging이 적용되려면 CTAS, create index여야 한다

-- 일반 itas일 경우에는 nologging의 효력이 거의 없다

-- 특히 redo writes보다 redo blocks written의 차이가 더 큰 이유는

-- nologging일 경우 실제 redo buffer에 기록되는 것 보다 --> 실제 버퍼에는 입력하나 리두 로그 파일엔 내리지 않음

-- redo log file에 기록을 훨씬 덜 하게 하면서 성능 상 좋아지기 때문

-- synonym test

select * from dba_tables

where table_name like '%EXTENT_TEST%'

 

SELECT * FROM DBA_INDEXES

WHERE 1=1

-- AND TABLE_NAME='TEMP_TEST'

AND INDEX_NAME='TEMP_TEST_IND'

 

create or replace public synonym EXTENT_TEST for system.EXTENT_TEST;

 

drop table EXTENT_TEST;

 

select * from dba_synonyms

where SYNONYM_NAME='EXTENT_TEST'

 

select * from EXTENT_TEST;

 

create table EXTENT_TEST

(a number, b varchar2(10));

 

-- 결론

-- 테이블(EXTENT_TEST)을 삭제했다가 같은 이름의 테이블을 재생성해도

-- 이미 설정해둔 SYNONYM은 사라지지 않고,

-- 새로 생성된 같은이름의 테이블에 적용된다

-- 테이블 삭제 후 시노님이 그대로 있을 경우에 테이블 조회 시

-- table does not exist error가 아닌 loop synonym error 발생

-- -> 테이블 삭제 후 시노님도 함께 삭제 필요!

 

Tablespace & Datafiles

- 오라클은 데이터를 저장하고 작업할 때 메모리에 논리적으로는 Tablespace라는 공간을 만들어서 작업하며, 물리적으로는 Data File을 만들어서 저장

- 사용자가 SQL을 수행하면 해당데이터는 반드시 메모리에 있는 Tablespace에 존재해야 한다.

- 메모리에 존재하지 않을 경우 Server Process는 데이터가 저장되어있는 Data File에 가서 해당 데이터가 있는 Block을 찾아서

   Tablespace로 가져온 후  사용자가 원하는 데이터를 전달

- Oracle에서는 Database Buffer cache에 Tablespace를 생성하며 사용하는 메모리 공간이 클수록 일반적으로 수행속도가 빨라짐

 

1. Tablespace의 종류 및 특징

1) SYSTEM Tablespace

- Data Dictionary들이 저장되어있음

- SYS 계정의 소유 Tablespace이지만 SYS 사용자도 이 테이블의 내용을 변경할 수 없음

- System table space 사이즈가 100%이면 어떤 오브젝트도 등록 불가능

- Data Dictionary 테이블 개수 조회

SQL> select count(*) from dictionary;

- SYSTEM Tablespace가 풀 차면 Auto extend가 on 되어 있다면 자동으로 공간이 늘어남

 

□  Data Dictionary

오라클 서버의 모든 정보를 저장하고 있는 아주 중요한 테이블이나 뷰

 

□ Data Dictionary에 있는 정보

1) 데이터베이스의 논리적인 구조와 물리적인 구조정보

2) 객체의 정의와 공간 활용 정보

3) 제약조건에 관련된 정보

4) 사용자에 관련된 정보

5) Role, Privilege 등에 관련된 정보

6) 감사 및 보안에 관련된 정보

 

□ Data Dictionary는 Base Table과 Data Dictionary View로 나눔

- Base Table : 데이터베이스를 생성할 때 생성되는 테이블로 dictionary의 원본 데이터가 존재

- Data Dictionary View : dbca로 database 생성 시 자동생성 되지만,

create database로 수동 생성 경우 catalog.sql을 수행해야 생성됨

- Base Table의 내용을 직접 보지 못하도록 Data Dictionary View를 제공해 주는 것

- Data Dictionary View는 다시 Static Dictionary와 Dynamic Dictionary로 나눔

 

□ Static Dictionary

- 수동으로 업데이트를 해야 정보가 갱신

- 인스턴스가 OPEN 되었을 경우 조회가능

- USER_XXX, ALL_XXX, DBA_XXX 뷰들

 

□ Dynamic Performance View

- 실시간으로 변경되는 내용을 볼 수 있는 뷰

- 사용자가 조회할 경우 그 시점의 Control File 이나 메모리에서 데이터를 가져와 보여준다

- v$로 시작되며, Database가 Nomount 상태일 때부터 조회가능

 

2) SYSAUX Tablespace

- 주로 오라클 서버의 성능 튜닝을 위한 데이터들이 저장 되어 있음

- 9i 버전까지는 튜닝관련 Dictionary들이 SYSTEM Tablespace에 존재

- 10g버전부터 성능 튜닝과 관련된 Dictionary들이 이곳에 별도로 저장

- 10g에 새로 등장한 자동튜닝 기능들 AWR, ADDM, ASH등이 이곳의 정보를 사용

 

3) 일반 Tablespace

- 일반적으로 많이 사용되는 Tablespace

- 관리자가 필요에 의해 만드는 Tablespace

 

TableSpace 실습

CREATE TABLESPACE haksa

datafile '/oracle11/oracle/oradata/testdb/haksa01.dbf' size 1M(autoextend on);

* autoextend on : 옵션 추가시 공간이 차면 자동으로 사이즈를 늘려줌

 

 

* 이건 segment로 조회 시 안 나옴 왜냐면 빈껍데기는 데이터용량 할당이 안 되기 때문에

다음 block에서 이유 자세히 배움

create table iphak2 (stduno number)

tablespace haksa; -- scott 계정에서

 

-- 대량의 데이터 insert

 

begin

for i in 1..50000 loop

insert into iphak values(i);

end loop

;

commit;

end;

/

 

select count(*) from iphak;

***************** Datafile이 가득 차서 더 이상 insert가 안될 때 **************************

1) 테이블스페이스에 Datafile 추가 -> 디스크 추가

alter tablespace haksa

add datafile '/oracle11/oracle/oradata/testdb/haksa02.dbf' size 20M;

--> 테이블 스페이스에 datafile을 추가함으로 디스크를 늘려줌

 

2) 테이블스페이스의 기존 Datafile 리사이즈

alter database datafile '/oracle11/oracle/oradata/testdb/haksa01.dbf'

resize 20M;

--> datafile 리사이즈

 

3) Autoextend 설정을 on 으로 변경

alter database datafile '/oracle11/oracle/oradata/testdb/haksa2_01.dbf'

autoextend on;

--SELECT * FROM DBA_DATA_FILES; 조회시 autoextensible yes로 바뀐거 확인

======> 파일 추가시 자동으로 사이즈가 늘어남

 

select file_name, tablespace_name, bytes/1024/1024 MB

from dba_data_files;

Tablespace Offline

- 더 이상 해당 Tablespace에 접근하지 않겠다는 의미로 읽기도 쓰기도 되지 않는 상태

- Tablespace의 위치 이동, 장애 복구 시 주로 사용

- Normal, Temporary, Immediate Mode 존재

1) Normal Mode

- 가장 일반적인 offline 방법

- Tablespace가 정상일 경우만 가능

- alter tablespace tablespace_name offline;

2) Temporary Mode

- offline 시키고자 하는 Tablespace의 Data file에 하나라도 이상이 생기게 될 경우 수행

- alter tablespace tablespace_name offline temporary;

3) Immediate Mode

- Data file에 장애가 나서 데이터를 내려쓰지 못하는 상황에서 offline을 해야 하는 경우

- 반드시 archive log mode일 경우에만 사용

- 나중에 online시 복구하라고 메시지 나옴

- alter tablespace haksa offline immediate;

 

--haksa테이블 스페이스 offline

alter tablespace haksa offline;

 

--scn 정보 확인

select a.file#

,a.ts#

,b.name

,a.status

,a.checkpoint_change#

from v$datafile a

,v$tablespace b

where a.ts# = b.ts#;

 

 

--haksa테이블 스페이스 online

alter tablespace haksa online;

 

 ----> 테이블 스페이스 off,online 작업 후에는 여유 있다면 checkpoint 갱신 --alter system checkpoint;

-- 정상적인 오프라인 후 온라인 시킬 시 안된다면 recover 적용

 

Data File Offline

- Data File 단위로도 offline이 가능

- archive mode 경우

  : alter database datafile '.dbf 경로' offline;

- No archive mode 일 경우에는 offline drop

  : alter database datafile 'dbf 경로' offline drop;

- Data file offline은 online 전에 recover 필요

- Data file은 Tablespace에 여러 개 존재하므로 Data File Offline은 비효율

 

alter database

datafile '/oracle11/oracle/oradata/testdb/haksa01.dbf'

offline; -- no archive mode가 아닐 때 하면 에러남

 

alter database

datafile '/oracle11/oracle/oradata/testdb/haksa01.dbf'

offline drop;

 

--scn 정보 확인

select a.file#

,a.ts#

,b.name

,a.status

,a.checkpoint_change#

from v$datafile a

,v$tablespace b

where a.ts# = b.ts#; 

 

alter tablespace haksa offline temporary;

 

alter tablespace haksa online;

--> dbf 파일 자체를 오프라인했기 때문에 recovery 작업을 해야 함

 

<sysdba> recover tablespace haksa;

 

Data File Rename ( = 파일 위치 옮기는 것 )

- 특정 디스크에 있는 데이터 파일들의 용량이 점점 증가하여 다른 더 큰 용량의 디스크를 설치 한 후 Data file을 이동 시 주로 사용

- Datafile이 사용 중일 때는 절대로 이동시키거나 복사하면 안 된다.

- Tablespace를 Offline 하거나, Shutdown 한 후 작업해야 함

- 일반 Tablespace는 Offline후 작업

- Offline이 불가능한 SYSTEM, UNDO, Default Temp Tablespace는 Shutdown 후 작업 가능

 

◆ 일반 Tablespace Rename 절차

1) 해당 Tablespace Offline

alter tablespace haksa offline; <ORANGE>

 

2) Datafile 물리적 rename(mv보다 cp로 하는 것이 안전)

cp /oracle11/oracle/oradata/testdb/haksa01.dbf /home/oracle/oradata/testdb/haksa01.dbf <XShell>

 

3) Control file 정보 변경

--controlfile 상의 datafile rename

alter tablespace haksa

rename datafile '/oracle11/oracle/oradata/testdb/haksa01.dbf'

to '/home/oracle/oradata/testdb/haksa01.dbf';

 

alter tablespace haksa

rename datafile '/oracle11/oracle/oradata/testdb/haksa02.dbf'

to '/home/oracle/oradata/testdb/haksa02.dbf';

 

4) 해당 Tablespace Online

alter tablespace haksa online;

alter system checkpoint;

 

◆ SYSTEM Tablespace Rename 절차

1) shutdown

2) Startup mount

3) Data file 물리적 rename

cp /oracle11/oracle/oradata/testdb/system01.dbf /home/oracle/oradata/testdb/system01.dbf <XShell>

4) Control file 정보 변경

alter database rename file '/oracle11/oracle/oradata/testdb/system01.dbf' to '/home/oracle/oradata/testdb/system01.dbf';

5) Open

select * from dba_data_files where tablespace_name='SYSTEM';

 

 

◆ 실습 : 리두 로그 파일 이동

--목적

--각 group의 member를 /oracle11 -> /home 영역으로 이동

--각 group에 /home영역에 member 생성

--리두 로그 확인

select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status

from v$logfile a, v$log b

where a.group# = b.group#;

--로그 파일 확인

select * from v$logfile;

 

SQL> shutdown immediate

SQL> startup mount

--cp

cp /oracle11/oracle/oradata/testdb/redo01.log /home/oracle/oradata/testdb/redo01_b.log

cp /oracle11/oracle/oradata/testdb/redo02.log /home/oracle/oradata/testdb/redo02_b.log 

 

cp /oracle11/oracle/oradata/testdb/redo03.log /home/oracle/oradata/testdb/redo03_b.log

--control파일 변경

alter database rename file '/oracle11/oracle/oradata/testdb/redo01.log' to '/home/oracle/oradata/testdb/redo01_b.log';

alter database rename file '/oracle11/oracle/oradata/testdb/redo02.log' to '/home/oracle/oradata/testdb/redo02_b.log'; 

 

alter database rename file '/oracle11/oracle/oradata/testdb/redo03.log' to '/home/oracle/oradata/testdb/redo03_b.log';

-- member 추가

alter database add logfile member

'/home/oracle/oradata/testdb/redo1_2.log' to group 1,

'/home/oracle/oradata/testdb/redo2_2.log' to group 2,

'/home/oracle/oradata/testdb/redo3_2.log' to group 3;

 

--member 확인

select member from v$logfile;

 

◆ Tablespace 삭제

- drop tablespace tablespace_name;

- Tablespace에 Table이 하나라도 있으면 삭제되지 않는다

- drop tablespace tablespace_name including contents and datafiles; 로 삭제 가능

- ASM이 아니라면 물리적으로도 data file 삭제 필요

 

◆ Undo Tablespace

- DML 작업 수행 시 원본 데이터(Undo data)를 저장하는 장소

- Undo Data를 저장하는 실질적 공간을 Undo Segment라 하고 이 Segment를 저장하는 Tablespace가 Undo Tablespace

- 기본적으로 하나의 세션 당 하나의 Undo segment를 할당

- Undo Tablespace에는 Undo Data 외의 다른 데이터는 저장될 수 없다

- 사용자가 관여하거나 수정할 수 없고 Oracle Server Process가 직접관리

- Undo Tablespace는 Instance당 여러 개가 존재할 수 있으나, 사용되는 것은 한번에 1개뿐

- 관리방법으로는 자동 모드와 수동모드가 있으며, Default는 자동모드

- undo_management= auto | manual 로 관리모드 변경 가능하지만 DB 재기동 필요

- Segment의 개수를 수동으로 관리하는 수동모드와는 달리 자동모드에서는 자동적으로 Segment 할당을 관리, 새로운 Segment를 생성

□ Undo Tablespace 사용 목적

1) Transaction Rollback : 최종 commit 하지 않고 Rollback 하고자 할 때 Undo 데이터를 이용하여 과거 이미지 전달

2) Transaction Recovery (instance Recovery시 rollback 단계)

: Instance Crash 발생 후 Redo를 이용해 Roll forward 단계가 완료되면 최종 Commit 되지 않은 변경사항까지

모두 복구할 때 쓰임

3) Read Consistency : 읽기 일관성을 위해 사용

 

 

*** select는 before이미지만 조회하는데 undo segment에 할당된 before 이미지가 사라지게되면 -> select 절은 조회자료가 사라져

 

Rollback

◆ Undo Segment 할당원리

1. 현재 아래와 같이 4개의 Segment 할당되어 4개의 사용자가 사용 중

A

B

C

D

2. 새로운 사용자 E가 DML 수행

3. E의 Server Process가 Undo Segment 할당을 위해 기존 Segment중에 재사용 할 것이 있는지 찾음

▶ 새로운 세션의 DML이 발생할 때마다 신규로 Undo Segment를 할당 받지 않음

▶ 기존 Segment는 Commit이나 Rollback이 되면 재사용 가능한 상태가 되고

즉시 Segment의 내용이 flush 되는 것이 아니고 재사용 되면서 flush

4. A가 그 시점에 Commit 수행

5. E는 A가 쓰고 있던 Segment를 재사용

 

E

B

C

D

 

6. 새로운 사용자 F가 접속해 DML 수행

7. 아무도 Commit이나 Rollback을 하지 않음

E

B

C

D

F

8. F는 새로운 Segment를 할당 받음

9. 계속해서 새로운 Segment를 할당 받다가 Datafile의 허용범위까지 늘어나면

하나의 Segment를 2개 세션 이상의 Undo Data를 함께 기록

10. 그 이후에도 공간이 없으면 해당 트랜잭션은 에러 발생 -> Commit되지 않은 모든 데이터들은 Rollback

 

◆ Undo Segment 특징

- Commit을 수행해도 Undo Segment안에 Undo Data는 지워지지 않고 남아있기 때문에 해당 Data file 사이즈를 줄일 수는 없음

- Commit 수행 시 다른 서버 프로세스가 덮어 쓸 수 있게 해주는 것일 뿐 Undo Segment 안의 자료를 지우는 것은 아님

- Undo Tablespace 크기가 비정상적으로 작을 경우에는 관리자가 다른 큰 undo Tablespace를 신규로 만들고,

Undo Tablespace를 신규 Undo Tablespace로 변경시킨 후 기존 Undo Tablespace를 삭제해 주어야 함

 

◆ Undo 관련 주요 파라미터

1. UNDO_RETENTION(초 단위)

- COMMIT 수행 후에도 해당 UNDO SEGMENT 내의 데이터를 다른 서버 프로세스가 덮어 쓰지 못하도록 막아주는 시간

- 그러나, Undo segment 여분이 있을 경우에만 적용되며, 만약 모든 Undo Segment가 사용 중일 경우에는 적용되지 않고

Undo Segment가 재사용되어 진다.

- 관련 에러 : Snap shot too old

* Ora-01555 : Snap shot too old

- 사용자 A : 1시간 정도 수행되는 특정 데이터의 집계작업을 수행 중

- 사용자 B : 사용자 A가 집계중인 데이터를 update 한 후 commit할 경우

- 사용자 A : 사용자 B가 변경전의 데이터를 undo Segment에서 찾아서 집계하게 됨

- 만약 B가 Commit 한 후 사용자 C가 사용자 B의 Undo Segment를 덮어 썼다면 사용자 A는 집계 쿼리를

완성할 수 없어 에러 발생

- 이때 발생하는 에러가 Snap shot too old

2. UNDO_RETENTION_GUARANTEE

- Undo_retention으로 설정된 값 만큼 Segment가 재사용 되지 않도록 보장해주는 파라미터

- 즉 Undo Segment가 부족하면 undo retention가 설정되어 있더라도 무시하고 재사용하는데 이를 재사용되지 않도록 보장

- commit을 수행해도 특정 기간 동안 Undo Segment를 재사용하지 않기 때문에 Undo Tablespace가 커짐

- default : NOGUARANTEE

- alter tablespace undotbs1 retention guarantee;

 

Undo 사용 현황 조회하기

1번 터미널

SQL > update emp set sal=5000 where empno=7902;

 

undo 사용량(record, block) 조회

SQL > select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk

from v$session a, v$transaction b

where a.saddr = b.ses_addr;

 

각 세션 별로 사용 중인 Undo Segment 확인하기

SQL > select s.sid, s.serial#, s.username, r.name "UNDO SEG",

s2.bytes/1024/1024 "UNDO SEG SIZE(MB)"

from v$session s, v$transaction t, v$rollname r, dba_segments s2

where s.taddr=t.addr

and t.xidusn=r.usn

and r.name=s2.segment_name;

***delete가 update보다 undo사이즈를 많이 잡는 이유 : update는 행을 지정해서 하지만 delete는 포함 행 모두를 undo segment에 저장

 

-- undo_retention에 의해 expired(undo_retention이 지나서 재사용 가능 공간)된 영역과

-- unexpired(아직 undo_retention이 지나지 않은)된 영역

-- active(실제 사용 중)인 영역

-- free(아직 할당받지 않은) 영역

select tablespace_name,

status,

sum(bytes)

from dba_undo_extents

group by tablespace_name,

status

order by 1;

 

-- 새로운 undo영역 생성 후 할당

create undo tablespace undo01

datafile '/oracle11/oracle/oradata/testdb/undo01.dbf'

size 100M;

select BYTES/1024/1024

from dba_data_files

where tablespace_name like '%UNDO%';

select * from dba_data_files;

alter system set undo_tablespace=undo01;

---> 신규 세션들에 대해서는 undo01에서 작업하고 기존은 그 스페이스에서 계속 작업함

 

drop tablespace UNDOTBS1; -- ACTIVE를 물고있으면 삭제가 안됨! 먼저 ACTIVE인지 확인하고 삭제진행

 

◆ TEMP Tablespace

- Sort Operation(PGA영역) 시에 임시로 사용됨

- 영구 테이블 스페이스로 변경 안 됨

- 성능을 위해 사용자 별로 1개씩 할당 권장

- default temporary tablespace 는 삭제 안 됨 --> 다른 temp tablespace 생성 후 default로 변경 후 삭제

 

--TEMP TableSpace 생성

create temporary tablespace temp2

tempfile '/oracle11/oracle/oradata/testdb/temp02.dbf' size 50M;

select * from dba_data_files; *dbf파일들을 모아 놓은 뷰

select FILE_NAME, BYTES/1204/1024 from dba_temp_files; *temp dbf파일들을 모아 놓은 뷰

select sum(bytes)/1024/1024/1024

from dba_segments

where 1=1

and SEGMENT_NAME like 'TEMP%';

select * from dba_users;

alter database default temporary tablespace temp2;

create temporary tablespace temp3

tempfile '/oracle11/oracle/oradata/testdb/temp03.dbf' size 50M;

 

alter user hr temporary tablespace temp3;