My blog has moved!

You should be automatically redirected in 5 seconds. If not, visit
http://www.oraexplorer.com
and update your bookmarks.

Saturday, March 15, 2008

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]
This is similar to what we’re all familiar with but for spfile parameters instead.

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.

Posted by Ittichai Chammavanijakul at 8:25 PM

4 comments:

  1. AnonymousMarch 16, 2008 at 2:45 AM

    nice tip!

    ReplyDelete
  2. Ittichai ChammavanijakulMarch 16, 2008 at 10:19 AM

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

    ReplyDelete
  3. Laurent SchneiderMarch 17, 2008 at 4: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>

    ReplyDelete
  4. Ittichai ChammavanijakulMarch 17, 2008 at 9:32 AM

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

    ReplyDelete

Subscribe to: Post Comments (Atom)