Oracle 資料庫創建三大方式深度對比：DBCA、命令行、腳本化自動化

建庫是 DBA 核心技能，三種方式各有優劣。本文從原理、適用場景、優缺點、實戰代碼四個維度深度對比，助你按需選型。

方式對比總覽

方式一：DBCA (Database Configuration Assistant)

# 圖形界面
dbca

# 靜默模式 (腳本化調用)
dbca -silent -createDatabase \
  -templateName General_Purpose.dbc \
  -gdbName mydb.example.com \
  -sid MYDB \
  -characterSet AL32UTF8 \
  -sysPassword SysPass123 \
  -systemPassword SysPass123 \
  -createListener LISTENER:1521

優點：自動處理字符集、紅日誌、表空間、EM 配置等細節。
缺點：交互慢；靜默模式參數極多需查文檔。

方式二：命令行手工建庫 (核心 SQL 流程)

-- 1. 準備參數文件 initMYDB.ora
db_name=MYDB
memory_target=2G
control_files=('/u01/oradata/MYDB/control01.ctl','/u02/oradata/MYDB/control02.ctl')
undo_tablespace=UNDOTBS1

-- 2. 啟動 NOMOUNT
SQLPLUS / AS SYSDBA
STARTUP NOMOUNT PFILE='/u01/app/oracle/admin/MYDB/pfile/initMYDB.ora';

-- 3. CREATE DATABASE
CREATE DATABASE MYDB
  USER SYS IDENTIFIED BY SysPass123
  USER SYSTEM IDENTIFIED BY SysPass123
  LOGFILE GROUP 1 ('/u01/oradata/MYDB/redo01a.log','/u02/oradata/MYDB/redo01b.log') SIZE 200M,
          GROUP 2 ('/u01/oradata/MYDB/redo02a.log','/u02/oradata/MYDB/redo02b.log') SIZE 200M
  MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXLOGHISTORY 1
  DATAFILE '/u01/oradata/MYDB/system01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/oradata/MYDB/sysaux01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
  DEFAULT TABLESPACE USERS DATAFILE '/u01/oradata/MYDB/users01.dbf' SIZE 200M REUSE AUTOEXTEND ON
  DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/MYDB/temp01.dbf' SIZE 100M REUSE
  UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/oradata/MYDB/undotbs01.dbf' SIZE 200M REUSE
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16;

-- 4. 打開並建立標準視圖
ALTER DATABASE OPEN;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql

方式三：腳本化自動化 (Windows 批處理 + SQL 模板)

主控批處理：create_MYDB.bat

@ECHO OFF
SET ORACLE_SID=MYDB
SET ORACLE_HOME=D:\app\oracle\product\19.0.0\dbhome_1
SET ADMIN_DIR=%ORACLE_HOME%\admin\MYDB

REM 1. 創建實例服務
%ORACLE_HOME%\bin\oradim -NEW -SID MYDB -INTPWD SysPass123 -STARTMODE MANUAL -PFILE %ADMIN_DIR%\pfile\init.ora

REM 2. 執行建庫 SQL
%ORACLE_HOME%\bin\sqlplus /NOLOG @%ADMIN_DIR%\scripts\create_db.sql

REM 3. 執行後續配置 SQL
%ORACLE_HOME%\bin\sqlplus /NOLOG @%ADMIN_DIR%\scripts\post_create.sql

REM 4. 設為自啟動
%ORACLE_HOME%\bin\oradim -EDIT -SID MYDB -STARTMODE AUTO
ECHO Database MYDB created successfully.

建庫 SQL：create_db.sql

SPOOL %ADMIN_DIR%\create_db.log
SET ECHO ON
CONNECT / AS SYSDBA
STARTUP NOMOUNT PFILE='%ADMIN_DIR%\pfile\init.ora'
CREATE DATABASE MYDB
  LOGFILE GROUP 1 ('%ORACLE_BASE%\oradata\MYDB\redo01.log') SIZE 100M,
          GROUP 2 ('%ORACLE_BASE%\oradata\MYDB\redo02.log') SIZE 100M
  DATAFILE '%ORACLE_BASE%\oradata\MYDB\system01.dbf' SIZE 500M REUSE
  CHARACTER SET AL32UTF8;
SPOOL OFF

後續配置：post_create.sql

SPOOL %ADMIN_DIR%\post_create.log
SET ECHO ON
CONNECT / AS SYSDBA
ALTER DATABASE DATAFILE '%ORACLE_BASE%\oradata\MYDB\system01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE TABLESPACE USERS DATAFILE '%ORACLE_BASE%\oradata\MYDB\users01.dbf' SIZE 200M AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '%ORACLE_BASE%\oradata\MYDB\temp01.dbf' SIZE 100M;
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '%ORACLE_BASE%\oradata\MYDB\undotbs01.dbf' SIZE 200M;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
SPOOL OFF

Linux 平台操作差異

# 環境變量
export ORACLE_SID=MYDB
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

# 啟動順序
su - oracle
sqlplus /nolog
CONN / AS SYSDBA
STARTUP

# 監聽器
lsnrctl start

# 關閉
lsnrctl stop
sqlplus /nolog
CONN / AS SYSDBA
SHUTDOWN IMMEDIATE

刪除資料庫 (慎用！)

SPOOL drop_db.bat
SELECT 'DEL ' || NAME FROM V$DATAFILE;
SELECT 'DEL ' || MEMBER FROM V$LOGFILE;
SPOOL OFF
-- 執行生成的 bat，再刪除控制文件、參數文件、密碼文件、實例服務