一、約束與字典查詢實戰
查找無主鍵的表
SELECT table_name
FROM user_tables t
WHERE NOT EXISTS (
SELECT 1 FROM user_constraints c
WHERE c.constraint_type = 'P'
AND c.table_name = t.table_name
);
核心字典視圖速查表
| 視圖 | 用途 | 關鍵欄位 |
|---|
| USER_TABLES | 用戶表清單 | TABLE_NAME, TABLESPACE_NAME, NUM_ROWS |
| USER_TAB_COLUMNS | 表欄位定義 | COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE |
| USER_CONSTRAINTS | 約束定義 | CONSTRAINT_NAME, CONSTRAINT_TYPE (P/R/U/C), TABLE_NAME |
| USER_CONS_COLUMNS | 約束-欄位映射 | CONSTRAINT_NAME, COLUMN_NAME, POSITION |
| USER_INDEXES | 索引定義 | INDEX_NAME, TABLE_NAME, UNIQUENESS, TABLESPACE_NAME |
| USER_IND_COLUMNS | 索引-欄位映射 | INDEX_NAME, COLUMN_NAME, COLUMN_POSITION |
二、DBMS_OUTPUT 緩衝區與 SQL*Plus 幫助
擴大輸出緩衝區
-- PL/SQL 區塊中
DBMS_OUTPUT.ENABLE(20000); -- 預設 20000 位元組，最大 1000000
-- SQL*Plus 顯示開關
SET SERVEROUTPUT ON [SIZE 1000000];
安裝 SQL*Plus 線上幫助
# 方法一：Shell 腳本 (需預設 SYSTEM_PASS)
export SYSTEM_PASS=system/manager
$ORACLE_HOME/bin/helpins
# 方法二：SQL 腳本 (需 SYSTEM 登入)
sqlplus system/manager
SQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql
# 使用方式
SQL> HELP INDEX
SQL> HELP CREATE TABLE
三、快速下載 Oracle 補丁包
- 取得 FTP 入口：
ftp://updates.oracle.com
- 用 Metalink 帳號密碼登入
- 進入補丁號目錄：
cd 3095277 (例：9.2.0.4 补丁集)
ls 列出所有平台封裝- 構建下載連結：
ftp://updates.oracle.com/3095277/p3095277_9204_AIX64-5L.zip
- 使用 FlashGet/IDM 等多線程工具下載，**必須填寫 Metalink 認證資訊**
四、資料檔線上移動兩大方案
方案 A：傳統離線移動 (需停機)
SHUTDOWN IMMEDIATE;
-- OS 層複製檔案至新位置
STARTUP MOUNT;
ALTER DATABASE RENAME DATAFILE '/old/path/file.dbf' TO '/new/path/file.dbf';
ALTER DATABASE OPEN;
方案 B：RMAN 線上移動 (零停機)
RMAN> SQL "ALTER DATABASE DATAFILE '/old/path/file.dbf' OFFLINE";
RMAN> RUN {
COPY DATAFILE '/old/path/file.dbf' TO '/new/path/file.dbf';
SWITCH DATAFILE '/old/path/file.dbf' TO DATAFILECOPY '/new/path/file.dbf';
}
RMAN> SQL "ALTER DATABASE DATAFILE '/new/path/file.dbf' ONLINE";
原理解析
COPY 相當於 OS cp，物理複製資料塊SWITCH 相當於 ALTER DATABASE RENAME FILE，更新控制檔指標- OS 複製 + 手動 RENAME 同樣可線上操作，步驟與 RMAN 邏輯一致
五、線上日誌組與成員管理速查
-- 新增日誌組
ALTER DATABASE ADD LOGFILE [GROUP n] '/path/logfile.log' SIZE 10M;
-- 組內新增成員 (鏡像)
ALTER DATABASE ADD LOGFILE MEMBER '/path/logfile_mirror.log' TO GROUP n;
-- 刪除成員
ALTER DATABASE DROP LOGFILE MEMBER '/path/logfile_mirror.log';
-- 刪除整組 (至少保留 2 組)
ALTER DATABASE DROP LOGFILE GROUP n;
-- RAC/OPS 環境需指定 THREAD 參數
六、Redo Block Size 計算公式
-- 公式：(REDO SIZE + REDO WASTAGE) / REDO BLOCKS WRITTEN + 16
SELECT (value_redo_size + value_redo_wastage) / value_redo_blocks_written + 16
AS "Redo Block Size (bytes)"
FROM (
SELECT
MAX(DECODE(name,'redo size',value)) value_redo_size,
MAX(DECODE(name,'redo wastage',value)) value_redo_wastage,
MAX(DECODE(name,'redo blocks written',value)) value_redo_blocks_written
FROM v$sysstat
WHERE name IN ('redo size','redo wastage','redo blocks written')
);
-- 範例結果：512 bytes (典型值為 512/1024/4096)
七、控制檔核心記錄條目 (DUMP 可見)
DATABASE ENTRY
CHECKPOINT PROGRESS RECORDS
REDO THREAD RECORDS
LOG FILE RECORDS
DATA FILE RECORDS
TEMP FILE RECORDS
TABLESPACE RECORDS
LOG FILE HISTORY RECORDS
OFFLINE RANGE RECORDS
ARCHIVED LOG RECORDS
BACKUP SET/PIECE/DATAFILE/LOG RECORDS
DATAFILE COPY RECORDS
CORRUPTION RECORDS
DELETION RECORDS
PROXY COPY RECORDS
INCARNATION RECORDS
八、壞塊數據搶救實戰
步驟 1：定位壞塊
-- DBVERIFY 離線檢查
dbv file=/u01/oradata/system01.dbf blocksize=8192
-- 或查詢 V$DATABASE_BLOCK_CORRUPTION (10g+)
步驟 2：關聯壞塊到段
SELECT segment_name, segment_type, extent_id, block_id, blocks
FROM dba_extents
WHERE file_id = &bad_file_id
AND &bad_block_id BETWEEN block_id AND block_id + blocks - 1;
步驟 3：分類搶救
| 段類型 | 搶救策略 |
|---|
| 表 | 建立良表排除壞塊 ROWID |
| 索引 | 直接 DROP + REBUILD |
方法 A：ROWID 過濾法 (需注意 8.0 前後 ROWID 格式差異)
CREATE TABLE good_table AS
SELECT * FROM bad_table
WHERE ROWID NOT IN (
SELECT ROWID FROM bad_table
WHERE SUBSTR(ROWID, 10, 6) = &bad_block_id_hex
);
方法 B：診斷事件 10231 法 (自動跳過壞塊)
ALTER SYSTEM SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
CREATE TABLE good_table AS SELECT * FROM bad_table;
ALTER SYSTEM SET EVENTS '10231 TRACE NAME CONTEXT OFF';
九、核心內建用戶清單與處置建議
| 用戶/密碼 | 角色定位 | 創建腳本 | 生產環境處置 |
|---|
| SYS/CHANGE_ON_INSTALL | 資料字典擁有者、SYSDBA | ?/rdbms/admin/sql.bsq + cat*.sql | 不可刪除，首要修改密碼 |
| SYSTEM/MANAGER | 預設 DBA 管理用戶 | ?/rdbms/admin/sql.bsq | 不可刪除，修改密碼、審計啟用 |
| OUTLN/OUTLN | 優化器計劃大綱存儲 | ?/rdbms/admin/sql.bsq | 不可刪除，修改密碼 |
| SCOTT/TIGER 等 | 示範/測試用戶 (EMP/DEPT) | ?/rdbms/admin/utlsampl.sql | 建議刪除或鎖定 |
| HR/OE/SH | 新版示範用戶 | ?/demo/schema/mksample.sql | 建議刪除或鎖定 |
| DBSNMP/DBSNMP | Intelligent Agent | ?/rdbms/admin/catsnmp.sql | 不需 Agent 可刪除 |
| CTXSYS, ORDPLUGINS, ORDSYS, DSSYS, MDSYS, AURORA$, PERFSTAT | 選裝功能用戶 (Text, Spatial, Time Series, Dynamic Services, Statspack) | 各功能對應 admin 目錄 | 未使用功能可不安裝/刪除 |
結語
本文涵蓋 DBA 日常運維高頻場景：約束治理、參數調優、字符集變更、補丁獲取、資料檔遷移、日誌管理、壞塊搶救、用戶清理。建議將核心 SQL 腳本封裝為標準化工具包 (如 dba_toolkit.sql)，配合排程自動巡檢，將經驗轉化為可複用的工程資產。
