Neo-Oracle Home

개요

http://blog.oracle48.nl/oracle-database-undo-space-explained/

만약 active undo extents만 존재하여 더이상 해당 트랜잭션에 대한 undo 공간을 확보할 수 없을 경우 ORA-30036 unable to extend segment in Undo tablespace 에러를 받게 되고 자동으로 트랜잭션은 rollback되게 된다.

이 현상이 얼마나 일어났는지 확인은 아래 sql을 통해 가능하다.

만약 active상태의 extents이 필요한데 expired extents이 없다면 unexpired(undo_retention 기간이 아직 지나지 않은 상태) extents를 빼앗게(steal) 되는데 해당 count를 확인하는 법은 아래와 같다.

※ steal 못하게 하려면 undo 테이블스페이스에 대해 retention guarantee 옵션을 주면 된다.

단.. 이러면 이제 unexpired extent를 뺏지 않기 때문에 조금 긴 dml의 경우 실패할 경우가 자주 생길 것이다. 이는 즉 ORA-30036과 ORA-01555 중 어느것을 선택하느냐이다.

만약 이렇게 steal된 unexpired extents에 대해 read consisteny read 수행하면 ORA-01555 snapshot too old 발생하고 이 역시 v$undostat에서 확인 가능하다.

※ undo extent 크기와 퍼센트를 확인하는 쿼리. 단 free는 포함되지 않음

■ undo size tune

1. 기본문. 예외처리가 없음(20)
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
begin
        select 1
        into dummy
        from em
        where department_id = v_department_id;
        dbms_output.put_line('ok');
end;
/

2. 사전정의된 예외처리방법(20)
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
begin
        select 1
        into dummy
        from em
        where department_id = v_department_id;
        dbms_output.put_line('ok');
exception
        when too_many_rows then
          dbms_output.put_line('too many rows return');
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

3. 사전정의안된 오류 예외처리 방법(20). too_many_rows 가 없다고 가정
declare
        v_department_id em.department_id%type := &deptno; --deptno를 받음
        dummy number;   --의미없지만 select가 돌아가게 하기 위해
       
        toomany exception;                     --1. exception 명 선언
        pragma exception_init(toomany, -1422);  --2.
begin
        select 1
        into dummy  --의미없지만 select가 돌아가게 하기 위해
        from em
        where department_id = v_department_id;
        dbms_output.put_line('ok');
exception
        when toomany then
          dbms_output.put_line('too many rows return');
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

4. 유저 정의 예외처리(300) 에러메시지 발생, 중지
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
begin
        if v_department_id >= 300 then
          raise_application_error(-20000, '300이상의 값을 입력하지 마시옵소서');
        else
          select 1
          into dummy
          from em
          where department_id = v_department_id;
            dbms_output.put_line('ok');
        end if;
exception
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

5. 유저 정의 예외처리(300) 실행 후 에러 메시지 정상 핸들링
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
        over_dept_err exception;
        pragma exception_init(over_dept_err, -20000);
begin
        if v_department_id >= 300 then
          raise_application_error(-20000, '300이상의 값을 입력하지 마시옵소서');
        else
          select 1
          into dummy
          from em
          where department_id = v_department_id;
            dbms_output.put_line('ok');
        end if;
exception
        when over_dept_err then
          dbms_output.put_line(sqlerrm);
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

이거 말고 더 예제는 있습니다.

1. rollup을 grouping sets로 바꿔본 예

-rollup

select manager_id, job_id, sum(salary)
from employees
where manager_id is not null
and manager_id < 120
group by rollup(manager_id, job_id);

-rollup to grouping sets

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by grouping sets (
                            (manager_id, job_id),
                            (manager_id),
                            ()
                          );

2. cube를 grouping sets로 바꿔본 예

-cube

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by cube(manager_id, job_id);

-cube to grouping sets

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by grouping sets(
                          (),
                          (job_id),
                          (manager_id),
                          (manager_id, job_id)
                          );

그런데 정렬이 동일하지 않다. 그래서 정렬도 추가한다. 단 null값은 기본적으로 오름차순시 가장 큰 값 위치로 정렬되기에 이를 바꾼다.

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by grouping sets(
                          (),
                          (job_id),
                          (manager_id),
                          (manager_id, job_id)
                          )
order by 1 nulls first, 2 asc nulls first;

한번 grouping sets을 터득해보니 cube와 rollup보다 자유로워진 느낌이다.
 

자주 헷갈리는 제약조건 지연에 대해 적어본다.

- 기능 : 기본적으로 제약조건이 걸린 컬럼에 대한 DML 수행시 한건씩 제약조건을 검사하며 제약조건 위반시 statement level rollback되는데

제약조건에 deferred를 설정하면 이를 한건씩이 아닌 commit 시에 트랜잭션 단위로 제약조건을 검사하고 제약조건 위반시 자동으로 트랜잭션을 rollback하는 기능

- 언제 쓰나? : 참조무결성 제약조건이 설정된 테이블들에 DML을 하려고 할 경우. 예를 들어 사원테이블의 부서번호를 60번에서 65번으로 바꿔야 하는데 부서테이블에는 60번만 존재할경우 부서테이블이나 사원테이블 어느 한군데서 update를 치면 FK 제약조건(걸려있으면)에 위배되게 된다. 이때 사원테이블의 FK를 deferred 시키고 진행하고 commit 하면 되겠다.

DEFERRABLE | NOT DEFERRABLE

- deferrable 과 not deferrable은 constraint(제약조건)를 지정(생성) 할때만 설정가능하며 not deferrable이 기본값이다.

- 애초에 deferrable이 설정 되어 있어야 그 아래 단계인 deferred와 immeidiate를 변경 가능하게 된다.

- 생성시 구문. 컬럼 레벨 테이블 레벨, alter 등 방법을 다 적기엔.. 밤이 늦었기에 alter table 기준으로 설명

SQL> create table dept2 as select * from departments;

SQL> alter table dept2

add constraint dept2_id_pk

primary key (department_id)

deferrable;

SQL> select status, deferrable, deferred
            from user_constraints where constraint_name ='DEPT2_ID_PK';

STATUS   DEFERRABLE     DEFERRED
-------- -------------- ---------
ENABLED  DEFERRABLE     IMMEDIATE

DEFERRED | IMMEDIATE

- 기본 값은 immediate이며, 만약 deferrable 상태가 not deferrable인데 deferred로 변경하면 아래와 같은 에러가 나온다.

SQL Error: ORA-02447: cannot defer a constraint that is not deferrable

에러 해설을 찾아보면

02447. 00000 -  "cannot defer a constraint that is not deferrable"
*Cause:    An attempt was made to defer a nondeferrable constraint
*Action:   Drop the constraint and create a new one that is deferrable

action에 제약조건을 drop하고 새로운걸 만들면서 deferrable로 설정하라고 한다.

위에서 말했듯이 deferrable 과 not deferrable은 constraint(제약조건)를 지정(생성) 할때만 설정가능하다는 것을 여기서도 알 수 있다.

- 방법. 제약조건 생성때 아예 아래처럼도 가능하다.

SQL> alter table dept2

add constraint dept2_id_pk

primary key (department_id)

deferrable initially deferred;

- 방법2 set constraints 사용. 이 방법이 가장 많이 쓰일 것이다.

SQL> set constraints dept2_id_pk deferred;

SQL> set constraints dept2_id_pk immediate;

- 방법3 alter session set constraints 사용. 그런데 테스트 해보니 결국 constraints의 속성에 따라 가기에 의미가 없어보인다. 아니면 약간 다른 용도로 사용하는 것도 같다.

SQL> alter session set constraints = immediate;

SQL> alter session set constraints = deferred;

평균의 신뢰도를 파악하기 위해 분산//혹은 표준편차를 구한다.

관련한 오라클 함수는 아래와 같다.

variance(=var_samp) : 표본분산

stddev(=stddev_samp) : 표본표준편차

var_pop : 모집단 분산

stddev_pop : 모집단 표준편차

분산을 먼저 구하고 표준편차를 구함

사례수가 20이고 평균이 8775 이며, 편차들의 제곱의 합은 608597500 이다.

모집단(전체) 분산과 모집단(전체) 표준편차

- 모집단 분산: 편차의 제곱의 합을 사례수로 나눈다. => 608597500/20 = 30429875

- 모집단 표준편차: 모집단분산을 제곱근한다. => 30429875^(1/2) = 5516.328036

표본(샘플)분산과 표본(샘플)표준편차

- 표본 분산: 편차의 제곱의 합을 사례수-1로 나눈다. => 608597500/19 = 32031447.37

- 표본 표준편차: 표본분산을 제곱근한다. => 32031447.37^(1/2)=5659.633148

샘플분산과 샘풀표준편차는 만약 이 사례수가 200명 중 20명을 샘플로 추출한 것이라면 20이 아닌 '자유도' 즉 N-1 개념이 들어가야 한다. 통계개념.

cp /etc/DIR_COLORS ~/.dir_colors 

- number형 자료는 null 연산시 결과가 항상 null이다.

- 항상 null이 포함될 우려가 있다면 nvl을 사용한다.

-숫자형 컬럼은 0이나 1등 적절한 숫자로 치환한 후 연산한다.

-문자형 컬럼은 ' ' 이나 특정 문자로 치환하여 조건절에 이용한다.

-null 비교연산시 where A is null 혹은 where A is not null을 사용한다.

-절대로 A=null 혹은 A != null을 사용하지 말자. 에러가 안나고 엉뚱한 결과가 나온다.

유용한 스크립트로 생각된다.

#!/bin/sh
tail -fn0 로그파일명 | \
while read line ; do
        echo "$line" | grep "액션하고자하는문자열"
        if [ $? = 0 ]
        then
                액션스크립트혹은명령어
        fi
done

이후 실행은 nohup ./action.sh &

cacti용으로 centos에서 주로 쓰는 기본 설정

com2sec local 127.0.0.1 커뮤니티값
com2sec mynetwork 192.168.0.0/16 커뮤니티값
group MyROGroup v1 mynetwork
group MyROGroup v2c mynetwork
view all included .1 80
view systemview included system
#       group          context sec.model sec.level prefix read       write  notif
access  MyROGroup      ""      any       noauth    exact  all        none none

 # disk used #
# disk /
# disk /log
# disk /usr
pass .1.3.6.1.4.1.4413.4.1 /usr/bin/ucd5820stat

!주의! sa 계정의 비밀번호를 설정하지 않고 윈도우 인증을 차단할 경우 DB ADMIN 접속 불가

위 사항을 주의하고 미리 sa 계정을 설정하고 진행

보통 서버의 administrator가 sql server의 windows 인증에 사용된다.

1. sa나 윈도우 인증을 통해 관리자로 로그인

2. 보안-로그인-xxxx\Administrator 우측클릭-속성클릭

3. 상태-로그인: 사용 안 함 체크 후 확인