SQLPlus Prompt with glogin.sql and login.sql






When SQL*Plus starts, and after CONNECT commands, the two site profile files are read, and based on the sql commands mention in them the prompt is set.

GLOGIN.sql


It is the global profile and will be used for all the instances running from a particular Oracle Home

Path:  $ORACLE_HOME/sqlplus/admin/glogin.sql

This helps when we have multiple instances running and we want to be sure to connect to the correct database. This will be very useful and can avoid Human errors and help in the customization of prompts.

In my example, I added a prompt which will show me the user name and the SID.


[oracle@ebs122 ~]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set sqlprompt "_user'@'_connect_identifier >"

So now my SQL prompt will look like

[oracle@ebs122 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 24 18:23:25 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@GOLD >



SYS is my user and GOLD is the SID.


Login.sql


User profile script that is intended to allow users to specifically customize their session.

Prior to SQL*Plus version 12.2.0.1.0, SQL*Plus searches for the User Profile in your current directory first, and then the directories you specify with the ORACLE_PATH/SQLPATH environment variable.


From 12.2.0.1.0, SQL*Plus will only search for the User Profile in the directories you specify with the ORACLE_PATH environment variable on Linux and SQLPATH on Windows.

use SQLPATH if you are on Windows, and use ORACLE_PATH if you are on UNIX.

I have mentioned time and timing in my login.sql and for the session exported it and now the when the SQLplus connects it picks up the time from login.sql
[oracle@ebs122 ~]$ export ORACLE_PATH=/home/oracle/Music
[oracle@ebs122 ~]$ cd /home/oracle/Music
[oracle@ebs122 Music]$ cat login.sql
set time on timing on
[oracle@ebs122 Music]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 24 18:32:42 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

18:32:43 SYS@GOLD >



If you like please follow and comment