CODEWITHSUNDEEP
sql
satya
satya

Reputation: 65

query to find all db parameter values including null

does this query get all values including nulls,where the parameter not available in Table.

select name,display_value 
from v$spparameter where nvl(name,'null') in(
    'memory_target','sga_target','pga_aggregate_target','pga_aggregate_limit','db_cache_size',
    'shared_pool_size','large_pool_size','result_cache_max_size','processes',
    'session_cached_cursors','open_cursors','db_securefile','cpu_count',
    'parallel_max_servers','job_queue_processes','log_buffer','_b_tree_bitmap_plans',
    'use_large_pages','audit_trail','nls_sort','plsql_code_type','resource_manager_plan',
    'shared_servers','max_shared_servers','dispatchers','event','undo_retention',
    '_highthreshold_undoretention','parallel_adaptive_multi_user','parallel_force_local',
    'db_files','db_performance_profile','_srvntfn_max_concurrent_jobs',
    '_optimizer_use_feedback','optimizer_dsdir_usage_control',
    '_sql_plan_directive_mgmt_control','_fix_control','global_txn_processes',
    '_disable_autotune_gtx', '_ges_server_processes','sga_max_size',
    '_securefiles_concurrency_estimate','streams_pool_size')
order by name;

Upvotes: 0

Views: 246

Answers (2)

asantaballa
asantaballa

Reputation: 4048

Almost. You're converting the null value into the string 'null', but you forgot to include it in the "in" list. Just add 'null' to list.

select name,display_value 
from v$spparameter where nvl(name,'null') in('null',
    'memory_target','sga_target','pga_aggregate_target','pga_aggregate_limit','db_cache_size',
    'shared_pool_size','large_pool_size','result_cache_max_size','processes',
    'session_cached_cursors','open_cursors','db_securefile','cpu_count',
    'parallel_max_servers','job_queue_processes','log_buffer','_b_tree_bitmap_plans',
    'use_large_pages','audit_trail','nls_sort','plsql_code_type','resource_manager_plan',
    'shared_servers','max_shared_servers','dispatchers','event','undo_retention',
    '_highthreshold_undoretention','parallel_adaptive_multi_user','parallel_force_local',
    'db_files','db_performance_profile','_srvntfn_max_concurrent_jobs',
    '_optimizer_use_feedback','optimizer_dsdir_usage_control',
    '_sql_plan_directive_mgmt_control','_fix_control','global_txn_processes',
    '_disable_autotune_gtx', '_ges_server_processes','sga_max_size',
    '_securefiles_concurrency_estimate','streams_pool_size')
order by name;

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try to add OR name is null in where caluse.

Because SQL NULL isn't a value. you need to use IS NULL to get it.

select name,display_value 
from v$spparameter 
where 
      name in('memory_target','sga_target','pga_aggregate_target','pga_aggregate_limit','db_cache_size','shared_pool_size','large_pool_size','result_cache_max_size','processes','session_cached_cursors','open_cursors','db_securefile','cpu_count','parallel_max_servers','job_queue_processes','log_buffer','_b_tree_bitmap_plans','use_large_pages','audit_trail','nls_sort','plsql_code_type','resource_manager_plan','shared_servers','max_shared_servers','dispatchers','event','undo_retention','_highthreshold_undoretention','parallel_adaptive_multi_user','parallel_force_local','db_files','db_performance_profile','_srvntfn_max_concurrent_jobs','_optimizer_use_feedback','_optimizer_dsdir_usage_control','_sql_plan_directive_mgmt_control','_fix_control','global_txn_processes','_disable_autotune_gtx', '_ges_server_processes','sga_max_size','_securefiles_concurrency_estimate','streams_pool_size') 
    OR 
      name is null
 order by name;

Upvotes: 1

Related Questions