개발은 너무해

Row Migration(행마이그레이션)이란 하나의 레코드는 하나의 블록안에 들어가야하는데 
update가 발생하여 레코드값이 커지면서 블록안에 남은 여유공간이 없어 그 레코드가 다른 블록으로 옮겨지게 되는 현상입니다.

행 마이그레이션을 방지하기 위해서 블록에 여유공간을 남겨 놓는데 이것을 PCTFREE 라 합니다.

PCTFREE의 필요성을 한번 테스트 해봅시다.

먼저 2개의 테이블 생성.

cf.

PCTUSED : 블록에 있던 데이터가 삭제가 되었을 때 어느 정도의 공간이 남게 된다면 그 블록은 다시 재사용이 가능한데 이것을 PCTUSED라고 하고 기준치를 퍼센트 단위로 설정하게 된다.

기준치를 높게 잡는다면 블록의 재사용률이 낮아질 것이고, 낮게 잡으면 행마이그레이션 일어날 확률이 높아질 것입니다.

SQL> select table_name, num_rows, chain_cnt, blocks from user_tables
  2  where table_name in ('AAA','BBB');

TABLE_NAME     NUM_ROWS  CHAIN_CNT     BLOCKS
-------------- -------------- ----------- -----------
AAA                             20000                0               65
BBB                             20000                0               80

현재 사용되어진 블록은 각각 65,80개가 사용되어지고 CHAIN_CNT를 확인하여보니 행마이그레이션은 일어나지 않았습니다.

여기서 각 테이블의 B 컬럼을 업데이트를 해보겠습니다.

SQL> update aaa set b='abcdefghijk';

20000 행이 갱신되었습니다.

SQL> update bbb set b='abcdefghijk';

20000 행이 갱신되었습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> analyze table aaa compute statistics;

테이블이 분석되었습니다.

SQL> analyze table bbb compute statistics;

테이블이 분석되었습니다.

SQL> select table_name, num_rows, chain_cnt, blocks from user_tables
  2  where table_name in ('AAA','BBB');

TABLE_NAME     NUM_ROWS  CHAIN_CNT     BLOCKS
-------------- -------------- ----------- -----------
AAA                             20000            5539             103
BBB                             20000                0               80

SQL>

BBB 테이블은 사용되어진 블록의 개수가 변하지 않고 그대로 80개의 블록이 사용되었지만, AAA 테이블은 사용되는 블록이 103개로 늘어나면서 행마이그레이션이 발생했다는 것을 알수있습니.  

결과적으로 PCTFREE 를 설정해줌으로써 큰 데이터 값으로 업데이트시에도 행마이그레이션이 발생하지 않았습니다.

행마이그레이션이 발생하지 않기 위해서는 PCTFREE, PCTUSED 에 대한 비율도 잘 맞춰주어서 블록의 빈 공간에 대해서도 효율적으로 관리를 해주어야 할 것 입니다.

이러한 번거로움 때문에 9i부터는 PCTUSED만 자동관리가 된다.

C:\> sqlplus "/as sysdba"

archive mode로 변경하기 전에 확인 해 봐야 할 것!!

SQL> archive log list

데이터베이스 로그 모드              아카이브 모드가 아님
자동 아카이브             사용 안함
아카이브 대상            USE_DB_RECOVERY_FILE_DEST
가장 오래된 온라인 로그 순서     32
현재 로그 순서           33

SQL>show parameter log_archive_max_processes; (활성화 되고 있는 archive process 확인)

NAME                                 TYPE               VALUE
--------------------------- --------------  -------
log_archive_max_processes  integer                      2

process를 늘려주고 싶을 때 : alter system set log_archive_max_processes=3;

저는 default 값으로 활성화 시키도록 하겠습니다.

이제 본격적으로 간단한 설정을 해보도록 하겠습니다.

archive file 저장 위치를 지정해준다.

SQL> alter system set log_archive_dest_1='location=c:\PROD\DISK1\Archive\arch01 reopen';

SQL> alter system set log_archive_dest_2='location=c:\PROD\DISK2\Archive\arch02 reopen';

cf.

mandatory : 아카이브하는 작업이 성공적으로 완료되어야 온라인 리두 로그파일을 겹쳐 쓸 수있음

optional : 아카이브하는 작업이 성공적으로 완료되지 않은 경우에도 온라인 리두 로그 파일을

              재사용할 수 있음.

reopen : 키워드와 함께 숫자를 지정하면, 실패 후 지정된 시간(초)이 경과한 다음 다시 쓰기를 시도함.

             default = 300, ex) reopen=500 

REOPEN을 지정하지 않은 경우 선택 대상에서 발생한 오류는 기록된 다음 무시되고 더 이상 이 대상으로

리두 로그가 보내지지 않는다. 필수 대상에서 오류가 발생하면 아카이브가 성공적으로 수행될 때까지 온라인 리두 로그를 재사용할 수 없다.

저는 process를 2개를 활성화 하였기 때문에 경로를 2개를 지정해주었습니다. 

서로 다른 디스크에 저장을 시켜주어야 하지만 여건상 같은 디스크에 넣을수 밖에 없었네요.^^

show parameter log_archive_dest 를 보면 위치가 설정된 것을 확인해 보실수 있습니다.

* process를 여러개 지정해주는 이유

1. 대량의 트랙잭션 로드 작업 과 동시에 다른 작업들이 맞물려 wait가 발생되는 archive 이목현상을

    방지하기 위해서

2. archive log 파일을 다중화 하여 복구를 대비하기 위하여(archive file도 깨질수있기때문임.)

SQL> alter system set log_archive_format='ARC%T%S%R.ARC' scope=spfile;

※ 주의할 점은 9i까지는 %s만 넣어도 상관없었지만 10g부터는 %t%s%r이 반드시 들어가야한다.

    참고로 저는 %s%t 만 넣었다가 DB가 startup이 안되서 복구시키느냐구 고생을 좀 하였습니다.^^;;

    제가 경험한 에러 및 복구 방안은 Oracle Error Catalog에 추후 등록시키도록 하겠습니다.

SQL> shutdown immediate;

데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.

SQL> startup mount;

ORACLE 인스턴스가 시작되었습니다.

Total System Global Area  285212672 bytes
Fixed Size                  1287016 bytes
Variable Size              92277912 bytes
Database Buffers          188743680 bytes
Redo Buffers                2904064 bytes
데이터베이스가 마운트되었습니다.

SQL> alter database archivelog;

데이타베이스가 변경되었습니다.

SQL> alter database open;

데이타베이스가 변경되었습니다.

이렇게 하면 archive로 설정이 되었습니다.

잘 되었는지 확인 해 봅시다.

SQL> archive log list  --처음에  확인했던 명령어이죠. 

데이터베이스 로그 모드              아카이브 모드
자동 아카이브             사용
아카이브 대상            C:\oraclexe\oradata\Archive\Arch2
가장 오래된 온라인 로그 순서     36
아카이브할 다음 로그   37
현재 로그 순서           37

SQL> alter system archive log current;

시스템이 변경되었습니다.

SQL>

Archive file이 저장되는 경로에 가서 파일이 생성되었는지 확인해 봅시다.

잘 생성되었다면 archive mode로 이상없이 변경 된 것입니다.

9i보다는 많이 간단해 진 것 같습니다.

또한 log_archive_start는 9i에서는 true로 설정을 해주어야했지만,

10g에서 obsolete parameter 입니다. 설정에 상관없이 true로 인식을 합니다.

--추가글(20100429)

만약 archive process를 2개로 가동시키다가 1개로 줄이고 싶다면 archive모드를 해제한후 다시 설정을 해야한다.

(지금까지 내가 알기로는 그렇다.)

Rollup 과 Cube는 9i 부터 생긴 함수 입니다.

Rollup 과 Cube는 8i 부터 생긴 함수 입니다.

그러므로 예전에 8i 때까지 사용하던 union과 비교를 해보시면 쉽게 이해 하실 수 있습니다.

8i와 9i의 쿼리문을 비교해보시면 쉽게 이해하실수 있을 거라 생각되어져 별다른 설명은 첨부하지 않았습니다. HR 계정에서 다음 쿼리문을 차근차근히 살펴보시고 실행을 한번 해보세요.

SQL> conn hr/hr

(1) Rollup

-Ver. 9i-

select department_id,job_id,avg(salary),count(*)
from employees
group by rollup(department_id,job_id)

----여기서 하나더 grouping 에 대해서도 함께 알아 보도록 하겠습니다.----

       (저도 확실하게 설명해 드릴수는 없지만 아는데까지만요^^;)

select department_id,job_id,count(*) cnt,avg(salary) avgsal,grouping(department_id) g1,grouping(job_id) g2
from employees
group by rollup(department_id,job_id)

다음과 같은 결과가 나올 것입니다.

DEPARTMENT_ID JOB_ID            CNT     AVGSAL         G1         G2
---------------- ---------- ---------- ---------- ------- -------
                     10 AD_ASST                1          4400          0          0
                     10                              1          4400          0          1
                                                      2          7000          1          1

G1 ,G2 컬럼의 0과 1의 숫자값은 그룹을 묶어 주었는지 여부를 판단할때 쓰입니다.

0은 그룹으로 묶어주었다

1은 그룹으로 묶어주지 않았다 를 의미합니다.

G1, G2의 값이 각각 0값인 데이터들을 보시면, 하나는 DEPARTMENT_ID 가 NULL이고, JOB_ID가 SA_REP 인것들을 그룹으로 묶어서 계산되어진거고, 아래를 또 보시면 DEPARTMET_ID가 10이고 JOB_ID가 AD_ASST인 것들을 그룹으로 묶어서 계산되어진거라 보시면 됩니다.

나머지 G1 이 0 이고 G2가 1인 것들은 DEPARTMENT_ID 값이 각각 NULL OR 10인 것들만 묶어서 계산되어졌다고 보시면 됩니다.

이해하셨을지 모르겠네요.ㅋㅋ^^;

-Ver. 8i -    8i 때는 어떻게 위 함수를 사용했는지 보시죠~

select department_id,job_id,avg(salary) avgsal,count(*) cnt

from employees
group by department_id,job_id
union
select department_id,to_char(null),avg(salary) avgsal,count(*) cnt
from employees
group by department_id
union
select to_number(null),to_char(null),avg(salary) avgsal,count(*) cnt
from employees

Tip. 참고로 실행 계획도 겸하여 보신다면 어떤 쿼리문이 효율적인지 금방 알수 있을 겁니다.

SQL> set autotrace on exp

아래와 같은 오류가 난다면 utlxplan.sql을 실행 시켜주세요!!

SP2-0613: PLAN_TABLE 포맷 또는 존재를 입증할 수 없습니다
SP2-0611: EXPLAIN 레포트를 사용 가능시 오류가 생겼습니다
SQL> @%oracle_home%\rdbms\admin\utlxplan.sql;

테이블이 생성되었습니다.

SQL> set autotrace on exp

(2) Cube

-Ver. 9i-

select department_id,job_id,count(*) cnt,avg(salary) avgsal

from employees
group by cube(department_id,job_id)

-Ver. 8i-

select department_id,job_id,count(*) cnt,avg(salary) avgsal
from employees
group by department_id,job_id
union
select department_id,to_char(null),count(*) cnt,avg(salary) avgsal
from employees
group by department_id
union
select to_number(null),job_id,count(*) cnt,avg(salary) avgsal
from employees
group by job_id
union
select to_number(null),to_char(null),count(*) cnt,avg(salary) avgsal
from employees

(3) Grouping set

-Ver. 9i-

select deptno, job, count(*) cnt, avg(sal) avgsal

from emp

group by grouping sets(deptno,job)

-Ver. 8i-

select deptno, null job, count(*) cnt, avg(sal) avgsal

from emp

group by deptno

union

select null, job, count(*) cnt, avg(sal) avgsal

from emp

group by job

(4) Quiz

마지막으로 문제 하나 내도록 하겠습니다.

제 친구가 회사에서 풀어보라고 했던 문제인데 풀어보라고 저에게 주더군요.

저도 나름대로 풀어보았습니다. 어거지로 풀긴 했지만요..ㅋㅋㅋ

문제] 아래 표를 보시면 원시 Table을 결과자료 처럼 나오게 하면 되는 겁니다.

         (어떤 쿼리문을 사용하시던 간에 나오기만 하면 되는거 아닌가요?^^ㅋㅋㅋㅋ)

        - 제가 한 쿼리문은 쪽지를 보내주시면 보내드리도록 하겠습니다.^^

           참고로 저는 rollup을 사용하였습니다.

- 원시 Table -                           - 결과자료 -

-- 원시 Table 만들기.

컬럼 이름은 아무거나 해도 될 듯 싶습니다.

create table test1
(no number(2)
,a varchar2(2)
,b number(2)
,c number(10))
tablespace users;

insert into test1 values(1,'A',1,100);
insert into test1 values(1,'A',1,100);
insert into test1 values(1,'B',1,200);
insert into test1 values(1,'B',1,200);
insert into test1 values(2,'A',1,300);
insert into test1 values(2,'A',1,300);

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

내용은 길지만 차근차근 따라하신다면 그렇게 긴 내용은 아닙니다.

업무 중에 할려니 힘드네요..ㅋㅋㅋ

감사합니다.

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인 데이터를 넣을려고 하니 오류메세지가 나오는 것입니다.

알고보면 굉장히 간단하고 쉬운건데 너무 제가 장황하게 설명한 것 같습니다.

저도 이것에 대해 몰랐습니다. 인터넷에 찾아봐도 확실한 대답이 없었던 터라 책도 찾아보고

인터넷 자료도 수집하면서 몇 시간을 고생했답니다. 근데 너무 간단하죠!^^ㅎㅎㅎ