Oracle 7 vs 8/8i/9i 大對象 (LOB) 演進全解析：從 LONG 到 SecureFile

關鍵存儲屬性 (CREATE TABLE ... LOB 子句)

CREATE TABLE doc_store (
    id          NUMBER PRIMARY KEY,
    content     CLOB,
    metadata    BLOB,
    ext_file    BFILE
)
LOB (content) STORE AS SECUREFILE content_lob_seg (
    TABLESPACE lob_ts
    ENABLE STORAGE IN ROW       -- 小於 ~4KB 存行內，大於存行外 (默認 ENABLE)
    CHUNK 8192                  -- 讀寫單元，8KB 標準，不可大於塊大小
    RETENTION                   -- 保留舊版本供讀一致性/閃回 (AUTO/MAX/MIN/NONE)
    CACHE                       -- 緩存 LOB 數據到 Buffer Cache (CACHE/NO CACHE/CACHE READS)
    COMPRESS HIGH               -- SecureFile 壓縮: LOW/MEDIUM/HIGH
    DEDUPLICATE                 -- SecureFile 去重: 同表/跨表
    ENCRYPT USING 'AES256'      -- SecureFile 加密: TDE 整合
)
LOB (metadata) STORE AS BASICFILE meta_lob_seg ( -- 舊版 BasicFile
    TABLESPACE lob_ts
    DISABLE STORAGE IN ROW
    CHUNK 8192
    PCTVERSION 10               -- BasicFile 版本管理
    NOCACHE
);

DBMS_LOB 核心包 — LOB 操作標準 API

LONG → LOB 遷移路徑 (生產環境標準化)

1. 11g+ 首選：`ALTER TABLE t MODIFY (long_col CLOB);` — **在線、秒級、元數據級、無數據移動、無鎖**。
2. 10g/11g 通用**：`CREATE TABLE t_new AS SELECT pk, TO_LOB(long_col) AS clob_col, ... FROM t_old;` — 需鎖表、雙倍空間、重建索引/約束/權限。
3. 跨平台/版本升級**：Data Pump `expdp/impdp` 導出導入，**12c+ impdp 自動將 LONG 轉 CLOB**。
4. 應用層同步修改**：
   • JDBC: `ResultSet.getClob()` / `PreparedStatement.setClob()` 替代 `getString`/`setString`。
   • ODP.NET: `OracleClob` / `OracleBlob` 對象流式讀寫。
   • PL/SQL: 變量類型 `LONG` → `CLOB`，直接賦值/比較/DBMS_LOB 操作。

BFILE 專項：外部文件只讀訪問

創建與授權

-- 1. OS 層創建目錄並授權 oracle 用戶讀取
# mkdir -p /u01/app/oracle/bfile_data
# chown oracle:oinstall /u01/app/oracle/bfile_data
# chmod 750 /u01/app/oracle/bfile_data

-- 2. 數據庫創建 DIRECTORY 對象
CREATE OR REPLACE DIRECTORY ext_media_dir AS '/u01/app/oracle/bfile_data';
GRANT READ ON DIRECTORY ext_media_dir TO app_user;

-- 3. 表中存儲 BFILE
CREATE TABLE media_catalog (
    id       NUMBER PRIMARY KEY,
    title    VARCHAR2(200),
    media    BFILE
);

-- 4. 插入 BFILE (僅存儲指針)
INSERT INTO media_catalog (id, title, media)
VALUES (1, 'Sample Video', BFILENAME('EXT_MEDIA_DIR', 'sample.mp4'));

-- 5. 讀取 BFILE (需 DBMS_LOB.OPEN/CLOSE/READ)
DECLARE
    v_bfile BFILE := BFILENAME('EXT_MEDIA_DIR', 'sample.mp4');
    v_blob  BLOB;
BEGIN
    DBMS_LOB.OPEN(v_bfile, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
    DBMS_LOB.LOADFROMFILE(v_blob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
    DBMS_LOB.CLOSE(v_bfile);
    -- v_blob 現在包含文件內容，可進一步處理
    DBMS_LOB.FREETEMPORARY(v_blob);
END;
/

SecureFile vs BasicFile 選型決策

總結

• Oracle 7 的 LONG/LONG RAW 為**順序訪問、功能殘缺、已徹底棄用**。
• Oracle 8.0 引入 **LOB 架構 (CLOB/BLOB/NCLOB/BFILE)**，確立 **Locator + Data 分離、隨機訪問、多列、4GB+、DBMS_LOB 標準 API** 核心模型，沿用至今。
• **SecureFile (11g+)** 通過壓縮/去重/加密/智能緩存/優化索引，將 LOB 性能與存儲效率提升數量級，**所有新建 LOB 列強制使用 SecureFile**。
• BFILE 解決**超大只讀外部文件**引用需求，不佔用數據庫存儲/備份空間，配合 `DBMS_LOB.LOADFROMFILE` 實現按需載入。
• 遷移鐵律：**LONG → CLOB (ALTER TABLE MODIFY)**，**BasicFile → SecureFile (ALTER TABLE MODIFY LOB)**，**應用層同步升級 LOB API**。