Oracle sqlplus set Login.sql tips _oracle

Sqlplus automatically runs two scripts at startup: Glogin.sql, login.sql these two files

The order of execution is

1. Default to find Glogin.sql file execution under $oracle_home/sqlplus/admin path

2. Default in the current path to find Login.sql file execution, if not found the corresponding file is executed 3

3. Determine whether to set the SQLPATH environment variable, if the variable is set to find and execute under the corresponding path, not found to stop the lookup

You can add some common settings to the Login.sql file to make it easier to use Sqlplus, and the following tests are performed on Oracle 11.2.0.1.0

Glogin.sql file exists under default $oracle_home/sqlplus/admin path

The current user's profile file sets the SQLPATH environment variable export sqlpath= $ORACLE _home/sqlplus/admin: $SQLPATH

Add our own Login.sql file under the default path, the following configuration is a common configuration

--sqlplus The default editor is set to VI
define _EDITOR=VI-
-open Dbma_output by default, so it is not necessary to enter this command every time and set the default buffer pool as large as possible
Serveroutput on size 1000000
--when spooling out text, the spaces on both ends of the text line are stripped, and the line width is variable, if set to OFF (the default setting), the text line width of the spool output is equal to the set of linesize sets
Trimspool
on--Sets the default number of bytes to display when selecting long and CLOB columns set
long 5000
--Sets the displayed text width to 200 characters set
linesize
Set Sqlplus how often to print a title, and set this parameter larger so that each page displays only one header
set pagesize 9999
--Sets Autotrace the default width for interpreting the planned output, typically 80 enough to lay down the whole plan
Column plan_plus_exp format A80
--set Sqlplus prompt, display format as user @ Database name
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

After using the Scott user login, the following

[Oracle@rhel65 ~]$ sqlplus scott/oracle sql*plus:release 11.2.0.1.0 on Sat off Production 00:11:26 Copyright (c) 1982, 2009, Oracle.
All rights reserved. Connected to:oracle Database 11g Enterprise Edition release 11.2.0.1.0-64bit Production with the partitioning, OLAP, Da
Ta Mining and real application testing options Scott@orcl>select * from EMP; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----------------------------------------------------------------------- -----------------7369 SMITH clerk 7902 1980-12-17 00:00:00 7499 ALLEN salesman 7698 1981-02-20 00:00:00 1600 300  7521 WARD salesman 7698 1981-02-22 00:00:00 1250 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975-7654 MARTIN Salesman 7698 1981-09-28 00:00:00 1250 1400 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850-7782 CLARK Manager 783 9 1981-06-09 00:00:00 2450 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 7844 TURNER SALESman 7698 1981-09-08 00:00:00 1500 0 7876 ADAMS clerk 7788 1987-05-23 00:00:00 1100 JAMES 7900 7698 1981-12-03 00:00:00 950 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 7934 MILLER clerk 7782 1982-01-23 00:00:00 1300 10 14
Rows selected.
Scott@orcl>set Autot trace Exp stat;
Scott@orcl>select * from EMP;
Rows selected. Execution Plan----------------------------------------------------------The plan hash value:3956160932--------------- -----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time | --------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS full| EMP | 14 | 532 | 3 (0) |
00:00:01 | --------------------------------------------------------------------------Statistics--------------------------- -------------------------------0 Recursive calls 0 DB block gets 8 consistent gets 0 physical reads 0 redoSize 1630 bytes sent via sql*net to client 524 bytes via received from Client 2 sql*net sql*net roundtrips Client 0 sorts (memory) 0 sorts (disk) rows processed scott@orcl>

You can see the Sqlplus follow the Login.sql file after you sign up

The above is a small set to share the Oracle Sqlplus settings Login.sql skills, hope to help.