Oracle PL/SQL Tutorial/SQL PLUS Session Environment/SHOW ALL

SHOW ALL displays all parameters

   <source lang="sql">

SQL> SQL> Show all appinfo is OFF and set to "SQL*Plus" arraysize 15 autocommit OFF autoprint OFF autorecovery OFF autotrace OFF blockterminator "." (hex 2e) btitle OFF and is the first few characters of the next SELECT statement cmdsep OFF colsep " " compatibility version NATIVE concat "." (hex 2e) copycommit 0 COPYTYPECHECK is ON define "&" (hex 26) describe DEPTH 1 LINENUM OFF INDENT ON echo ON editfile "afiedt.buf" embedded OFF escape OFF FEEDBACK ON for 100 or more rows flagger OFF flush ON heading ON headsep "|" (hex 7c) instance "local" linesize 300 lno 15 loboffset 1 logsource "" long 300 longchunksize 80 markup HTML OFF HEAD "<style type="text/css"> body {font:10pt Arial,Helvetica,sans-serif; color:blac k; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table ,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0 px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; backgroun d:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699;

background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding

0px 0px 0px 0px;} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-c

olor:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#66330 0; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plu s Report</title>" BODY "" TABLE "border="1" width="90%" align="center" summary="Script output"" SPOO L OFF ENTMAP ON PREFORMAT OFF newpage 1 null "" numformat "" numwidth 10 pagesize 200 PAUSE is OFF pno 1 recsep WRAP recsepchar " " (hex 20) release 1002000100 repfooter OFF and is NULL repheader OFF and is NULL serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED shiftinout INVISIBLE showmode OFF spool OFF sqlblanklines OFF sqlcase MIXED sqlcode 0 sqlcontinue "> " sqlnumber ON sqlpluscompatibility 10.2.0 sqlprefix "#" (hex 23) sqlprompt "SQL> " sqlterminator ";" (hex 3b) suffix "sql" tab ON termout ON timing OFF trimout ON trimspool OFF ttitle OFF and is the first few characters of the next SELECT statement underline "-" (hex 2d) USER is "SYS" verify ON wrap : lines will be wrapped SQL></source>

show all markup

   <source lang="sql">

SQL> SQL> show markup markup HTML OFF HEAD "<style type="text/css"> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table ,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; backgroun d:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding

0px 0px 0px 0px;} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#66330

0; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border="1" width="90%" align="center" summary="Script output"" SPOO L OFF ENTMAP ON PREFORMAT OFF SQL> set markup SP2-0281: markup missing set option Usage: SET MARKUP HTML [ON|OFF] [HEAD text] [BODY text]

[TABLE text] [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]

SQL></source>

show autocommit

   <source lang="sql">

SQL> SQL> set autocommit on SQL> show autocommit autocommit IMMEDIATE SQL> set autocommit 42 SQL> show autocommit AUTOCOMMIT ON for every 42 DML statements SQL> set autocommit off SQL> SQL></source>

show concat

   <source lang="sql">

SQL> SQL> show concat concat "." (hex 2e) SQL></source>

show define

   <source lang="sql">

SQL> SQL> SQL> show define define "&" (hex 26) SQL></source>

show parameter optimizer_features

   <source lang="sql">

SQL> create table t as select * from all_objects; SQL> SQL> create or replace function get_row_cnt return number

 2  as
 3          countValue   number;
 4  begin
 5          select count(*) into countValue from t;
 6          return countValue;
 7  end;
 8  /

SQL> SQL> show parameter optimizer_features

NAME TYPE VALUE

---

----------- ------------------------------

optimizer_features_enable string 8.1.5 SQL> SQL> alter session set sql_trace=true; SQL> SQL> SQL> exec dbms_output.put_line( get_row_cnt ); 12583 SQL> SQL> alter system

 2  set optimizer_features_enable = "8.1.5" scope = spfile;

SQL> SQL> drop table t; SQL></source>

show verify

   <source lang="sql">

SQL> SQL> SQL> set verify on SQL> set verify off SQL> show verify verify OFF SQL> SQL></source>