BS Blog: dba

레이블이 dba인 게시물을 표시합니다. 모든 게시물 표시

2012-09-16

DBMS/오라클]database link 사용법

Database Link사용법

원격지에 있는 데이터베이스를 link하는 법은 다음과 같다.

원격지의 Database의 Service Name이 piruks.kang.com이다.
나의 tnsnames.ora파일에는 Database alias가 piruks가 잡혀있다.

piruks.kang.com(원 격지DB)의 init.ora내용
db_name = "piruks"
db_domain = kang.com
instance_name = piruks
service_names = piruks.kang.com

maddog.kang.com(로컬 DB)의 tnsnames.ora내용
PIRUKS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PIRUKS.KANG.COM)
    )
  )

원 격지의 패러미터파일(init.ora)에 global_names를 true로 하면 dblink의 이름과 접속
하는 db의 이름이
동일하도록 요구한다.

global_names = true [ or false ]

이 설정은 다음과 같이 해서 알 수 있다.
SQL> select name, value from v$parameter where name='global_names';

NAME                 VALUE
-------------------- ------
global_names         FALSE

현재 데이터베이스의 이름은 global_names에 질의함으로써 알 수 있다.

SQL> connect kang/xxxxxx@piruks;
연 결되었습니다.
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------
PIRUKS.KANG.COM

local db에 kang이라는 사용자로 접속한다.
SQL> connect kang/xxxxxx
연결되었습니다.

이 제 원격지(piruks.kang.com)의 DB에 kang이라는 사용자로 연결되는 Database link
를 생성한다.
여기서 piruks.kang.com은 원격지의 [Database name].[Domain name]이다.
보통 [Database name].[Domain name]는 Service Name이라 불리운다.
하지만 위와 같이 해서 제대로 않되는 경우가 있다.
이럴때는 global_name에 있는 이름을 dblink이름으로 정하면 해결된다.
piruks는 database connect string이다.
SQL> create database link piruks.kang.com
  2  connect to kang identified by xxxxxx
  3  using 'piruks';
데이타베이스 링크가 생성되었습니다.

사 용법은 다음과 같다.
보통의 DML문장에 '@piruks.kang.com'을 추가한다.
SQL> select * from tab at piruks.kang.com;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ ------------
-- ----------
TEST                                                         TABLE

SQL> select * from test at piruks.kang.com;

NAME                        AGE
-------------------- ----------
강명규                       27

SQL> insert into test at piruks.kang.com values('홍길동', 30);

1 개의 행이 만들어졌습니다.

SQL> update test at piruks.kang.com set age=31 where age=30;

1 행이 갱신되었습니다.

SQL> select * from test at piruks.kang.com;

NAME                        AGE
-------------------- ----------
강명규                       27
홍길동                       31

SQL> delete from test at piruks.kang.com where age=31;

1 행이 삭제되었습니다.

데이터베이스링크에서 DDL문장은 적용되지 않는다.
SQL> drop table test at piruks.kang.com;
drop table test at piruks.kang.com
                *
1 행에 오류:
ORA-02021: 원격 데이터베이스에 DDL 조작들이 허용되지 않습니다

SQL> drop database link piruks.kang.com;

데이타베이스 링크가 삭제되었습니다.

SQL>

삭 제할대 DBLINK의 소유자로 삭제 하여야 한다.

========================================================================
Database Link(데이터베이스 링크)
먼저 디비 링크를 사용하기 위해서는 v$parameter 에 global_names=false로 되어 있어
야 합니다..
init.ora를 고치던지.아님 alter system set global_names=false;로 해 줘야 합니다.
그리고 grant create database link to scott; 로 해당 유저한테 권한을 부여 합니다.
그리고 tnsnames.ora파일을 수정해 해당 연결하고자 하는 database의 알리아스를 만
들어 주면 됩니다.

우선 고려되어야 사항은 ORACLE INSTANCE가 두 개 이상이고 각기 다른 PLATFORM에
서 운용된다는 가정하에서 각각의 HOST NAME과 ORACLE_SID는 다르고
NLS_CHARACTER_SET 은 동일하게 되어 있어야 합니다.

만약 같은 MACHINE에서 INSTANCE의 ORACLE_SID가 같다면 TNS ERROR가 발생할
것이다.
또한 미래를 위해 다른 MACHINE이라 할지라도 ORACLE_SID는 규칙에 의해 다르게 가
져가는 것이 좋습니다.
그리고, NLS_CHARACTER_SET이 동일하게 되어 있지 않으면 DATA 입/출력 시 한글 데
이타가 ?????로 나타날 것입니다.

그럼 환경 점검이 끝났으니 ORACLE7에서 ORACLE V6(ORACLE7의 경우도 비슷)에 있
는 TABLE의 DATA를 DB LINK를 이용하여 SELECT하거나 VIEW를 작성하여 보겠습니
다.

HOST NAME    :      HP7    ->    SUN7
ORACLE_SID   :      ORA7   -     ORATEST  이라 할 때

   1)  HP7 에서 SUN7로 dblink생성하기.

       scott/tiger 로 Login

       SQL> create public database link HP7TOSUN7
            connect to scott identified by tiger
            using 'ORATEST';
       로 하면 된다.

       이때 V2인 경우의 ORATEST는 $ORACLE_HOME/network/admin directory의
       tnsnames.ora file 내에 지정된 service name이다.
       tnsnames.ora의 service name이 잘 setting 되어 있는지 확인하는 방법
       : SQL*Plus scott/tiger@service name했을 때, SQL*Plus에 log-in되어야
       합니다.

   2> SUN7 에 있는 TABLE의 select 및 view(view는 필요에 따라 생성) 작성,
      HP에서 작업

       SQL> select * from emp@HP7TOSUN7;

       SQL> create view emp_view as select * from emp@HP7TOSUN7  a
                 where a.deptno = 10;

   3> HP7 에서 SYNONYM을 생성하여 사용하는 경우

       SQL> create synonym emp for emp@HP7TOSUN7;
       SQL> select * from emp;

로 한다면 간단히 분산 DB의 환경에서 사용 할 수 있습니다.

select 를 제외한 DML(insert,update,delete) 를 하려면,sqlplus log-in 시에 다음과 같
은 option 이 display 되어야 합니다.

   SQL*Plus: Release 3.3.3.0.0 - Production on Mon Jan 19 14:18:47 1998
   Copyright (c) Oracle Corporation 1979, 1996.  All rights reserved.

   Connected to:
   Oracle7 Server Release 7.3.3.4.0 with the 64-bit option - Production
   Release With the distributed, ......
   ------------------------------------

   remote 작업의 예

   select * from table_name@HP7TOSUN7;
   insert into table_name@HP7TOSUN7;
   delete table_name@HP7TOSUN7;

   (단 SERVER TO SERVER로 NETWORK 환경이 구축되어 있어야 하고,
   listener 가 반드시 떠 있어야 합니다.)

SQL>   create public database link link_test
  2    connect to scott identified by tiger
  3    using 'haksan21'; -- service name

데이타베이스 링크가 생성되었습니다.

SQL>  select * from all_db_links;

OWNER      DB_LINK              USERNAME   HOST       CREATED
---------- -------------------- ---------- ---------- --------
PUBLIC     HSA.WORLD            HSA        haksan21   99/03/12
PUBLIC     HSE.WORLD            HSE        haksan21   99/03/30
PUBLIC     HSR.WORLD            HSR        haksan21   99/03/23
PUBLIC     LINK_TEST.WORLD      SCOTT      haksan21   99/05/07

SQL> drop public database link link_test;

데이타베이스 링크가 삭제되었습니다.

SQL> select * from all_db_links;

OWNER      DB_LINK              USERNAME   HOST       CREATED
---------- -------------------- ---------- ---------- --------
PUBLIC     HSA.WORLD            HSA        haksan21   99/03/12
PUBLIC     HSE.WORLD            HSE        haksan21   99/03/30
PUBLIC     HSR.WORLD            HSR        haksan21   99/03/23

** export, import 작업을 수행할 경우 (또는 동일한 환경으로 새로운 유저를 만들경
우) ??

Synonym 처음
* 목적 : 다른 사용자가 소유한 테이블을 쉽게 사용하기 위해
         OBject 이름을 짧게 하기 위해

* SYNONYM의 정보를 가진 Data Dictionary
  USER_SYNONYMS
  ALL_SYNONYMS

* 예문
  create synonym tmp00 for fsr.tmp00;

* 삭제 : drop synonym synonym_name

*** DB LINK의 사용

    다른 기계의 데이타베이스를 사용하고자 할 때 이용

    create public database link link_name
    connect to user_name identified by password
    using 'db_alias_name';

    확인 : all_db_links

    사용 : select * from user_name.table_name@link_name;
    synonym : create synonym synonym_name for
user_name.table_name@link_name;

View 처음
* 뷰는 테이블이나 다른 뷰를 기반으로 한 논리적 테이블이다. 스스로 자료를 가지지 않
으 나
  조회, 수정할 수 있는 창문과 같다. 뷰의 근거가 되는 테이블을 base table이라 한다. 뷰
는
  select 문장의 형태로 data dictionary에 저장된다.

* 잇점 : 1) DB의 특정 부분만 조회하므로 접근을 제한할 수 있다.
         2) 복잡한 질의를 단순화할 수 있다. (여러 테이블의 조인관계를 숨길 수 있다.)

* 뷰를 만드는 여러 예문
  create or replace view empvu30
  as select * from emp where deptno = 30
     with check option constraint empvu30_ck; -- deptno가 30인 자료만 가능

  create or replace view empvu30 (id_number, employee, job) -- alias 변경
  as select empno, ename, job from emp where deptno = 30 with read only; --
delete불가

  create or replace view v_yycnt as
  select sum(aa) aa, sum(bb) bb, sum(cc) cc
  from (
       select count(*) aa, 0 bb, 0 cc from emp
        where to_char(hiredate, 'yy') = '81'
       union all
       select 0 aa, count(*) bb, 0 cc from emp
        where to_char(hiredate, 'yy') = '82'
       union all
       select 0 aa, 0 bb, count(*) cc from emp
        where to_char(hiredate, 'yy') = '83');

* 뷰의 정보를 가진 Data Dictionary
  USER_VIEWS;
  ALL_VIEWS;
  DESC view_name;

* 삭제 : drop view view_name;

DBMS/오라클]EXP-00003: 세그먼트 관련 에러

증상 : EXP-00003: 세그먼트... 에러가 발생하는 경우 해결 방법

0. 오라클 클라이언트의 버전을 동일 버전으로 업그레이드 한다.

1. oracle 9.x 버전(클라이언트) 에서 10g 서버의 데이터 익스포트시 :

EXP-00003 : no storage definition found for segment(7,1955)

라는 에러 발생시

dba 권한 사용자가 아래의 명령을 실행하면 해결 된다. export시 참조하는 view를 만드는 쿼리문이다.

SQL> @?/rdbms/admin/catexp

2. ==>(원문 출처 : ) http://link.allblog.net/13777149/http://gampol.tistory.com/entry/9i-EXP-00003

When you use old version of exp to export tables with LOB column from Oracle 9.2.0.5 or higher version, you will get an error "EXP-00003 : no storage definition found for segment .....", actually this is an Oracle bug, you could temporary get it resolved by replace a view "exu9tne", as following:

Before exporting, run the following SQL under sys:

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * FROM SYS.EXU9TNEB
/

After exporting, run the following to restore the view definition according to Metalink Notes.

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1

DBMS/오라클]ora-09925 해결책(파일시스템 풀인 경우)

원본출처 : http://yayongi.tistory.com/entry/ora-09925-%ED%95%B4%EA%B2%B0%EC%B1%85

ora-09925 해결책

Programming/Oracle2008/02/29 10:58

oracle의 admin/sid/pfile 에 보면 init 파일이 존재하는데 이곳에서 다음의 블록 부분을 확인한다.

그런다음 그 위치로

가서 디렉토리의 용량이 찼는지 확인한 다음, 만약 찼으면 파일들을 지워준다.

그러면 해결될 것이당~~^^)/

DBMS/오라클]Oracle Database 10g: DBA를 위한 20가지 주요 기능

scheduler

dbms_job 패키지의 실행 주기 설정을 수작업으로 관리하는 것이 번거로우십니까? 이제 10g데이타베이스가 제공하는 Scheduler를 이용해 보십시오.

여러분들 중 일부는 백그라운드 데이타베이스 작업 스케줄을 설정하기 위해 dbms_job 패키지를 사용하고 있을 것입니다. 하지만 필자가 알기로는 대부분의 DBA들이 dbms_job 패키지를 사용하지 않고 있습니다.

이 패키지는 PL/SQL 코드 세그먼트만을 처리할 수 있으며, 데이타베이스 외부의 운영체제 파일 또는 실행 파일 이미지를 처리할 수 없다는 기능적 한계를 갖고 있습니다. 이 때문에 DBA들은 Unix의 cron, Windows의 AT 명령 등을 사용하여 운영 체제 레벨에서 스케줄링을 설정하는 방법을 선택합니다. 또는 그래픽 사용자 인터페이스를 제공하는 써드 파티 툴을 사용하기도 합니다.

그 러나 dbms_job은 이들과 다른 장점들이 있습니다. 이 중 하나는 데이터베이스가 실행되고 있는 경우에만 활성화된다는 것입니다. 만약 데이터베이스가 다운되어 있다면 해당 Job들은 실행되지 않습니다. 데이터베이스 외부에 존재하는 툴들은 수작업을 통해 데이터베이스가 실행되고 있는지 조사되어야 하며, 복잡한 작업일 수 있습니다. Dbms_job의 다른 장점으론 데이터베이스 내부에 존재한다는 것입니다. 그러므로 SQL*Plus와 같은 유틸리티를 이용해 쉽게 접근 가능하다는 것입니다.

Oracle Database 10g Scheduler는 모든 종류의 작업을 지원하는 내장형 작업 스케줄러 유틸리티를 제공합니다. 10g Scheduler의 가장 큰 장점은, 데이타베이스에 포함된 형태로 제공되므로 추가적인 비용이 들지 않는다는 것입니다. 이번 연재에서는 Scheduler의 기능에 대해 자세히 살펴보기로 합니다.

Creating Jobs Without Programs

개념의 이해를 돕기 위해 예를 통해 설명하겠습니다. 아카이브 로그 파일을 다른 파일시스템으로 이동하기 위해 아래와 같은 이름의 셸 스크립트를 생성했다고 가정해 봅시다:

/home/arup/dbtools/move_arcs.sh

별도의 프로그램을 생성하지 않고도 작업 유형으로 “Executable”을 지정하고 스케줄링을 설정할 수 있습니다.

          

           

          begin

             dbms_scheduler.create_job

             (

                job_name      => 'ARC_MOVE_2',

                schedule_name => 'EVERY_30_MINS',

                job_type      => 'EXECUTABLE',

                job_action    => '/home/arup/dbtools/move_arcs.sh',

                enabled       => true,

                comments      => 'Move Archived Logs to a Different Directory'

             );

          end;

          /

또 Schedule Name을 설정하지 않고 작업을 생성하는 것도 가능합니다.

          

           

          begin

             dbms_scheduler.create_job

             (

                job_name        => 'ARC_MOVE_3',

                job_type        => 'EXECUTABLE',

                job_action      => '/home/arup/dbtools/move_arcs.sh',

                repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',

                enabled         => true,

                comments        => 'Move Archived Logs to a Different Directory'

             );

          end;

          /

앞의 두 가지 예를 통해 dbms_job과 비교했을 때의 Scheduler의 장점을 확인할 수 있습니다. Scheduler를 이용하면 PL/SQL 프로그램뿐 아니라 OS 유틸리티와 프로그램을 실행하는 것이 가능합니다. 이러한 기능을 활용하여 범용적인 데이타베이스 작업 관리 환경을 구현할 수 있습니다. 또 Scheduler는 자연 언어(natural language)를 사용하여 실행 주기를 정의할 수 있다는 매우 중요한 이점을 제공합니다. 스케줄을 매 30분 단위로 실행하고자 하는 경우, (PL/SQL 문법 대신) 자연 언어 형식의 표현을 사용하여 REPEAT_INTERVAL 매개변수를 아래와 같이 정의할 수 있습니다:

'FREQ=MINUTELY; INTERVAL=30'

좀 더 복잡한 예를 들어 설명해 보겠습니다. 운영 중인 애플리케이션이 오전 7시에서 오후 3시까지의 시간대에 집중적으로 사용된다고 가정해 봅시다. Statspack을 실행하면 월요일~금요일 오전 7시 ~ 오후 3시의 시스템 통계를 수집할 수 있습니다. DBMS_JOB.SUBMIT을 사용하여 작업을 생성하는 경우라면, NEXT_DATE 매개변수는 아래와 같이 정의됩니다:

          

           

          DECODE

          (

             SIGN

             (

                15 - TO_CHAR(SYSDATE,'HH24')

             ), 

             1,

                TRUNC(SYSDATE)+15/24,

             TRUNC

             (

                SYSDATE +

                DECODE

                ( 

                    TO_CHAR(SYSDATE,'D'), 6, 3, 1

                )

              )

              +7/24

          )

위의 코드가 이해하기 쉽습니까? 전혀 그렇지 않습니다.

이번에는 DBMS_SCHEDULER를 이용하는 방법을 살펴 보겠습니다. REPEAT_INTERVAL 매개변수는 아래와 같이 간단하게 정의됩니다:

'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15'

이 매개변수를 이용해 실행 주기를 다양한 형태로 설정할 수 있습니다. 몇 가지 예가 아래와 같습니다:

● 매월 마지막 일요일:
FREQ=MONTHLY; BYDAY=-1SUN

● 매월 세 번째 금요일:
FREQ=MONTHLY; BYDAY=3FRI

● 매월 뒤에서 두 번째 금요일
FREQ=MONTHLY; BYDAY=-2FRI

마이너스(-) 기호는 숫자가 뒤에서부터 계산된다는 의미입니다.

설정된 실행 주기가 올바른지 확인하려면 어떻게 해야 할까요? 캘린더 문자열을 이용해서 날짜를 미리 확인할 수 있다면 편리하지 않을까요? EVALUATE_CALENDAR_STRING 프로시저를 이용하여 실행 예정 시각을 미리 검토할 수 있습니다. 위의 예 ? 월~금7:00 AM ~ 3:00 PM Statspack 실행 ? 에서 설정된 실행 주기를 확인하는 방법이 아래와 같습니다:

          

           

          set serveroutput on size 999999

          declare

             L_start_date    TIMESTAMP;

             l_next_date     TIMESTAMP;

             l_return_date   TIMESTAMP;

          begin

             l_start_date := trunc(SYSTIMESTAMP);

             l_return_date := l_start_date;

             for ctr in 1..10 loop

                dbms_scheduler.evaluate_calendar_string(

                  'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15',

                   l_start_date, l_return_date, l_next_date

                );

                dbms_output.put_line('Next Run on: ' ||

                    to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')

                );

                l_return_date := l_next_date;

             end loop;

          end;

          /

실행 결과는 다음과 같습니다:

Next Run on: 03/22/2004 07:00:00
Next Run on: 03/22/2004 15:00:00
Next Run on: 03/23/2004 07:00:00
Next Run on: 03/23/2004 15:00:00
Next Run on: 03/24/2004 07:00:00
Next Run on: 03/24/2004 15:00:00
Next Run on: 03/25/2004 07:00:00
Next Run on: 03/25/2004 15:00:00
Next Run on: 03/26/2004 07:00:00
Next Run on: 03/26/2004 15:00:00

위 결과로 미루어 설정된 내용에 문제가 없음을 확인할 수 있습니다.

작업과 프로그램의 연계

위의 사례는 특정 프로그램과 연결되지 않은 작업을 생성하는 방법을 설명하고 있습니다. 이번에는 특정 OS 유틸리티를 사용하는 프로그램을 생성하고, 실행 주기를 정의한 스케줄을 설정한 뒤, 이 두 가지를 조합하여 새로운 작업을 생성하는 방법을 설명합니다.

먼저 작업 내에서 프로그램을 사용한다는 사실을 데이타베이스가 인지하도록 해야 합니다. 이 프로그램을 생성하려면, CREATE JOB 권한을 갖고 있어야 합니다.

          

           

          begin

              dbms_scheduler.create_program

              (

                 program_name   => 'MOVE_ARCS',

                 program_type   => 'EXECUTABLE',

                 program_action => '/home/arup/dbtools/move_arcs.sh',

                 enabled        => TRUE,

                 comments       => 'Moving Archived Logs to Staging Directory'

              );

          end;

          /

이제 named program unit을 생성하여, 그 유형을 “executable”로 지정하고 실행될 program unit의 이름을 정의하였습니다.

다음으로, 매 30분 간격으로 실행되는 EVERY_30_MIN이라는 이름의 스케줄을 생성합니다:

          

           

          begin

              dbms_scheduler.create_schedule

              (

                 schedule_name   => 'EVERY_30_MINS',

                 repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',

                 comments        => 'Every 30-mins'

              );

          end;

          /

프로그램과 스케줄을 생성한 뒤, 이 두 가지를 연관시킴으로써 새로운 작업을 생성합니다.

          

           

          begin

             dbms_scheduler.create_job

             (

                job_name      => 'ARC_MOVE',

                program_name  => 'MOVE_ARCS',

                schedule_name => 'EVERY_30_MINS',

                comments      => 'Move Archived Logs to a Different Directory',

                enabled       => TRUE

             );

          end;

          /

이제 매 30분마다 move_arcs.sh 셸 스크립트를 실행하는 작업이 생성되었습니다. 이 스케줄은 데이타베이스 내부의 Scheduler 기능을 통해 관리되며, 따라서 cron 또는 AT 유틸리티를 사용할 필요가 없습니다.

Classes, Plans, and Windows

작업 스케줄링 시스템에 효과적으로 활용하려면 작업의 우선순위 지정이 가능해야 합니다. 예를 들어 OLTP 워크로드가 실행 중인 시간대에 통계 수집 작업이 갑자기 실행되어 성능을 저하시킬 수 있습니다. 통계 수집 작업이 OLTP 성능에 영향을 미치지 않도록 하기 위해, Scheduler가 제공하는 job classes, resource plans, and Scheduler Windows기능을 활용할 수 있습니다.

Job class는 할당된 자원을 공유하는 “resource consumer group”으로 매핑됩니다. Job class를 생성하기 위해, 먼저 OLTP_GROUP이라는 이름의 resource consumer group을 정의해 보겠습니다.

          

           

          begin

             dbms_resource_manager.clear_pending_area();

             dbms_resource_manager.create_pending_area();

             dbms_resource_manager.create_consumer_group (

                 consumer_group => 'oltp_group',   

                 comment => 'OLTP Activity Group'

             );

             dbms_resource_manager.submit_pending_area();

          end;

          /

다음에는 resource plan을 생성합니다.

          

           

          begin

             dbms_resource_manager.clear_pending_area();

             dbms_resource_manager.create_pending_area();

             dbms_resource_manager.create_plan

                ('OLTP_PLAN', 'OLTP Database Activity Plan');

             dbms_resource_manager.create_plan_directive(

                plan => 'OLTP_PLAN',

                group_or_subplan => 'OLTP_GROUP',

                comment => 'This is the OLTP Plan',

                cpu_p1 => 80, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,

                cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,

                parallel_degree_limit_p1 => 4,

                active_sess_pool_p1 => NULL,

                queueing_p1 => NULL,

                switch_group => 'OTHER_GROUPS',

                switch_time => 10,

                switch_estimate => true,

                max_est_exec_time => 10,

                undo_pool => 500,

                max_idle_time => NULL,

                max_idle_blocker_time => NULL,

                switch_time_in_call => NULL

             );

             dbms_resource_manager.create_plan_directive(

                plan => 'OLTP_PLAN',

                group_or_subplan => 'OTHER_GROUPS',

                comment => NULL,

                cpu_p1 => 20, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,

                cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,

                parallel_degree_limit_p1 => 0,

                active_sess_pool_p1 => 0,

                queueing_p1 => 0,

                switch_group => NULL,

                switch_time => NULL,

                switch_estimate => false,

                max_est_exec_time => 0,

                undo_pool => 10,

                max_idle_time => NULL,

                max_idle_blocker_time => NULL,

                switch_time_in_call => NULL

             );

             dbms_resource_manager.submit_pending_area();

          end;

          /

마지막으로 앞에서 생성된 resource consumer group을 이용해 job class를 생성합니다.

          

           

          begin

             dbms_scheduler.create_job_class(

                job_class_name => 'OLTP_JOBS',

                logging_level => DBMS_SCHEDULER.LOGGING_FULL,

                log_history => 45,

                resource_consumer_group => 'OLTP_GROUP',

                comments => 'OLTP Related Jobs'

             );

          end;

          /

이 프로시저에서 사용된 매개변수들을 설명해 보겠습니다. LOGGING_LEVEL 매개변수는 해당 job class를 위해 얼마나 많은 로그 데이타를 기록할 것인지 정의하는데 사용됩니다. LOGGING_FULL은 job class에 포함된 작업의 모든 활동(생성, 삭제, 실행, 변경 등)을 로그에 기록함을 의미합니다. 로그는 DBA_SCHEDULER_JOB_LOG 뷰를 통해 확인할 수 있으며, LOG_HISTORY 매개변수에 저장된 대로 45일간 보관됩니다 (디폴트 값은 30일입니다). 이 class와 연관된 resource_consumer_group 도 정의되어 있습니다. DBA_SCHEDULER_JOB_CLASSES 뷰를 통해 정의된 job class들을 확인할 수 있습니다.

작업을 생성하는 과정에서, 필요한 경우 해당 작업을 job class에 할당할 수 있습니다. 예를

들어 collect_opt_stats() 저장 프로시저를 실행하여 옵티마이저 통계를 수집하는 COLLECT_STATS 작업을 생성하면서, 아래와 같이 job class를 할당할 수 있습니다.

          

           

          begin

             dbms_scheduler.create_job

             (

                job_name        => 'COLLECT_STATS',

                job_type        => 'STORED_PROCEDURE',

                job_action      => 'collect_opt_stats',

                job_class       => 'OLTP_JOBS',

                repeat_interval => 'FREQ=WEEKLY; INTERVAL=1',

                enabled         => true,

                comments        => 'Collect Optimizer Stats'

             );

          end;

          /

위 명령을 실행하면 생성된 작업이 OLTP_JOBS 클래스에 할당됩니다. OLTP_JOBS 클래스에 적용되는 OLTP_GROUP resource plan을 통해 프로세스에 할당되는 CPU 자원, 다른 그룹으로 전환되기 전에 최대 실행 가능한 횟수, 전환되는 그룹 등을 설정할 수 있습니다. 동일한 job class에 할당된 작업은 동일한 resource plan의 적용을 받습니다. 이 기능을 활용하면 서로 다른 유형의 작업이 같은 리소스를 두고 경합을 벌이는 상황을 방지할 수 있습니다.

Scheduler Window는 특정 resource plan이 사용되는 시간대를 의미합니다. 예를 들어, 실시간 의사결정 작업에 관련한 업데이트 배치 작업이 주간에는 높은 우선순위를 갖는 반면 야간에는 낮은 우선순위를 갖는다고 가정해 봅시다. 이 경우 시간대별로 다른 resource plan을 정의하고, 정의된 내용을 Scheduler Window를 이용해 적용할 수 있습니다.

모니터링

실행 중인 작업의 상태는 DBA_SCHEDULER_JOB_LOG 뷰를 통해 확인할 수 있습니다. 이 뷰의 STATUS 컬럼은 작업의 현재 상태를 표시하는데 사용됩니다. 만일 STATUS 컬럼이 FAILED로 표시된다면, DBA_SCHEDULER_JOB_RUNS_DETAILS 뷰를 통해 그 원인을 확인할 수 있습니다.

관리

지금까지 여러 가지 유형의 오브젝트(program, schedule, job, job class 등)를 생성하는 방법에 대해 설명했습니다. 이렇게 생성된 오브젝트를 변경할 필요가 있다면, DBMS_SCHEDULER 패키지가 제공하는 API를 사용하면 됩니다.

Enterprise Manager 10g 홈 페이지에서 Administration 링크를 클릭하면, 그림 1과 같이 Administration 화면이 표시됩니다. Scheduler와 관련된 작업은 우측 하단의 “Scheduler” 항목에 표시됩니다 (그림의 붉은색 원 참조).

그림 1: Administration 페이지

이 페이지에서 제공되는 하이퍼링크를 활용하면 작업의 생성, 삭제, 관리와 같은 Scheduler 관련 작업을 쉽게 수행할 수 있습니다. 몇 가지 예를 들어 설명해 보겠습니다. 이미 작업을 생성해 둔 상태이므로, Job 탭을 클릭하면 그림 2와 같은 화면이 표시될 것입니다.

그림 2: 작업 스케줄의 확인

COLLECT_STATS 작업을 클릭하여 그 속성을 변경해 보도록 합시다. “Name” 필드를 클릭하면 그림 3과 같은 화면이 표시됩니다.

그림 3: 작업 매개변수

지금까지 확인한 것처럼, EM을 이용하면 작업, 스케줄, 옵션 등에 관련한 매개변수를 수정할 수 있습니다. 변경 작업을 완료한 뒤 “Apply” 버튼을 누르면 변경사항은 영구적으로 적용됩니다. “Apply” 버튼을 누르기 전에 “Show SQL” 버튼을 눌러 실행되는 SQL 구문을 확인하는 것도 가능합니다. 또 SQL 구문을 스크립트에 저장하여 나중에 실행하거나, 템플릿 용도로 활용할 수도 있습니다.