Mir Sayeed Hassan – Oracle Blog

Set up your own SID name by connecting Sql*Plus in Oracle database

Below example will change the Sql*Plus view & display your DB Name (SID) by connecting username instead of word “SQL”

By default in all the Oracle database as shown below:

SQL> show user;
USER is “SYS”

You need to modify the file “glogin.sql” which exit in default location $ORACLE_HOME/sqlplus/admin

[oracle@testdb-local admin]$ cd $ORACLE_HOME/sqlplus/admin

[oracle@testdb-local admin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin

[oracle@testdb-local admin]$ ls
glogin.sql  help  libsqlplus.def  plustrce.sql  pupbld.sql

Add the below command in “glogin.sql”

set termout off
define gname=testdb
column global_name new_value gname
select lower(user)||’@’ ||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name, instr(global_name,’.’) dot from global_name);
set sqlprompt ‘&gname>’
set termout on

[oracle@oracleTestServer admin]$ vi glogin.sql

[oracle@oracleTestServer admin]$ cat glogin.sql
-- Copyright (c) 1988, 2011, Oracle and/or its affiliates.
-- 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
---- The below command are added by mir to change the default sqlplus name to our own database name
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@' ||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name, instr(global_name,'.') dot from global_name);
set sqlprompt '&gname>'
set termout on

Now add the alias to the given location as vi .bash_profile file by using the below command as an alias for sqlplus / as sysdba

[oracle@testdb-local ~]$ vi .bash_profile
## The command is added by mir to update the alies from the sql to own own database name
alias sqlplus=’export ORACLE_SID=TESTDB; sqlplus “/ as sysdba”‘
:wq!

[oracle@ogg-test1 ~]$ . .bash_profile

Next time when you try to login to sqlplus, you will connect with database as “TESTDB” — Prompt will be as SYS@TESTDB>

[oracle@testdb-local ~]$ !sq
sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 15:42:30 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@TESTDB>

sys@TESTDB> sho user;
USER is "SYS"

It also shows your login username as shown below:

sys@TESTDB> conn mir/mir123;
Connected.

mir@TESTDB>

============Hence change the display name of default SQL to Its own DB Name==============