61. What Is Input Buffer in SQL*Plus?
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.
62. How To Revise and Re-Run the Last SQL Command?
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.
63. How Run SQL*Plus Commands That Are Stored in a Local File?
If you have a group of commands that you need to run them repeatedly every day, you can save those commands in a file (called SQL script file), and using the "@fileName" command to run them in SQL*Plus. If you want to try this, create a file called tempinput.sql with:
SELECT 'Welcome to' FROM DUAL;
SELECT 'atoztarget.com!' FROM DUAL;
Then run the "@" command in SQL*Plus as:
SQL> connect HR/retneclgg
SQL> @tempinput.sql
'WELCOMETO
----------
Welcome to
'atoztarget.COM
--------------
atoztarget.com!
64. How To Use SQL*Plus Built-in Timers?
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
65. What Is Oracle Server Autotrace in Oracle?
Autotrace is Oracle server feature that generates two statement execution reports very useful for performance tuning:
* Statement execution path - Shows you the execution loop logic of a DML statement.
* Statement execution statistics - Shows you various execution statistics of a DML statement.
To turn on the autotrace feature, the Oracle server DBA need to:
* Create a special table called PLAN_TABLE.
* Create a special security role called PLUSTRACE.
* Grant PLUSTRACE role your user account.
66. How To Set Up Autotrace for a User Account?
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.
67. How To Get Execution Path Reports on Query Statements?
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.
68. How To Get Execution Statistics Reports on Query Statements?
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.
69. What Is SQL in Oracle?
SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.
70. How Many Categories of Data Types in Oracle?
Oracles supports the following categories of data types:
* Oracle Built-in Datatypes.
* ANSI, DB2, and SQL/DS Datatypes.
* User-Defined Types.
* Oracle-Supplied Types.
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.
62. How To Revise and Re-Run the Last SQL Command?
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.
63. How Run SQL*Plus Commands That Are Stored in a Local File?
If you have a group of commands that you need to run them repeatedly every day, you can save those commands in a file (called SQL script file), and using the "@fileName" command to run them in SQL*Plus. If you want to try this, create a file called tempinput.sql with:
SELECT 'Welcome to' FROM DUAL;
SELECT 'atoztarget.com!' FROM DUAL;
Then run the "@" command in SQL*Plus as:
SQL> connect HR/retneclgg
SQL> @tempinput.sql
'WELCOMETO
----------
Welcome to
'atoztarget.COM
--------------
atoztarget.com!
64. How To Use SQL*Plus Built-in Timers?
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
65. What Is Oracle Server Autotrace in Oracle?
Autotrace is Oracle server feature that generates two statement execution reports very useful for performance tuning:
* Statement execution path - Shows you the execution loop logic of a DML statement.
* Statement execution statistics - Shows you various execution statistics of a DML statement.
To turn on the autotrace feature, the Oracle server DBA need to:
* Create a special table called PLAN_TABLE.
* Create a special security role called PLUSTRACE.
* Grant PLUSTRACE role your user account.
66. How To Set Up Autotrace for a User Account?
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.
67. How To Get Execution Path Reports on Query Statements?
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.
68. How To Get Execution Statistics Reports on Query Statements?
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.
69. What Is SQL in Oracle?
SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.
70. How Many Categories of Data Types in Oracle?
Oracles supports the following categories of data types:
* Oracle Built-in Datatypes.
* ANSI, DB2, and SQL/DS Datatypes.
* User-Defined Types.
* Oracle-Supplied Types.
More Questions & Answers :-
No comments:
Post a Comment