Mar 16 2008

show spparameters

Category: SQLPlusittichai @ 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
-------- ----------------------------- ----------- ------------------
*        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.