Oracle 進階管理問題與解答（四）：歸檔模式、參數檔、Top-N 查詢與日期運算

資料庫模式、參數與查詢優化

99. 判斷資料庫是否運行於歸檔模式

使用 DBA Studio：歷程 → 資料庫 → 歸檔查看；或查詢 V$DATABASE 的 LOG_MODE 欄位。

100. STARTUP PFILE / IFILE / SPFILE 差異

• PFILE：傳統文字參數檔
• IFILE：類似 C 語言 #include，引入另一參數檔
• SPFILE：9i 新增二進位參數檔，預設使用，支援動態修改持久化

STARTUP 後僅能指定 PFILE。

101. 取得前 N 筆記錄

SELECT * FROM EMPLOYEE WHERE ROWNUM < N ORDER BY EMPNO;

102. 查詢支援最大並發用戶數

SHOW PARAMETER PROCESSES;

103. DB_BLOCK_SIZE 可否修改？

一般不可修改，不建議嘗試。

104. 統計兩表記錄總數

SELECT (SELECT COUNT(ID) FROM AA) + (SELECT COUNT(ID) FROM BB) 總數 FROM DUAL;

105. 查詢單欄位第 N 大值

SELECT * FROM (
  SELECT T.*, DENSE_RANK() OVER (ORDER BY SAL) RANK FROM EMPLOYEE
) WHERE RANK = N;

106. 日期加減運算

-- 加 2 年
SELECT ADD_MONTHS(SYSDATE, 24) FROM DUAL;

107. USED_UBLK 為負值含義

屬無害現象，可忽略。

108. Connect String 定義

指 TNSNAMES.ORA 中服務名稱後的連線描述字串。

109. 擴大 REDO LOG 大小

建立臨時 Redo Log Group → 切換日誌 → 刪除舊日誌 → 建立新日誌。

110. Tablespace 是否有 4G 上限？

無限制。

111-112. CEIL / FLOOR 函數

SELECT CEIL(N) FROM DUAL;   -- 大於等於 N 的最小整數
SELECT FLOOR(N) FROM DUAL;  -- 小於等於 N 的最大整數

113. 取得當月最後一天

SELECT LAST_DAY(SYSDATE) FROM DUAL;

114. 不同用戶間資料匯入

IMP SYSTEM/MANAGER FILE=AA.DMP FROMUSER=USER_OLD TOUSER=USER_NEW ROWS=Y INDEXES=Y

115. 查詢表主鍵欄位名稱

SELECT * FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE='P' AND TABLE_NAME='TABLE_NAME';

116-117. 集合運算子

INTERSECT  -- 交集
UNION      -- 聯集 (去重)
UNION ALL  -- 聯集 (保留重複)
MINUS      -- 差集

118. Sequence 建立與使用

CREATE SEQUENCE SEQ_CUSTID START WITH 1 INCREMENT BY 1;
-- 建表時
CREATE TABLE CUST (CUST_ID SMALLINT NOT NULL, ...);
-- 插入時
INSERT INTO CUST VALUES (SEQ_CUSTID.NEXTVAL, ...);

119-132. 日期格式化元件速查

133-135. 偽欄位

• CURRVAL / NEXTVAL：Sequence 當前/下一值
• ROWNUM：查詢結果排序序號
• ROWID：資料列物理位址

136. 秒數轉時分秒格式

DECLARE
  N NUMBER := 1000000;
  RET VARCHAR2(100);
BEGIN
  RET := TRUNC(N/3600) || '小時' ||
         TO_CHAR(TO_DATE(MOD(N,3600),'SSSSS'),'FMMi"分 "SS"秒"');
  DBMS_OUTPUT.PUT_LINE(RET);
END;

137-138. 大量排序進程與 SQL 追蹤

-- 大排序進程
SELECT B.TABLESPACE, B.SEGFILE#, B.SEGBLK#, B.BLOCKS,
       A.SID, A.SERIAL#, A.USERNAME, A.OSUSER, A.STATUS
FROM V$SESSION A, V$SORT_USAGE B
WHERE A.SADDR = B.SESSION_ADDR
ORDER BY B.TABLESPACE, B.SEGFILE#, B.SEGBLK#, B.BLOCKS;

-- 該進程 SQL 全文
SELECT /*+ ORDERED */ SQL_TEXT
FROM V$SQLTEXT A
WHERE A.HASH_VALUE = (
  SELECT SQL_HASH_VALUE FROM V$SESSION B
  WHERE B.SID = &SID AND B.SERIAL# = &SERIAL
)
ORDER BY PIECE ASC;

139-140. 查找與刪除重複記錄 (ROWID 法)

-- 查找
SELECT * FROM TABLE_NAME
WHERE ROWID != (SELECT MAX(ROWID) FROM TABLE_NAME D
                WHERE TABLE_NAME.COL1 = D.COL1
                  AND TABLE_NAME.COL2 = D.COL2);
-- 刪除
DELETE FROM TABLE_NAME
WHERE ROWID != (SELECT MAX(ROWID) FROM TABLE_NAME D
                WHERE TABLE_NAME.COL1 = D.COL1
                  AND TABLE_NAME.COL2 = D.COL2);