SQL*Plus and useful settings

SQL*Plus has a plenty of settings, but there are few really useful for me.
For formatting the output

• PAGESIZE - this controls the numbers of rows displayed between displaying headers. Usually I set it to 1000 (which means that I have got headers only once) and for some scripts for 0 (which means no headers at all)
• LINESIZE - this controls the line width in characters in which a row is fit in. Default 80 fits to default terminal settings. The best shape of data for me is the model 1 row per line, so I often modify this to get such result
• TRIMOUT - for cutting off unnecessary spaces filling lines
• COL FOR a - for formatting character columns
• COL FOR 999999 - for formatting number columns
• LONG - for setting how much of LOBs to show on the display - very useful especially with DBMS_METADATA.GET_DDL calls
• HEAD - for turning on and off the headers - important for scripts, when we want some values from within the database - then sqlplus -s /nolog with CONNECT command

For testing purposes

• FEEDBACK - for turning on and off a message at the end of display summarizing the number of affected rows or PL/SQL command status (SUCCESS|FAILURE)
• TIME - enables time in sqlprompt, which may be used as a marker for script performance
• TIMING - returns information about performance time for operations

For scripts creating scripts

• SQLPROMPT - cool for identifying terminals, when one works on plenty of environments (especially when some of them are production ones), but even cooler as "---> " when it comes to the creation of SQL scripts - prompt messages in the spool are seen as comments

For executing scripts

• TERMOUT - this works only if one calls commands from underlying script - very important for long-running scripts with plenty of output with enabled spooling, when on terminal output we need only command and information about success or error messages.
• SPOOL - for logging
• TRIMSPOOL - useful especially with connection to wide LINESIZE - in order to remove unnecessary spaces (with them logs become very large)
• SERVEROUTPUT - for turning on|off printing from PL/SQL to screen
• ECHO - turns on|off the lines with replaced &variables

Some details about bind section of the 10046 event trace

In general very good description of the 10046 event trace interpretation is contained on MOS as the article with 39817.1 id.
However with time there showed new entries not really mentioned in the article indicated above.
Let's see an example of the bind section:

Bind#2
oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=02 csi=2000 siz=0 off=56
kxsbbbfp=7fb04313fe80 bln=32 avl=02 flg=01
value=0 54

oac prefix I simply ignore and not sure what it stands for.

• dty is an id for data type
• mxl is a maximum length of bind variable (in parentheses there is provided a private maximum length - for me this is current bind variable length)
• mxlc is not sure for me, but I saw it always equal to 00
• mal is a maximum array length
• scl stands for scale
• pre stands for precision
• flg is a flag with bind options
• fl2 is a continuation of flag
• frm stays unknown
• csi is character set id, which can be decrypted with nls_charset_name function; the nls_charset_id function, working in an opposite way, is available as well
• siz is an amount of memory allocated for this chunk
• off is a memory offset of a buffer for this variable within the chunk
• kxsbbbfp is a bind addres
• bln is a bind buffer length and I suppose usually it is equal to mxl, unless data does not fit into one chunk
• avl is a current bind value length (or array length)
• flg is another flag (this time for bind status)
• value - finally current value