[20240816]oracle21c環境變數ORACLE_PATH與SQLPATH(linux).txt

[20240816]oracle21c環境變數ORACLE_PATH與SQLPATH(linux).txt

--//我記憶以前測試過這個問題,當時是家裡的筆記本,安裝oracle 12.2c for windows.OS:windows 7,發現無法訪問SQLPATH或者
--//ORACLE_PATH環境變數定義的路徑下login.sql檔案.我當時解決辦法就是登入手工執行init.sql命令,主要原因我自己也不常開啟資料
--//庫.

--//參考連結:[20230124]12c訪問login.sql指令碼.txt=>
--//當時的總結如下:
--//1.windows下至少我的測試版本12c對於ORACLE_PATH,SQLPATH定義無效,指預設訪問login.sql檔案,以下情況類似.
--//2.linux下11g版本可以定義ORACLE_PATH,SQLPATH有效,建議僅僅使用ORACLE_PATH環境變數.
--//3.linux下12c以後版本僅僅定義ORACLE_PATH有效.
--//4.也是oracle出於安全的原因做出這樣的修改操作.
--//4.連結https://blog.dbi-services.com/oracle-12cr2-changes-for-login-sql/給出許多測試.
--//5.我個人建立不要ORACLE_PATH或者SQLPATH定義多個訪問目錄路徑,這樣容易混亂.

--//這次遇到的情況不同客戶端:OS windows 10,sqlplus的版本是21c, 資料庫是21c for linux, 可以訪問SQLPATH或者ORACLE_PATH環境
--//變數的login.sql指令碼.

--//oracle升級新版本,總是做一些改進,測試看看在linux下客戶端21c 的sqlplus的情況.

1.環境:
SYS@192.168.56.101:1521/book> @ prxx
==============================
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.

2.測試:
$ ORACLE_PATH=~/ORACLE_PATH1:~/ORACLE_PATH2 SQLPATH=~/SQLPATH1:~/SQLPATH2 strace -e trace=file -o aa3.txt sqlplus -s /nolog <<<quit

$ grep -i login.sql aa3.txt
access("/home/oracle/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
stat("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/glogin.sql", {st_mode=S_IFREG|0640, st_size=367, ...}) = 0
access("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/glogin.sql", F_OK) = 0
statfs("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/glogin.sql", {f_type=0x58465342, f_bsize=4096, f_blocks=5240064, f_bfree=2243310, f_bavail=2243310, f_files=10485248, f_ffree=10455021, f_fsid={2065, 0}, f_namelen=255, f_frsize=4096}) = 0
open("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/glogin.sql", O_RDONLY) = 9
stat("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/zzlogin.sql", {st_mode=S_IFREG|0644, st_size=3055, ...}) = 0
access("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/zzlogin.sql", F_OK) = 0
statfs("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/zzlogin.sql", {f_type=0x58465342, f_bsize=4096, f_blocks=5240064, f_bfree=2243310, f_bavail=2243310, f_files=10485248, f_ffree=10455021, f_fsid={2065, 0}, f_namelen=255, f_frsize=4096}) = 0
open("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/zzlogin.sql", O_RDONLY) = 10
--//可以發現linux使用環境變數ORACLE_PATH有效.

3.測試-R 3引數:
$ ORACLE_PATH=~/ORACLE_PATH1:~/ORACLE_PATH2 SQLPATH=~/SQLPATH1:~/SQLPATH2 strace -e trace=file -o aa4.txt sqlplus -R 3 -s /nolog <<<quit
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available

$ grep -i login.sql aa4.txt
stat("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/glogin.sql", {st_mode=S_IFREG|0640, st_size=367, ...}) = 0
access("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/glogin.sql", F_OK) = 0
statfs("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/glogin.sql", {f_type=0x58465342, f_bsize=4096, f_blocks=5240064, f_bfree=2243310, f_bavail=2243310, f_files=10485248, f_ffree=10455021, f_fsid={2065, 0}, f_namelen=255, f_frsize=4096}) = 0
open("/u01/app/oracle/product/21.0.0/dbhome_1/sqlplus/admin/glogin.sql", O_RDONLY) = 9

4.總結:
--//在linux下21c的sqlplus客戶端,與windows的情況相反,設定ORACLE_PATH有效,SQLPATH無效.
--//sqlplus選擇-R 3引數,依舊會訪問ORACLE_HOME目錄下sqlplus/admin/glogin.sql.不會訪問ORACLE_PATH的設定的目錄.
--//我感覺不管怎麼環境,最好兩個環境變數都設定一樣.
--//個人建議不要ORACLE_PATH或者SQLPATH定義多個訪問目錄路徑,這樣容易混亂.