농부지기

[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/