REM Akadia AG, Zieglerstrasse 34, CH-3007 Bern REM ---------------------------------------------------------------------- REM REM File: initPAR1.ora REM REM Autor: Martin Zahn / 10.07.1998 REM REM Purpose: Create Database Script REM REM Location: $ORACLE_HOME\Database REM REM Certified: Oracle V.8.0.3 on Windows-NT REM --------------------------------------------------------------------- SPOOL D:\Orant\Database\initPAR1.log CONNECT sys/manager AS SYSDBA; REM ************************************************************* REM * * REM * Startup database * REM * * REM ************************************************************* STARTUP NOMOUNT pfile=D:\Orant\Database\initPAR1_init.ora REM ************************************************************* REM * * REM * Create database. * REM * * REM ************************************************************* CREATE DATABASE PAR1 CONTROLFILE REUSE MAXDATAFILES 256 MAXINSTANCES 4 MAXLOGFILES 62 MAXLOGMEMBERS 5 MAXLOGHISTORY 1600 CHARACTER SET "WE8ISO8859P1" NATIONAL CHARACTER SET "WE8ISO8859P1" DATAFILE 'E:\Oradata\PAR1\sys\PAR1_sys1.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED LOGFILE GROUP 1 ('E:\Oradata\PAR1\rdo\PAR1_log1A.rdo', 'E:\Oradata\PAR1\rdo\PAR1_log1B.rdo') SIZE 4M REUSE, GROUP 2 ('E:\Oradata\PAR1\rdo\PAR1_log2A.rdo', 'E:\Oradata\PAR1\rdo\PAR1_log2B.rdo') SIZE 4M 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\PAR1\rbs\PAR1_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\PAR1\tmp\PAR1_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\PAR1\usr\PAR1_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\PAR1\tab\PAR1_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\PAR1\cdr\PAR1_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\PAR1\cre\PAR1_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\PAR1\idx\PAR1_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\PAR1\oem\PAR1_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\PAR1\des\PAR1_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 * Set security for the administrative users. * REM * * REM ************************************************************* ALTER USER sys TEMPORARY TABLESPACE temp; ALTER USER system TEMPORARY TABLESPACE temp; ALTER USER system IDENTIFIED BY ...; ALTER USER sys IDENTIFIED BY ...; 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:\ORANT\RDBMS80\admin\catalog.sql @D:\ORANT\RDBMS80\admin\catparr.sql @D:\ORANT\RDBMS80\admin\catproc.sql @D:\ORANT\RDBMS80\admin\catrep.sql @D:\ORANT\RDBMS80\admin\catio.sql @D:\ORANT\RDBMS80\admin\dbmsiotc.sql @D:\ORANT\RDBMS80\admin\prvtiotc.plb @D:\ORANT\RDBMS80\admin\dbmspool.sql @D:\ORANT\RDBMS80\admin\utlchain.sql @D:\ORANT\RDBMS80\admin\utlexcpt.sql @D:\ORANT\RDBMS80\admin\utltkprf.sql @D:\ORANT\RDBMS80\admin\utlxplan.sql @D:\ORANT\RDBMS80\admin\catperf.sql @D:\ORANT\RDBMS80\admin\catexp7.sql @D:\ORANT\RDBMS80\admin\catblock.sql @D:\ORANT\RDBMS80\admin\utllockt.sql @D:\ORANT\RDBMS80\admin\dbmspipe.sql @D:\ORANT\RDBMS80\admin\dbmsalrt.sql @D:\ORANT\RDBMS80\admin\utlvalid.sql @D:\ORANT\RDBMS80\admin\utlraw.sql @D:\ORANT\RDBMS80\admin\prvtrawb.plb @D:\ORANT\RDBMS80\admin\dbmsoctk.sql @D:\ORANT\RDBMS80\admin\prvtoctk.plb @D:\ORANT\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; CONNECT system/manager; CREATE USER ppb IDENTIFIED BY ... DEFAULT TABLESPACE tab TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON cdr QUOTA UNLIMITED ON cre QUOTA UNLIMITED ON idx QUOTA UNLIMITED ON rbs QUOTA 0 ON system QUOTA UNLIMITED ON tab QUOTA UNLIMITED ON temp QUOTA UNLIMITED ON users PROFILE default; GRANT CONNECT,RESOURCE TO ppb; GRANT ALL PRIVILEGES TO ppb; CREATE USER des IDENTIFIED BY ... DEFAULT TABLESPACE des TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON des QUOTA UNLIMITED ON cdr QUOTA UNLIMITED ON cre QUOTA UNLIMITED ON idx QUOTA UNLIMITED ON rbs QUOTA 0 ON system QUOTA UNLIMITED ON tab QUOTA UNLIMITED ON temp QUOTA UNLIMITED ON users PROFILE default; GRANT CONNECT,RESOURCE TO des; GRANT ALL PRIVILEGES TO des; CREATE USER desuser IDENTIFIED BY ... DEFAULT TABLESPACE des TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON des QUOTA UNLIMITED ON cdr QUOTA UNLIMITED ON cre QUOTA UNLIMITED ON idx QUOTA UNLIMITED ON rbs QUOTA 0 ON system QUOTA UNLIMITED ON tab QUOTA UNLIMITED ON temp QUOTA UNLIMITED ON users PROFILE default; GRANT CONNECT, CREATE SESSION TO desuser; GRANT ALL PRIVILEGES TO desuser; DROP USER client; CREATE USER client IDENTIFIED BY ... DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON cdr QUOTA UNLIMITED ON cre QUOTA UNLIMITED ON idx QUOTA UNLIMITED ON rbs QUOTA 0 ON system QUOTA UNLIMITED ON tab QUOTA UNLIMITED ON temp QUOTA UNLIMITED ON users PROFILE default; DROP USER ops$ppb; CREATE USER ops$ppb IDENTIFIED EXTERNALLY DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON cdr QUOTA UNLIMITED ON cre QUOTA UNLIMITED ON idx QUOTA UNLIMITED ON rbs QUOTA 0 ON system QUOTA UNLIMITED ON tab QUOTA UNLIMITED ON temp QUOTA UNLIMITED ON users PROFILE default; CREATE USER oem IDENTIFIED BY oem DEFAULT TABLESPACE oem TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON oem QUOTA UNLIMITED ON cdr QUOTA UNLIMITED ON cre QUOTA UNLIMITED ON idx QUOTA UNLIMITED ON rbs QUOTA 0 ON system QUOTA UNLIMITED ON tab QUOTA UNLIMITED ON temp QUOTA UNLIMITED ON users PROFILE default; GRANT CONNECT,RESOURCE TO oem; GRANT ALL PRIVILEGES TO oem; ALTER USER dbsnmp TEMPORARY TABLESPACE temp; 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; CONNECT sys/manager AS SYSDBA; 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; ### sql*plus product user profile ### ----------------------------- CONNECT system/manager; @D:\ORANT\DBS\pupbld.sql ### Grant Privileges for SET AUTOTRACE in SQL*PLUS ### ---------------------------------------------- CONNECT sys/manager AS SYSDBA; 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:\ORANT\PLUS80\plustrce.sql GRANT plustrace to ELAN_ADMIN; GRANT plustrace to PPB_PROCESS; GRANT plustrace to PPB_CLIENT; SPOOL OFF; EXIT;