db creation scripts, our example to create databases

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Wed, 18 Feb 2004 09:55:30 -0400
Message-ID: <009f01c3f626$df1855d0$2501a8c0@dazasoftware.com>

• IMPORTANTE IMPORTANTE
• en tnsnames conexión debe estar configurada como dedicada, ver formulario instalacion oracle 9i
• fin importante SET ECHO ON connect SYS/change_on_install_at_xxx as SYSDBA; shutdown immediate; connect SYS/change_on_install_at_xxx as SYSDBA; startup nomount pfile=E:\oraxxx\init\initxxx.ora;

SPOOL D:\ORAxxx\CREDATA.LOG;

• PCTFREE 5 STORAGE ( INITIAL 64K NEXT 64K PCTINCREASE 0 MAXEXTENTS UNLIMITED );

CREATE DATABASE xxx
CONTROLFILE REUSE
LOGFILE

GROUP 1 ('D:\oraxxx\logfiles\log_1xxx.ora',
'E:\oraxxx\logfiles\log_1xxx.ora') SIZE 10M,
GROUP 2 ('D:\oraxxx\logfiles\log_2xxx.ora',
'E:\oraxxx\logfiles\log_2xxx.ora') SIZE 10M

MAXLOGFILES 32
DATAFILE 'E:\oraxxx\datafiles\dfl_sys_xxx'

   size 200M
   REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED    EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TBL_TEMP TEMPFILE 'D:\oraxxx\datafiles\DFL_TEMP_xxx'

   SIZE 50M
   REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 640K UNDO TABLESPACE TBL_UNDO DATAFILE 'E:\oraxxx\datafiles\dfl_undo_xxx'

   SIZE 50M
   REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED MAXLOGHISTORY 1
MAXLOGFILES 3
MAXLOGMEMBERS 3
MAXDATAFILES 15
MAXINSTANCES 1
ARCHIVELOG
EXCLUSIVE
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
;

• Tablespace usuario CREATE TABLESPACE TBL_USERS DATAFILE 'E:\oraxxx\datafiles\DFL_USER_xxx' SIZE 100M REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K ; CREATE TABLESPACE TBL_INDX DATAFILE 'D:\oraxxx\datafiles\DFL_INDX_xxx' SIZE 100M REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K ; CREATE TABLESPACE TBL_OVERFLOW DATAFILE 'D:\oraxxx\datafiles\DFL_OVERFLOW_xxx' SIZE 1M REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K ; CREATE TABLESPACE TBL_BLOB DATAFILE 'E:\oraxxx\datafiles\DFL_LOB_xxx' size 1M REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED ; CREATE TABLESPACE TBL_STATPACK DATAFILE 'E:\oraxxx\datafiles\DFL_STATPACK_xxx' SIZE 100M REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K ;
• Tablespace Read-Only CREATE TABLESPACE TBL_readonly DATAFILE 'e:\OraxxxReadOnly\DFL_READONLY_xxx' SIZE 100M REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K ; CREATE TABLESPACE TBL_readonly_IDX DATAFILE 'd:\OraxxxReadOnly\DFL_READONLY_IDX_xxx' SIZE 100M REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K ; ALTER TABLESPACE "TBL_READONLY" READ ONLY; ALTER TABLESPACE TBL_READONLY_idx READ ONLY;
• data files para paquetes adicionales CREATE TABLESPACE TBL_DRSYS DATAFILE 'D:\oraxxx\datafiles\DFL_DRSYS_xxx' SIZE 2M REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K ; CREATE TABLESPACE TBL_XDB DATAFILE 'D:\oraxxx\datafiles\DFL_XDB_xxx' SIZE 2M REUSE AUTOEXTEND ON NEXT 640k MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K ;

set echo off
@C:\oracle\ora92\rdbms\admin\catalog.sql;
alter system archive log all;
@C:\oracle\ora92\rdbms\admin\catblock.sql;
alter system archive log all;
@C:\oracle\ora92\rdbms\admin\catproc.sql;
@c:\oracle\ora92\rdbms\admin\catoctk.sql;
@c:\oracle\ora92\rdbms\admin\owminst.plb;

connect SYSTEM/manager_at_xxx;
SPOOL D:\ORAxxx\CREDATA2.LOG;
@C:\oracle\ora92\sqlplus\admin\pupbld.sql;
@C:\oracle\ora92\sqlplus\admin\help\hlpbld.sql helpus.sql;
connect SYS/change_on_install_at_xxx as SYSDBA;

alter system flush shared_pool;
connect SYS/change_on_install_at_xxx as SYSDBA; SPOOL D:\ORAxxx\CREDATA3.LOG;
@c:\oracle\ora92\javavm\install\initjvm.sql;
@c:\oracle\ora92\xdk\admin\initxml.sql;
@c:\oracle\ora92\xdk\admin\xmlja.sql;
@c:\oracle\ora92\rdbms\admin\catjava.sql;

alter system flush shared_pool;
connect SYS/change_on_install_at_xxx as SYSDBA; SPOOL D:\ORAxxx\CREDATA4.LOG;
@c:\oracle\ora92\ord\admin\ordinst.sql;

connect SYS/change_on_install_at_xxx as SYSDBA; SPOOL D:\ORAxxx\CREDATA5.LOG;
@c:\oracle\ora92\ord\im\admin\iminst.sql;
alter system archive log all;

spool c:\oracle\ora92\assistants\dbca\logs\context.log connect SYS/change_on_install_at_xxx as SYSDBA; SPOOL D:\ORAxxx\CREDATA6.LOG;
@c:\oracle\ora92\ctx\admin\dr0csys change_on_install TBL_DRSYS TBL_TEMP;
connect CTXSYS/change_on_install_at_xxx;
SPOOL D:\ORAxxx\CREDATA7.LOG;
@c:\oracle\ora92\ctx\admin\dr0inst c:\oracle\ora92\bin\oractxx9.dll;
@c:\oracle\ora92\ctx\admin\defaults\dr0defin.sql ENGLISH;
spool off

connect SYS/change_on_install_at_xxx as SYSDBA; alter system flush shared_pool;
SPOOL D:\ORAxxx\CREDATA8.LOG;
@c:\oracle\ora92\rdbms\admin\catqm.sql change_on_install TBL_XDB TBL_TEMP;
connect SYS/change_on_install_at_xxx as SYSDBA; SPOOL D:\ORAxxx\CREDATA9.LOG;
@c:\oracle\ora92\rdbms\admin\catxdbj.sql;

alter system flush shared_pool;
connect SYS/change_on_install_at_xxx as SYSDBA; SPOOL D:\ORAxxx\CREDATA10.LOG;
@C:\oracle\ora92\rdbms\admin\utlexpt1.sql;
@C:\oracle\ora92\rdbms\admin\utldtree.sql;
@C:\oracle\ora92\rdbms\admin\dbmspool.sql;
alter system archive log all;

connect SYSTEM/manager_at_xxx;
SPOOL D:\ORAxxx\CREDATA11.LOG;

• Para Menú Developer 6 create or replace view FRM50_ENABLED_ROLES as select urp.granted_role role, sum(distinct decode(rrp.granted_role, 'ORAFORMS$OSC',2, 'ORAFORMS$BGM',4, 'ORAFORMS$DBG',1,0)) flag from sys.user_role_privs urp, role_role_privs rrp where urp.granted_role = rrp.role (+) and urp.granted_role not like 'ORAFORMS$%' group by urp.granted_role; create public synonym FRM50_ENABLED_ROLES for system.FRM50_ENABLED_ROLES; create role ORAFORMS$OSC; create role ORAFORMS$DBG; create role ORAFORMS$BGM;
• Fin Para Menú Developer 6

set echo on

connect SYS/change_on_install_at_xxx as SYSDBA; shutdown normal;
startup pfile=E:\oraxxx\init\initxxx.ora;

• ************************
• ************************ update global_name set global_name='xxx';
• ************************

ALTER USER SYS

 QUOTA UNLIMITED ON TBL_USERS    QUOTA UNLIMITED ON TBL_INDX
 QUOTA UNLIMITED ON TBL_HI_USERS QUOTA UNLIMITED ON TBL_HI_USERS
 QUOTA UNLIMITED ON TBL_HI_INDX  QUOTA UNLIMITED ON TBL_HI_INDX
 QUOTA UNLIMITED ON TBL_TEMP     QUOTA UNLIMITED ON TBL_OVERFLOW
 QUOTA UNLIMITED ON TBL_BLOB     QUOTA UNLIMITED ON TBL_BLOB
 QUOTA UNLIMITED ON TBL_TEMP     QUOTA UNLIMITED ON TBL_OVERFLOW

 QUOTA UNLIMITED ON SYSTEM
/
ALTER USER SYSTEM

 QUOTA UNLIMITED ON TBL_USERS    QUOTA UNLIMITED ON TBL_INDX
 QUOTA UNLIMITED ON TBL_HI_USERS QUOTA UNLIMITED ON TBL_HI_USERS
 QUOTA UNLIMITED ON TBL_HI_INDX  QUOTA UNLIMITED ON TBL_HI_INDX
 QUOTA UNLIMITED ON TBL_TEMP     QUOTA UNLIMITED ON TBL_OVERFLOW
 QUOTA UNLIMITED ON TBL_BLOB     QUOTA UNLIMITED ON TBL_BLOB
 QUOTA UNLIMITED ON TBL_TEMP     QUOTA UNLIMITED ON TBL_OVERFLOW

 QUOTA UNLIMITED ON SYSTEM
/

connect SYS/change_on_install_at_xxx as SYSDBA; SPOOL D:\ORAxxx\CREDATA12.LOG;
shutdown normal;
startup pfile=E:\oraxxx\init\initxxx.ora;
@c:\oracle\ora92\rdbms\admin\utlrp.sql;

---

connect SYSTEM/manager_at_xxx;
SPOOL D:\ORAxxx\CREDATA13.LOG;

• Para Menú Developer 6 create or replace view FRM50_ENABLED_ROLES as select urp.granted_role role, sum(distinct decode(rrp.granted_role, 'ORAFORMS$OSC',2, 'ORAFORMS$BGM',4, 'ORAFORMS$DBG',1,0)) flag from sys.user_role_privs urp, role_role_privs rrp where urp.granted_role = rrp.role (+) and urp.granted_role not like 'ORAFORMS$%' group by urp.granted_role; create public synonym FRM50_ENABLED_ROLES for system.FRM50_ENABLED_ROLES; create role ORAFORMS$OSC; create role ORAFORMS$DBG; create role ORAFORMS$BGM;
• Fin Para Menú Developer 6

set echo on

connect SYS/change_on_install_at_xxx as SYSDBA; SPOOL D:\ORAxxx\CREDATA14.LOG;
shutdown normal;
startup pfile=E:\oraxxx\init\initxxx.ora;

• ************************ GRANT SELECT ON V_$SESSION TO PUBLIC WITH GRANT OPTION; GRANT SELECT ON V_$SESSION TO PUBLIC;
• ************************
• ************************ update global_name set global_name='xxx';
• ************************

ALTER USER SYS

 QUOTA UNLIMITED ON TBL_USERS    QUOTA UNLIMITED ON TBL_INDX
 QUOTA UNLIMITED ON TBL_HI_USERS QUOTA UNLIMITED ON TBL_HI_USERS
 QUOTA UNLIMITED ON TBL_HI_INDX  QUOTA UNLIMITED ON TBL_HI_INDX
 QUOTA UNLIMITED ON TBL_TEMP     QUOTA UNLIMITED ON TBL_OVERFLOW
 QUOTA UNLIMITED ON TBL_BLOB     QUOTA UNLIMITED ON TBL_BLOB
 QUOTA UNLIMITED ON TBL_TEMP     QUOTA UNLIMITED ON TBL_OVERFLOW

 QUOTA UNLIMITED ON SYSTEM
/
ALTER USER SYSTEM

 QUOTA UNLIMITED ON TBL_USERS    QUOTA UNLIMITED ON TBL_INDX
 QUOTA UNLIMITED ON TBL_HI_USERS QUOTA UNLIMITED ON TBL_HI_USERS
 QUOTA UNLIMITED ON TBL_HI_INDX  QUOTA UNLIMITED ON TBL_HI_INDX
 QUOTA UNLIMITED ON TBL_TEMP     QUOTA UNLIMITED ON TBL_OVERFLOW
 QUOTA UNLIMITED ON TBL_BLOB     QUOTA UNLIMITED ON TBL_BLOB
 QUOTA UNLIMITED ON TBL_TEMP     QUOTA UNLIMITED ON TBL_OVERFLOW

 QUOTA UNLIMITED ON SYSTEM
/

connect SYS/change_on_install_at_xxx as SYSDBA; SPOOL D:\ORAxxx\CREDATA15.LOG;
shutdown normal;
startup pfile=E:\oraxxx\init\initxxx.ora;

Hola aqui tienes que salir
Cambiar el LOCAL de REGEDIT/ORACLE/hOME0

• Inicio Instalacion perfstat SET INSTANCE xxx;
  @c:\oracle\ora92\rdbms\admin\spcreate.sql
  
• password PERFSTAT
• default tablespace TBL_STATPACK
• temporary tablespace TBL_TEMP
• Fin Instalacion perfstat

@c:\oracle\ora92\rdbms\admin\utlrp.sql;

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Feb 18 2004 - 07:55:30 CST