2.4 How do I…View and modify SQL*Plus system variables?Problem
I know there are many SQL*Plus system variables and that these variables control the behavior of SQL*Plus. I often need to see the value of a variable and, if necessary, change it. How do I view and modify the values of the SQL*Plus system variables?
Technique
You can use the SHOW command to view the values of SQL*Plus system variables. The ALL keyword makes SQL*Plus show the value of all 68 system variables. System variables are changed with the SET command. Any system variable can be changed, and it will retain its new value throughout the SQL*Plus session.
Steps
1. View the values of the system variables by executing the SHOW ALL command. The trailing portion of the output is shown in Figure 2.9.
2. Display the value of a single system variable by replacing the ALL keyword with the name of the variable to display.
SQL> SHOW FLUSH
flush ON
3. Change the value of the FLUSH system variable with the SET command. The FLUSH system variable can have the values ON or OFF. To change the value to OFF, execute the SET command specifying the system variable and the new value.
SQL> SET FLUSH OFF
SQL>
How It Works
The SHOW and SET commands are the two commands used to maintain SQL*Plus system variables. Most system variables are restricted to specific values. If an attempt is made to change a system variable to an invalid value, an error is returned. Step 1 uses the SHOW ALL command to display all 68 system variables. Step 2 specifies a single system variable to display using the SHOW command. Step 3 uses the SET command to change the value of a system variable.
Comments
When the values of SQL*Plus system variables are changed, they are only changed for the duration of the current session. If SQL*Plus is exited, all the system variables will return to their default settings the next time SQL*Plus is started. If there are certain settings you want to change every time SQL*Plus is used, you can place them in a file and execute with the START command.