Oracle 初學者百問精解 (下)：第 51~100 題

資料庫物件管理

51. 小表放入 KEEP Pool：ALTER TABLE xxx STORAGE (BUFFER_POOL KEEP);
52. 查詢已安裝 Patch：檢查 oraInventory 目錄下 ContentsXML 或 opatch lsinventory。
53. SELECT 自動產生序號：SELECT ROWNUM, col FROM table;
54. 查表所在 Tablespace：SELECT tablespace_name FROM user_tables WHERE table_name='TEST';
55. SQL*Plus 修改 Procedure：SELECT line, TRIM(text) FROM user_source WHERE name='PROC' ORDER BY line; 再重建。
56. 解除 Procedure 鎖定：ALTER SYSTEM KILL SESSION 'sid,serial#'; 或重命名 Procedure。

工具與文件

57. SQL Reference：Oracle 官方 SQL 語法/函數手冊，OTN 文件中心可下載。
58. 查看資料庫狀態：Unix ps -ef | grep ora；Windows 看服務 + 能否連線。

DDL 操作

59. 修改主鍵：ALTER TABLE aaa DROP CONSTRAINT aaa_key; ALTER TABLE aaa ADD CONSTRAINT aaa_key PRIMARY KEY (a1,b1);
60. 調整資料檔大小：ALTER DATABASE DATAFILE 'path' RESIZE 2G; (縮小需確保高水位以下)。

動態效能視圖

61. 查看運行中程序：SELECT * FROM v$session;
62. Tablespace 數量：SELECT * FROM dba_tablespaces;
63. 修改用戶連線數上限：調整 init.ora processes 參數並重啟。
64. 查詢列最後更新時間：使用 LogMiner 分析 Redo/Archive Log。

PL/SQL 進階

65. 讀寫 OS 檔案：UTL_FILE 套件 (需 UTL_FILE_DIR 或 Directory 物件)。
66. 插入 '&' 字元：INSERT INTO a VALUES (TRANSLATE('at{&}t','at{}','at')); 或 SET DEFINE OFF。
67. EXP 使用 QUERY 參數：exp user/pass FILE=a.dmp TABLES=bscempms QUERY='"WHERE emp_no=\'S09394\'"

字元集與備份

68. 8i 支援簡繁中文：ZHS16GBK。
69. Data Guard：Standby Database 代產品，提供災難恢復/讀取分流。

SPFILE 與核心參數

70. 建立 SPFILE：CREATE SPFILE FROM PFILE='.../init.ora'; 或指定輸出路徑。
71. 核心參數速查：
    • shmmax = 物理記憶體 50%
    • shmmin = 1
    • shmmni = 100+
    • shmseg = 20
    • semmni = 所有實例 MAX(processes) + 10
    • semmns = Σ(processes) - MAX + MAX×2 + 10×實例數
    • semmsl = MAX(processes) + 10

權限與備份

72. 查 SYSDBA/SYSOPER 用戶：SELECT * FROM v$pwfile_users;
73. 單表備份：exp user/pass TABLES=(t1,t2)
74. 單用戶備份：exp system/manager OWNER=(u1,u2) FILE=exp.dmp
75. CLOB 全文檢索：SELECT * FROM a WHERE DBMS_LOB.INSTR(a.clob_col,'K',1,1) > 0;

常用查詢技巧

76. 顯示當前用戶：SHOW USER
77. 查資料檔路徑：SELECT tablespace_name, file_id, bytes/1024/1024, file_name FROM dba_data_files ORDER BY file_id;
78. 查 Rollback Segment 狀態：SELECT segment_name, owner, tablespace_name, status FROM dba_rollback_segs;
79. 修改 CHECK 約束範圍：Drop 再 Add。
80. 核心系統檔視圖：v$database, v$datafile, v$logfile, v$controlfile, v$parameter

SQL 進階語法

81. 內連結：SELECT a.* FROM a, b WHERE a.dpt_no = b.dpt_no;
82. 外連結：WHERE a.dpt_no = b.dpt_no(+) (右外) / WHERE a.dpt_no(+) = b.dpt_no (左外)。
83. 執行腳本：@$PATH/file.sql
84. 快速清大表：TRUNCATE TABLE table_name;
85. 實例數：SELECT * FROM v$instance;
86. 表總數：SELECT * FROM all_tables;
87. 測試 SQL 耗時：SET TIMING ON
88. CHR 反函數：ASCII()
89. 字串串接：|| 或 CONCAT()
90. 結果導出檔案：SPOOL c:\out.txt; SELECT ...; SPOOL OFF;
91. 估算 I/O：SET AUTOTRACE ON 或 SELECT * FROM v$filestat;
92. 修改欄位長度：ALTER TABLE t MODIFY (col VARCHAR2(100)); (只能增不能減，除非欄為空)。
93. 查某日資料：WHERE TRUNC(dt_col) = TO_DATE('2003-05-02','YYYY-MM-DD')
94. 插入全年日期：INSERT INTO bsyear SELECT TO_DATE('20030101','YYYYMMDD')+ROWNUM-1 FROM all_objects WHERE ROWNUM <= TO_CHAR(TO_DATE('20031231','YYYYMMDD'),'DDD');
95. 改表名：ALTER TABLE old RENAME TO new;
96. 取得命令回傳碼：SQLCODE (0=成功)。
97. 查用戶權限：SELECT * FROM dba_sys_privs;

授權與模式

98. 下載版 vs 商業版：功能相同，OTN 版僅供開發/測試/原型，禁止生產商業用途。
99. 判斷歸檔模式：DBA Studio → 歷程 → 資料庫 → 歸檔；或 ARCHIVE LOG LIST。
100. PFILE/IFILE/SPFILE 差異：PFILE=文字參數檔；IFILE=include 引入其他檔；SPFILE=二進位、持久化、9i 預設；STARTUP 只能接 PFILE/SPFILE。