Further to my post on Self-inflicted unable to startup or shutdown database from OEM, I wondered if there might be a way to make the login.sql conditional.
The windows registry HKey_Local_Machine\ORACLE\Key_OraDb11g_home1 key has a Value field named SQLPATH
When you run a file from insied sqlplus using @ or “start”, sqlplus will search any folders listed in sqlpath for the file.
Also on starting sqlplus, it will check folders listed in sqlplus for any file named “login.sql”. If it finds a file, then this gets run before a prompt is presented. Also if any “connect /” is done in sqlplus, then a login.sql is again run.
If my pc does a sqlplus/nolog then I do not want my login.sql to do anything (because there is no connection)
Likewise once someone is connected, if they do a “connect sys/password as sysdba”, I do not want my login.sql to do anything (namely because this blocks OEM from starting/shutting down my database)
Otherwise for any non-sys/non-system connect, I do want a login.sql to be run.
In sqlplus there are some predefined variables.
You can see these by typing “define”. The following is what you get after a sqlplus/nolog
SQL> define DEFINE _DATE = "15-MAR-10" (CHAR) DEFINE _CONNECT_IDENTIFIER = "" (CHAR) DEFINE _USER = "" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1101000700" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR)
After connect as test, you get
SQL> define _user DEFINE _USER = "TEST" (CHAR)
So I created the following files:
“login.sql” with one line:
“start_.sql” and “start_SYS.sql” and “start_SYSTEM.sql” are empty files
start_TEST.sql with any number of lines to do something useful such as
This works as planned for “sqlplus/nolog”, “connect sys…” and “connect test/…”
Of course, a big downside here is that for each username you need a file named “start_username.sql”
With any login.sql script you need to worry about someone adding lines to your start_SYS.sql such as
grant dba to bad_guy;