::::::::::::::
cr_TDB8.sql
::::::::::::::
-- This script calls other scripts to create a new database, tablespaces,
-- rollback segments, data dictionary views, setup admin users and roles
--
@@cr_db.sql
@@cr_ts.sql
@@cr_rs.sql
@@cr_dd.sql
@@cr_users.sql
@@cr_roles.sql
@@utlmontr.sql
::::::::::::::
cr_db.sql
::::::::::::::
set echo on
spool cr_db.log
connect internal
startup nomount pfile=/tools/oracle/admin/TDB8/pfile/initTDB8.ora
create database "TDB8"
maxinstances 16
maxlogfiles 32
maxdatafiles 1022
maxlogmembers 4
maxloghistory 1600
character set "US7ASCII"
datafile
'/disk1/oradata/TDB8/system01.dbf' SIZE 75M REUSE
logfile
group 1 ('/disk1/oradata/TDB8/redo01.log',
'/disk2/oradata/TDB8/redo01_m.log' ) SIZE 10M REUSE,
group 2 ('/disk1/oradata/TDB8/redo02.log',
'/disk2/oradata/TDB8/redo02_m.log' ) SIZE 10M REUSE,
group 3 ('/disk1/oradata/TDB8/redo03.log',
'/disk2/oradata/TDB8/redo03_m.log' ) SIZE 10M REUSE,
group 4 ('/disk1/oradata/TDB8/redo04.log',
'/disk2/oradata/TDB8/redo04_m.log' ) SIZE 10M REUSE
/
spool off
::::::::::::::
cr_ts.sql
::::::::::::::
set echo on
spool cr_ts.log
CREATE TABLESPACE rollback
DATAFILE '/disk1/oradata/TDB8/rollback01.dbf' SIZE 200M REUSE
DEFAULT STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 5 PCTINCREASE 0)
/
CREATE TABLESPACE temp
DATAFILE '/disk2/oradata/TDB8/temp01.dbf' SIZE 100M REUSE
DEFAULT STORAGE (PCTINCREASE 0)
TEMPORARY
/
CREATE TABLESPACE users
DATAFILE '/disk3/oradata/TDB8/users01.dbf' SIZE 1M REUSE
DEFAULT STORAGE (PCTINCREASE 0)
/
CREATE TABLESPACE data_01
DATAFILE '/disk1/oradata/TDB8/data01.dbf' SIZE 200M REUSE
DEFAULT STORAGE (PCTINCREASE 0)
/
CREATE TABLESPACE indx_01
DATAFILE '/disk2/oradata/TDB8/indx01.dbf' SIZE 200M REUSE
DEFAULT STORAGE (PCTINCREASE 0)
/
spool off
::::::::::::::
cr_rs.sql
::::::::::::::
set echo on
spool cr_rs.log
CREATE ROLLBACK SEGMENT r01 TABLESPACE rollback
STORAGE (OPTIMAL 10M)
/
CREATE ROLLBACK SEGMENT r02 TABLESPACE rollback
STORAGE (OPTIMAL 10M)
/
CREATE ROLLBACK SEGMENT r03 TABLESPACE rollback
STORAGE (OPTIMAL 10M)
/
CREATE ROLLBACK SEGMENT r04 TABLESPACE rollback
STORAGE (OPTIMAL 10M)
/
ALTER ROLLBACK SEGMENT r01 ONLINE
/
ALTER ROLLBACK SEGMENT r02 ONLINE
/
ALTER ROLLBACK SEGMENT r03 ONLINE
/
ALTER ROLLBACK SEGMENT r04 ONLINE
/
spool off
::::::::::::::
cr_dd.sql
::::::::::::::
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/catparr.sql
@$ORACLE_HOME/rdbms/admin/catblock.sql
@$ORACLE_HOME/rdbms/admin/catrep.sql
connect system/manager
@$ORACLE_HOME/sqlplus/admin/pupbld.sql
CREATE SYNONYM dba_kgllock FOR sys.dba_kgllock
/
CREATE SYNONYM dba_locks FOR sys.dba_locks
/
CREATE SYNONYM dba_lock FOR sys.dba_lock
/
CREATE SYNONYM dba_lock_internal FOR sys.dba_lock_internal
/
CREATE SYNONYM dba_dml_locks FOR sys.dba_dml_locks
/
CREATE SYNONYM dba_ddl_locks FOR sys.dba_ddl_locks
/
CREATE SYNONYM dba_waiters FOR sys.dba_waiters
/
CREATE SYNONYM dba_blockers FOR sys.dba_blockers
/
::::::::::::::
cr_users.sql
::::::::::::::
-- create user OPS$ORACLE and grant appropriate privileges
CREATE USER ops$oracle IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
/
GRANT analyze any,
alter database,
alter tablespace,
create session,
create synonym,
exp_full_database,
restricted session
TO ops$oracle
/
-- alter DBSNMP's privileges
GRANT create session TO dbsnmp
/
REVOKE connect, resource FROM dbsnmp
/
ALTER USER dbsnmp DEFAULT TABLESPACE users
/
-- alter user temporary tablespace
ALTER USER SYS TEMPORARY TABLESPACE temp
/
ALTER USER SYSTEM TEMPORARY TABLESPACE temp
/
ALTER USER OUTLN TEMPORARY TABLESPACE temp
/
ALTER USER DBSNMP TEMPORARY TABLESPACE temp
/
ALTER USER TRACESVR TEMPORARY TABLESPACE temp
/
ALTER USER AURORA$ORB$UNAUTHENTICATED TEMPORARY TABLESPACE temp
/
::::::::::::::
cr_roles.sql
::::::::::::::
CREATE ROLE product_owner
/
GRANT CREATE PROCEDURE TO product_owner
/
GRANT CREATE PUBLIC DATABASE LINK TO product_owner
/
GRANT CREATE DATABASE LINK TO product_owner
/
GRANT CREATE SYNONYM TO product_owner
/
GRANT CREATE PUBLIC SYNONYM TO product_owner
/
GRANT CREATE ROLE TO product_owner
/
GRANT CREATE SNAPSHOT TO product_owner
/
GRANT CREATE TABLE TO product_owner
/
GRANT CREATE VIEW TO product_owner
/
GRANT CREATE SEQUENCE TO product_owner
/
GRANT DROP PUBLIC SYNONYM TO product_owner
/
GRANT DROP PUBLIC DATABASE LINK TO product_owner
/
GRANT CREATE TRIGGER TO product_owner
/
GRANT CREATE CLUSTER TO product_owner
/