Oracle: customise the SQL*Plus prompt and session with login.sql
Configure SQL*Plus with login.sql or glogin.sql: date format and a prompt showing the user, the database or the date.
The file login.sql (or glogin.sql), placed in $ORACLE_HOME/sqlplus/admin, sets session options and the SQL*Plus prompt automatically at each connection. Inspired by dbaora.com.
Date format and simple prompt
alter session set nls_date_format='YYYY/MM/DD hh:mi:ss';
set sqlprompt "_user'@'_connect_identifier':'_date> "
With these lines, SQL*Plus displays dates in the chosen format and the prompt shows the user, the connect identifier and the date.
Prompt showing the database name
This block builds the prompt from the global database name (global_name):
set termout off
define new_prompt='nolog'
column value new_value new_prompt
select username || ': ' || substr(substr(global_name,1,30),1,instr(substr(global_name,1,30),'.')-1) value
from user_users, global_name;
set sqlprompt "&new_prompt> "
set termout on
Example of result
export ORACLE_SID=PROD
sqlplus <user>/<password>
PROD> select name from v$database;
PROD
1 Row selected.
PROD>