SQL * Plus set login. SQL Logon Settings

Source: Internet
Author: User

When using SQL * Plus, sqlplus may require set pagesize \ set linesize \ set serveroutput every time. For convenience, you can use the login. SQL method provided by Oracle to set custom parameters in login. SQL and automatically call each time you open SQL * Plus. Instead of setting it manually each time.

You can copy the login. SQL file to the current directory of the oracle user (home/oracle) or set the SQLPATH environment variable to point to the directory where login. SQL is located.

When using other parameters, You can manually add them to the login. SQL file.

Define _ editor = vi
Set serveroutput on size 1000000
Set trimspool on
Sets long 5000
Set linesize 100
Set pagesize 9999
Column plan_plus_exp format a80
Column global_name new_value gname
Set termout off
Define gname = idle
Column global_name new_value gname
Select lower (user) | '@' | substr (global_name, 1, decode (dot, 0, length (global_name), dot-1) global_name
From (select global_name, instr (global_name, '.') dot from global_name );
Set sqlprompt '& gname>'
Set termout on


Appendix: Some parameter descriptions in the login. SQL file:

1. define _ editor = vi: sets the Default editor used by SQL * Plus.

2. set serveroutput on size 1000000: this will open DBMS_OUTPUT by default (so you do not have to type this command every time ). In addition, the default buffer size setting is only possible to be large.

3. set trimspool on: when the text is output offline, spaces at both ends of the text line are removed, and the line width is not fixed. If it is set to off (default ), the width of the text line output from offline is equal to the linesize.

4. set long 5000: set the default number of bytes displayed when LONG and CLOB columns are selected.

5. set linesize 100 set the text displayed in SQL * Plus to 100 characters in width

6. set pagesize 9999: pagesize can control how often SQL * Plus prints the title.

7. column plan_plus_exp format a80: Set the default width of explain Plan output from AUTOTRACE.


Define gname = idle

Column global_name new_value gname

Select lower (user) | '@' | substr (global_name, 1, decode (dot, 0, length (global_name), dot-1) global_name

From (select global_name, instr (global_name, '.') dot from global_name;

Set sqlprompt '& gname>'

Set termout on

The column global_name new_value gname Command tells SQL * Plus to obtain the last VALUE IN THE global_name column, assign this value to the replace variable gname, and then select global_name from the database, and connect to my login username. The SQL * Plus prompt is user_analysis @ ORA9I> to know who the current user is and the SID of the connected database.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.