About
You can show Oracle Database spparameter with the SHOW command
Articles Related
How to
list all parameters
To see the current settings for initialization parameters, use the following SQL*Plus command:
SQL> SHOW SPPARAMETERS
This command displays all parameters in alphabetical order, along with their current values.
list one parameter
Enter the following text string to display all parameters having BLOCK in their names:
SQL> SHOW SPPARAMETERS BLOCK
Example
show spparameters
* O7_DICTIONARY_ACCESSIBILITY boolean
* active_instance_count integer
* aq_tm_processes integer
* archive_lag_target integer
* asm_diskgroups string
* asm_diskstring string
* asm_power_limit integer
* asm_preferred_read_failure_gr string
oups
* audit_file_dest string C:\app\gerard\admin\orcl\adu
mp
* audit_sys_operations boolean
* audit_trail string db
* awr_snapshot_time_offset integer
* background_core_dump string
* background_dump_dest string
* backup_tape_io_slaves boolean
* bitmap_merge_area_size integer
* blank_trimming boolean
* buffer_pool_keep string
* buffer_pool_recycle string
* cell_offload_compaction string
* cell_offload_decryption boolean
* cell_offload_parameters string
* cell_offload_plan_display string
* cell_offload_processing boolean
* cell_offloadgroup_name string
* circuits integer
* client_result_cache_lag big integer
* client_result_cache_size big integer
* clonedb boolean
* cluster_database boolean
* cluster_database_instances integer
* cluster_interconnects string
* commit_logging string
* commit_point_strength integer
* commit_wait string
* commit_write string
* compatible string 12.1.0.0.0
* connection_brokers string
* control_file_record_keep_time integer
* control_files string C:\app\gerard\fast_recovery_
area\orcl\control02.ctl
* control_files string C:\app\gerard\oradata\orcl\c
ontrol01.ctl
* control_management_pack_acces string
s
* core_dump_dest string
* cpu_count integer
* create_bitmap_area_size integer
* create_stored_outlines string
* cursor_bind_capture_destinati string
on
* cursor_sharing string
* cursor_space_for_time boolean
* db_16k_cache_size big integer
* db_2k_cache_size big integer
* db_32k_cache_size big integer
* db_4k_cache_size big integer
* db_8k_cache_size big integer
* db_big_table_cache_percent_ta string
rget
* db_block_buffers integer
* db_block_checking string
* db_block_checksum string
* db_block_size integer 8192
* db_cache_advice string
* db_cache_size big integer
* db_create_file_dest string
* db_create_online_log_dest_1 string
* db_create_online_log_dest_2 string
* db_create_online_log_dest_3 string
* db_create_online_log_dest_4 string
* db_create_online_log_dest_5 string
* db_domain string HotITem.local
* db_file_multiblock_read_count integer
* db_file_name_convert string
* db_files integer
* db_flash_cache_file string
* db_flash_cache_size big integer
* db_flashback_retention_target integer
* db_index_compression_inherita string
nce
* db_keep_cache_size big integer
* db_lost_write_protect string
* db_name string orcl
* db_recovery_file_dest string C:\app\gerard\fast_recovery_
area
* db_recovery_file_dest_size big integer 6930M
* db_recycle_cache_size big integer
* db_securefile string
* db_ultra_safe string
* db_unique_name string
* db_unrecoverable_scn_tracking boolean
* db_writer_processes integer
* dbwr_io_slaves integer
* ddl_lock_timeout integer
* deferred_segment_creation boolean
* dg_broker_config_file1 string
* dg_broker_config_file2 string
* dg_broker_start boolean
* diagnostic_dest string C:\app\gerard
* disk_asynch_io boolean
* dispatchers string (PROTOCOL=TCP)
(SERVICE=orclXDB)
* distributed_lock_timeout integer
* dml_locks integer
* dnfs_batch_size integer
* dst_upgrade_insert_conv boolean
* enable_ddl_logging boolean
* enable_pluggable_database boolean true
* event string
* fal_client string
* fal_server string
* fast_start_io_target integer
* fast_start_mttr_target integer
* fast_start_parallel_rollback string
* file_mapping boolean
* fileio_network_adapters string
* filesystemio_options string
* fixed_date string
* gcs_server_processes integer
* global_context_pool_size string
* global_names boolean
* global_txn_processes integer
* hash_area_size integer
* heat_map string
* hi_shared_memory_address integer
* hs_autoregister boolean
* ifile file
* instance_groups string
* instance_name string
* instance_number integer
* instance_type string
* java_jit_enabled boolean
* java_max_sessionspace_size integer
* java_pool_size big integer
* java_soft_sessionspace_limit integer
* job_queue_processes integer
* large_pool_size big integer
* ldap_directory_access string
* ldap_directory_sysauth string
* license_max_sessions integer
* license_max_users integer
* license_sessions_warning integer
* listener_networks string
* local_listener string
* lock_name_space string
* lock_sga boolean
* log_archive_config string
* log_archive_dest string
* log_archive_dest_1 string
* log_archive_dest_10 string
* log_archive_dest_11 string
* log_archive_dest_12 string
* log_archive_dest_13 string
* log_archive_dest_14 string
* log_archive_dest_15 string
* log_archive_dest_16 string
* log_archive_dest_17 string
* log_archive_dest_18 string
* log_archive_dest_19 string
* log_archive_dest_2 string
* log_archive_dest_20 string
* log_archive_dest_21 string
* log_archive_dest_22 string
* log_archive_dest_23 string
* log_archive_dest_24 string
* log_archive_dest_25 string
* log_archive_dest_26 string
* log_archive_dest_27 string
* log_archive_dest_28 string
* log_archive_dest_29 string
* log_archive_dest_3 string
* log_archive_dest_30 string
* log_archive_dest_31 string
* log_archive_dest_4 string
* log_archive_dest_5 string
* log_archive_dest_6 string
* log_archive_dest_7 string
* log_archive_dest_8 string
* log_archive_dest_9 string
* log_archive_dest_state_1 string
* log_archive_dest_state_10 string
* log_archive_dest_state_11 string
* log_archive_dest_state_12 string
* log_archive_dest_state_13 string
* log_archive_dest_state_14 string
* log_archive_dest_state_15 string
* log_archive_dest_state_16 string
* log_archive_dest_state_17 string
* log_archive_dest_state_18 string
* log_archive_dest_state_19 string
* log_archive_dest_state_2 string
* log_archive_dest_state_20 string
* log_archive_dest_state_21 string
* log_archive_dest_state_22 string
* log_archive_dest_state_23 string
* log_archive_dest_state_24 string
* log_archive_dest_state_25 string
* log_archive_dest_state_26 string
* log_archive_dest_state_27 string
* log_archive_dest_state_28 string
* log_archive_dest_state_29 string
* log_archive_dest_state_3 string
* log_archive_dest_state_30 string
* log_archive_dest_state_31 string
* log_archive_dest_state_4 string
* log_archive_dest_state_5 string
* log_archive_dest_state_6 string
* log_archive_dest_state_7 string
* log_archive_dest_state_8 string
* log_archive_dest_state_9 string
* log_archive_duplex_dest string
* log_archive_format string
* log_archive_local_first boolean
* log_archive_max_processes integer
* log_archive_min_succeed_dest integer
* log_archive_start boolean
* log_archive_trace integer
* log_buffer integer
* log_checkpoint_interval integer
* log_checkpoint_timeout integer
* log_checkpoints_to_alert boolean
* log_file_name_convert string
* max_dispatchers integer
* max_dump_file_size string
* max_enabled_roles integer
* max_shared_servers integer
* max_string_size string
* memory_max_target big integer
* memory_target big integer 1503M
* nls_calendar string
* nls_comp string
* nls_currency string
* nls_date_format string
* nls_date_language string
* nls_dual_currency string
* nls_iso_currency string
* nls_language string
* nls_length_semantics string
* nls_nchar_conv_excp string
* nls_numeric_characters string
* nls_sort string
* nls_territory string
* nls_time_format string
* nls_time_tz_format string
* nls_timestamp_format string
* nls_timestamp_tz_format string
* noncdb_compatible boolean
* object_cache_max_size_percent integer
* object_cache_optimal_size integer
* olap_page_pool_size big integer
* open_cursors integer 300
* open_links integer
* open_links_per_instance integer
* optimizer_adaptive_features boolean
* optimizer_adaptive_reporting_ boolean
only
* optimizer_capture_sql_plan_ba boolean
selines
* optimizer_dynamic_sampling integer
* optimizer_features_enable string
* optimizer_index_caching integer
* optimizer_index_cost_adj integer
* optimizer_mode string
* optimizer_secure_view_merging boolean
* optimizer_use_invisible_index boolean
es
* optimizer_use_pending_statist boolean
ics
* optimizer_use_sql_plan_baseli boolean
nes
* os_authent_prefix string
* os_roles boolean
* parallel_adaptive_multi_user boolean
* parallel_automatic_tuning boolean
* parallel_degree_level integer
* parallel_degree_limit string
* parallel_degree_policy string
* parallel_execution_message_si integer
ze
* parallel_fault_tolerance_enab boolean
led
* parallel_force_local boolean
* parallel_instance_group string
* parallel_io_cap_enabled boolean
* parallel_max_servers integer
* parallel_min_percent integer
* parallel_min_servers integer
* parallel_min_time_threshold string
* parallel_server boolean
* parallel_server_instances integer
* parallel_servers_target integer
* parallel_threads_per_cpu integer
* pdb_file_name_convert string
* permit_92_wrap_format boolean
* pga_aggregate_limit big integer
* pga_aggregate_target big integer
* plscope_settings string
* plsql_ccflags string
* plsql_code_type string
* plsql_debug boolean
* plsql_optimize_level integer
* plsql_v2_compatibility boolean
* plsql_warnings string
* pre_page_sga boolean
* processes integer 300
* processor_group_name string
* query_rewrite_enabled string
* query_rewrite_integrity string
* rdbms_server_dn string
* read_only_open_delayed boolean
* recovery_parallelism integer
* recyclebin string
* redo_transport_user string
* remote_dependencies_mode string
* remote_listener string
* remote_login_passwordfile string EXCLUSIVE
* remote_os_authent boolean
* remote_os_roles boolean
* replication_dependency_tracki boolean
ng
* resource_limit boolean
* resource_manager_cpu_allocation integer
* resource_manager_plan string
* result_cache_max_result integer
* result_cache_max_size big integer
* result_cache_mode string
* result_cache_remote_expiration integer
* resumable_timeout integer
* rollback_segments string
* sec_case_sensitive_logon boolean
* sec_max_failed_login_attempts integer
* sec_protocol_error_further_ac string
tion
* sec_protocol_error_trace_acti string
on
* sec_return_server_release_ban boolean
ner
* serial_reuse string
* service_names string
* session_cached_cursors integer
* session_max_open_files integer
* sessions integer
* sga_max_size big integer
* sga_target big integer
* shadow_core_dump string
* shared_memory_address integer
* shared_pool_reserved_size big integer
* shared_pool_size big integer
* shared_server_sessions integer
* shared_servers integer
* skip_unusable_indexes boolean
* smtp_out_server string
* sort_area_retained_size integer
* sort_area_size integer
* spatial_vector_acceleration boolean
* spfile string
* sql92_security boolean
* sql_trace boolean
* sqltune_category string
* standby_archive_dest string
* standby_file_management string
* star_transformation_enabled string
* statistics_level string
* streams_pool_size big integer
* tape_asynch_io boolean
* temp_undo_enabled boolean
* thread integer
* threaded_execution boolean
* timed_os_statistics integer
* timed_statistics boolean
* trace_enabled boolean
* tracefile_identifier string
* transactions integer
* transactions_per_rollback_seg integer
ment
* undo_management string
* undo_retention integer
* undo_tablespace string UNDOTBS1
* unified_audit_sga_queue_size integer
* use_dedicated_broker boolean
* use_indirect_data_buffers boolean
* use_large_pages string
* user_dump_dest string
* utl_file_dir string
* workarea_size_policy string
* xml_db_events string