

### Startup database
### ----------------

STARTUP NOMOUNT pfile=D:\Oracle\Product\8.1.6\Database\initSAP1_init.ora

### Create database
### ---------------

CREATE DATABASE        SAP1
       CONTROLFILE     REUSE
       MAXDATAFILES    256
       MAXINSTANCES    4
       MAXLOGFILES     62
       MAXLOGMEMBERS   5
       MAXLOGHISTORY   1600
       CHARACTER SET   "UTF8"
       NATIONAL CHARACTER SET "WE8ISO8859P1"
       DATAFILE 'D:\Oradata\SAP1\sys\SAP1_sys1.dbf' SIZE 200M REUSE
       AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
 LOGFILE GROUP 1 ('D:\Oradata\SAP1\rdo\SAP1_log1A.rdo',
                  'C:\Oradata\SAP1\rdo\SAP1_log1B.rdo') SIZE 5M REUSE,
         GROUP 2 ('D:\Oradata\SAP1\rdo\SAP1_log2A.rdo',
                  'C:\Oradata\SAP1\rdo\SAP1_log2B.rdo') SIZE 5M REUSE,
         GROUP 3 ('D:\Oradata\SAP1\rdo\SAP1_log3A.rdo',
                  'C:\Oradata\SAP1\rdo\SAP1_log3B.rdo') SIZE 5M REUSE,
         GROUP 4 ('D:\Oradata\SAP1\rdo\SAP1_log4A.rdo',
                  'C:\Oradata\SAP1\rdo\SAP1_log4B.rdo') SIZE 5M REUSE;

CREATE ROLLBACK SEGMENT sys_rb2
        TABLESPACE SYSTEM
        STORAGE
        (
          INITIAL     100K
          NEXT        200K
          MINEXTENTS    2
          MAXEXTENTS  100
        );

ALTER ROLLBACK SEGMENT sys_rb2 ONLINE;

### Create tablespace for rollback segments
###
### Do not set PCTINCREASE for rollback segment tablespace
### nor for any rollback segment. Rollback segments always
### have a PCTINCREASE value of 0.
### -------------------------------------------------------

### Dictionary managed

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

### Locally managed (SIZE + 64K for Header Bitmap)
### e.g. for 500MB: (1024*500+64)=512064K

CREATE TABLESPACE rbs
        DATAFILE 'D:\Oradata\SAP1\rbs\SAP1_rbs1.dbf' SIZE 512064K REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
        PERMANENT
        ONLINE;

CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs
  STORAGE
  (INITIAL     1M
   NEXT        1M
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs01 ONLINE;

CREATE ROLLBACK SEGMENT rbs02 TABLESPACE rbs
  STORAGE
  (INITIAL     1M
   NEXT        1M
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs02 ONLINE;

CREATE ROLLBACK SEGMENT rbs03 TABLESPACE rbs
  STORAGE
  (INITIAL     1M
   NEXT        1M
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs03 ONLINE;

CREATE ROLLBACK SEGMENT rbs04 TABLESPACE rbs
  STORAGE
  (INITIAL     1M
   NEXT        1M
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs04 ONLINE;

CREATE ROLLBACK SEGMENT rbs05 TABLESPACE rbs
  STORAGE
  (INITIAL     1M
   NEXT        1M
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs05 ONLINE;

CREATE ROLLBACK SEGMENT rbs06 TABLESPACE rbs
  STORAGE
  (INITIAL     1M
   NEXT        1M
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs06 ONLINE;

### The rollback segments are to be set in the configuration file
### rollback_segments = (rbs01,rbs02,rbs03,rbs04,rbs05, ........
### The second rollback segment in system can now be set offline
### -------------------------------------------------------------

ALTER ROLLBACK SEGMENT sys_rb2 OFFLINE;

### Create temporary tablespace
### ---------------------------

### Create Dictionary managed TEMP tablespace for initial DB creation

# CREATE TABLESPACE temp
#         DATAFILE 'D:\Oradata\SAP1\tmp\SAP1_temp.dbf' SIZE 512064K REUSE
#         AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
#         DEFAULT STORAGE (INITIAL     2M
#                          NEXT        2M
#                          MINEXTENTS  1
#                          MAXEXTENTS  UNLIMITED
#                          PCTINCREASE 0)
#         TEMPORARY
#         ONLINE;

### Create locally managed TEMP tablespace after DB creation
### --------------------------------------------------------
### Locally managed (SIZE + 64K for Header Bitmap)

CREATE TEMPORARY TABLESPACE temp
       TEMPFILE 'D:\Oradata\SAP1\tmp\SAP1_temp1.dbf' SIZE 512064K REUSE
       AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

### Create users tablespace
### -----------------------

### Dictionary managed

# CREATE TABLESPACE users
#         DATAFILE 'D:\Oradata\SAP1\usr\SAP1_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;

### Locally managed (SIZE + 64K for Header Bitmap)

CREATE TABLESPACE users
        DATAFILE 'D:\Oradata\SAP1\usr\SAP1_users1.dbf' SIZE 10304K REUSE
        AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
        PERMANENT
        ONLINE;

### Create tablespace for small objects
### -----------------------------------

### Dictionary managed

# CREATE TABLESPACE tab
#         DATAFILE 'D:\Oradata\SAP1\tab\SAP1_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;

### Locally managed (SIZE + 64K for Header Bitmap)

CREATE TABLESPACE tab
        DATAFILE 'D:\Oradata\SAP1\tab\SAP1_tab1.dbf' SIZE 204864K REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
        PERMANENT
        ONLINE;

### Create tablespace for small Indexes
### -----------------------------------

### Dictionary managed

# CREATE TABLESPACE idx
#         DATAFILE 'D:\Oradata\SAP1\idx\SAP1_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;

### Locally managed (SIZE + 64K for Header Bitmap)

CREATE TABLESPACE idx
        DATAFILE 'D:\Oradata\SAP1\idx\SAP1_idx1.dbf' SIZE 204864K REUSE
        AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
        PERMANENT
        ONLINE;







### Load the system scripts
### -----------------------

SET STOPONERROR OFF;
SET TERMOUT ON;

# Creates data dictionary views. This is the very first
# Script which must be executed during Database Building
@D:\Oracle\Product\8.1.6\Rdbms\admin\catalog.sql

# Parallel-Server specific views for performance queries
@D:\Oracle\Product\8.1.6\Rdbms\admin\catparr.sql

# Scripts for the PL/SQL procedural option
@D:\Oracle\Product\8.1.6\Rdbms\admin\catproc.sql

# Run all sql scripts for replication option
@D:\Oracle\Product\8.1.6\Rdbms\admin\catrep.sql

# Collect I/O per table (actually object) statistics by
# statistical  sampling
@D:\Oracle\Product\8.1.6\Rdbms\admin\catio.sql

# This package creates a table into which references to
# the chained rows for an IOT (Index-Only-Table) can be
# placed using the ANALYZE command.
@D:\Oracle\Product\8.1.6\Rdbms\admin\dbmsiotc.sql

# Wrap Package which creates IOTs (Index-Only-Table)
@D:\Oracle\Product\8.1.6\Rdbms\admin\prvtiotc.plb

# This package allows you to display the sizes of objects in the
# shared pool, and mark them for keeping or unkeeping in order to
# reduce memory fragmentation.
@D:\Oracle\Product\8.1.6\Rdbms\admin\dbmspool.sql

# Creates the default table for storing the output
# of the ANALYZE LIST CHAINED ROWS command
@D:\Oracle\Product\8.1.6\Rdbms\admin\utlchain.sql

# Creates the EXCEPTION table
@D:\Oracle\Product\8.1.6\Rdbms\admin\utlexcpt.sql

# Grant public access to all views used by TKPROF
# with verbose=y option
@D:\Oracle\Product\8.1.6\Rdbms\admin\utltkprf.sql

# Create table PLAN_TABLE that is used by the EXPLAIN PLAN
# statement. The explain statement requires the presence of this
# table in order to store the descriptions of the row sources.
@D:\Oracle\Product\8.1.6\Rdbms\admin\utlxplan.sql

# Create performance tuning views
@D:\Oracle\Product\8.1.6\Rdbms\admin\catperf.sql

# Create v7 style export/import views against the v8 RDBMS
# so that EXP/IMP v7 can be used to read out data in a v8 RDBMS.
# These views are necessary if you want to export from Oracle8
# and import in an Oracle7 database.
@D:\Oracle\Product\8.1.6\Rdbms\admin\catexp7.sql

# Create views of oracle locks
@D:\Oracle\Product\8.1.6\Rdbms\admin\catblock.sql

# Print out the lock wait-for graph in a tree structured fashion
@D:\Oracle\Product\8.1.6\Rdbms\admin\utllockt.sql

# Creates the default table for storing the output of the
# analyze validate command on a partitioned table
@D:\Oracle\Product\8.1.6\Rdbms\admin\utlvalid.sql

# PL/SQL Package of utility routines for raw datatypes
@D:\Oracle\Product\8.1.6\Rdbms\admin\utlraw.sql
@D:\Oracle\Product\8.1.6\Rdbms\admin\prvtrawb.plb

# Contains the PL/SQL interface to the cryptographic toolkit
@D:\Oracle\Product\8.1.6\Rdbms\admin\dbmsoctk.sql
@D:\Oracle\Product\8.1.6\Rdbms\admin\prvtoctk.plb

# This package provides a built-in random number generator. It is
# faster than generators written in PL/SQL because it calls Oracle's
#internal random number generator.
@D:\Oracle\Product\8.1.6\Rdbms\admin\dbmsrand.sql

# DBMS package specification for Oracle8 Large Object
# This package provides routines for operations on BLOB
# and CLOB datatypes.
@D:\Oracle\Product\8.1.6\Rdbms\admin\dbmslob.sql

# Procedures for instrumenting database applications
# DBMS_APPLICATION_INFO package spec.
@D:\Oracle\Product\8.1.6\Rdbms\admin\dbmsapin.sql

### Recompile all INVALID objects
### -----------------------------
@D:\Oracle\Product\8.1.6\Rdbms\admin\utlrp.sql

### Password Verify Function
### ------------------------
### @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

### Load scripts for Oracle Enterprise Manager
### ------------------------------------------

@smptsi80.sql
@xpview.sql

### Create roles and users
### ----------------------
### GRANT EXECUTE ON dbms_pipe, dbms_alert must be granted
### to the real spm users, not through a role !

DISCONNECT;



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



DISCONNECT;


GRANT EXECUTE ON dbms_pipe TO scott;
GRANT EXECUTE ON dbms_alert TO scott;

### Setup PLAN_TABLE for TKPROF, SQL-Tuning
### ---------------------------------------

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


@D:\Oracle\Product\8.1.6\sqlplus\admin\pupbld.sql
@D:\Oracle\Product\8.1.6\sqlplus\admin\plustrce.sql
GRANT plustrace to scott;

EXIT;
