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.
nice tip!
Laurent,
Thanks for stopping by. I enjoy your blog very much.
Ittichai
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>
Yes, that was the first thing I tested. 🙁 Too bad.
-ittichai