[20241013]sqlplus spool與檔案覆蓋.txt

[20241013]sqlplus spool與檔案覆蓋.txt

--//這個問題在8月份遇到的問題，我發現在sqlplus下spool a.sql檔案，並沒有在當前目錄產生a.sql檔案，後來我發現建立在環境變數
--//ORACLE_PATH定義的目錄下,當時以為自己開啟多個會話，沒有注意自己工作的當前目錄。事後我測試，問題視乎消失了，我再沒有仔
--//細探究。

--//昨天在使用spool命令時再次出現這種詭異的現象，才引起我的注意，這才發現問題的根本原因，透過例子將問題演示出來。

1.環境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.

$ echo -e "$SQLPATH\n$ORACLE_PATH"
/home/oracle/sqllaji:/home/oracle/sqllaji/tpt
/home/oracle/sqllaji:/home/oracle/sqllaji/tpt
--//我定義2個環境變數SQLPATH與ORACLE_PATH，在linux下ORACLE_PATH定義有效，windows下正好相反，建議如果記不住，兩個都定義相
--//同的值。

2.測試：
--//session 1:
SCOTT@book01p> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---- ---------- -------- --------- ---- --- ---------- --------------------------------------------------
275 27250 4289 DEDICATED 4291 66 12 alter system kill session '275,27250' immediate;

SCOTT@book01p> host pwd
/home/oracle/study/202410
--//當前執行sqlplus時的當前目錄是/home/oracle/study/202410.

--//window 1:
$ pwd
/home/oracle/sqllaji

--//在/home/oracle/sqllaji目錄下建立檔案tmp123.txt。
$ echo $(date) >| tmp123.txt
$ cat tmp123.txt
Sun Oct 13 10:14:43 CST 2024

$ wc tmp123.txt
1 6 29 tmp123.txt

--//session 1:
SCOTT@book01p> spool tmp123.txt

--//window 1:
$ ls -l /proc/4291/fd | grep tmp
--//連線oracle的程序並沒有看到開啟tmp123.txt的檔案控制代碼,當時測試也遇到類似困惑。不過馬上明白應該看看sqlplus程序，因為我在
--//本地連線資料庫，sqlplus程序對應前面顯示PROCESS欄位，也就是4289.

$ ls -l /proc/4289/fd | grep tmp
l-wx------. 1 oracle oinstall 64 2024-10-13 10:17:25 10 -> /home/oracle/sqllaji/tmp123.txt
--//可以發現在sqlplus下執行spool時，如果環境變數SQLPATH與ORACLE_PATH定義的目錄該檔案存在，優先將檔案開啟建立在該目錄，這
--//樣的結果導致環境變數SQLPATH與ORACLE_PATH對應檔案被覆蓋。

--//session 1:
SCOTT@book01p> spool tmp123.txt
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SCOTT@book01p> spool off

--//window 1:
$ ls -l /proc/4289/fd | grep tmp
--//檔案控制代碼關閉。

$ wc /home/oracle/sqllaji/tmp123.txt
25 106 1909 /home/oracle/sqllaji/tmp123.txt
--//對比前面的執行wc的輸出，檔案長度已經發生變化，該檔案被破壞了。
--//這個在運維中應該引起足夠注意，好的解決方法限制環境變數SQLPATH，ORACLE_PATH目錄下的檔案只讀，或者
--//透過chattr修改檔案目錄屬性，限制修改操作。不過這樣當真正需要修改時要取消設定，比較麻煩。

--//另外spool時如果不指定sql字尾的檔案，也許一定程度減少覆蓋。

--//還有一個方法就是使用絕對目錄。我覺得最不可思議的地方是使用相對目錄，竟然優先使用的也是SQLPATH或者ORACLE_PATH定義環境
--//的檔案。

--//補充測試：
--//session 1,使用相對目錄有問題!!
SCOTT@book01p> host pwd
/home/oracle/study/202410

SCOTT@book01p> spool ./tmp123.txt

--//window 1:
$ ls -l /proc/3796/fd | grep tmp
l-wx------. 1 oracle oinstall 64 2024-10-14 08:56:04 10 -> /home/oracle/sqllaji/tmp123.txt

--//session 1,使用絕對目錄沒有問題。
SCOTT@book01p> spool /home/oracle/study/202410/tmp123.txt
SCOTT@book01p> host ls -l /proc/3796/fd | grep tmp
l-wx------. 1 oracle oinstall 64 Oct 14 08:56 10 -> /home/oracle/study/202410/tmp123.txt

--//session 1,將檔案建立在環境變數ORACLE_PATH指定的第2個目錄下。
$ rm /home/oracle/sqllaji/tmp123.txt
SCOTT@book01p> host touch /home/oracle/sqllaji/tpt/tmp123.txt
--//採用相對目錄。
SCOTT@book01p> spool ./tmp123.txt
SCOTT@book01p> host ls -l /proc/3796/fd | grep tmp
l-wx------. 1 oracle oinstall 64 Oct 14 08:56 10 -> /home/oracle/sqllaji/tpt-oracle-master/tmp123.txt
--//這樣ls看到的檔案屬性前面有1個l，估計表示lock。
--//注：我的測試環境tpt是軟連線指向tpt-oracle-master
$ ls -l | grep tpt
lrwxrwxrwx. 1 oracle oinstall 17 2021-11-22 09:28:29 tpt -> tpt-oracle-master
drwxr-xr-x. 19 oracle oinstall 20480 2024-10-14 09:09:10 tpt-oracle-master
drwxr-xr-x. 19 oracle oinstall 20480 2024-09-14 17:37:54 tpt-oracle-master.org

--//session 1，3個目錄檔案都存在的情況下：
SCOTT@book01p> host touch /home/oracle/sqllaji/tmp123.txt
SCOTT@book01p> host touch /home/oracle/sqllaji/tpt/tmp123.txt
SCOTT@book01p> host touch /home/oracle/study/202410/tmp123.txt
SCOTT@book01p> spool tmp123.txt
SCOTT@book01p> host ls -l /proc/3796/fd | grep tmp
l-wx------. 1 oracle oinstall 64 Oct 14 08:56 10 -> /home/oracle/sqllaji/tmp123.txt
--//優先使用環境變數ORACLE_PATH指向的第一個目錄下的檔案。

SCOTT@book01p> spool ./tmp123.txt
SCOTT@book01p> host ls -l /proc/3796/fd | grep tmp
l-wx------. 1 oracle oinstall 64 Oct 14 08:56 10 -> /home/oracle/sqllaji/tmp123.txt

3.補充測試：
--//如果是目錄呢？
--//window 1:
$ pwd
/home/oracle/sqllaji

$ mkdir tmp123.txt

--//session 1:
SCOTT@book01p> spool tmp123.txt
SP2-0606: Cannot create SPOOL file "/home/oracle/sqllaji/tmp123.txt"

--//報錯，再次證明我前面的分析正確，感覺oracle的sqlplus spool不應該這樣設計，這樣不小心會導致檔案覆蓋。
--//我前面執行spool a.sql,實際上正好破環了tpt目錄a.sql,我當時發現直接刪除該檔案。因為我有時候需要修改tpt目錄下檔案，
--//原始檔案我有備份。
$ pwd
/home/oracle/sqllaji

$ find . -name a.sql -print
./tpt-oracle-master.org/a.sql

--//a.sql 用來 Display CURRENT active sessions。
--//事後我測試，問題視乎消失了，實際上spool指定的檔案不在環境變數SQLPATH與ORACLE_PATH中存在，問題自然不存在。
--//再次說明，做好運維一些細節很重要，不要放棄自己在工作中遇到的任何問題，最好筆記，這樣再次遇到就會做到臨危不亂。
--//如果昨天不是spool a.sql,而tpt目錄下正好有1個a.sql檔案，這個問題也許永遠不被發現。

4.收尾：
--//刪除相關目錄下tmp123.txt檔案。
--//修復破壞現場。
$ pwd
/home/oracle/sqllaji

$ rmdir tmp123.txt
$ cp tpt-oracle-master.org/a.sql tpt/

SCOTT@book01p> @ a
A-Script: Display CURRENT active sessions...
no rows selected