Rem
Rem $Header: catpexe.sql 13-dec-2007.18:21:04 achoi Exp $
Rem
Rem catpexe.sql
Rem
Rem Copyright (c) 2007, Oracle.  All rights reserved.  
Rem
Rem    NAME
Rem      catpexe.sql - CATalog script for DBMS_PARALLEL_EXECUTE
Rem
Rem    DESCRIPTION
Rem      It creates a role, tables and views for DBMS_PARALLEL_EXECUTE
Rem      package.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    achoi       11/01/07 - Created
Rem

-- Administrator Role for DBMS_PARALLEL_EXECUTE.ADM_ subprograms
create role ADM_PARALLEL_EXECUTE_TASK;

-- Create the DBMS_PARALLEL_EXECUTE_TASK$ table
CREATE TABLE DBMS_PARALLEL_EXECUTE_TASK$ 
                 (TASK_OWNER#                NUMBER        NOT NULL,
                  TASK_NAME                  VARCHAR2(128) NOT NULL, 
                  CHUNK_TYPE                 NUMBER        NOT NULL,
                  STATUS                     NUMBER        NOT NULL,
                  TABLE_OWNER                VARCHAR2(30),
                  TABLE_NAME                 VARCHAR2(30),
                  NUMBER_COLUMN              VARCHAR2(30),
                  CMT                        VARCHAR2(4000),
                  JOB_PREFIX                 VARCHAR2(30),
                  STOP_FLAG                  NUMBER,
                  SQL_STMT                   CLOB,
                  LANGUAGE_FLAG              NUMBER,
                  EDITION                    VARCHAR2(30),
                  APPLY_CROSSEDITION_TRIGGER VARCHAR2(30),
                  FIRE_APPLY_TRIGGER         VARCHAR2(10),
                  PARALLEL_LEVEL             NUMBER,
                  JOB_CLASS                  VARCHAR2(30),
                    CONSTRAINT PK_DBMS_PARALLEL_EXECUTE_1
                      PRIMARY KEY (TASK_OWNER#, TASK_NAME)
                 );

-- Create DBMS_PARALLEL_EXECUTE_CHUNKS$ table
CREATE TABLE DBMS_PARALLEL_EXECUTE_CHUNKS$
                 (CHUNK_ID       NUMBER        NOT NULL PRIMARY KEY,
                  TASK_OWNER#    NUMBER        NOT NULL,
                  TASK_NAME      VARCHAR2(128) NOT NULL,
                  STATUS         NUMBER        NOT NULL,
                  START_ROWID    ROWID,
                  END_ROWID      ROWID,
                  START_ID       NUMBER,
                  END_ID         NUMBER,
                  JOB_NAME       VARCHAR2(30),
                  START_TS       TIMESTAMP,
                  END_TS         TIMESTAMP,
                  ERROR_CODE     NUMBER,
                  ERROR_MESSAGE  VARCHAR2(4000),
                    CONSTRAINT FK_DBMS_PARALLEL_EXECUTE_1
                      FOREIGN KEY (TASK_OWNER#, TASK_NAME)
                      REFERENCES DBMS_PARALLEL_EXECUTE_TASK$
                                   (TASK_OWNER#, TASK_NAME)
                      ON DELETE CASCADE
                 );

-- Create DBMS_PARALLEL_EXECUTE_SEQ$ sequence
create sequence dbms_parallel_execute_seq$;