SQL*Plus Customize User Profile

Problem Definition:

DBA’s usually works on many stage and production database using SQL*Plus every day. To execute any command DBA’s need to always check in which database they are working on and they need to make sure that they are executing a command in right database. Moreover, DBA’s need to know sometimes which time they have executed the command and how long it takes to execute the command. When there are many instances in a server then DBA’s also need to know which instance they are log in. All of the above requirement can be check by DBA’s but it takes time. DBA's might do mistake by executing a command in wrong database since SQL prompt by default looks similar in all database. It will be useful for DBA's if they can see the user information and connection identifier in SQL command Prompt.

Solution:

A customize user profile can save DBA’s time and it will help them to see which database they are working on along with user. so It will reduce the risk to execute a command in wrong database.

Example:

Here the user name is "library" and the Connect Identifier is university

[oracle@server ~]$ sqlplus library

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 17 14:41:46 2014

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Session altered.

library@university 02:41:50> select count(*) from BOOK_LIST;

COUNT(*)

----------

725592

Elapsed: 00:00:00.05

Note: This login.sql file can be use by any user . The user need to put that file in their home directory.

How:

Create login.sql file in oracle user Home Directory. You can customize your session by setting parameter in login.sql fike.

-- login.sql

-- SQL*Plus user login startup file

-- This script is automatically run after glogin.sql

/* Description:

SQL*Plus command-line supports a User Customize Profile Script. In this script user can customize their session. This Customize script generally named login.sql.

This script should be located in user home directory. WHen SQL*PLUS start, it automatically searches for the user profile and executes the commands it contains.

A user profile is not used in iSQL*Plus.

-- Set the database date format to show time

ALTER SESSION SET nls_date_format = 'HH:MI:SS';

-- Set USER,CONNECT_IDENTIFIER,DATE

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "

-- Set timing on to see query execution time

SET timing on;