create script Oracle

SQL 4DBA's Creation scripts

Create Database (Oracle on Unix and Windows )

Read the manual
Decide where you want to put the datafiles, controlfiles and redolog files
Decide on the character set: the 7bit US7ASCII is more limited then the 8bit WE8DEC set which can handle European characters like € (Euro) and Ü (Umlaut).

Some Gotchas to be aware of.

Create a an OFA-compliant directory structure for every database, so for the TEST database use:
- /disk1/oradata/TEST/
- /disk2/oradata/TEST/
- /disk3/oradata/TEST/ ... etc.
for the PROD database use:
- /disk1/oradata/PROD/
- /disk2/oradata/PROD/
- /disk3/oradata/PROD/ ... etc.
Make sure all directories are empty (or specify "reuse").
Check the initSID.ora file (in %ORACLE_HOME%\database on WNT, $ORACLE_HOME/dbs on Unix).

Create database on Windows.

Advice: use the "Database Creation Assistant" (dbca), make sure you keep the create scripts. If you don't like the result just throw the datafiles and run the create script manually.

Set the 'control_files' path and 'db_name' in the initSID.orafile.
If it is a new instance, use oradim to define it in the WNT registry
oradim -NEW -SID [sid] -INTPWD [password] -STARTMODE AUTO
This creates the services named OracleService[sid] and OracleStart[sid] with the instance [sid] and a password for SYS.
In the registry HKEY_LOCAL_MACHINE- SOFTWARE- ORACLE- a few things are now defined:
- ORACLE_HOME - The path to the Oracle root.
- ORA_[sid]_PFILE - The path to your init[sid].ora file
- DATABASE_STARTUP (auto)
- SQLPATH - Where SQLPlus looks for .sql scripts.
- Check the language_country.codepage NLS_LANG (AMERICAN_AMERICA.WE8ISO8859P1)
Check if the Oracle services are started (Control Panel, services )

Then open a command window and...

set ORACLE_SID=[sid]
start sqlplus: $ sqlplus /nolog
SQL> connect sys/change_on_install as SYSDBA
SQL> startup nomount
SQL> @create_db.sql


rem   Name: create_db.sql 
rem   Desc: create db on WNT. Run as user "SYS" ( /nolog )
rem   Note: this create script is suitable for Oracle9i

create database DEMO 
controlfile reuse 
    maxlogfiles 32 
    maxlogmembers 2 
    maxdatafiles 200 
    maxinstances 16 
    maxloghistory 1815 
logfile 
  group 1 'D:\ORACLE\ORADATA\DEMO\REDO03.LOG'  size 1M, 
  group 2 'D:\ORACLE\ORADATA\DEMO\REDO02.LOG'  size 1M, 
  group 3 'D:\ORACLE\ORADATA\DEMO\REDO01.LOG'  size 1M 
datafile 'D:\ORACLE\ORADATA\DEMO\SYSTEM01.DBF' size 300M REUSE 
extent management local 
undo tablespace UNDOTBS1 
    datafile 'D:\ORACLE\ORADATA\DEMO\UNDOTBS01.DBF' size 100M 
NOARCHIVELOG 
CHARACTER SET WE8ISO8859P1 
; 

rem   Create DBA views:
@%oracle_home%  catalog.sql
@%oracle_home%  catproc.sql
spool off

Create Oracle9 database on UNIX.

You should set the ORACLE_SID :
export ORACLE_SID=[name] (where [name]=SID of your new db e.g. PROD,DEMO )

Edit the file initSID.ora in $ORACLE_HOME/database. Edit the 'control_files' and 'db_name'.

This script needs Undo information in the initSID.ora file:

undo_management           = auto 
undo_tablespace           = UNDO1 
undo_suppress_errors      = true 
undo_retention            = 3600  # seconds

Start sqlplus : $ sqlplus "/ as sysdba"
SQL> @create_db.sql

rem   Name: cre_demo.sql 
rem   Desc: create db on Unix. Run as user SYS ("/ as sysdba")
rem   Note: this create script is suitable for Oracle9i
rem
spool cre_demo.log
startup nomount pfile=/oracle/admin/demo/pfile/initdemo.ora
whenever sqlerror exit

create database       demo
        maxlogfiles   32
        maxlogmembers 5
        maxdatafiles  255
        maxloghistory 500
character set  WE8ISO8859P15
datafile  '/oracle/data/demo/ts01/system01.dbf' size 200M  REUSE
        autoextend ON next 50M maxsize 500M 
        extent management local
default temporary tablespace temp
        tempfile  '/oracle/data/demo/ts02/temp01.dbf' size 50M
        autoextend on next 50M maxsize 8000M
        extent management local uniform size 1M
undo tablespace UNDO1
     datafile '/oracle/data/demo/ts01/undo1.dbf' size 100M 
controlfile reuse 
logfile
  group   1 '/oracle/data/demo/rd01/redo01a.rdo' size 10m
, group   2 '/oracle/data/demo/rd02/redo02a.rdo' size 10m
, group   3 '/oracle/data/demo/rd03/redo03a.rdo' size 10m
noarchivelog
;
spool off

whenever sqlerror continue
spool cre_demo2.log
@$ORACLE_HOME/rdbms/admin/catalog
@$ORACLE_HOME/rdbms/admin/catproc

connect system/manager
@$ORACLE_HOME/sqlplus/admin/pupbld
spool off

Create BIG database database on UNIX.

You should set the ORACLE_SID :
export ORACLE_SID=[name] (where [name]=SID of your new db e.g. PROD,DEMO )

Edit the file initSID.ora in $ORACLE_HOME/database. Edit the 'control_files' and 'db_name'.

This script needs Undo information in the initSID.ora file:

undo_management           = auto 
undo_tablespace           = UNDO1 
undo_suppress_errors      = true 
undo_retention            = 7200  # seconds

Start sqlplus : $ sqlplus "/ as sysdba"
SQL> @cre_big.sql

rem   Name: cre_big.sql 
rem   Desc: create BIG db on Unix. Run as user SYS ("/ as sysdba")
rem   Note: this create script is suitable for Oracle9i
rem
spool cre_big.log
startup nomount pfile=/oracle/admin/bigp/pfile/initbigp.ora
whenever sqlerror exit

create database       BIGP
        maxlogfiles   32
        maxlogmembers 5
        maxdatafiles  255
        maxloghistory 500
character set WE8ISO8859P15  -- (or WE8DEC or US7ASCII )
datafile  '/oracle/data/bigp/ts01/system01.dbf' size 300M  REUSE
        autoextend ON next 50M maxsize 1000M 
        extent management local
default temporary tablespace temp
        tempfile  '/oracle/data/bigp/ts02/temp01.dbf' size 200M
        autoextend on next 50M maxsize 8000M
        extent management local uniform size 10M
undo tablespace UNDO1
     datafile '/oracle/data/bigp/ts01/undo1.dbf' size 100M 
controlfile reuse
logfile
  group  1 ( '/oracle/data/bigp/rd01/redo01a.rdo'
            ,'/oracle/data/bigp/rd02/redo01b.rdo' ) size 100M
, group  2 ( '/oracle/data/bigp/rd02/redo02a.rdo'
           , '/oracle/data/bigp/rd01/redo02b.rdo' ) size 100M
, group  3 ( '/oracle/data/bigp/rd03/redo03a.rdo'
           , '/oracle/data/bigp/rd02/redo03b.rdo' ) size 100M
noarchivelog
;
spool off

whenever sqlerror continue
spool cre_big2.log
@$ORACLE_HOME/rdbms/admin/catalog
@$ORACLE_HOME/rdbms/admin/catproc

connect system/manager
@$ORACLE_HOME/sqlplus/admin/pupbld
spool off

The following scripts should be run as user SYS :

@$ORACLE_HOME/rdbms/admin/catalog.sql -creates dictionary views(DBA_*, ALL_* and USER_* views)
@$ORACLE_HOME/rdbms/admin/catproc.sql -creates the procedural option packages (PL/SQL)

The following scripts should be run as user SYSTEM :

@$ORACLE_HOME/sqlplus/admin/pupbld.sql -creates the *product_profile tables
@$ORACLE_HOME/rdbms/admin/catdbsyn.sql -creates private synonyms for DBA views

Optional scripts

To create the Dbms_shared_pool package (optional) run these scripts as user SYS :

@$ORACLE_HOME/rdbms/admin/dbmspool.sql
@$ORACLE_HOME/rdbms/admin/prvtpool.plb

Problems with export or import? (RE)Create the export/import views with:

@$ORACLE_HOME/rdbms/admin/catexp.sql

rem   CREATE USER  
rem   Desc: Create new user with create session priv.
rem
prompt "Create new user ..."
create user &&Username identified by &Password
default tablespace USERS temporary tablespace TEMP ;
Grant create session to &&USERNAME ;


rem   CREATE OWNER Create new owner
prompt "Create new owner ..."

create user &&Username identified by &Password
default tablespace &&default_tablespace  temporary tablespace TEMP ;

Alter user &&Username quota unlimited  -- ( or 1000M )
        on &&default_tablespace ;

Grant create session   to &&USERNAME ;
Grant create table     to &&USERNAME ;
Grant create view      to &&USERNAME ;
Grant create trigger   to &&USERNAME ;
Grant create procedure to &&USERNAME ;
Grant create sequence  to &&USERNAME ;
grant create synonym   to &&USERNAME ;
Grant create public synonym to &&USERNAME ;
Grant drop   public synonym to &&USERNAME ;

undefine default_tablespace
undefine Username



rem  COPY_USER - copy one user and correct system,role privs and quota's
rem   
set heading off feed off verify off wrap ON

select 'create user '||username||' identified by values '''||password||'''
  default tablespace '||default_tablespace||'
  temporary tablespace TEMP ;'
from   sys.DBA_USERS
where  username like upper('&&owner%')

spool C:\temp\&&owner..sql   
/
select 'grant '||PRIVILEGE||' to '||grantee||';'
from   sys.DBA_SYS_PRIVS
where  grantee like upper('&&owner%')
/
select 'grant '||GRANTED_ROLE||' to '||grantee||';'
from   sys.DBA_ROLE_PRIVS
where  grantee like upper('&&owner%')
/
select 'grant '||PRIVILEGE||' on '||TABLE_NAME||' to '||GRANTEE
     ||decode(GRANTABLE,'YES','with admin option ;',';')
from   sys.dba_tab_privs
where  grantee like upper('&&owner%')
/
select 'alter user '||username||' quota '||BYTES||' on '||TABLESPACE_NAME||';'
from   sys.DBA_TS_QUOTAS
where  username like upper('&&owner%')
and    bytes    > 0
/
spool off
prompt Create script is C:\temp\&&owner..sql   
set heading ON feed ON
undefine owner


rem   COPY GRANT This script generates a "grant.tmp" script
rem   which can be used to create identical grants in another database.
rem   Parameters: tablename. Press return for all tables.

set heading off feed off wrap ON
set linesize 80 pages 0
define table=&tablename

select 'connect '||grantor||'/passwd' GR
      ,'grant '||PRIVILEGE||' on '||TABLE_NAME||' to '||GRANTEE
       ||decode(GRANTABLE,'YES','with admin option ;',';')
from   sys.DBA_tab_privs
where  table_name like upper('&table%')
order by grantor

spool grant.tmp
/
spool off
set feed ON heading ON
prompt
prompt Change the passwd and run script grant.tmp


rem   CREATE PUBLIC SYNONYMS Creates PUBLIC synonym script for all tables of 1 owner.
rem   Uses DBA_table table.

set heading off feed off
select
  'create public synonym '||table_name||' for '||owner||'.'||table_name||';'
from   sys.DBA_tables
where  owner like upper('&Owner')
 
spool syn.tmp
/
spool off
set feed ON heading ON
prompt
prompt Run syn.tmp


rem   User SYNONYMS Creates synonym script for all tables of 1 owner.
rem   Uses User_synonyms table.

set heading off feed off wrap ON trimspool ON
select 'create synonym '||synonym_name||' 
  for '||table_owner||'.'||table_name||' ;'
from   user_synonyms
order by table_owner,table_name
 
spool syn.tmp
/
spool off
set feed ON heading ON
prompt
prompt Run syn.tmp

SQL 4DBA's Creation scripts