

REM *************************************************************
REM *                                                           *
REM * Startup database                                          *
REM *                                                           *
REM *************************************************************

STARTUP NOMOUNT pfile=D:\Oracle\Product\8.0.5\Database\initARK2_init.ora

REM *************************************************************
REM *                                                           *
REM * Create database.                                          *
REM *                                                           *
REM *************************************************************

CREATE DATABASE        ARK2
       CONTROLFILE     REUSE
       MAXDATAFILES    256
       MAXINSTANCES    4
       MAXLOGFILES     62
       MAXLOGMEMBERS   5
       MAXLOGHISTORY   1600
       CHARACTER SET   "WE8ISO8859P1"
       NATIONAL CHARACTER SET   "WE8ISO8859P1"
       DATAFILE 'E:\Oradata\ARK2\sys\ARK2_sys1.dbf' SIZE 100M REUSE
       AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
 LOGFILE GROUP 1 ('E:\Oradata\ARK2\rdo\ARK2_log1A.rdo',
                  'E:\Oradata\ARK2\rdo\ARK2_log1B.rdo') SIZE 15M REUSE,
         GROUP 2 ('E:\Oradata\ARK2\rdo\ARK2_log2A.rdo',
                  'E:\Oradata\ARK2\rdo\ARK2_log2B.rdo') SIZE 15M REUSE,
         GROUP 3 ('E:\Oradata\ARK2\rdo\ARK2_log3A.rdo',
                  'E:\Oradata\ARK2\rdo\ARK2_log3B.rdo') SIZE 15M REUSE,
         GROUP 4 ('E:\Oradata\ARK2\rdo\ARK2_log4A.rdo',
                  'E:\Oradata\ARK2\rdo\ARK2_log4B.rdo') SIZE 15M REUSE;
CREATE ROLLBACK SEGMENT sys_rb2
        TABLESPACE SYSTEM
        STORAGE
        (
          INITIAL     100K
          NEXT        200K
          MINEXTENTS    2
          MAXEXTENTS  100
        );

ALTER ROLLBACK SEGMENT sys_rb2 ONLINE;

REM *************************************************************
REM *                                                           *
REM * Create tablespace for rollback segments                   *
REM * (Do not set PCTINCREASE for rollback segment tablespace   *
REM *  nor for any rollback segment. Rollback segments always   *
REM *  have a PCTINCREASE value of 0.)
REM *                                                           *
REM *************************************************************

CREATE TABLESPACE rbs
        DATAFILE 'E:\Oradata\ARK2\rbs\ARK2_rbs1.dbf' SIZE 100M REUSE 
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     100K 
                         NEXT        200K
                         MINEXTENTS  5
                         MAXEXTENTS  100)
        PERMANENT
        ONLINE;

CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs 
  STORAGE
  (INITIAL     4M
   NEXT        4M
   MINEXTENTS  2
   MAXEXTENTS  UNLIMITED
   OPTIMAL     8M);
ALTER ROLLBACK SEGMENT rbs01 ONLINE;


CREATE ROLLBACK SEGMENT rbs02 TABLESPACE rbs
  STORAGE
  (INITIAL     4M
   NEXT        4M
   MINEXTENTS  2
   MAXEXTENTS  UNLIMITED
   OPTIMAL     8M);
ALTER ROLLBACK SEGMENT rbs02 ONLINE;

CREATE ROLLBACK SEGMENT rbs03 TABLESPACE rbs
  STORAGE
  (INITIAL     4M
   NEXT        4M
   MINEXTENTS  2
   MAXEXTENTS  UNLIMITED
   OPTIMAL     8M);
ALTER ROLLBACK SEGMENT rbs03 ONLINE;

CREATE ROLLBACK SEGMENT rbs04 TABLESPACE rbs
  STORAGE
  (INITIAL     4M
   NEXT        4M
   MINEXTENTS  2
   MAXEXTENTS  UNLIMITED
   OPTIMAL     8M);
ALTER ROLLBACK SEGMENT rbs04 ONLINE;

CREATE ROLLBACK SEGMENT rbs05 TABLESPACE rbs
  STORAGE
  (INITIAL     4M
   NEXT        4M
   MINEXTENTS  2
   MAXEXTENTS  UNLIMITED
   OPTIMAL     8M);
ALTER ROLLBACK SEGMENT rbs05 ONLINE;

CREATE ROLLBACK SEGMENT rbs06 TABLESPACE rbs
  STORAGE
  (INITIAL     4M
   NEXT        4M
   MINEXTENTS  2
   MAXEXTENTS  UNLIMITED
   OPTIMAL     8M);
ALTER ROLLBACK SEGMENT rbs06 ONLINE;

CREATE ROLLBACK SEGMENT rbs_big TABLESPACE rbs
  STORAGE
  (INITIAL     10M
   NEXT        10M
   MINEXTENTS  2
   MAXEXTENTS  UNLIMITED
   OPTIMAL     40M);
ALTER ROLLBACK SEGMENT rbs_big ONLINE;
ALTER ROLLBACK SEGMENT rbs_big OFFLINE;

REM ********************************************************************
REM * The rollback segments are to be set in the configuration file:   *
REM * rollback_segments     = (r01,r02,r03,r04,r05,r06)                *
REM * The second rollback segment in system can now be set offline.    *
REM ********************************************************************
 
ALTER ROLLBACK SEGMENT sys_rb2 OFFLINE;

REM ************************************************************
REM *                                                          *
REM * Create temporary tablespace                              *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE temp
        DATAFILE 'E:\Oradata\ARK2\tmp\ARK2_temp1.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     2M 
                         NEXT        2M
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        TEMPORARY
        ONLINE;

REM ************************************************************
REM *                                                          *
REM * Create users tablespace                                  *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE users
        DATAFILE 'E:\Oradata\ARK2\usr\ARK2_users1.dbf' SIZE 10M REUSE
        AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     300K 
                         NEXT        500K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

REM ************************************************************
REM *                                                          *
REM * Create PPB tablespace for small objects                  *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE tab
        DATAFILE 'E:\Oradata\ARK2\tab\ARK2_tab1.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     64K 
                         NEXT        64K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

REM ************************************************************
REM *                                                          *
REM * Create PPB tablespace for CDR table                      *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE cdr
        DATAFILE 'E:\Oradata\ARK2\cdr\ARK2_cdr1.dbf' SIZE 50M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     5M 
                         NEXT        5M
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

REM ************************************************************
REM *                                                          *
REM * Create PPB tablespace for CREDIT table                   *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE cre
        DATAFILE 'E:\Oradata\ARK2\cre\ARK2_cre1.dbf' SIZE 50M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     5M 
                         NEXT        5M
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

REM ************************************************************
REM *                                                          *
REM * Create Index tablespace                                  *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE idx
        DATAFILE 'E:\Oradata\ARK2\idx\ARK2_idx1.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     300K 
                         NEXT        500K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

REM ************************************************************
REM *                                                          *
REM * Create Oracle Enterprise Manager tablespace              *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE oem
        DATAFILE 'E:\Oradata\ARK2\oem\ARK2_oem1.dbf' SIZE 150M REUSE
        AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     300K 
                         NEXT        500K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

REM ************************************************************
REM *                                                          *
REM * Create Oracle Designer/2000 tablespace                   *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE des
        DATAFILE 'E:\Oradata\ARK2\des\ARK2_des1.dbf' SIZE 150M REUSE
        AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     300K 
                         NEXT        500K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;









REM *************************************************************
REM *                                                           *
REM * Load the system scripts.                                  *
REM * Load the scripts for procedural options (PL/SQL)          *
REM *                                                           *
REM *************************************************************

SET STOPONERROR OFF;
SET TERMOUT ON;

### scripts for data dictionary & server options 

@D:\Oracle\Product\8.0.5\RDBMS80\admin\catalog.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\catparr.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\catproc.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\catrep.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\catio.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\dbmsiotc.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\prvtiotc.plb
@D:\Oracle\Product\8.0.5\RDBMS80\admin\dbmspool.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\utlchain.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\utlexcpt.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\utltkprf.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\utlxplan.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\catperf.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\catexp7.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\catblock.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\utllockt.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\dbmspipe.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\dbmsalrt.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\utlvalid.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\utlraw.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\prvtrawb.plb
@D:\Oracle\Product\8.0.5\RDBMS80\admin\dbmsoctk.sql
@D:\Oracle\Product\8.0.5\RDBMS80\admin\prvtoctk.plb
@D:\Oracle\Product\8.0.5\RDBMS80\admin\dbmsrand.sql

### Oracle Enterprise Manager 
### -------------------------
@smptsi80.sql
@xpview.sql

REM *************************************************************
REM *                                                           *
REM * Create roles and users                                    *
REM *                                                           *
REM * Note: GRANT EXECUTE ON dbms_pipe must be granted to the   *
REM *       real sipp users, not through a role !               *
REM *                                                           *
REM *************************************************************

DISCONNECT;



    GRANT CONNECT,RESOURCE TO ppb;
    GRANT ALL PRIVILEGES TO ppb;


    GRANT CONNECT,RESOURCE TO des;
    GRANT ALL PRIVILEGES TO des;


    GRANT CONNECT, CREATE SESSION TO desuser;
    GRANT ALL PRIVILEGES TO desuser;

DROP USER client;


DROP USER ops$ppb;



    GRANT CONNECT,RESOURCE TO elan;
    GRANT ALL PRIVILEGES TO elan;

DROP USER sp_elan;



    GRANT CONNECT,RESOURCE TO oem;
    GRANT ALL PRIVILEGES TO oem;



CREATE ROLE PPB_PROCESS;
CREATE ROLE PPB_CLIENT;
CREATE ROLE ELAN_CLERK;
CREATE ROLE ELAN_ADMIN;

GRANT CREATE SESSION TO PPB_PROCESS;
GRANT CREATE SESSION TO PPB_CLIENT;
GRANT CREATE SESSION TO ELAN_CLERK;
GRANT CREATE SESSION TO ELAN_ADMIN;

GRANT PPB_PROCESS TO ppb;
GRANT PPB_PROCESS TO ops$ppb;
GRANT PPB_CLIENT TO client;
GRANT ELAN_ADMIN TO elan;
GRANT ELAN_ADMIN TO sp_elan;

DISCONNECT;


GRANT EXECUTE ON dbms_pipe TO client;
GRANT EXECUTE ON dbms_pipe TO ops$ppb;
GRANT EXECUTE ON dbms_pipe TO ppb;
GRANT EXECUTE ON dbms_alert TO client;
GRANT EXECUTE ON dbms_alert TO ops$ppb;
GRANT EXECUTE ON dbms_alert TO ppb;

DISCONNECT;



GRANT DELETE,INSERT,UPDATE,SELECT ON sys.plan_table TO ELAN_ADMIN;
GRANT DELETE,INSERT,UPDATE,SELECT ON sys.plan_table TO PPB_PROCESS;
GRANT DELETE,INSERT,UPDATE,SELECT ON sys.plan_table TO PPB_CLIENT;
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT SELECT ON V_$PARAMETER TO PPB_PROCESS;
GRANT SELECT ON V_$PARAMETER TO PPB_CLIENT;
GRANT SELECT ON V_$PARAMETER TO ELAN_ADMIN;

@D:\Oracle\Product\8.0.5\PLUS80\plustrce.sql
GRANT plustrace to ELAN_ADMIN;
GRANT plustrace to PPB_PROCESS;
GRANT plustrace to PPB_CLIENT;

SPOOL OFF;
EXIT;