SQLplus user profile; showing connection name in the sqlplus prompt

SQLplus user profile is maintained in the file glogin.sql which is executed at the time of login in to a database connection. The default location of this file is

 $ORACLE_HOME/sqlplus/admin/

Usually when a query is executed through sqlplus terminal, at first it tries to fetch profile form the current directory, if no there in that case from the default location.

Suppose, by defualt if we connect to a database using  sqlplus, it dones not show the connected database information in the screen.  It only prompts with: 

sqlplus>

Now if we want to  show the connected database information in the sqlplus command line as:

username@db_connect_name>

In that case, we cas easily do that by modification in the glogin file as below:

set sqlprompt "_user '@' _connect_identifier >"



There are some other keywords for using in glogin file as:

SET LINESIZE	Followed by a number, sets the number of characters as page width of the query results.
SET NUMFORMAT	Followed by a number format (such as $99,999), sets the default format for displaying numbers in query results.
SET PAGESIZE	Followed by a number, sets the number of lines per page.
SET PAUSE	Followed by ON, causes SQL*Plus to pause at the beginning of each page of output (SQL*Plus continues scrolling after you enter Return). Followed by text, sets the text to be displayed each time SQL*Plus pauses (you must also set PAUSE to ON).
SET SQLPROMPT	Followed by the connect information variable in the form: SET SQLPROMPT '&_CONNECT_IDENTIFIER > ' changes the SQL*Plus command-line prompt to display the SID of the database you are connected to.
SET TIME	Followed by ON, displays the current time before each command prompt.

Get elapsed time for an individual command

In sqlplus, to see the elapsed time for an individual query, we can use the "set timing on" command.

SQL> set timing on;

SQL> select surname from personal_details where firstname='udvas';

Elapsed: 00:00:02.52

Again, the "set timing on" command is a SQL*Plus command, but we can measure run time for Oracle SQL with a variety of Oracle tools.

Sometimes when working on SQL command optimizations, all that is desired is a rough timing estimate; namely, the SQL*Plus client elapsed execution time, or simple clock time. Often that simple metric is sufficient for some very basic tuning needs. SQL*Plus has a built-in capability to do exactly this - it is the SET TIMING  command. It essentially records the clock time before and after the SQL command execution, then displays the run time difference.

This commands works for the single command.

SQL Plus || Changing the Prompt to show connected user and database

Amend your $ORACLE_HOME\sqlplus\admin\glogin.sql script - add:

set sqlprompt "_user '@' _connect_identifier > "

to the end of the file.

In Oracle 10g this will change correctly each time you issue a "conn". For clients before 10g it won't change when you do a "conn" but will remain as the username/db you first connected to.

You can also use _date for the current date and _privilege for the privilege (eg SYSDBA) of the connected user.