Oracle ---- SQL * plus (2)

Display and set Environment Variables
Two methods
Use SQL * plus tool: option --- Environment
Command Line: SQL> show linesize autocommit
SQL code
Linesize 80
Autocommit OFF
SQL> set linesize 200 autocommit on
(1) display all environment variables
SQL code
Show all
(2) arraysize
This variable is used to set the number of rows extracted from the database. The default value is 15. The larger the value, the lower the network overhead and the larger the memory usage.
SQL code
SQL> show arraysize
Arraysize 15
SQL> set arraysize 20
SQL>
(3) autocommit
This variable is used to set whether to automatically submit dml statements. When it is set to on, it is automatically submitted every time the user executes the dml operation.
SQL code
SQL> show autocommit
Autocommit OFF
SQL> set autocommit on
SQL>
(4) colsep
This variable is used to set the delimiter between selected columns. The default value is space.
SQL code
SQL> show colsep
Colsep ""
SQL> set colsep ""
SQL>
(5) freeback
This variable is used to specify the minimum number of rows for display feedback. The default value is 6. If you want to disable the display of the number of rows, set feedback to off. If you want to return the number of rows as long as there is a returned result, you can set the environment variable value to 1.
SQL code
SQL & gt; select empno, ename from emp where empno = 7844;
 
EMPNO ENAME
--------------------
7844 TURNER
 
SQL> set freedback 1
SQL> set feedback 1
SQL & gt; select empno, ename from emp where empno = 7844;
 
EMPNO ENAME
--------------------
7844 TURNER
 
Select 1 line.
 
SQL>
 
(6) heading
This variable is used to set whether to display the title (field name). The default value is on. If the column title is not displayed, it is set to offSQL> set heading off.
SQL code
SQL & gt; select empno, ename from emp where empno = 7844;
 
7844 TURNER
 
Select 1 line.
 
SQL> set heading on
SQL & gt; select empno, ename from emp where empno = 7844;
 
EMPNO ENAME
--------------------
7844 TURNER
 
Select 1 line.
 
SQL>
(7) linesize
This parameter is used to set the row length. The default value is 80 SQL> show linesize;
SQL code
Linesize 80
SQL> set linesize 200
SQL & gt; select * from emp where empno = 7844;
 
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-----------------------------------------------------------------------------------
7844 turner salesman 7698 month-81 1500 0 30
 
Select 1 line.
 
SQL>
 
(8) long
This variable is used to set the display length of the long and lob types. The default value is 80. That is, when querying the long or lob columns, only the first 80 characters of the column are displayed. SQL> show long
SQL code
Long 80
SQL> set long 100
SQL>
(9) pagesize
This variable is used to set the number of lines displayed on each page. The default value is 14.
SQL code
SQL> show pagesize
Pagesize 14
SQL> set pagesize 20
(10) serveroutput
This variable is used to set whether to display the output result of running the dbms_output.put_line command.
SQL code
SQL> exec dbms_output.put_line ('hello ')
 
The PL/SQL process is successfully completed.
SQL> set serveroutput on
SQL> exec dbms_output.put_line ('hello ')
Hello
 
The PL/SQL process is successfully completed.
 
SQL>
 
 
(11) time
This variable is used to set whether to display the time before the SQL * plus command prompt. The default value is off.
SQL code
SQL> show time
Time OFF
SQL> set time on
14:52:13 SQL> set time off
SQL>
(12) timing
This variable is used to set whether to display the SQL statement execution time. The default value is off.
SQL code
SQL> set timing on
SQL> select avg (sal) from emp;
 
AVG (SAL)
----------
2073.21429
 
Select 1 line.
 
Used time: 00: 00: 00.01
SQL>
 

Other common commands
<! -- [If! SupportLists] --> (1) <! -- [Endif] --> display table structure
Use the desc [ribe] command to display the structure information of any table.
SQL code
SQL> desc dept;
Is the name empty? Type
-----------------------------------------------------------------------------
Deptno not null number (2)
DNAME VARCHAR2 (14)
LOC VARCHAR2 (13)
2. Save the SQL * plus screen content to a file
Use spool command
SQL code
SQL> spool off
SQL> spool c: \ spool.txt
SQL> select * from dept where deptno = 10;
 
DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
 
Select 1 line.
 
Used time: 00: 00: 00.01
SQL> spool off;
SQL>
 
(3) clear the screen clear screen
SQL code
Clear screen
4. Help commands
SQL code
SQL> help describe
 
DESCRIBE
--------
 
Lists the column definitions for a table, view, or synonym,
Or the specifications for a function or procedure.
 
DESC [RIBE] {[schema.] object [@ connect_identifier]}
 
 
SQL>

Author: ln-ydc"