Sqlplus command line tool formatting command

At work, there are often no third-party SQL tools such as sqplus developer, which must be directly queried on the server using sqlplus.

For example, to view the SGA value of the system parameter;

The execution result is as follows:

 

1. [Gysy01 @ Oracle]/Oracle> sqlplus "/
   Sysdba"
   
2. 
   
3. SQL * Plus: Release 11.1.0.7.0-production on Thursday
   February 17 17:35:16 2011
   
4. 
   
5. Copyright (c) 1982,200 8, Oracle. All rights
   Reserved.
   
6. 
   
7. 
   
8. Connected:
   
9. Oracle Database 11g Enterprise Edition Release 11.1.0.7.0-64bit
   Production
   
10. With the partitioning, real Application Clusters, OLAP, Data Mining
    
11. And real application testing options
    
12. 
    
13. SQL> show parameter SGA;
    
14. 
    
15. Name type
    
16. ----------------------------------------------------------
    
17. 
    
18. Value
    
19. ------------------------------
    
20. 
    
21. Lock_sga Boolean
    
22. False
    
23. Pre_page_sga Boolean
    
24. False
    
25. Sga_max_size big integer
    
26. 4G
    
27. Sga_target big integer
    
28. 3G
    
29. SQL>

We can see that the serial problem is very serious. In this case, you need to set the number of line characters. The following figure shows the Parameter Modification result.

 

 

1. SQL> set linesize 1000
   
2. SQL> show parameter SGA;
   
3. 
   
4. Name type value
   
5. ----------------------------------------------------------------------------------------
   
6. 
   
7. Lock_sga Boolean false
   
8. Pre_page_sga Boolean false
   
9. Sga_max_size big integer 4G
   
10. Sga_target big integer 3G
    
11. SQL>

Below are some common parameter descriptions, which use linesize, pagesize, and rowwidth parameters.

 

SQL> set colsep ''; //-domain output Separator

SQL> set echo off; // displays each SQL command in the Start script. The default value is on.

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

SQL> set feedback on; // The "XX rows selected" is displayed"

SQL> set feedback off; // display the number of records processed by this SQL command. The default value is on.

SQL> set heading off; // output domain title, on by default

SQL> set pagesize 0; // number of output lines per page. The default value is 24. To avoid paging, you can set the value to 0.

SQL> set linesize 80; // output the number of characters in a row. The default value is 80.

SQL> set numwidth 12; // The length of the output number field. The default value is 10.

SQL> set termout off; // displays the execution results of commands in the script. The default value is on.

SQL> set trimout on; // Remove trailing spaces in each row of the standard output. The default value is off.

SQL> set trimspool on; // remove the trailing space of each row from the redirection (Spool) output. The default value is off.

SQL> set serveroutput on; // set to allow display of output similar to dbms_output

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

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

Set verify off // you can close or open the prompt to confirm the display of information old 1 and new 1.

 

Export result to text:

Spool <spool_flat_file>

Example: spool D: \ spool_flatquery.txt

In this way, SQL * Plus will specify all output and on-screen commands to this file.

Execute the query output. At this time, the system does not save the result to the file, but saves it to the buffer.

After the query is complete, close the file. Command Format: spool off.