농부지기

[ Oracle - Tablespace ]

[Oracle  TABLE SIZE 및 INDEX SIZE(크기) 계산]

1. TABLE SIZE 계산 공식(ORACLE BLOCK SIZE : 2K 로 가정)

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

   $ sqlplus scott/tiger

     SQL> SELECT   GREATEST(4, ceil(ROW_COUNT /

                 ((round(((1958 - (initrans * 23)) *

                 ((100 - PCTFREE) /100)) / ADJ_ROW_SIZE)))) * BLOCK_SIZE)

                 TableSize_Kbytes

        FROM dual;

   *. 한 개의 BLOCK에 Available 한 Bytes - 1958

   *. 각 initrans 는 23 Bytes

   *. PCT_FREE : Table 의 pctfree 값(default 10)

   *. ADJ_ROW_SIZE : 각 row 의 평균 SIZE 추정치

   *. ROW_COUNT : table 의 row 의 갯수

   *. BLOCK_SIZE : 1 block의 크기 (단위: K)

예) table 이름이 EMP 일 경우

ROW_COUNT : select count(*) from emp;

ADJ_ROW_SIZE :

 analyze table emp compute statistics;

 (또는 건수가 매우 많을 때에는 compute 대신 estimate 사용)

 select avg_row_len

 from user_tables

 where table_name='EMP';

2. INDEX SIZE 계산 공식

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

   SQL> SELECT   GREATEST(4, (1.01) * ((ROW_COUNT /

                 ((floor(((2048 - 113 - (initrans * 23)) *

                 (1 - (PCTFREE/100))) /

                 ((10 + uniqueness) + number_col_index +

                 (total_col_length)))))) * DB_BLOCK_SIZE))

                 IndexSize_Kbytes

        FROM dual;

   *. 한 개의 block에 available 한 bytes ( 1935 or 2048 - 113 )

   *. 각 initrans 는 23 Bytes

   *. ROW_COUNT : table 의 row 의 갯수

   *. PCTFREE : Index 의 pctfree 값(default 10)

   *. number_col_index : Index 에서 column 의 수

   *. total_col_length : Index 의 길이 추정치

   *. uniqueness : 만일 unique index 이면 1, non-unique index 이면 0.

   *. DB_BLOCK_SIZE : 1 block의 크기 (단위: K)

 [ 테이블 DROP시 휴지통 기능 ]

1. 정의 : 테이블을 삭제 하면 오라클도 휴지통을 만들어 놓았다.

2. oracle 10g부터 존재

3. table을 drop 하면 : [ BIN$H+0On1PSLBbgRACAFyioeA==$0 ] 와 같은 형식의 테이블이 생성된다.

4. 휴지통 비우기
     SQL> purge recyclebin;                        사용자의 휴지통 비우기
     SQL> purge dba_recyclebin;                 휴지통내의 모든 놈들 비우기
     SQL> purge tablespace users;              users테이블스페이스의 휴지통 비우기
     SQL> purge table "휴지통내의 이름" ;    휴지통의 하나의 객체만 제거시

6. 휴지통 기능을 enable/disable 상태 확인 및 처리 방법

    상태확인 : SQL>  SELECT a.ksppinm, b.ksppstvl, b.ksppstdf 
                    FROM x$ksppi a, x$ksppcv b WHERE a.indx = b.indx AND a.ksppinm like '%recycle%'
                    ORDER BY a.ksppinm;

     처리방법 : * 10g Release1 의 경우
                                  SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;

                    * 10g Release2 의 경우 (R2는 default 값이 ON 이므로 OFF 로 만들어 버린다.)
                                  SQL> ALTER SESSION SET recyclebin = OFF;
                                  SQL> ALTER SYSTEM SET recyclebin = OFF;

 7. 휴지통: drop된 DB객체들을 아래의 상황이전까지 보유한다.
                - purge명령으로 영구히 삭제
                - undrop명령으로 drop된 객체를 복구
                - 테이블스페이스내 (휴지통의) 공간이 부족할때
                - 테이블스페이스가 extend될 때

8. 휴지통에 들어있는 객체들 보기
      user_recyclebin
      dba_recyclebin

9. 휴지통의 객체 복구
         SQL> flashback table "휴지통내의 이름" to before drop;
    예) SQL>flashback table "BIN$VPaE9OG4Qwa+KC0MesYKqw==$0" to before drop;

10. 휴지통에 넣지 않고 drop
      SQL> drop table 테이블명 purge;

11.

[ Oracle - Trigger 확장 ]

============================================================ Database Event Triggers and DDL Event Triggers in Oracle8i ============================================================ 8.1.X 부터는 Database내에서 database event에 의해서 fire되는 trigger로 새로운 type의 trigger을 제공한다. Database event로는 SERVERERROR, user logon, user logoff, database startup ,database shutdown이다. 또한 DDL event에 따라 fire되는 trigger도 만들 수 있다. ----------------------- Type Timing ----------------------- LOGON AFTER LOGOFF BEFORE STARTUP AFTER SHUTDOWN BEFORE SERVERERROR AFTER ----------------------- 예제 1 ) user logon을 audit하는 trigger CREATE TABLE log_info (login_date DATE, username VARCHAR2(20)); CREATE OR REPLACE TRIGGER log_errors AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR (1017)) THEN insert into system.log_info values(sysdate,'ORA-1017'); END IF; END; / CREATE OR REPLACE TRIGGER logon_audit AFTER LOGON ON DATABASE BEGIN insert into system.log_info values(sysdate,user); END; / $ sqlplus scott/tiger ( A session ) $ sqlplus system/manager ( B session ) SQL> select * from log_info; LOGIN_DATE USERNAME ---------- -------------------- 2000/06/15 SCOTT 2000/06/15 SYSTEM 만약 ORA-01017 error가 발생하면 log_errors trigger가 error가 발생한 시간과 error number을 가지고 fire된다. user가 logon을 성공하면 logon_audit trigger가 시간과 username을 기록할 것이다. $ sqlplus system/manager ERROR: ORA-04098: trigger 'LOGON_AUDIT' is invalid and failed re-validation AFTER LOGON trigger을 사용함에 있어서 logging을 남기는 table(log_info)에 space가 부족해서 위와 같이 ORA-04098 error와 함께 DBA user을 포함한 모든 user가 logon 하지 못하는 경우가 발생할 수도 있다. 이럴 경우 아래와 같이 해결한다. SVRMGR> connect internal Connected. SVRMGR> ALTER TRIGGER system.logon_audit DISABLE; Statement processed. 예제 2 ) Database uptime을 logging하는 trigger CREATE TABLE up_time (action_date DATE, action VARCHAR2(20)); CREATE OR REPLACE TRIGGER db_start AFTER STARTUP ON DATABASE BEGIN insert into system.up_time values(sysdate,'STARTUP'); END; / CREATE OR REPLACE TRIGGER db_stop BEFORE SHUTDOWN ON DATABASE BEGIN insert into system.up_time values(sysdate,'SHUTDOWN'); END; / 다음과 같이 실행해 본다. SHUTDOWN IMMEDIATE STARTUP SHUTDOWN ABORT STARTUP SQL> select to_char(action_date,'DD-MON-YY:HH:MI:SS), action from up_time; ACTION_DATE ACTION ------------------ -------------------- 15-JUN-00:03:31:35 SHUTDOWN 15-JUN-00:03:32:09 STARTUP 15-JUN-00:03:32:47 STARTUP 위와 같이 query해 보면 SHUTDOWN ABORT가 빠져 있는 것을 볼 수 있다. SHUTDOWN ABORT시에는 db_stop trigger가 fire되지 않는다. 예제 3 ) Database startup시에 package을 shared_pool pin하는 trigger SVRMGR> @?/rdbms/admin/dbmspool CREATE OR REPLACE TRIGGER db_start_pin AFTER STARTUP ON DATABASE BEGIN sys.dbms_shared_pool.keep('SYS.STANDARD'); END; / 예제 4 ) User가 logon시에 NLS_DATE_FORMAT을 set하는 LOGON trigger CREATE OR REPLACE TRIGGER on_logon AFTER LOGON ON scott.schema DECLARE v_cur integer; v_cur2 integer; BEGIN v_cur := dbms_sql.open_cursor; dbms_sql.parse(v_cur,'alter session set nls_date_format='DD-MON-YYYY'', dbms_sql.native); v_cur2 := dbms_sql.execute(v_cur); END; / $sqlplus scott/tiger SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT'; PARAMETER ------------------------------------------------------------ VALUE ------------------------------------------------------------ NLS_DATE_FORMAT DD-MON-YYYY 예제 5 ) User을 Create할때마다 권한 부여하는 trigger $sqlplus system/manager CREATE OR REPLACE TRIGGER grant_connect AFTER CREATE ON DATABASE WHEN (dictionary_obj_type = 'USER') begin execute immediate 'grant connect,resource to '||dictionary_obj_name; end; / SQL> create user joo identified by joo; 이 trigger을 만든후 connect,resource 권한을 안 주셔도 connect되는 것을 확인할 수 있다. 예제 6 ) Table을 만들때마다 어떤 user(test user)에게 grant하는 trigger $sqlplus scott/tiger CREATE OR REPLACE TRIGGER grant_select AFTER CREATE ON schema WHEN (sys.dictionary_obj_type = 'TABLE') begin execute immediate 'grant select on '|| sys.dictionary_obj_name || ' to joo' ; end; / SQL> create table test_tab (id number, name varchar2(10)); Table created. SQL> connect joo/joo SQL> select * from scott.test_tab; from oracle 발주처 : http://www.koug.net/

[ Oracle - Trigger INSTEAD OF ]

[ Oracle - Trigger Sample ]

create or replace trigger lot_in_trg after insert or update or delete on lot_in for each row declare cnt number(4); seq_r number(4); qty number(11,3); qty_r number(11,3); errnum char(6):='-20999'; hhmm char(4); begin select to_char(sysdate,'hh24mi') into hhmm from dual; if inserting then if nvl(:new.qty_do,0) != 0 then /*------------------------------------------------------------------*/ /* (KEY부에 대한 변화는 delete후 insert가 됨, DATA부에 대한 변화는 */ /* update가 됨). */ /*------------------------------------------------------------------*/ update lot_stock set qty_norm = nvl(qty_norm,0) + nvl(:new.qty_do,0) where ymd = :new.ymd and lot = :new.lot and prdt_code = :new.prdt_code; if sql%notfound or sqlcode = 100 then insert into lot_stock (ymd, lot, prdt_code, qty_norm, qty_bad, bad_stat, bigo) values (:new.ymd, :new.lot, :new.prdt_code, :new.qty_do, 0, null , null ); end if; /*------------------------------------------------------------------*/ /* 생산량실적정보를 update한다. */ /*------------------------------------------------------------------*/ errnum := '-20901'; select count(*) into cnt from numbering where gubun = 'J' and ymd = :new.ymd; if cnt = 0 then seq_r := 1; errnum := '-20902'; insert into numbering (gubun, ymd, seq) values ('J', :new.ymd, seq_r); else select seq into seq_r from numbering where gubun = 'J' and ymd = :new.ymd; seq_r := seq_r + 1; errnum := '-20903'; update numbering set seq = seq_r where gubun = 'J' and ymd = :new.ymd; end if; errnum := '-20904'; insert into result (ymd, manage_no, line, prdt_code, day_gubun, qty, gubun, hhmm) values (:new.ymd, substr(:new.ymd,3,6) || lpad(to_char(seq_r),4,'0'), :new.line, :new.prdt_code, :new.day_gubun,:new.qty_do, 'PF', hhmm); end if; elsif updating then qty := nvl(:new.qty_do,0) - nvl(:old.qty_do,0); qty_r := nvl(:new.qty_do,0) - nvl(:old.qty_do,0); /*------------------------------------------------------------------*/ errnum := '-20991'; update lot_stock set qty_norm = nvl(qty_norm,0) + qty where ymd = :new.ymd and lot = :new.lot and prdt_code = :new.prdt_code; if sql%notfound or sqlcode = 100 then insert into lot_stock (ymd, lot, prdt_code, qty_norm, qty_bad, bad_stat, bigo) values (:new.ymd, :new.lot, :new.prdt_code, qty, 0, null , null); end if; /*------------------------------------------------------------------*/ /* 생산량실적정보를 update한다. */ /*------------------------------------------------------------------*/ errnum := '-20994'; if qty <> 0 then errnum := '-20901'; select count(*) into cnt from numbering where gubun = 'J' and ymd = :new.ymd; if cnt = 0 then seq_r := 1; errnum := '-20902'; insert into numbering (gubun, ymd, seq) values ('J', :new.ymd, seq_r); else select seq into seq_r from numbering where gubun = 'J' and ymd = :new.ymd; seq_r := seq_r + 1; errnum := '-20903'; update numbering set seq = seq_r where gubun = 'J' and ymd = :new.ymd; end if; errnum := '-20904'; insert into result (ymd, manage_no, line, prdt_code, day_gubun, qty, gubun, hhmm) values (:new.ymd, substr(:new.ymd,3,6) || lpad(to_char(seq_r),4,'0'), :new.line, :new.prdt_code, :new.day_gubun,qty_r, 'PF', hhmm); end if; elsif deleting then /*------------------------------------------------------------------*/ /* 삭제시에는 OLD.LOT, OLD.단량에서 OLD.수량만큼 -Update한다. */ /*------------------------------------------------------------------*/ errnum := '-20907'; update lot_stock set qty_norm = nvl(qty_norm,0) - nvl(:old.qty_do,0) where ymd = :old.ymd and lot = :old.lot and prdt_code = :old.prdt_code; if sqlcode = 100 or sql%notfound then null; end if; errnum := '-20909'; /*------------------------------------------------------------------*/ /* 생산량실적정보를 update한다. */ /*------------------------------------------------------------------*/ errnum := '-20907'; select count(*) into cnt from numbering where gubun = 'J' and ymd = :old.ymd; if cnt = 0 then seq_r := 1; errnum := '-20902'; insert into numbering (gubun, ymd, seq) values ('J', :old.ymd, seq_r); else select seq into seq_r from numbering where gubun = 'J' and ymd = :old.ymd; seq_r := seq_r + 1; errnum := '-20903'; update numbering set seq = seq_r where gubun = 'J' and ymd = :old.ymd; end if; errnum := '-20904'; insert into result (ymd, manage_no, line, prdt_code, day_gubun, qty, gubun, hhmm) values (:old.ymd, substr(:old.ymd,3,6) || lpad(to_char(seq_r),4,'0'), :old.line, :old.prdt_code, :old.day_gubun,:old.qty_do * -1, 'PF', hhmm); end if; /*-------------------------------------------------------*/ /*- 후처리 -----------------------------------------------*/ /*-------------------------------------------------------*/ delete from lot_stock where nvl(qty_norm,0) = 0 and nvl(qty_bad,0) = 0; if sqlcode = 100 or sql%notfound then null; end if; update lot_stock set seqno1 = null, seqno2 = null where nvl(qty_norm,0) < 0; if sqlcode = 100 or sql%notfound then null; end if; /*------------------------------------------------------------*/ /*- Exception처리 --------------------------------------------*/ /*------------------------------------------------------------*/ exception when no_data_found then raise_application_error(errnum,'Location재고 NOTFOUND! '); when others then raise_application_error(errnum,'Location재고보정처리중 Error발생!'); end; / 발주처 : http://myhome.netsgo.com/mino70/main_03_oracle03.htm

[ Oracle - Trigger 생성시 multating 에러와 해결책]

 친구 놈이 mutating에러가 난다고 해서 제 나름대로 자료를 찾아서 정리를 해보았습니다. 쓰는 방식이 잘못 되었더군요.

  보통 default와 trigger를 구분해서 잘 쓰셔야 하는데 default는 그 column에 대해 언급이 없을때 그 값이 들어가고

  trigger는 무슨 값이 들어 오던지 trigger에 기술된 내용이 들어가는 것입니다.

1. MUTATING ERROR란 무엇인가?

    어느 TABLE에 DML(INSERT, UPDATE, DELETE 등)이 실행될 때마다 프로그램에 구애받지 않고 특정 작업을 수행하려할 때

    database trigger를 사용한다.

    예)EMP table에 data insert, update, delete 시 부서별 평균 급여 table에

        updating 하는 경우.

        이 경우 trigger를 사용하지 않고 같은 작업을 하려면 평균 급여를 구하는

        PL/SQL program을 개발하여 EMP 테이블에 action이 발생 시마다 call하여 사용

        하든가, 아니면 각 action 발생 후 동일한 routine을 반복 수행시켜야 한다.

        이 때 만일 user가 EMP table에 update 시마다 EMP table에 어떤 처리를 수행

        하는 trigger를 만든다면 원치 않는 결과를 일으킬 수 있고 OS memory가 소진

        될 때까지 trigger가 trigger를 recursive하게 fire시켜 마치 looping

        program과 같은 상황을 초래할 수도 있다.

        이러한 trigger를 recursive trigger라 부르며 이런 불상사를 막기 위해

        ORACLE은 EMP table에 row trigger를 만들어 원천적으로 trigger 내에서

        EMP table을 아예 access 할 수 없도록 하고 있고, 이와 같은 원칙에 위배될

        경우 발생되는 error를 mutating error 라고 부른다.

        이 경우 user가 trigger를 만든 후 DML(insert, update, delete)을 수행 시

        "ORA-4091:table SCOTT.EMP is mutating, trigger/function may not see

        it." 와 같은 error를 만나게 된다.

2. ERROR가 발생하는 조건.

    TRIGGER에는 다음과 같은 두 종류가 있다.

    *row trigger - 프로그램에서 한 row 단위로 처리 시 처리할 때마다 fire되는 trigger.

    *statement trigger - 프로그램 당 한번만 fire되는 trigger.

    위와 같으므로 만일 application에서 한 row만 처리한다면 두 type에는 차이가

    없고 여러 row를 처리할 경우 두 type 간의 차이가 발생한다.

    Statement trigger는 일부 제한은 있으나 원칙적으로 mutating error를 발생 시키지 않는다.

    Row trigger는 하나의 row 처리 후 해당 table에 대한 계속된 row 처리가 있을

    수 있으므로 작업이 완료되기까지 해당 table을 access하는 것이 금지되지만

    statement trigger는 일단 하나의 statement가 완료되었다는 보장을 할 수

    있으므로 mutating의 기본 속성인 "현재 변화되고 있는 table" 이라는 범위에 들지 않는다.

    따라서, mutating error는 row trigger에서의 제한 사항이라 해도 무리가 없다.

3. 해결 방법.

    위에서 보았 듯 mutating error를 피해 나가려면 statement trigger를 사용하면

    어려움이 없으나 statement trigger에서는 row trigger에서와 같이 row 단위로

    변경 전 후 column data를 handling할 수 없다는 단점이 있다.

    즉 :new.column, :old.column을 사용하지 못한다.

    이와 같은 문제로 인하여 row trigger를 사용 시는 temp table 이나 PLSQL table을 이용하여 피해갈 수가 있다.

    다음은 row trigger를 사용 시 mutating error를 유발하는 case(A)와

    이를 statement trigger로 전환하여 error를 피해가는 case(B) 에 대한 내용이다.

    예) EMP table에 insert, update, delete 시 부서별 평균 급여를 계산하여

    DEPT table에 load한다. (TABLE COLUMN은 다음과 같다.)

SQL> desc emp

Name Null? Type

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

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

SQL> desc dept

Name Null? Type

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

DEPTNO NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SAL NUMBER(7,2)

(CASE A) ROW Trigger 만을 사용 시 에러가 발생하는 case.

1) row trigger 생성

create or replace trigger emp_aft_row_trigger

after insert or update or delete on emp

for each row

declare

v_sal emp.sal%type;

begin

select avg(sal) into v_sal from emp

where deptno=:old.deptno;

update emp

set sal=v_sal

where deptno=:old.deptno;

if :old.deptno != :new.deptno then

select avg(sal) into v_sal from emp

where deptno=:new.deptno;

update emp

set sal=v_sal

where deptno=:new.deptno;

end if;

end;

/

2) DATA 1건을 UPDATE한다.

SQL)update emp

set sal=10000

where empno= 7934;

SQL)

update emp

*

ERROR at line 1:

ORA-04091: table JMKIM.EMP is mutating, trigger/function may not

see it

ORA-06512: at line 4

ORA-04088: error during execution of trigger 'JMKIM.EMP_AFT_ROW_TRIGGER'

(CASE B) 에러를 피해 가는 방법

1) PL/SQL table을 생성한다.

SQL) create or replace PACKAGE emp_pkg as

TYPE emp_tab_type is table of EMP.DEPTNO%TYPE

index by binary_integer;

emp_old emp_tab_type;

emp_new emp_tab_type;

emp_index binary_integer;

end emp_pkg;

/

Package created.

2) BEFORE STATEMENT trigger를 생성한다.

SQL)create or replace TRIGGER emp_bef_stm_all

before insert or update or delete on emp

begin

emp_pkg.emp_index :=0;

end;

/

SQL)

Trigger created.

3) AFTER ROW trigger를 생성한다.

SQL>create or replace TRIGGER emp_aft_row_all

after insert or update or delete on emp

for each row

begin

emp_pkg.emp_index := emp_pkg.emp_index + 1;

emp_pkg.emp_old(emp_pkg.emp_index) := :old.deptno;

emp_pkg.emp_new(emp_pkg.emp_index) := :new.deptno;

end;

/

SQL>Trigger created.

4) AFTER STATEMENT trigger를 생성한다.

SQL>create or replace TRIGGER emp_aft_stm_all

after insert or update or delete on emp

declare

v_sal emp.sal%type;

begin

for i in 1 .. emp_pkg.emp_index loop

select avg(sal) into v_sal from emp

where deptno=emp_pkg.emp_old(i);

update dept

set sal = v_sal

where deptno=emp_pkg.emp_old(i);

dbms_output.put_line('DEPTNO(old)=>'||to_char(emp_pkg.emp_old(i)));

if emp_pkg.emp_new(i) != emp_pkg.emp_old(i) then

select avg(sal) into v_sal from emp

where deptno=emp_pkg.emp_new(i);

update dept

set sal = v_sal

where deptno=emp_pkg.emp_new(i);

dbms_output.put_line('DEPTNO(new)=>'||to_char(emp_pkg.emp_new(i)));

end if;

end loop;

emp_pkg.emp_index :=0;

end;

/

SQL>

Package created.

5) data insert 및 확인

SQL> update emp

set sal = 9000

where empno=7902;

SQL>

DEPTNO(old)=>20

1 row updated.

발주처 : http://www.koug.net/

[ Oracle - Trigger Compile 시 Error 및 해결 방법 ]

1.   에러내역    (  실행 Emulate 는 : Golden )

      ㄱ.  Scipt 중  주석문에   &&   를 둔 경우다.  Trigger 에서 주선문에서라도  &&  라는 특수 문자를 두면 않된다.

      ㄴ. 위 Error 가 발생 해도 Trigger 는 생성 된다.

[ Oracle - Trigger 문법 Procedure 호출 ]

1. 다음 방법으로 SP 호출

    CALL check_sal(:new.job, :new.sal, :new.ename)

2. SP 호출

   sp_ins_pap01(:NEW.DEPT_CODE, :NEW.CALL_YEAR, :NEW.CALL_SEQ, :NEW.CALL_DEPT,

                :NEW.CONFIRM_DATE);

[ Oracle - Trigger  문법_선언문(Declare) ]