Oracle Database

Input buffer is a nice feature of the command-line SQL*Plus tool. It allows you to revise a multiple-line command and re-run it with a couple of simple commands. By default, input buffer is always turned on in SQL*Plus. The last SQL statement is always stored in the buffer. All you need is to remember to following commonly used commands:

* LIST - Displays the SQL statement (the last executed SQL statement) in the buffer.

* RUN - Runs the SQL statement in the buffer again. ";" is a quick command equivalent to RUN.

* CLEAR BUFFER - Removes the SQL statement in the buffer.

* INPUT line - Adds a new line into the buffer.

* APPEND text - Appends more text to the last line in the buffer.

* DEL - Deletes one line from the buffer.

* CHANGE /old/new - Replaces 'old' text with 'new' text in the buffer.

If executed a long SQL statement, found a mistake in the statement, and you don't want enter that long statement again, you can use the input buffer commands to the correct last statement and re-run it. The following tutorial exercise gives you a good example:

SQL> connect HR/retneclgg

SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE

2 FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%';

FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%'

*

ERROR at line 2:

ORA-00942: table or view does not exist

SQL> LIST

1 SELECT FIRST_NAME, LAST_NAME, HIRE_DATE

2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'

SQL> CHANGE /EMPLOYEE/EMPLOYEES/

2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'

SQL> RUN

(Query output)

SQL> INPUT ORDER BY FIRE_DATE

SQL> LIST

1 SELECT FIRST_NAME, LAST_NAME, HIRE_DATE

2 FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%'

3* ORDER BY HIRE_DATE

SQL> RUN

(Query output)

SQL> CLEAR BUFFER

buffer cleared

SQL> LIST

SP2-0223: No lines in SQL buffer.

If you don't have a stopwatch/timer and want to measure elapsed periods of time, you can SQL*Plus Built-in Timers with the following commands:

* TIMING - Displays number of timers.

* TIMING START [name] - Starts a new timer with or without a name.

* TIMING SHOW [name] - Shows the current time of the named or not-named timer.

* TIMING STOP [name] - Stops the named or not-named timer.

The following tutorial exercise shows you a good example of using SQL*Plus built-in timers:

SQL> TIMING START timer_1

(some seconds later)

SQL> TIMING START timer_2

(some seconds later)

SQL> TIMING START timer_3

(some seconds later)

SQL> TIMING SHOW timer_1

timing for: timer_2

Elapsed: 00:00:19.43

(some seconds later)

SQL> TIMING STOP timer_2

timing for: timer_2

Elapsed: 00:00:36.32

SQL> TIMING

2 timing elements in use

If an Oracle user wants to use the autotrace feature, you can use the tutorial as an example to create the required table PLAN_TABLE, the required security role PLUSTRACE, and grant the role to that user:

SQL> CONNECT HR/retneclgg

SQL> @oraclexeapporacleproduct10.2.0server

RDBMSADMINUTLXPLAN.SQL

Table (HR.PLAN_TABLE) created.

SQL> CONNECT / AS SYSDBA

SQL> @C:oraclexeapporacleproduct10.2.0server

SQLPLUSADMINPLUSTRCE.SQL

SQL> drop role plustrace;

Role (PLUSTRACE) dropped.

SQL> create role plustrace;

Role (PLUSTRACE) created.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> GRANT PLUSTRACE TO HR;

Grant succeeded.

Remember that PLAN_TABLE table must be created under the user schema HR.

If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON EXPLAIN" command to turn on execution path reports on query statements. The tutorial exercise bellow shows you a good example:

SQL> CONNECT HR/retneclgg

SQL> SET AUTOTRACE ON EXPLAIN

SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE

2 FROM EMPLOYEES E, JOBS J

3 WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;

LAST_NAME   SALARY JOB_TITLE

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

King    24000 President

Kochhar   17000 Administration Vice President

De Haan   17000 Administration Vice President

Russell   14000 Sales Manager

Partners   13500 Sales Manager

Hartstein   13000 Marketing Manager

6 rows selected.

If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON STATISTICS" command to turn on execution statistics reports on query statements. The tutorial exercise bellow shows you a good example:

SQL> CONNECT HR/retneclgg

SQL> SET AUTOTRACE ON STATISTICS

SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE

2 FROM EMPLOYEES E, JOBS J

3 WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;

LAST_NAME   SALARY JOB_TITLE

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

King    24000 President

Kochhar   17000 Administration Vice President

De Haan   17000 Administration Vice President

Russell   14000 Sales Manager

Partners   13500 Sales Manager

Hartstein   13000 Marketing Manager

6 rows selected.