DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, December 29, 2017

To Display the Sql prompt with Database Name.


Check the SQL Prompt value:


[oracle@TESTSERVER:TESTDB] dba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 29 00:17:56 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Note : Sql prompt didn't have any database name value. So it may lead the admin to wrongly run the command under the prod database.To avoid that we can enable this feature in the following file "/wwi/wwdb/db/oracle/product/12.2.0.1/db_1/sqlplus/admin/glogin.sql ".
By default we don't have any command in this file.

Add this lines to the file:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
SET PAGESIZE 100
SET LINESIZE 200


Modify the file with the value.

[oracle@TESTSERVER:TESTDB] vi /wwi/wwdb/db/oracle/product/12.2.0.1/db_1/sqlplus/admin/glogin.sql

Check the modification:

[oracle@TESTSERVER:TESTDB] cat /wwi/wwdb/db/oracle/product/12.2.0.1/db_1/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> "
SET PAGESIZE 100
SET LINESIZE 200


Check the database prompt now:

[oracle@TESTSERVER:TESTDB] dba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 29 00:19:32 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SYS@TESTDB> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@TESTSERVER:TESTDB]

No comments: