Use Oracle SQL * Plus to analyze SQL

Use Oracle SQL * Plus to analyze SQL

I. Analyze and execute SQL statements

SQL> set autotrace on; Description: Enables automatic analysis statistics and displays the running results of SQL statements.

SQL> set autotrace traceonly; Note: Enable Automatic Analysis Statistics without displaying the running results of SQL statements. Then you can run the test SQL statement to see the analysis statistics. Generally, our SQL statement should avoid full table scanning for large tables.

SQL> after you enter an SQL statement, you must enter a semicolon (;). View the secondary items

SQL> set autotrace off; Description: Disable Automatic Analysis Statistics.

SQL> Set feed [Back] {6 | n | on | off}: whether to display the number of rows queried or modified by the current SQL statement. By default, only rows with more than 6 results are displayed. If set feedback 1 is returned, no matter how many rows are queried. When it is off, the number of queried rows is not displayed.

SQL> set time on; Description: SQL> set timing {on | off} shows the execution time of each SQL statement.

Ii. Common auxiliary functions

1. Execute an SQL script file (this method is usually used to edit the SQL statement using other editing tools)

SQL> Start file_name or SQL> @ file_name such as: Start C:/SQL .txt we can save multiple SQL statements in one text file, in this way, when you want to execute all the SQL statements in this file, you can use any of the following commands, which is similar to batch processing in DOS.

2. Output all content on the screen to the specified file, including the SQL statement and analysis you entered. (Facilitate analysis by other tools, such as utlraedit)

SQL> spool C:/analysis.txt

... Done a lot of operations

SQL> spool off is equivalent to file.close(), and all internal volumes are output to analysis.txt

3. Edit the current input.

SQL> Edit

4. re-run the last SQL statement SQL>/

5. Set whether the current session will automatically submit the modified data

SQL> set auto [Commit] {on | off | Imm [ediate] | n}

6. Set the number of characters that a row can hold

SQL> set LiN [esize] {80 | n}

7. Set the number of rows on a page. SQL> set pages [ize] {24 | n}

8. display the structure of a table SQL> DESC table_name

9. SQL> HOST: Enter cmd to run commands in the executable operating system.

10. Stored Procedures execute procedure_name

11. Connect to the specified database connect user_name/passwd @ db_alias in the specified SQL * Plus

12. Ask for help if you have any questions. The help of the SQL * Plus command is displayed.

How to install the Help file:

SQL> @? // Sqlplus // admin // help // hlpbld. SQL? // Sqlplus // admin // help // helpus. SQL

SQL> Help Index; displays all help projects

 

Related Words:

SQL Buffer: (Save the buffer for the last executed SQL * Plus statement) in SQL * Plus, you can run the SQL * Plus command and SQL * Plus statement. The DML, DDL, and DCL statements we usually call are SQL * Plus statements. After they are executed, they can be stored in a memory area called SQL Buffer, in addition, only one recently executed SQL statement can be saved. We can modify the SQL statement stored in SQL buffer and execute it again. SQL * Plus generally deals with databases.