How to customize your SQLPLUS utility

In this article we will sew how to customize our sqlplus environment.

This can be of good use when working with more systems in order for you to identify your current session.

Sqlplus environment can be customized using 2 files login.sql and glogin.sql, file in $ORACLE_HOME/sqlplus/admin. Normaly they are empty or commented.

  • Example of a customized glogin.sql file.

    • Just add the following lines to your glogin.sql file and save it.

    ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH:MI:SS';
-- SET the SQLPROMPT to include the _USER, _CONNECT_IDENTIFIER
-- and _DATE variables.
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
SET PAGESIZE 24
set linesize 159;
column name format a30
column db_unique_name format a30
column database_role format a30
column open_mode  format a30
column HOST_NAME format a30
select name,db_unique_name,database_role,open_mode from v$database;
select inst_id,instance_name,status,HOST_NAME,startup_time from gv$instance;

    Now test your script by using your sqlplus tool

      [oracle@DCG023 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 11 14:49:38 2014
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.


NAME                           DB_UNIQUE_NAME                 DATABASE_ROLE                  OPEN_MODE
------------------------------ ------------------------------ ------------------------------ ------------------------------
EMR                            EMR                            PRIMARY                        READ WRITE


   INST_ID INSTANCE_NAME    STATUS       HOST_NAME                      STARTUP_TIME
---------- ---------------- ------------ ------------------------------ --------------------
         1 EMR              OPEN         DCG023                         26-JAN-2014 09:47:00

SYS@EMR>

    See that our prompt is identified with the user@instance names, you get other details listed at the start of your session as well.

    Copyright 2020. All Rights Reserved