You can show Oracle Database spparameter with the SHOW command
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.
Enter the following text string to display all parameters having BLOCK in their names:
SQL> SHOW SPPARAMETERS BLOCK
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