DBA Tips Archive for Oracle
Obsolete Database Parameters
by Jeff Hunter, Sr. Database Administrator
With each new release of the database, Oracle will always add and remove certain database parameters found in the init.ora file. This month, I will show several views provided by Oracle that are useful for detecting obsolete parameters.
Oracle provides the view V$OBSOLETE_PARAMETER to display all obsolete and underscored parameters. This view contains the name of the parameter and a flag ISSPECIFIED indicating if the parameter has actually been set in the init.ora file. Keep in mind that Oracle has not really retired all of the parameters contained in V$OBSOLETE_PARAMETER, some of them have been underscored. Underscored parameters are ones that Oracle wants to keep around, but does not want you to use them unless absolutely necessary. These underscored parameters are better know as hidden parameters. They are not documented and they all start with an _, like _lgwr_io_slaves in Oracle8i.
Use the following query to see the list of obsolete parameter names and if they have been specified in your init.ora:
SQL> SELECT name, isspecified 2 FROM v$obsolete_parameter;
To check if the parameter name (found in V$OBSOLETE_PARAMETER) was removed or underscored, you can query the X$KSPPO view. The value in the KSPPOFLAG column identifies if the parameter has been removed or underscored. Removed values are identified by a "1" and underscored values are identified by "2".
SQL> SELECT kspponm, 2 DECODE(ksppoflg, 1,'Obsolete', 2, 'Underscored') 3 FROM x$ksppo 4 ORDER BY kspponm; KSPPONM DECODE(KSPP ---------------------------------------------------------------- ----------- allow_partial_sn_results Obsolete arch_io_slaves Underscored b_tree_bitmap_plans Underscored backup_disk_io_slaves Underscored cache_size_threshold Obsolete cleanup_rollback_entries Underscored close_cached_open_cursors Underscored compatible_no_recovery Underscored complex_view_merging Underscored db_block_checkpoint_batch Obsolete db_block_lru_extended_statistics Obsolete db_block_lru_statistics Obsolete db_file_simultaneous_writes Obsolete delayed_logging_block_cleanouts Obsolete discrete_transactions_enabled Underscored distributed_lock_timeout Underscored distributed_recovery_connection_hold_time Underscored fast_full_scan_enabled Underscored freeze_DB_for_fast_instance_recovery Underscored gc_latches Underscored gc_lck_procs Underscored job_queue_keep_connections Obsolete large_pool_min_alloc Underscored lgwr_io_slaves Underscored lock_sga_areas Underscored log_archive_buffer_size Underscored log_archive_buffers Underscored log_block_checksum Obsolete log_files Obsolete log_simultaneous_copies Underscored log_small_entry_max_size Obsolete max_transaction_branches Underscored ogms_home Underscored ops_admin_group Obsolete optimizer_search_limit Underscored parallel_default_max_instances Underscored parallel_min_message_pool Underscored parallel_server_idle_time Underscored parallel_transaction_resource_timeout Obsolete push_join_predicate Underscored row_cache_cursors Underscored sequence_cache_entries Obsolete sequence_cache_hash_buckets Obsolete shared_pool_reserved_min_alloc Underscored snapshot_refresh_interval Obsolete snapshot_refresh_keep_connections Obsolete snapshot_refresh_processes Obsolete sort_direct_writes Obsolete sort_read_fac Obsolete sort_spacemap_size Obsolete sort_write_buffer_size Obsolete sort_write_buffers Obsolete spin_count Underscored temporary_table_locks Obsolete use_ism Underscored 55 rows selected.
HINT: Whenever I install a new version of Oracle, one of the first tasks I perform is to generate a standard init.ora file for that version of the database. The init.ora file should be setup with all of the possible parameters in alphabetical order. Parameters that are not specifically used (i.e. that are not being explicitly set) may be commented out. Here is a query that can be run against a new database to build a default init.ora file that contains all possible parameters.SET pagesize 9000 SET head OFF SET term OFF SPOOL initParameters.ora SELECT DECODE(isdefault, 'TRUE', '# ') || DECODE(isdefault, 'TRUE', RPAD(name,43), RPAD(name,45)) || ' = ' || value FROM v$parameter ORDER BY name; SPOOL off
NOTE: Hidden parameters are not listed by the above SQL command. One of the more popular hidden parameters is "_trace_files_public = true". All hidden parameters should be at the top of the init.ora file.
Copyright (c) 1998-2014 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at email@example.com.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.