SQL Plus - SpParameter

About

You can show Oracle Database spparameter with the SHOW command

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

Powered by ComboStrap