Excel 自動報表遇 ora-12516 錯誤排除

在更新公司的營運報表(Excel直接讀取資料庫)時會出現 ORA-12516 TNS:listener could not find available handler with matching protocol stack 的錯誤,但每一個 SQL 指令去分別執行時都很正常,詢問過資料庫維護廠商後給我們的答案是 Oracle Client Driver 異常,這點我覺得不太可能是,應該是出在我在短時間對幾個不同的 DB 下查詢指令,而這些資料庫都是透過 Oracle DB 做 DBLink 連結到大陸的子公司去,造成極短時間負荷過大 (Processes 及 Sessions 被吃光),所以調整一下 Excel 的查詢規則

如果也遇到大量查詢時出現上述的錯誤,可以試著把 “啟用幕後執行更改作業” 的勾取消,這樣不免的會導致 Excel 查詢這些SQL指令同時間只會執行一個,但是可以降低系統的負荷就不會產生這個錯誤.

快速變更 Excel 查詢資料庫密碼

最近公司開始做共用帳號密碼的變更作業,先前幫很多同事做了 Excel 自動撈取 Database 的自動更新報表,一但要變更帳號就會變得很痛苦,依照該 Excel 表內用了幾個SQL查詢,就需要變更幾次密碼,坐下來沒有幾個小時做不完。

還好 Excel 還有功能可以直些列出所有使用到的資料連線,其路徑為 資料 -> 連線 就會顯示如下,Double-click 個別的連線就可編輯這個內容。

Excel Data connect list

Excel Data connect list

Continue reading “快速變更 Excel 查詢資料庫密碼”

解決MS Query 執行時出現 「記憶體已用完」 的錯誤

Out of Memory when running MS query32
Out of Memory when running MS query32

最近發現先前做好的 Excel 表格直接讀取 Oracle DB 資料(作法請參考: 由Excel直接撈取Oracle DB的步驟與應用 ), 需要重新編輯的時候常常發現記憶體不足, 剛開始認為是Windows執行久了, 裡面總有些不乾淨去影響到軟體的執行, 花了將近一天重新安裝 Windows 7 (Sp1) 及 Windows XP (SP3) 分別來測試, 依舊得到相同的結果, 確認應該跟環境不相關, 再度利用 Google 來找答案, 這次運氣就很好直接找到對解決方案.

找到的連結 請點選 , 主要是說 MS Query 一開始執行時會主動把所有的 Table, View, 等等東西一口氣全部 Load 進來, 隨著資料庫的成長當然記憶體就被吃光光, 連編輯都不給做, 這時候隨著 Luker 的方式來改變 MS Query 一開始執行的行為來避免掉這個錯誤.

Continue reading “解決MS Query 執行時出現 「記憶體已用完」 的錯誤”

由Excel直接撈取Oracle DB的步驟與應用

1. 確認 Oracle 服務的名稱及連結參數是否設定完整

2. 進入 Excel 選擇 資料 -> 匯入外部資料 -> 新增資料庫查詢

3. 選擇<新資料來源> ->確定

4. 設定參數

  • 來源名稱: 自取
  • 驅動程式: 選擇 Microsoft ODBC for Oracle
  • 選擇連接, 彈出使用者帳號資訊, 這個部分要詢問一下 DBA 正確的帳密資料
  • 伺服器選擇在 Oracle Net Manager 所設定的服務名稱

5. 很有誠意的把資料庫的 Table 全部列給你參考, 但是我們直接在外部利用其他的 SQL Editor 先把 SQL 查好, 所以這邊選擇取消

6. , 我們還要繼續編輯所以選擇 “Y”

7. 接下來看的這個訊息, 因為不把算用Table 拉出來去串資料, 選擇關閉

8. 在選單 Icon 上面點選SQL

9. 之後會提示你 SQL 視窗, 把準備好的 SQL 填入, 選擇 確定

10. 完成後系統自動帶出關連表格等畫面, Access 所看到的是類似的

11. 接下來可以選擇存檔(建議啦, 免得辛苦的東西不見了), 之後關閉該視窗, 回到 Excel 之後選擇要寫回 Excel 那一個欄位, 選擇樞紐分析表可以達到的功能比較多.

12. 直接就跳到樞紐分析表的第三步驟

13. 把查詢出來的欄位拉到指定的地方去就可以產生不同的效果

14. 這樣就可以造成不同的報表效果, 由不同的分析面來看同一份資料

15. 與日期相關的變數使用方法

  • add_months(Sysdate,-1) 往前一個月
  • SYSDATE+NUMTOYMINTERVAL(-1,’YEAR’) 往前推一年
  • (B.DAT_REQ Between to_Char(Sysdate,’YYYY’)||’01’ And to_char(sysdate,’YYYYMM’)) 今年一月到本月份