SQL Plus - SpParameter

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

1 - About

You can show Oracle Database spparameter with the SHOW command

3 - How to

3.1 - 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.

Advertising

3.2 - list one parameter

Enter the following text string to display all parameters having BLOCK in their names:

SQL> SHOW SPPARAMETERS BLOCK

4 - 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