Dream for inspiration

PL/SQL을 이용하여 생성한 Package, Procedure, Function 등 다양한 OBJECTS와 Tables간의

Dependency 정보를 확인하기 위해서 어떻게 해야 할까??

개발할 때 마다, 이를 관리하기 위한 정보를 생성해??

아니다. Oracle Dictionary 중에서 "USER_DEPENDENCIES"를 조회하면 된다.

USER_OBJECTS나 USER_TABLES 정보와 잘 Join해서 검색한다면 보다 다양한 형태로 원하는 Dependency

정보를 확인해볼 수 있을 것입니다.

그러면 이쯤에서 하나 더....

Package나 Procedure, Function등을 개발하고, 이를 Input Parameter를 Application 개발자에게 문서로

전달해야 한다면 어떻게 할 것인가??

Source를 일일이 하나씩 열어서 문서로 옮겨 적어??     

Oh~~ No!!

눈치 빠른 분이라면 이미 아시겠지만, 이 또한 Dictionary 중에서 USER_ARGUMENTS 정보를 이용하면 된다.

Oracle로 개발하고, 이를 문서화하고자 한다면 일단 Dictionary에 있는 정보를 잘 참고해보도록 하자.

정말 존재하지 않는 Data를 문서화 하려고 한다면야 어쩔 수 없겠지만, 활용할 수 있는 정보들이 있음에도

불구하고 일일이 백지에 Typing으로 입력하려면 그야 말로 "머리가 나쁘면 손발이 고생한다"는 속담을

상기시켜주는 꼴이다.

제발 이렇게 시간 낭비하지 말자. 개발만 해도 부족한 시간 아니던가....

With the REF_CURSOR you can return a recordset/cursor from a stored procedure.

There are 2 basic types: Strong ref cursor and weak ref cursor
For the strong ref cursor the returning columns with datatype and length need to be known at compile time.
For the weak ref cursor the structure does not need to be known at compile time.

The STRONG_REF_CURSOR and until Oracle 9i also the weak-type need to be declared in a package structure lik this:

create or replace
package REFCURSOR_PKG as
      TYPE WEAK8i_REF_CURSOR   IS REF CURSOR;
      TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;

The pl/sql procedure that returns a ref-cursor looks like this:

/** until Oracle 9 */
create or replace
procedure test( p_deptno IN number,
                      p_cursor  OUT REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
     open p_cursor FOR
     select *
     from   emp
     where  deptno = p_deptno;
end test;

Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning REF_CURSOR.

/** From Oracle 9 */
create or replace
procedure test( p_deptno IN number,
                      p_cursor OUT SYS_REFCURSOR)
is
begin
      open p_cursor FOR
      select *
      from   emp
      where  deptno = p_deptno;
end test;

/* Strong type */
create or replace
procedure test( p_deptno  IN number,
                      p_cursor  OUT REFCURSOR_PKG.STRONG REF_CURSOR)
is
begin
      open p_cursor FOR
      select *
      from   emp
      where  deptno = p_deptno;
end test;

Selecting the ref_cursor from JDBC
To get the cursor from Java you can use the following JDBC-code:

public void method() throws SQLException{
  Connection conn = getConnection();
  CallableStatement cstmt = null;
  ResultSet rs = null;
  int deptno = 10;
  Object temp;
  try{
      cstmt = conn.prepareCall("begin  test(?,?); end;");
      cstmt.setInt(1, deptno);
      cstmt.registerOutParameter(2, OracleTypes.CURSOR);
      cstmt.execute();
      rs = (ResultSet) cstmt.getObject(2);
      ResultSetMetaData rsm = rs.getMetaData();
      int columnCount = rsm.getColumnCount();
      while (rs.next()){
         for (int j=0;j< columnCount;j++){
            temp = rs.getObject(j+1);
         }
      }
  } finally {
      if (!rs==null){
        rs.close();
      }
      if (!stmt==null){
        stmt.close();
      }
      if (!conn==null){
        conn.close();
      } 
  }
}
Calling ref-cursor from pl/sql
create or replace procedure test_call is
  c_cursor REFCURSOR_PKG.STRONG_REF_CURSOR;
  r_emp    c_emp%rowtype;
begin
  test(10,c_cursor);
  loop
    fetch c_cursor into r_emp;
    exit when c_cursor%notfound;
    dbms_output.put_line(r_emp.name);
  end loop;
  close c_cursor;
end test_call;

출처 : http://www.oradev.com/ref_cursor.jsp

ㅇ 환경적 요소

    Oracle 9iR2이 설치된 DB 서버 한개와 Oracle 10gR2이 설치된 DB 서버 한개가 각각 물리적으로 떨어져서

    구성되어 있는 환경에서 DB Link를 이용하여 양쪽 DB 서버에 존재하는 Table에 접근하여 Data를 읽어가도록

    하기 위해 진행된 상황


ㅇ 증상

    Oracle9i의 A Table와 Oracle10g의 B Table을 조인할 경우, Query 수행 시 App에서 응답없음 상태가

    되는 문제가 발생함.


ㅇ 분석

    Oracle9i의 A Table은 전체 약 300만건의 Data가 존재하고, Join시 사용될 Key값에 대해서 PK가 설정되어

    있는 상태이다.

    Oracle10g의 B Table은 전체 약 400건의 Data가 존재하고, Join시 사용될 Key값에 대해서 PK가 설정되어

    있는 상태이다.

    Query문 예시)    select  a.key, a.value, b.name
                           from   t_b b, t_a@db_link a
                         where  b.key = a.key

    Test Case 1) Oracle9i에 있는 t_a Table에 대해서 단일 Table Query 수행시 정상적인 실행계획을 가지고

                       결과값이 바로 나타남.

    Test Case 2) Oracle10g에 있는 t_b Table에 대해서 단일 Table Query 수행시 정상적인 실행계획을 가지고

                       결과 값이 바로 나타남

    Test Case 3) 예시문에 제시된 Query문 수행시, 30s가 지나도 결과값이 안나타남.
     
                         ==> 이를 실행 계획을 확인해보니, t_b Table을 Index Full Scan을 하고,

                               t_a Table은 Remote ~~ 라고 표시되어 실행계획이 확인이 안됨.

                               이렇게 두 Table을 읽은 후에, Hash Join으로 처리함.

                               Hash로 실행계획이 풀리는 것을 보니, Oracle 9i의 t_a Table이 DB Link를 통해서

                               읽어오는 과정에서 Full Table Scan으로 풀려지는 것을 의심함.

                        ==> 예시문에 제시된 Query문에 각종 Index Hint를 적용해도 해결이 안됨.

    Test Case 4) Oracle10g쪽 DB Server에서 Oracle9i의 t_a Table을 읽을 때, Where 절을 이용해서
 
                       검색해본 바, 상수값을 입력하여 조회시에는 정상적으로 실행계획이 타는 것처럼

                       결과 값이 바로 나타남. 하지만, 상수값 대신 Oracle10g의 t_b Table의 값을 Where절에서

                       In을 이용해서 적용하면 Test Case 3과 동일한 증상이 나타남.


ㅇ 원인

    A query with an in-subquery where the outer query is remote mapped may produce a poor execution plan
   
     Oracle Support에서 원인을 찾아본 바, 이와 같이 DB Link의 Bug가 발생한다고 함.


ㅇ 해결방법

    Oracle Support에서는 DB Patch를 시행하라고 권고하였으나, 이는 서비스 DB에서 바로 시행할 수 있는

    것이 아니기에, Oracle 9i의 t_a Table을 Oracle9i에 존재하는 임의의 t_c Table과 Index Hint를 이용하여

    무조건 Index를 타도록 Query문을 작성한 후, 이를 View로 생성했다.

    그리고 나서 Oracle10g쪽에서 이 View를 이용해서 Join을 수행하도록 하였더니, 내가 원하는 형태로

    실행 계획이 풀리면서 결과가 정상적으로 나타남.

 
그동안 DB Link를 너무 무심하게 가져다 사용한 것 같다는 생각이 드는 사건이었습니다. 이번 사건을 계기로

DB Link에 대해서 알아본 바, 생각보다 많은 Bug들이 존재한다는 점이 충격적으로 다가오는군요.

내가 잘 알고 있다고, 잘 사용해왔다고 생각했던 기술들이 나의 뒷통수를 후려치는 순간입니다.

"돌다리도 두들겨 건너라."라는 옛 속담처럼, 확인에 확인을 하는 습관만이 삽질과 야근으로부터

해방시켜줄 수 있는 방안이 아닐까 합니다.

Profile의 유형이 추가된 것을 제외하고, Profile가 Default인것만 비교해보았을 때,

위와 같이 빨간색으로 표시한 부분의 값들에 대한 Default Value가 변경되었습니다.

그럼 변경된 내용들이 어떤 의미가 있는지 확인해보겠습니다.

1. FAILED_LOGIN_ATTEMPTS : 로그인을 실패했을 경우에 대한 제한 횟수

2. PASSWORD_LIFE_TIME : 암호 변경 일수를 의미합니다. 180일 이후에는 암호가 Expired 상태가 됩니다.

3. PASSWORD_LOCK_TIME: 암호 잠김 시간을 의미합니다. (1/1440 으로 설정하면 1분동안 잠김)

4. PASSWORD_GRACE_TIME : 암호 변경 메세지를 출력 날짜를 의미합니다. 이 설정으로 인해 서두에서
                                             나타났던 "ORA-28002"이라는 Alert Message가 나타나는 것입니다.

추가적으로 변경되지 않은 3개의 Resource에 대해서 설명을 하자면,

PASSWORD_REUSE_TIME : 사용했던 암호를 다시 사용 가능한 기간을 의미합니다.

PASSWORD_REUSE_MAX : 사용했던 암호 기억 횟수를 의미합니다. 사용했던 암호를 재사용하지 못하도록
                                        하기 위함입니다.

PASSWORD_VERIFY_FUNCTION : 암호 복잡성 검사 함수를 사용을 하기 위함입니다.

만약, Profle의 값을 변경하고자 한다면 아래와 같이 간단하게 수정할 수 있습니다.

============================================================================
sys@david11g> alter profile default limit
  2  password_life_time unlimited;

프로파일이 변경되었습니다.

SYS@david11g> select resource_name, limit
  2  from dba_profiles
  3  where profile = 'DEFAULT'
  4  and resource_name = 'PASSWORD_LIFE_TIME'

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
PASSWORD_LIFE_TIME               UNLIMITED

============================================================================

PURPOSE
--------------------------------------------------------------------------------------------------
Intermedia text 나 Oracle Text 를 사용할 때, Text index는 삭제되었는 데,  meta data가 삭제되지 않은
경우가 생긴다. 이런 경우, 수동으로 meta data 까지 모두 삭제하는 방법을 알아보자.

Explanation
--------------------------------------------------------------------------------------------------
이 방법은 잘못되는 경우 CTXSYS user를 다시 초기화 해야하는 문제가 발생할 수 있으므로 이를 감안하여
작업해야 한다.

수동으로 삭제하는 방법은 다음과 같은 절차를 이용하여 작업할 수 있다.

1. 먼저 해당 user에서 다음과 같은 command로 index를 drop 해 본다.
SQL> drop index INDEX_NAME force;

2. drop command로 drop이 정상적으로 되지 않으면 다음과 같이 실행한다.
sqlplus ctxsys/ctxsys

select idx_id from dr$index where idx_name='<TEXT_INDEX_NAME>';
=> 조회된 index id가 1092 인 경우 아래와 같이 실행한다.

delete from dr$index_value where IXV_IDX_ID = 1092;
delete from dr$index_object where IXO_IDX_ID = 1092;
delete from dr$index where idx_id = 1092;
commit;

3. Text index를 생성한 user에서 DR$<index_name>$i 이름의 TABLE을 모두 다음과 같이 drop한다.
SQL> drop table dr$<index_name>$i;
SQL> drop table dr$<index_name>$k;
SQL> drop table dr$<index_name>$n;
SQL> drop table dr$<index_name>$r;

4. 이제 해당 Text index를 다시 생성하면 된다.

Reference Documents
--------------------------------------------------------------------------------------------------
<Note:133482.1>

Problem Description

alert log 및 트레이스 화일에는 이와 같은 에러가 남게 된다.

ORA-00600: internal error code, arguments: [729], [560], [space leak], [], [], [], [], []

Explanation

Heap dump의 내용과 어떤 경우에 이런 에러가 발생하는지에 대해 좀 더 자세히 알아보기로 한다.

Oracle이 process를 종료하면, 점유하였던 메모리와 release되는 memory를 비교하여
size가 정확히 일치하지 않으면 ora-600[729] 에러를 발생시키고, shutdown 시에 SGA 등을
모두 release하고 일부 메모리 영역이 여전히 남아있으면 ora-600[730]을 report하게 된다.

User가 oracle에 접속하면 user process가 생성이 되고, 각 user process마다 heap이 할당된다.
모든 프로세스들은 자신의 memory heap을 갖게 된다.
Heap을 구성하는 각 extent는 연속적인 chunk로 이루어져 있는데, 이러한 chunk들은 각각
다음과 같은 type들을 가질 수 있다.

1. FREE : free chunk는 heap manager가 다시 할당할 수 있는 비어있는 부분.
Free 영역은 할당된 chunk가 해제가 된 상태로 이것은 인접한 free chunk들과
merge한 다음에 Free list에 등록된다.

2. FREEABLE : freeable은 flush를 시켜서 다시 merge를 할 수 있는 후보.
SYS.X$KSMSP를 조회하면 freeable space에 대한 조각을 확인할 수 있다.

3. RECREATABLE : memory chunk가 heap에 할당될 때, chunk의 내용이 재생성가능한 것으로
명시할 수 있다. 이러한 option으로 생성되면, 이 chunk는 사용 중이지 않을 때 명시적으로
'unpinned'될 수 있다. 사용자가 heap으로부터 space를 요청할 때 공간이 없으면,
이러한 unpinned 혹은 recreatable chunk를 해제하고 사용할 수 있다.
Unpinned chunk는 LRU list에 존재하게 되어 무엇을 가장 먼저 비게 할 것인지 결정할 수
있으며, 이러한 방법은 row cache나 library cache에 이용된다.

4. PERMANENT : 이 영역은 일단 할당되면 해제되지 않는 메모리 부분인데, 각 heap descriptor는
이러한 permanent chunk 부분을 가리키는 pointer를 갖고 있다.
이 permanent chunk는 두 부분으로 나누어지는데, 앞 부분은 이미 permanent로 할당된
메모리 부분이고, 뒷 부분은 아직 할당되지 않은 reserved area 이다.
Chunk의 header에는 어느 부분부터가 reserved area의 시작인지 나타내는 pointer가 있다.
실제 permanent 메모리는 프로세스가 종료하여 heap 자체가 해제되면 그 영역도 해제가 된다.

5. FREEABLE WITH MARK

Permanent 메모리 영역을 해제할 때 완전히 free 시키지 않고, Freeable with MARK 상태로
두었다가 다시 restore 하기도 하는데, 이 chunk type은 oracle 10g 에서는 널리 알려져
있지는 않다.

[ Extent의 할당과 해제 ]

연속된 chunk들의 집합을 extent라고 한다.
즉, heap에 포함되어 있는 extent들의 집합 안에서 요청한 크기의 조각을 할당받게 된다.
만약 이 때 발견하지 못하면, heap manager는 새로운 extent를 요청하여 이것을 heap에 추가한다.

각 extent는 오직 한 종류의 chunk type만 갖는 것이 아니고, 다양한 type의 chunk를 가질 수 있다.
프로세스가 memory chunk를 요청할 때 heap manager는 필요한 size 만큼의 공간을 할당해 준다.
프로세스가 종료하면 프로세스에게 할당되었던 모든 메모리는 자동으로 release가 된다.
프로세스 종료(logoff) 시 RECREATABLE chunk와 FREEABLE chunk는 FREELIST 로 등록되었다고
판단을 하고, 메모리가 release될 때 아직 남아 있는 할당된 heap들은 free가 되는데,
ORACLE server 입장에서는 일반적으로 heap이 free가 될 때 PERMANENT chunk로 할당된 chunk와
freelist 상에 있는 FREE chunk들을 free시킬 대상으로 삼는다.

프로세스가 FREEABLE 혹은 RECREATABLE type 으로 남아 있는 chunk를 발견한 경우는
해당 프로세스가 memory deallcation 을 제대로 수행하지 않았다는 것을 의미하게 된다.
이러한 상황을 space leak 이라 부른다.
오라클은 일반적으로 SGA heap, UGA heap, Large pool heap, 그리고, PGA heap에 대해
space leak을 check한다. Space leak error는 BACKGROUND_DUMP_DEST 또는
USER_DUMP_DEST 내에 trace file 안에 남게 된다.

Symptoms

Space leak problem은 일반적으로 trace information과 heap dump를 capture하여 떨어뜨리게 된다.
OS와 ORACLE process header 정보 다음에 trace file 내에 이와 같은 정보를 볼 수 있다.

*** 2006-10-03 18:43:11.598
*** SESSION ID:(34.50354) 2006-10-03 18:43:11.597
******** ERROR: UGA memory leak detected 560 ********

바로 위 라인을 보면 이 memory leak 은 UGA 영역에서 발생한 것이고, leak이 일어난 size는
560 bytes임을 알 수 있다.

Heap은 연속된 chunk의 집합으로 이루어지고, heap이란 heap descriptor와 extent라고 불리는
메모리 조각의 집합으로 구성되며, 각 extent는 연속된 메모리 조각으로 정의가 된다.

******************************************************
HEAP DUMP heap name="session heap" desc=0xaef81d0
extent sz=0xffb8 alt=32767 het=32767 rec=0 flg=3 opc=3
parent=0xaeb63e0 owner=0x7a4b7078 nex=(nil) xsz=0xffb8

위 dump에서 heap name은 SESSION HEAP 이고, Heap descriptor는 0xaef81d0 임을 알 수 있다.

아래의 extent 상세 정보를 보면 release되지 않은 chunk는 4085a350임을 나타낸다.
이것은 recreatable chunk이고, 그 size는 560 bytes이다. 이 수치는 [729], [560], [space leak]
에러에 나타난 bytes와 수치가 정확히 일치한다.

EXTENT 0 addr=0x407cf048
Chunk 407cf050 sz= 65456 free " "
EXTENT 1 addr=0x408a0048
Chunk 408a0050 sz= 65456 free " "
EXTENT 2 addr=0x40890048
Chunk 40890050 sz= 65456 free " "
EXTENT 3 addr=0x40850048
Chunk 40850050 sz= 41728 free " "
Chunk 4085a350 sz= 560 recreate "bind var heap " latch=(nil)
EXTENT 4 addr=0x407df048
Chunk 407df050 sz= 65456 free " "
EXTENT 5 addr=0x40f91048
Chunk 40f91050 sz= 65456 free " "
EXTENT 6 addr=0x40880048
Chunk 40880050 sz= 65456 free " "
EXTENT 7 addr=0x40870048
Chunk 40870050 sz= 65456 free " "

메모리 leak issue를 분석할 때 FREEABLE과 RECREATABLE type의 chunk를 식별해야 한다.
그 수치는 에러의 arguments에 나타난 leak 현상을 보이는 memory bytes의 sum 과 일치해야 한다.

원인

ora-600[729] error는 memory leak 현상으로 발생하는 오라클 에러라 할 수 있다.
그러나, 이러한 현상은 정상적인 경우도 발생할 수 있으며, 지속적으로 반복적으로 발생하여
memory free 영역이 계속 줄어드는 현상으로 나타나지 않는 한, 특별히 문제 시 되지 않으며,
일정 사이즈 이하의 leak은 message로 나타나게 하지 않도록 조치할 수 있다.

Workaround

아래의 예는 2M 이하의 leak 발생 시 에러가 나지 않게 하는 것으로, 만약 100k정도나 1~2M 이하의
leak 문제가 보인다면 발생하지 않게 이와 같이 셋팅할 수 있다.

event="10262 trace name context forever, level 2000000"

Solution Description

Space leak 문제를 어떻게 다룰지에 대해 자세한 사항은 다음의 문서에서 안내하고 있다.

Step 1. alert log를 review하고, trace file 정보를 분석해야 한다.
alert log 에는 이와 같은 에러가 남았을 것이다.

Sat Dec 02 21:52:17 2006
Errors in file d:\oracle\admin\testdb\udump\testdb_ora_5928.trc:
ORA-00600: internal error code, arguments: [729], [152], [space leak], [], [], [], [], []

a. argument [729]는 space leak 문제 발생 시 대표적인 에러 code이다.
b. argument [152]는 에러에 보고된 leak된 bytes의 수치이다.
c. argument [space leak] 는 항상 space leak 으로 나타난다.

Step 2. 연관된 트레이스 화일 분석

일반적인 trace information은 다음과 같다.

*** 2006-12-13 02:01:13.859
*** SESSION ID:(54.11635) 2006-12-13 02:01:13.859
******** ERROR: UGA memory leak detected 152 ********
******************************************************

위 트레이스는 UGA로부터 leak된 메모리라는 것과 memory leak의 size가 152 bytes 라는 것을
의미한다.

Step 3. leak이 session logoff로부터 발생했다는 것을 확인한다.

Call Stack Trace 부분을 보면 opilof 가 stack 상에 보인다면 이것은 session logoff 시
발생했음을 알 수 있다.
실제 발생한 call stack trace의 예는 다음과 같다.

Call Stack Trace
==========
calling call entry argument values in hex
location type point (? means dubious value)
========= ======== ============ ==============
ksedmp()+184 ? ksedst() 800000010000B938 ?
ksfdmp()+32 ? ksedmp() 800003FFBFFF6418 ?
kgeriv()+152 ? ksfdmp() 20000000B168 ?
kgesiv()+132 ? kgeriv() 40000000000002D9 ?
ksesic2()+124 ? kgesiv() 000000000 ?
ksmuhe()+1040 ? ksesic2() 000000000 ?
ksmugf()+400 ? ksmuhe() 000000000 ?
ksuxds()+2692 ? ksmugf() 800003FFBFFF4020 ?
ksudel()+104 ? ksuxds() 8000000100131B38 ?
opilof()+876 ? ksudel() 800003FFBFFF5808 ?
opiodr()+2416 ? opilof() 0650AB9D8 ?
ttcpip()+1320 ? opiodr() 8000000100004790 ?
opitsk()+1260 ? ttcpip() 000000100 ?
opiino()+1484 ? opitsk() 8000000100138268 ?
opiodr()+2416 ? opiino() 000001560 ?
opidrv()+752 ? opiodr() 800003FFBFFF0870 ?
sou2o()+40 ? opidrv() 000000000 ?
main()+228 ? sou2o() 000000000 ?

Step 4. Dedicated Server인지, MTS 환경인지 확인한다.

1) 만약 Dedicated server 환경이라면 이 error의 impact은 process가 종료할 때 끝날 것이다.
사실 에러의 영향도는 거의 없고, 데이타베이스에 실질적인 문제는 없을 것이다.
2) MTS(Multi Threaded Server) 환경이거나 XA transaction process manager/monitor를 사용하는 환경이라면
leaked memory가 SGA 영역에 있을 것이다. 이 때에는 ora-4030, ora-4031 과 같은 다른 에러가 발생했는지
alert log를 보아야 한다.

Step 5. Leak problem을 무시할 수 있는가 ?

1) 이 물음에 대해서는 다른 에러는 없는지 우선 살펴보아야 한다. 만약 동시에 발생한 다른 에러가 전혀 없다면
이 에러는 무시해도 되고, 다시 재현하기가 쉽지 않은 case일 것이다. 무엇보다도 90,000 bytes 보다 작은
size의 leak은 중요하지 않다. 이에 대한 solution은 event 10262 를 셋팅하는 것이다.

2) 만약 leak이 SGA 내에 발생했는가?
ALERT log를 살펴보고, shared pool 이나 OS memory 와 관련한 추가적인 문제는 없는지 ORA-4030 또는
ORA-4031 발생 여부를 확인해야 한다.

3) 메모리 leak 현상이 특정 작업을 수행 시에 재현이 되는가?
만약 특정 작업 시에 재현이 된다면, leak 현상이 known bug인지 확인을 위해
추가적인 investigation이 필요한 경우이다.
다음 문서를 보면 known bug 와 fix된 version들을 확인할 수 있다.

Note 31056.1 ORA-600 [729] UGA Space Leak for a list of known bugs and fixes

Step 6. Event 10262 셋팅하기

만일 leak된 bytes 수치가 무시해도 될 만한 수치라는 것이 확인이 되면, 이 에러를
무시하기 위해 event를 걸 수 있다.

이 event를 셋팅을 하게 되면 90,000 bytes보다 작은 크기의 leak에 대해서는 alert log에 나타나지 않게 할 수 있다.

a. 다음과 같이 initSID.ora file에 event를 셋팅한다.

event="10262 trace name context forever, level 90000"

b. 데이타베이스를 restart해야 event가 효력을 미친다.

[ 참고 ]

만일 level 을 1로 하게 되면, space leak checking이 disable된다. 1로 하게 되면 매우
큰 사이즈의 memory leak을 놓칠 수 있으므로, 권장하지 않는다.
Level 을 1보다 큰 수로 하게 되면 event 안에 명시된 숫자보다 작은 memory leak에
대해서는 무시하도록 하는 역할을 한다.

Reference documents

<Note:403584.1> Understanding and Diagnosing ORA-600 [729] Space Leak Errors

출처 : http://j.mp/cRNlnc


Oracle 11gR1 11.1.0.6의 Memory Leak Bug와 연관하여, Memory Leak Error에 대한 이해를 돕기 위한

문서입니다.

PURPOSE
이 자료는 Database 에 인증되지 않은 tools 의 접근을 막는 방법에 대한 설명이다.

Explanation

SQL*Plus 나 기타 다른 tools 의 접속을 제한할 필요가 발생할 수 있다.

단지 지정한 tools 만 사용하는 user 만 access 허용을 원한다면
예를 들어, forms 나 Reports 또는 다른 tools 만 사용하는 환경에서 SQL*Plus 나
다른 software 의 tools 을 사용하는 end user 의 접속을 제한하고, 단지 인증된 tools 만
접속이 되도록 하려고 한다.

그럴 경우에 PRODUCT_USER_PROFILE 테이블을 사용하여 SQL*PLUS 을 실행하는 session에
제한을 넣을 수 있다. 어떻게 수행되는지는 <Note:2181.1>를 참조해 보면 된다.

그러나 이 제한은 단지 SQL*PLUS 에서만 작동하고 다른 tools 에서는 동작을 하지 않을 수도 있다. Oracle8i 에서는 logon trigger를 이용하면 이 기능을 사용할 수 있다.

Example
인증되지 않은 access 를 검사하는 테이블을 생성한다.

SQL> conn sys/manager

Connected.

SQL> DROP TABLE LOGONAUDITTABLE CASCADE CONSTRAINTS;

SQL> CREATE TABLE LOGONAUDITTABLE (

EVENT VARCHAR2 (10),

TIMESTAMP DATE,

SCHEMA VARCHAR2 (30),

OSUSERID VARCHAR2 (30),

MACHINENAME VARCHAR2 (64),

SID NUMBER,

SERIAL# NUMBER,

PROGRAM VARCHAR2 (100));

Table created.

scott schema 에 아래의 trigger를 생성한다.

SQL> CREATE OR REPLACE TRIGGER logonauditing

AFTER LOGON ON scott.SCHEMA

DECLARE

machinename VARCHAR2(64);

osuserid VARCHAR2(30);

sid NUMBER;

serial# NUMBER;

program VARCHAR2(100);

CURSOR c1 IS

SELECT osuser, machine , sid , serial# , program

FROM v$session

WHERE audsid = USERENV( 'sessionid' );

BEGIN

DELETE LOGONAUDITTABLE;

COMMIT;

OPEN c1;

FETCH c1 INTO osuserid, machinename, sid , serial# , program ;

INSERT INTO LOGONAUDITTABLE VALUES ( 'LOGON', SYSDATE,

USER, osuserid, machinename , sid , serial#, program);

CLOSE c1;

COMMIT;

dbms_job.isubmit(12345,'sys.killsession;',SYSDATE);

END;

Trigger created.

log on trigger로부터는 같은 session 을 kill 시킬 수는 없다. 이 SESSION은 DBMS_JOB PACKAGE를
사용하여 매 10 초마다 해당하는 SESSION 을 찾아서 KILL 시키는 SCHEDULING 을 걸어서
사용할 수 있다.

sys 나 system schema로 아래의 procedure를 생성한다.

SQL> conn sys/manager

Connected.

SQL> CREATE OR REPLACE PROCEDURE KILLSESSION AS

sid NUMBER;

serial# NUMBER;

timestamp DATE;

CURSOR c1 IS SELECT sid , serial# , timestamp FROM scott.LOGONAUDITTABLE WHERE

INSTR(UPPER(program),UPPER('C:\Documents and Settings\All Users\시작 메뉴\프')) > 0;

/* 위의 부분에서 session 의 program column 값에서 원하는 application 의 value를 지정한다. */

BEGIN

FOR i1 IN c1 LOOP

dbms_output.put_line('alter system kill session ' ||''''||i1.sid||','||i1.serial#||'''');

execute IMMEDIATE 'alter system kill session ' ||''''||i1.sid||','||i1.serial#||'''';

DELETE scott.LOGONAUDITTABLE WHERE sid = i1.sid AND serial#=i1.serial# AND timestamp = i1.timestamp;

END LOOP;

COMMIT;

END;

Procedure created.

위의 procedure를 수행하기 위해서는 init.ora 파일에 아래의 parameters를 적용해 주어야 한다.

job_queue_process = 1

job_queue_interval = 10

위와 같이 setting 을 하면 아래의 PL/SQL block 을 사용하여 인증되지 않은 sessions 들을
매 10 초 마다 KILLSESSION procedure 를 fire시킬 수 있다.

SQL> connect sys/manager

begin

    dbms_job.isubmit(2345,'KILLSESSION;',sysdate,'sysdate+1/(24*60*6)');

end;

위의 명령을 실행하면 Client sqlplus 에서 아래의 메시지가 떨어진다.

SQL> select * from logonaudittable;

EVENT TIMESTAMP SCHEMA OSUSERID
------------------ ------------------------------

MACHINENAME SID SERIAL#
---------
PROGRAM

LOGON 03-FEB-03 SCOTT Administrator

BKCHEON 11 43911

C:\Documents and Settings\Administrator\시작 메

LOGON 03-FEB-03 SCOTT Administrator

BKCHEON 11 43923

C:\Documents and Settings\All Users\시작 메뉴\프

LOGON 03-FEB-03 SCOTT Administrator

BKCHEON 15 55572

C:\Documents and Settings\All Users\시작 메뉴\프

SQL> select * from tab;

select * from tab

*

ERROR at line 1:

ORA-00028: your session has been killed

Reference Documents

NOTE:105438.1

Note:70679.1 PL/SQL Example: How to Audit Logon Events with Triggers

원본 위치 <http://kr.forums.oracle.com/forums/thread.jspa?threadID=473589&tstart=150>