Create/Drop Database

Create a Database

建立 createDB.sql：

• 修改 SYS 密碼
• 修改檔案路徑
• 修改 redolog size

CREATE DATABASE win
   USER SYS IDENTIFIED BY mypass
   USER SYSTEM IDENTIFIED BY mypass
   LOGFILE GROUP 1 ('/opt/oracle/oradata/win/redo01.log') SIZE 100M,
           GROUP 2 ('/opt/oracle/oradata/win/redo02.log') SIZE 100M,
           GROUP 3 ('/opt/oracle/oradata/win/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   MAXINSTANCES 1
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/opt/oracle/oradata/win/system01.dbf' SIZE 500M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/opt/oracle/oradata/win/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/opt/oracle/oradata/win/temp01.dbf' 
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE 2048M
   UNDO TABLESPACE undotbs1 
      DATAFILE '/opt/oracle/oradata/win/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

TIPs:

redo LOG 建議可用 2G x 4

開始建立

確認環境變數
$ echo $ORACLE_SID
win
$ echo $ORACLE_HOME
/opt/oracle/product/10.2.0/db_1

$ sqlplus /nolog

SQL> connect / as sysdba
SQL> startup nomount
SQL> @createDB.sql

DB 建立完後，還需要執行幾個 SQL

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql

SQL> conn system/pass
SQL> @?/sqlplus/admin/pupbld.sql  

PUPBLD.SQL       Create user profiles，必須用 SYSTEM 執行，執行過程中會出現一些錯誤是正常的；如果沒有執行，一般用戶會無法連線 DB。

更多其他的 SQL 可參閱 http://docs.oracle.com/cd/B14117_01/...s.htm#REFRN005

 

Drop a Database：

$ export ORACLE_SID=<my_SID>
$ sqlplus / as sysdba

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive restrict
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  2095672 bytes
Variable Size             109053384 bytes
Database Buffers          163577856 bytes
Redo Buffers                6291456 bytes
Database mounted.

SQL> drop database;

Database dropped.