Oracle Database 12c on Oracle Linux 6 – Step 26: Modify glogin.sql

Part of this series of posts: Oracle Database 12c on Oracle Linux 6

Type the following commands, pressing Enter after each command.

cd $ORACLE_HOME
cd sqlplus/admin/
gedit glogin.sql

The previous command will open the existing glogin.sql script.

Add the below lines to the glogin.sql file and then click Save.

set trimspool on
set long 5000
set linesize 100
set pagesize 9999
col table_name for a30
column global_name new_value gname
set termout off
define gname=idle

SELECT LOWER(user) || '@' ||
              SUBSTR ( global_name, 1, decode (dot, 0, length(global_name), dot-1)) 
             as global_name 
    from
             (select global_name, instr(global_name,'.') as dot from global_name );
set sqlprompt '&gname> '
set termout on

Select File -> Quit.

2016-02-15 Update: Laurent Schneider (laurentsch via twitter) suggested adding the col table_name for a30 which is always useful…

2015-04-10 Update:

While the above glogin.sql is really cool, it does have the side effect of not allowing Enterprise Manager Cloud Control 12c (12.1.0.4 at least) to start the database. It appears that the select statement (which of course errors out when the database is down, but since we turned off terminal output with the ‘set termout off’ and then back on with the ‘set termout on’ it really shouldn’t make a difference…) breaks EM. I’ve asked Oracle to log a bug on this.

2016-02-05 Update:

Very cool! I just tested this out with Enterprise Manager 13c and it’s fixed. I’ve got a login.sql (instead of glogin.sql) and a SQLPATH environment variable that points to it and was just able to start my database with EMCC 13c. Thanks Oracle!

Oracle Database 12c on Oracle Linux 6 – Step 25: Create the orcl and pdborcl databases < Previous Post – Next Post > Oracle Database 12c on Oracle Linux 6 – Step 27: Configure the Pluggable Database to Autostart on Reboot