REM Akadia AG, Arvenweg 4, CH-3604 Thun initSMA1.sql REM ---------------------------------------------------------------------- REM REM File: initSMA1.sql REM REM Autor: Martin Zahn / 10.07.2000 REM REM Purpose: Create Oracle Database on NT REM REM Location: $ORACLE_HOME\Database REM REM Certified: Oracle V.8.1.7 on Windows-NT REM ---------------------------------------------------------------------- SPOOL D:\Oracle\Product\8.1.7\Database\initSMA1.log CONNECT sys/manager AS SYSDBA; ### Startup database ### ---------------- STARTUP NOMOUNT pfile=D:\Oracle\Product\8.1.7\Database\initSMA1_init.ora ### Create database ### --------------- CREATE DATABASE SMA1 CONTROLFILE REUSE MAXDATAFILES 256 MAXINSTANCES 4 MAXLOGFILES 62 MAXLOGMEMBERS 5 MAXLOGHISTORY 1600 CHARACTER SET "UTF8" NATIONAL CHARACTER SET "WE8ISO8859P1" DATAFILE 'D:\Oradata\SMA1\sys\SMA1_sys1.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED LOGFILE GROUP 1 ('D:\Oradata\SMA1\rdo\SMA1_log1A.rdo', 'C:\Oradata\SMA1\rdo\SMA1_log1B.rdo') SIZE 5M REUSE, GROUP 2 ('D:\Oradata\SMA1\rdo\SMA1_log2A.rdo', 'C:\Oradata\SMA1\rdo\SMA1_log2B.rdo') SIZE 5M REUSE, GROUP 3 ('D:\Oradata\SMA1\rdo\SMA1_log3A.rdo', 'C:\Oradata\SMA1\rdo\SMA1_log3B.rdo') SIZE 5M REUSE, GROUP 4 ('D:\Oradata\SMA1\rdo\SMA1_log4A.rdo', 'C:\Oradata\SMA1\rdo\SMA1_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\SMA1\rbs\SMA1_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\SMA1\rbs\SMA1_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 1024K NEXT 1024K MINEXTENTS 20 MAXEXTENTS UNLIMITED); ALTER ROLLBACK SEGMENT rbs01 ONLINE; CREATE ROLLBACK SEGMENT rbs02 TABLESPACE rbs STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 20 MAXEXTENTS UNLIMITED); ALTER ROLLBACK SEGMENT rbs02 ONLINE; CREATE ROLLBACK SEGMENT rbs03 TABLESPACE rbs STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 20 MAXEXTENTS UNLIMITED); ALTER ROLLBACK SEGMENT rbs03 ONLINE; CREATE ROLLBACK SEGMENT rbs04 TABLESPACE rbs STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 20 MAXEXTENTS UNLIMITED); ALTER ROLLBACK SEGMENT rbs04 ONLINE; CREATE ROLLBACK SEGMENT rbs05 TABLESPACE rbs STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 20 MAXEXTENTS UNLIMITED); ALTER ROLLBACK SEGMENT rbs05 ONLINE; CREATE ROLLBACK SEGMENT rbs06 TABLESPACE rbs STORAGE (INITIAL 1024K NEXT 1024K 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\SMA1\tmp\SMA1_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\SMA1\tmp\SMA1_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\SMA1\usr\SMA1_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\SMA1\usr\SMA1_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\SMA1\tab\SMA1_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\SMA1\tab\SMA1_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\SMA1\idx\SMA1_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\SMA1\idx\SMA1_idx1.dbf' SIZE 204864K REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K PERMANENT ONLINE; ### Set security for the administrative users ### ----------------------------------------- ALTER USER sys TEMPORARY TABLESPACE temp; ALTER USER sys IDENTIFIED BY manager; ALTER USER system TEMPORARY TABLESPACE temp; ALTER USER system IDENTIFIED BY manager; ### 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.7\Rdbms\admin\catalog.sql # Parallel-Server specific views for performance queries @D:\Oracle\Product\8.1.7\Rdbms\admin\catparr.sql # Scripts for the PL/SQL procedural option @D:\Oracle\Product\8.1.7\Rdbms\admin\catproc.sql # Run all sql scripts for replication option @D:\Oracle\Product\8.1.7\Rdbms\admin\catrep.sql # Collect I/O per table (actually object) statistics by # statistical sampling @D:\Oracle\Product\8.1.7\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.7\Rdbms\admin\dbmsiotc.sql # Wrap Package which creates IOTs (Index-Only-Table) @D:\Oracle\Product\8.1.7\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.7\Rdbms\admin\dbmspool.sql # Creates the default table for storing the output # of the ANALYZE LIST CHAINED ROWS command @D:\Oracle\Product\8.1.7\Rdbms\admin\utlchain.sql # Creates the EXCEPTION table @D:\Oracle\Product\8.1.7\Rdbms\admin\utlexcpt.sql # Grant public access to all views used by TKPROF # with verbose=y option @D:\Oracle\Product\8.1.7\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.7\Rdbms\admin\utlxplan.sql # Create performance tuning views @D:\Oracle\Product\8.1.7\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.7\Rdbms\admin\catexp7.sql # Create views of oracle locks @D:\Oracle\Product\8.1.7\Rdbms\admin\catblock.sql # Print out the lock wait-for graph in a tree structured fashion @D:\Oracle\Product\8.1.7\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.7\Rdbms\admin\utlvalid.sql # PL/SQL Package of utility routines for raw datatypes @D:\Oracle\Product\8.1.7\Rdbms\admin\utlraw.sql @D:\Oracle\Product\8.1.7\Rdbms\admin\prvtrawb.plb # Contains the PL/SQL interface to the cryptographic toolkit @D:\Oracle\Product\8.1.7\Rdbms\admin\dbmsoctk.sql @D:\Oracle\Product\8.1.7\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.7\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.7\Rdbms\admin\dbmslob.sql # Procedures for instrumenting database applications # DBMS_APPLICATION_INFO package spec. @D:\Oracle\Product\8.1.7\Rdbms\admin\dbmsapin.sql ### Recompile all INVALID objects ### ----------------------------- @D:\Oracle\Product\8.1.7\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; CONNECT system/manager; CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE tab TEMPORARY TABLESPACE temp 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 scott; GRANT ALL PRIVILEGES TO scott; ALTER USER dbsnmp TEMPORARY TABLESPACE temp; DISCONNECT; CONNECT sys/manager AS SYSDBA; 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; ### Setup AUTO TRACE for SQL*PLUS ### ----------------------------- CONNECT system/manager; @D:\Oracle\Product\8.1.7\sqlplus\admin\pupbld.sql @D:\Oracle\Product\8.1.7\sqlplus\admin\plustrce.sql GRANT plustrace to scott; EXIT;