自動目錄
工作的需要接觸了oracle db，從小聽到大第一次碰還真是非常陌生。
感覺不是很親善的資料庫，概念和我們熟知的 database/table 架構有類似也有差異。
docker 安裝 oracle
OS CentOS Linux release 7.9.2009 (Core)
docker compose 內容：
image: gvenzl/oracle-xe:21-slim
container_name: oracle
ports:
- "1521:1521"
- "8080:8080" # 若開啟 APEX
environment:
- ORACLE_PASSWORD=root.pwd #<system的密碼>
- APP_USER=user #<帳號>
- APP_USER_PASSWORD=your.pwd #<密碼>
- ORACLE_CHARACTERSET=AL32UTF8
- NLS_LANG=TRADITIONAL CHINESE_TAIWAN.AL32UTF8
volumes:
- ./oracle/data:/opt/oracle/oradata
綠色的兩行是為了處理中文字變成問號或是亂碼的問題。
手動建立檔案存放目錄 ./oracle/data
# mkdir -p ./oracle/data
給予此目錄權限和使用者id 54321 是指定的
# chown -R 54321:54321 ./oracle/data/
# chmod -R 755 ./oracle/data/
啟動
# docker compose up -d oracle
安裝 agent
到oracle官網找到相對應版本的 sqlplus RPM 檔
下載安裝
# rpm -ivh oracle-instantclient-sqlplus-21.12.0.0.0-1.el9.x86_64.rpm
連線(使用者)
$ sqlplus64 user/your.pwd@localhost:1521/XEPDB1
SQL*Plus: Release 21.0.0.0.0 - Production on Tue Jul 8 01:30:48 2025
Version 21.12.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Mon Jul 07 2025 21:05:47 +08:00
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL>
連線(特權帳號)
$ sqlplus64 system/root.pwd@localhost:1521/XEPDB1
ORACLE 概念
整理自己不懂的幾個 oracle 概念
XEPDB1 是什麼？
是 Oracle 12c 以後 引入的 Pluggable Database (PDB、可插拔式資料庫） 的名稱，也可以簡單視為「邏輯資料庫的名稱」。
| 層級 | 名稱 | 說明 |
|---|---|---|
| 1 | CDB（容器資料庫） | Container Database，主資料庫 |
| 2 | PDB（可插拔資料庫） | Pluggable Database，實際用的資料庫 |
簡單來說 CDB 是個容器，裡面可以放很多的 PDB，其中Oracle XE 預設PDB的就是 XEPDB1
查看裡面有哪些 PDB？(以system權限才能執行)
這裡就得看看 pdb查詢的不個view表
圖表來源 copliot 20250708
試著其中三個指令
PDB_NAME
--------------------------------------------------------------------------------
STATUS
----------
XEPDB1
NORMAL
或
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
XEPDB1
READ WRITE
或
SQL> SELECT PDB_ID, PDB_NAME, STATUS, CREATION_SCN, CON_ID FROM CDB_PDBS;
PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
STATUS CREATION_SCN CON_ID
---------- ------------ ----------
3
XEPDB1
NORMAL 2619322 3
CREATION_SCN 是什麼？
全名：Creation System Change Number
用途：表示該 PDB 被建立時的 SCN（System Change Number）
SCN 是什麼？
SCN 是 Oracle 資料庫的「邏輯時鐘」，每次資料變更或交易提交時都會產生新的 SCN。它是 Oracle 用來追蹤資料一致性與恢復的核心機制。應用場景：
判斷 PDB 建立的時間點（使用 SCN_TO_TIMESTAMP(CREATION_SCN) 函數可轉換為時間戳）
用於資料同步、複製、備份等操作的依據
CON_ID 是什麼？
全名：Container ID
用途：識別該筆資料所屬的容器（Container）
常見值： | CON_ID | 容器名稱 |
| 0 | 整體 CDB（Container Database） | 1 | CDB$ROOT（根容器） | 2 | PDB$SEED（種子容器） | ≥3 | 使用者建立的 PDB（如 PDB1, PDB2 等）應用場景：
在 CDB_PDBS、V$CONTAINERS 等視圖中辨識每筆資料屬於哪個容器
多租戶架構下的資料隔離與管理
這個顯示方式說真的很不習慣，兩個欄位是上下排列的顯示，多個欄位就隨便他擺了，這是在擺盤嗎？
至於上網查了 show pdbs; 的指令得到了錯誤：
SP2-0382: The SHOW PDBS command is not available
不知所以然…可能是新版才有吧？？
所以預設安裝好oracle後只有一個pdb，也就是 xepdb1，名稱大小寫好像是無關的。
SID 是什麼？
SID（System Identifier） 是指「資料庫實例的唯一識別碼」，這點就把我搞得很糊塗。
好在借助ai的幫助，他們的解釋讓我理解：
SID 通常對應整個「CDB（容器資料庫）」
PDB（可插拔資料庫） 是「實際使用者資料所在的邏輯資料庫」
使用者通常是連進 PDB，不直接用 SID（CDB）
CHATGPT給了一張圖：
│
SID: XE ← 傳統識別碼
│
┌────────┴────────┐
│ │
PDB: XEPDB1 PDB: MYDB_PDB2
（預設） （你可新增）
簡單來說，上面塗成綠色的就是多個PDB住在同一個CDB，我們直接用 SID 來進行連線管理。
所以透過SID連線可以管理多個 PDB，而透過 PDB 來連線的話，只能管理那個 PDB。
下面給出他們之間的簡單說明
| 名稱 | 解釋 | 傳統版（11g以前） | 現代版（12c以後） |
|---|---|---|---|
| SID | System Identifier，資料庫實體的唯一代號 | 就是資料庫本身 | 多數情況下是 CDB |
| CDB | Container Database，容器資料庫（Oracle 架構） | 無 | 新增結構 |
| PDB | Pluggable Database，可插拔資料庫（實際使用） | 無 | 你會使用它來存取資料 |
| Service Name | 資料庫服務名稱，通常對應某個 PDB | 和 SID 相同 | 通常會是 PDB 名稱（如 XEPDB1） |
上面又出現了另一個奇怪的東西 Service Name，這在 WINDOWS SQL SERVER裡也出現過，他又是什麼東西？
先不管了，來查自己 SID
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
XE
原來 XE 就是我們的 SID，那麼改用 SID來進行連線：
重新連線
<版本號略>
SQL>
SQL> SELECT PDB_ID, PDB_NAME, STATUS, CREATION_SCN, CON_ID FROM CDB_PDBS; <查詢 PDB>
PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
STATUS CREATION_SCN CON_ID
---------- ------------ ----------
3
XEPDB1
NORMAL 2619322 3
2
PDB$SEED
NORMAL 2571577 2
PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
STATUS CREATION_SCN CON_ID
---------- ------------ ----------
竟然多了一個叫作 $SEED 的什麼東西？他是oracle的種子資料庫，又是啥玩意？
Service Name 是什麼？
上面提到出現的奇怪的東西 Service Name，又是什麼東西？
Service Name 就是 Oracle 資料庫「提供給外部連線的名稱」，通常對應一個 PDB 或一個資料庫實體。 使用下面的指令可以查看目前的Service Name
SQL> SELECT NAME FROM V$SERVICES;
NAME
----------------------------------------------------------------
xeXDB
SYS$BACKGROUND
SYS$USERS
freepdb1
FREE
xe
xepdb1
7 rows selected.
換言之，一開始連入的方式就是採用 Service Name的方式，即指令最後的 XEPDB1
$ sqlplus64 user/your.pwd@localhost:1521/XEPDB1
他顯然是不分大小寫的，後面的 XEPDB1 寫成小寫也可以。
用戶端連線範例（TNS）是什麼？
TNS（Transparent Network Substrate） 是一種網路連線協定，用來讓用戶端連接到資料庫伺服器。TNS 連線通常透過 tnsnames.ora 檔案來設定，這個檔案定義了資料庫的連線資訊。
簡單來說就是連線字串，把參數寫在裡面給程式用，例如：
tnsnames.ora
後面的 SERVICE_NAME 也可以用 sid，例如：
tnsnames.ora
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = your-db-host)(PORT = 1521))
(CONNECT_DATA =
(SID = orcl)
)
)
tnsnames.ora 這個檔到底放在哪？
預設是沒有的， sqlpluss/sqlplus64 透過系統常數 $ORACLE_HOME 來查找這個檔案，所以要先指定這個變數：
$ export TNS_ADMIN=~
或是寫在你的profile 中，例如 ~/.bashrc 加一行
然後在這個目錄下新增tnsnames.ora 這個檔，內容就參照上面的改。例如：
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID = xe)
)
)
然後連線指令就能簡化成
# sqlplus64 system/root.pwd@XE_SID
ORACLE 的基本操作
上面是處理連線，下面是基本的指令
讀取系統時間
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;
切換 PDB
SQL> ALTER SESSION SET CONTAINER = ORCLPDB1;
查詢裡面有哪些表
SQL> SELECT table_name FROM user_tables;
全域查詢所有表格(要 DBA權限)
SQL> SELECT owner, table_name FROM all_tables;
建立一個表 ACCOUNT 並設定主鍵
CREATE TABLE account (
sno NUMBER(10) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
item VARCHAR2(50),
cost NUMBER(10),
subtotal NUMBER(10),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
刪除一個表(可以flashback恢復)
DROP TABLE table_name;
刪除一個表(不可以恢復)
DROP TABLE table_name PURGE;
清空表格內容
TRUNCATE TABLE table_name;
插入一筆資料到 ACCOUNT
INSERT INTO ACCOUNT (item, cost, subtotal) VALUES ('手續費', 3329, 306324);
原則上oracle都是不分大小寫的，包括表格、欄位、資料庫名。
取回前10筆資料
SQL> select * from account WHERE ROWNUM <= 10;
注意：ROWNUM 是 Oracle 的「虛擬欄位」，在查詢結果產生時即分配，不能用來取第 N 筆以後的資料（例如 ROWNUM > 10 不會有結果）。
選擇位置10開始5筆資料(第11筆~15筆資料)
SQL> select * from account OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
DOCKER+PHP支援ORACLE
在docker上要支援oracle 需要安裝2個主要的套件。請在DOCKERFILE 新增下面三筆
# 可以改版本，例如：21_11、19_21 等，視 PHP 相容性與 DB 版本，檔案的連結可能會異動，請參考[1] 下載適合的zip
WORKDIR /opt/oracle
RUN wget https://download.oracle.com/otn_software/linux/instantclient/2112000/el9/instantclient-basiclite-linux.x64-21.12.0.0.0dbru.el9.zip && \
wget https://download.oracle.com/otn_software/linux/instantclient/2112000/el9/instantclient-sdk-linux.x64-21.12.0.0.0dbru.el9.zip && \
unzip instantclient-basiclite-linux.x64-21.12.0.0.0dbru.el9.zip && \
unzip instantclient-sdk-linux.x64-21.12.0.0.0dbru.el9.zip && \
rm *.zip && \
ln -s /opt/oracle/instantclient_21_12 /opt/oracle/instantclient && \
echo /opt/oracle/instantclient > /etc/ld.so.conf.d/oracle-instantclient.conf && \
ldconfig
RUN docker-php-ext-configure oci8 --with-oci8=instantclient,/opt/oracle/instantclient && docker-php-ext-install oci8
上面的載點和版號可能會異動，如果有異動，檔案名稱和路徑都要配合調整，包括下面的 ln -s 的路徑。
安裝後檢測
查看 phpinfo 或執行 php -i
/usr/local/etc/php/conf.d/docker-php-ext-oci8.ini,
oci8
oci8.connection_class => no value => no value
oci8.default_prefetch => 100 => 100
oci8.events => Off => Off
oci8.max_persistent => -1 => -1
oci8.old_oci_close_semantics => Off => Off
oci8.persistent_timeout => -1 => -1
oci8.ping_interval => 60 => 60
oci8.privileged_connect => Off => Off
oci8.statement_cache_size => 20 => 20
root@php1:/opt/oracle# php -i | grep OCI
OCI8 Support => enabled
OCI8 DTrace Support => disabled
OCI8 Version => 2.2.0
上面的結果就是有支援。
PHP+ADODB 連oracle 範例
使用 adodb 來連 oracle 是很簡單的，來看範例
putenv("NLS_LANG=TRADITIONAL CHINESE_TAIWAN.AL32UTF8");
$db = NewADOConnection('oci8');
$tns= "
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = XEPDB1)
)
)";
$user = 'user;
$pass = 'your.pwd';
關鍵第2行，指定資料庫編碼，不然中文字就是一堆問號 ???? 這裡我搞很久。
傾印
$rs = $db->Execute( $sql);
if ($rs && !$rs->EOF) {
echo "<table border='1' cellpadding='5' cellspacing='0'>";
// 印出表頭
echo "<tr>";
foreach (array_keys($rs->fields) as $colName) {
echo "<th>" . htmlspecialchars($colName) . "</th>";
}
echo "</tr>";
// 印出每一列
while (!$rs->EOF) {
echo "<tr>";
foreach ($rs->fields as $val) {
echo "<td>" . htmlspecialchars($val, ENT_QUOTES, 'UTF-8') . "</td>";
}
echo "</tr>";
$rs->MoveNext();
}
echo "</table>";
} else {
echo "查無資料";
}
以上
其它
改善sqlplus 不能按上下鍵查閱歷史資料的問題
在 SQL*Plus 中預設是無法使用「上鍵」來取回歷史指令的，這是因為 SQL*Plus 是一個非常簡單的命令列工具，不具備像 Bash 或 Zsh 那樣的指令歷史功能。
安裝 rlwrap
# sudo yum install rlwrap
連線指令前面加一個 rlwrap, 例如：
$ rlwrap sqlplus64 user/your.pwd@XE_SID
錯誤排除
ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified
語系檔錯誤，我的經驗是下指令
$ export NLS_LANG=TRADITIONAL CHINESE_TAIWAN.AL32UTF8
下完後就登不進去，所以要改成這樣
$ export NLS_LANG="TRADITIONAL CHINESE_TAIWAN.AL32UTF8"
查看你的 nls
SQL> SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8
結論
不知不覺寫得又臭又長，但還只是皮毛。
經過二日的學習和實作，總算解決了大部分的問題。
感謝ai、讚嘆ai。
參考資料
[1] https://www.oracle.com/tw/database/technologies/instant-client/linux-x86-64-downloads.html
。
