Oracle 9i データベース作成手順 スクリプト

Oracle において Oracle9i データベース作成手順 に関して示す。

以下の順番でスクリプトを実行する。

initadachidb.ora

  compatible=9.2.0.5.0
  db_block_size=8192
  undo_management=AUTO
  undo_tablespace=UNDOTBS
  db_name=adachidb
  instance_name=adachidb
  control_files=('/oradata/adachidb/CONTROL01.ctl',
  '/oradata/adachidb/CONTROL02.ctl','/oradata/adachidb/CONTROL03.ctl')
  java_pool_size=0
  large_pool_size=0
  shared_pool_size=60000000
  background_dump_dest=/oradata/adachidb/dump/bfile
  core_dump_dest=/oradata/adachidb/dump/cfile
  user_dump_dest=/oradata/adachidb/dump/ufile
  log_archive_start=true
  log_archive_dest=/oradata/adachidb/arc
  log_archive_format=arch_%t_%s.arc

01_createDB.sql

  spool /oradata/adachidb/dbca/logs/01_createDB.log

  connect / as SYSDBA
  set echo on

  startup nomount pfile="/oradata/adachidb/initadachidb.ora";

  CREATE DATABASE "adachidb"
  MAXINSTANCES 8
  MAXLOGFILES 32
  MAXLOGMEMBERS 3
  MAXDATAFILES 150
  DATAFILE '/oradata/adachidb/SYSTEM01.dbf' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL
  DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/adachidb/TEMP01.dbf' SIZE 100M REUSE
  UNDO TABLESPACE "UNDOTBS" DATAFILE '/oradata/adachidb/UNDOTBS01.dbf' SIZE 100M REUSE
  CHARACTER SET JA16EUC
  NATIONAL CHARACTER SET AL16UTF16
  LOGFILE GROUP 1 ('/oradata/adachidb/member1/REDO01.log',
  '/oradata/adachidb/member2/REDO01.log') SIZE 5120K,
  GROUP 2 ('/oradata/adachidb/member1/REDO02.log',
  '/oradata/adachidb/member2/REDO02.log') SIZE 5120K,
  GROUP 3 ('/oradata/adachidb/member1/REDO03.log',
  '/oradata/adachidb/member2/REDO03.log') SIZE 5120K
  ;

  spool off

02_createDBcatalog.sql

  spool /oradata/adachidb/dbca/logs/02_createDBcatalog.log

  connect / as SYSDBA
  set echo on

  connect / as SYSDBA
  @$ORACLE_HOME/rdbms/admin/catalog.sql
  @$ORACLE_HOME/rdbms/admin/catproc.sql

  connect system/manager
  @$ORACLE_HOME/sqlplus/admin/pupbld.sql

  spool off

03_createDBfiles.sql

  spool /oradata/adachidb/dbca/logs/03_createDBfiles.log

  connect / as SYSDBA
  set echo on

  CREATE TABLESPACE "USERS" DATAFILE '/oradata/adachidb/USERS01.dbf'
  SIZE 100M REUSE AUTOEXTEND ON NEXT 16K, '/oradata/adachidb/USERS02.dbf'
  SIZE 100M REUSE AUTOEXTEND ON NEXT 16K;
  ALTER DATABASE DEFAULT TABLESPACE "USERS";

  spool off

04_postDBcreation.sql

  spool /oradata/adachidb/dbca/logs/04_postDBcreation.log

  connect / as SYSDBA
  set echo on
  shutdown immediate;

  connect / as SYSDBA
  startup mount pfile="/oradata/adachidb/initadachidb.ora";
  alter database archivelog;
  alter database open;

  spool off

05_users_admin.sql

  spool /oradata/adachidb/dbca/logs/05_users_admin.log

  connect / as SYSDBA
  set echo on

  drop user wa_admin cascade;

  create user wa_admin
    identified by wa_admin
    quota unlimited on users
    quota unlimited on temp
    default tablespace users
    temporary tablespace temp;

  drop role wa_admin_role;

  create role wa_admin_role;

  grant create session,
        create table,
        create sequence,
        create procedure,
        create view,
        create trigger,
        create type,
        execute any procedure
  to wa_admin_role;

  grant wa_admin_role to wa_admin;

  spool off

06_tables.sql

  spool /oradata/adachidb/dbca/logs/06_tables.log

  connect wa_admin/wa_admin
  set echo on

  CREATE TABLE WA_ADMIN.DEPT
     (DEPTNO NUMBER(2,0) NOT NULL,
     DNAME VARCHAR2(14) NULL,
     LOC VARCHAR2(13) NULL,
     PRIMARY KEY (DEPTNO)
     );

  CREATE TABLE WA_ADMIN.EMP
     (EMPNO NUMBER(4,0) NOT NULL,
     ENAME VARCHAR2(10) NULL,
     JOB VARCHAR2(9) NULL,
     MGR NUMBER(4,0) NULL,
     SAL NUMBER(7,2) NULL,
     COMM NUMBER(7,2) NULL,
     DEPTNO NUMBER(2,0) NULL,
     FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
     PRIMARY KEY (EMPNO)
     );

  INSERT INTO WA_ADMIN.DEPT VALUES(11,'Sales','Texas');
  INSERT INTO WA_ADMIN.DEPT VALUES(22,'Accounting','Washington');
  INSERT INTO WA_ADMIN.DEPT VALUES(33,'Finance','Maine');

  INSERT INTO WA_ADMIN.EMP VALUES(123,'Bob','Sales',555,35000,12,11);
  INSERT INTO WA_ADMIN.EMP VALUES(321,'Sue','Finance',555,42000,12,33);
  INSERT INTO WA_ADMIN.EMP VALUES(234,'Mary','Account',555,33000,12,22);

  spool off

07_users_user.sql

  spool /oradata/adachidb/dbca/logs/07_users_user.log

  connect / as SYSDBA
  set echo on

  drop user wa_user cascade;

  create user wa_user
    identified by wa_user
    quota unlimited on users
    quota unlimited on temp
    default tablespace users
    temporary tablespace temp;

  drop role wa_user_role;

  create role wa_user_role;

  grant create session,
        create view,
        create synonym,
        execute any procedure,
        execute any type
  to wa_user_role;

  grant select, insert, update, delete
  on    wa_admin.dept
  to    wa_user_role;

  grant select, insert, update, delete
  on    wa_admin.emp
  to    wa_user_role;

  grant wa_user_role to wa_user;

  spool off

ご訪問頂き有難う御座います。 当サイトを効率良く使うためにまずは FrontPage を見て下さい。 検索方法、一覧表示などの各情報を纏めています。
当サイトの説明 → Frontpage