show spparameters

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.

4 thoughts on “show spparameters”

  1. Laurent Schneider

    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>

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top