How to Change SQL Plus Settings

by Apurva T. on April 3, 2013

in Oracle Database

I do not like the default size of the SQL *Plus window and prefer to customize it as per my preference. You can also do the same by changing the default parameters of SQL Plus.

You can perform the following changing in SQL *Plus:

  • Change cursor size
  • Change command history
  • Change fonts
  • Change layout
  • Change colors
  • Other minor changes

Have a look at my SQL *Plus window and you will observe that it is pretty different than what is delivered. This is because I’ve customized it to my taste.

change sql plus settings

Changing SQL Plus settings is quite simple. All you need to do is launch SQL Plus and right click the top bar -> Defaults. Now you will see options to change SQL Plus settings. You can play around with the settings and close the current SQL Plus session to launch anew with the new settings.

You also have the option to change these settings to be a permanent one or just for your current session. You can make the changes in your global profile so that the changes are permanent or otherwise, you can run the same commands in your current session to get the desired changes effective. Login file is located at C:\Oracle_Home\Home\sqlplus\admin\glogin.sql (Note that Oracle_Home will be different as per install in your workstation)

Some of the parameters you will find useful are as follows:

You can modify your LOGIN file just as you would any other script. You may wish to add some of the following commands to the LOGIN file:

Also Read:  PeopleSoft Performance Tuning Using OEM

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.

In Oracle, the default page and line size settings for SQL *Plus are 14 lines per page and 80 characters per line respectively. This can make viewing large result sets difficult when using SQL *PLus.

Fortunately, these settings can easily be changed using the SET command:

set linesize 1000
set pagesize 1000

Executing these commands however only changes the settings for the current instance of SQLPlus. The next time you run it, the values default back to their original values. If these settings are placed in a file calledlogin.sql in the current working directory however, they will be loaded when SQLPlus starts.

 

Also, refer to the Oracle SQL *Plus documentation for further information you may need.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: