Mar 16 2008

show spparameters

Category: SQLPlus — ittichai @ 1:25 am

I found one of the interesting features in SQL*Plus 11g for Oracle11g database when doing “help show” from the tool.

SHOW  SPPARAMETERS [parameter_name]

Show specific parameters -

SYSTEM @db11r1> show spparameters sga
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ------------------
*        lock_sga                      boolean
*        pre_page_sga                  boolean
*        sga_max_size                  big integer
*        sga_target                    big integer

Show all parameters -

SYSTEM @db11r1> show spparameters
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ------------------
*        O7_DICTIONARY_ACCESSIBILITY   boolean
*        active_instance_count         integer
*        aq_tm_processes               integer     2
*        archive_lag_target            integer
*        asm_diskgroups                string
*        asm_diskstring                string
*        asm_power_limit               integer
*        asm_preferred_read_failure_gr string
:         :         :
*        use_indirect_data_buffers     boolean
*        user_dump_dest                string
*        utl_file_dir                  string
*        workarea_size_policy          string
*        xml_db_events                 string

Originally I thought it is a feature of SQL*Plus 11g, but if I connect to the 10g database, it does not display anything. Note that error indicates that Server version too low for this feature.

SYSTEM @db10w1> show spparameters
SP2-0614: Server version too low for this feature
SP2-0735: unknown SHOW option beginning "lege'@'_co..."
SP2-0158: unknown SHOW option """

If database is started up with pfile, all values of “show spparameters” will be empty.

Tags:

4 Responses to “show spparameters”

  1. Laurent Schneider says:
    March 16th, 2008 7:45 am

    nice tip!

    Reply
  2. IC says:
    March 16th, 2008 3:19 pm

    Laurent,
    Thanks for stopping by. I enjoy your blog very much.
    Ittichai

    Reply
  3. Laurent Schneider says:
    March 17th, 2008 9:15 am

    thank you.
    Unfortunately it does not work with a 10g db :-(

    SQL> sho spparameter hash
    SP2-0614: Server version too low for this feature
    SP2-0158: unknown SHOW option “default”
    SP2-0158: unknown SHOW option “vfs.”
    SP2-0735: unknown SHOW option beginning “%defaultvf…”
    SP2-0158: unknown SHOW option “jfs2″
    SP2-0158: unknown SHOW option “nfs”
    SP2-0158: unknown SHOW option “#”
    SP2-0158: unknown SHOW option “cdrfs”
    SP2-0158: unknown SHOW option “5″
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “procfs”
    SP2-0158: unknown SHOW option “6″
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “jfs”
    SP2-0158: unknown SHOW option “3″
    SP2-0158: unknown SHOW option “none”
    SP2-0735: unknown SHOW option beginning “/sbin/help…”
    SP2-0158: unknown SHOW option “jfs2″
    SP2-0158: unknown SHOW option “0″
    SP2-0735: unknown SHOW option beginning “/sbin/help…”
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “nfs”
    SP2-0158: unknown SHOW option “2″
    SP2-0735: unknown SHOW option beginning “/sbin/help…”
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “remote”
    SP2-0158: unknown SHOW option “sfs”
    SP2-0158: unknown SHOW option “16″
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “nfs3″
    SP2-0158: unknown SHOW option “18″
    SP2-0735: unknown SHOW option beginning “/sbin/help…”
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “remote”
    SP2-0158: unknown SHOW option “nfs4″
    SP2-0158: unknown SHOW option “35″
    SP2-0735: unknown SHOW option beginning “/sbin/help…”
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “remote”
    SP2-0158: unknown SHOW option “cachefs”
    SP2-0158: unknown SHOW option “17″
    SP2-0735: unknown SHOW option beginning “/sbin/help…”
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “remote”
    SP2-0158: unknown SHOW option “udfs”
    SP2-0158: unknown SHOW option “34″
    SP2-0735: unknown SHOW option beginning “/sbin/help…”
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “cifs”
    SP2-0158: unknown SHOW option “37″
    SP2-0735: unknown SHOW option beginning “/sbin/help…”
    SP2-0158: unknown SHOW option “none”
    SP2-0158: unknown SHOW option “autofs”
    SP2-0158: unknown SHOW option “19″
    SP2-0735: unknown SHOW option beginning “/sbin/help…”
    SP2-0158: unknown SHOW option “none”
    SQL>

    Reply
  4. IC says:
    March 17th, 2008 2:32 pm

    Yes, that was the first thing I tested. :-( Too bad.
    -ittichai

    Reply

Leave a Reply