How to know what SETS are ON in SQL PLUS?

1.2k views Asked by At

Is there a way in SQL Plus to know the state of all the SET commands?

For example whether or not WRAP is set to OFF or to ON, etc..

I've searched online and I couldn't find anything. I also thought it might be something similar to the SET from CMD, but if you try this:

SET

You get:

SP2-0545: SET command requires an argument.

How can I get the value of all the current SETs?

In case I am not being clear, I want to know if there is a command similar to this:

SHOW SETS

That will return something like

WRAP ON 
LONG 3000
LINESIZE 344
VERIFY OFF
etc..
1

There are 1 answers

0
MT0 On BEST ANSWER

You can use the SHOW command:

  • SHOW ALL - lists the settings of all SHOW options, except ERRORS and SGA, in alphabetical order.
  • SHOW PARAMETERS or SHOW PARAMETERS parameter_name - displays the current values for one or more initialization parameters; without any string following the command it displays all initialization parameters. You need SELECT ON V_$PARAMETER object privileges to use the PARAMETERS clause.
  • SHOW SPPARAMETERS or SHOW SPPARAMETERS parameter_name - As for SHOW PARAMETERS except this displays current values for initialization parameters for all instances.

There are also other options associated with SHOW including (see the link above for further options):

  • SHOW ERRORS or SHOW ERRORS {FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} SCHEMA.NAME - Shows the compilation errors of a stored procedure (includes stored functions, procedures, and packages). When you specify SHOW ERRORS with no arguments, SQL*Plus shows compilation errors for the most recently created or altered stored procedure. When you specify the type and the name of the PL/SQL stored procedure, SQL*Plus shows errors for that stored procedure.
  • SHOW SGA - displays information about the current instance's System Global Area. You need SELECT ON V_$SGA object privileges.
  • SHOW RECYCLEBIN - Shows objects in the recycle bin that can be reverted with the FLASHBACK BEFORE DROP command.