Several common set statements in Oracle Database SQLPLUS

When writing scripts, we often use the set statement to set some required format specifications. The following describes the application of several common set statements.

SQL> set echo on ----------------- set whether to display the statement when running the command

SQL> set feedback on ---------------- set to show "XX rows selected"

SQL> set colsep | ----------------- sets the delimiter between a column and a column.

SQL> set pagesize 10 ---------------- set the number of rows per page

SQL> SET SERVEROUTPUT ON ----------- SET to allow display of output similar to dbms_output.putline

SQL> set heading on ---------------- set the display column name

SQL> set timing on ----------------- set to display "time in use: XXXX"

SQL> set time on ------------------ set to display the current time

SQL> set autotrace on ---------------- set to allow analysis of executed SQL statements

00:38:28 SQL> set echo on

00:38:37 SQL> @ E: echo. SQL

00:38:41 SQL> select status from v $ datafile where rownum <6;

STATUS

-------

SYSTEM

ONLINE

ONLINE

ONLINE

ONLINE

Five rows have been selected.

Used time: 00: 00: 01.08

00:38:43 SQL> set echo off

00:38:58 SQL> @ E: echo. SQL

STATUS

---

SYSTEM

ONLINE

ONLINE

ONLINE

ONLINE

Five rows have been selected.

Used time: 00: 00: 01.09

00:39:08 SQL>

SQL> set feedback on

SQL> set colsep | SQL> set pagesize 10

SQL> set serveroutput onSQL> set heading on

SQL> set timing on

SQL> set time on00: 54: 11

SQL> SELECT FILE #, STATUS, ENABLED FROM V $ DATAFILE;

FILE # | STATUS | ENABLED ---- | --- | ----

1 | SYSTEM | READ WRITE

2 | ONLINE | READ WRITE

3 | ONLINE | READ WRITE

4 | ONLINE | READ WRITE

5 | ONLINE | READ WRITE

6 | ONLINE | READ WRITE

7 | ONLINE | READ WRITE

FILE # | STATUS | ENABLED ---- | --- | ----

9 | ONLINE | READ WRITE

10 | ONLINE | READ WRITE

11 | ONLINE | READ WRITE

12 | ONLINE | READ WRITE11 rows selected.

Elapsed: 00:00:01. 0700: 54: 34

SQL> DECLARE

00:55:00 2 BEGIN

00:55:00 3 DBMS_OUTPUT.PUT_LINE ('= This is dbms_output.put_line! = ');

00:55:00 4 END;

00:55:02 5/

=== This is dbms_output.put_line! ===

PL/SQL procedure successfully completed.

Elapsed: 00:00:00. 0200:55:05 SQL>

SQL> set autotrace on

SQL> SELECT E. LAST_NAME, D. DEPARTMENT_NAME, D. LOCATION_ID

2 from hr. EMPLOYEES E

3 join hr. Orders ments D

4 USING (DEPARTMENT_ID );

LAST_NAME DEPARTMENT_NAME LOCATION_ID

-----------------------

King Executive 1700

Kochhar Executive 1700

De Haan Executive 1700

Hunold IT 1400

Ernst IT 1400 ......

106 rows selected.

Execution Plan

--------------------

0 select statement Optimizer = CHOOSE (Cost = 5 Card = 106 Bytes = 318

0)

1 0 hash join (Cost = 5 Card = 106 Bytes = 3180)

2 1 table access (FULL) OF 'hangzhous' (Cost = 2 Card = 27 Byt

Es = 513)

3 1 table access (FULL) OF 'ployees' (Cost = 2 Card = 107 Byte

S = 1177)

Statistics

--------------------

7 recursive cballs

0 db block gets

23 consistent gets

12 physical reads

0 redo size

2801 bytes sent via SQL * Net to client

580 bytes encoded ed via SQL * Net from client

9 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

106 rows processed

SQL>