[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.环境:
[email protected]: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定义多个访问目录路径,这样容易混乱.