Parameter Query Tool (xparm80.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Parameter Query Tool

This script provides a user-friendly way to query Oracle system parameters with clear formatting and modification indicators. It works with the advanced parameters view created by xparmall.run.

Script: xparm80.sql

rem  xparmall.sql
rem
ttitle 'All System Parameters'
rem
col num format 999 heading 'ID'
col name format a41 heading 'NAME'
col type format 9 heading 'T'
col value format a36 heading 'VALUE'
col isdefault format a5 heading 'ISDEF'
col m format a1 heading 'M'
col isses_modifiable format a5 heading 'IS|SESS|MOD'
col issys_modifiable format a9 heading 'IS|SYSTEM|MOD'
col ismodifiable format a8 heading 'IS|MOD'
col isadjusted format a5 heading 'ISADJ'
col description format a25 heading 'DESCPIPTION'
rem
select
/*
       num,
       type,
       decode( isses_modifiable, 'FALSE', null, isses_modifiable ) isses_modifiable,
       decode( issys_modifiable, 'FALSE', null, issys_modifiable ) issys_modifiable,
       decode( ismodifiable, 'FALSE', null, ismodifiable ) ismodifiable,
       decode( isadjusted, 'FALSE', null, isadjusted ) isadjusted,
       description
*/
       name,
       decode( isdefault, 'TRUE', null, 'FALSE', '*', '?' ) m,
       value
  from x$system_parameters
 where name like '&name'
 order by name;

What This Script Does

This script provides an interactive parameter lookup tool that:

• Prompts for Parameter Names: Uses substitution variable for pattern matching
• Shows Modification Status: Displays ’*’ for non-default parameters
• Formatted Output: Clean, readable parameter display
• Pattern Matching: Supports SQL wildcards for parameter searches
• Requires Advanced View: Works with the x$system_parameters view

Prerequisites

This script requires the x$system_parameters view created by:

@xparmall.run

See Advanced System Parameters View (xparmall.run) for setup instructions.

Usage

-- Run the script and enter parameter pattern when prompted
@xparm80.sql

-- Examples of patterns to enter:
-- sga%          (all SGA-related parameters)
-- %optimizer%   (all optimizer parameters)
-- processes     (exact parameter name)
-- %memory%      (all memory-related parameters)
-- _trace%       (all trace-related hidden parameters)

Required Privileges

• SELECT privilege on X$SYSTEM_PARAMETERS view
• The view must exist (created by xparmall.run)
• Typically requires DBA role

Sample Output

Enter value for name: sga%

                              All System Parameters

NAME                                     M VALUE
---------------------------------------- - ------------------------------------
sga_max_size                             * 2147483648
sga_target                               * 1073741824

Enter value for name: %optimizer%

                              All System Parameters

NAME                                     M VALUE
---------------------------------------- - ------------------------------------
optimizer_adaptive_features                TRUE
optimizer_adaptive_plans                   TRUE
optimizer_adaptive_statistics              FALSE
optimizer_adaptive_reporting_only          FALSE
optimizer_capture_sql_plan_baselines       FALSE
optimizer_dynamic_sampling               * 11
optimizer_features_enable                * 19.1.0
optimizer_index_caching                    0
optimizer_index_cost_adj                   100
optimizer_mode                           * FIRST_ROWS
optimizer_secure_view_merging              TRUE
optimizer_use_invisible_indexes            FALSE
optimizer_use_pending_statistics           FALSE
optimizer_use_sql_plan_baselines           TRUE

Key Output Elements

Element	Description
NAME	Parameter name (up to 41 characters)
M	Modification indicator: blank=default, *=modified, ?=unknown
VALUE	Current parameter value (up to 36 characters)

Modification Indicators

• Blank: Parameter is at its default value
• *****: Parameter has been modified from default
• ?: Parameter status is unknown

Common Search Patterns

1. Memory-Related Parameters

-- When prompted, enter: %memory%
-- Or run directly:
SELECT name, decode(isdefault, 'TRUE', null, 'FALSE', '*', '?') m, value
FROM x$system_parameters
WHERE name LIKE '%memory%'
ORDER BY name;

2. SGA Parameters

-- When prompted, enter: sga%
-- Shows all SGA-related settings

3. Optimizer Parameters

-- When prompted, enter: %optimizer%
-- Shows all optimizer configuration

4. Hidden Parameters

-- When prompted, enter: _%
-- Shows all hidden/underscore parameters

5. Process Parameters

-- When prompted, enter: %process%
-- Shows process-related settings

6. Parallel Parameters

-- When prompted, enter: parallel%
-- Shows parallel execution settings

Advanced Usage

Extended Information Query

You can modify the script to show more details by uncommenting the extended section:

select
       num,
       name,
       type,
       decode( isdefault, 'TRUE', null, 'FALSE', '*', '?' ) m,
       value,
       decode( isses_modifiable, 'FALSE', null, isses_modifiable ) isses_modifiable,
       decode( issys_modifiable, 'FALSE', null, issys_modifiable ) issys_modifiable,
       decode( ismodifiable, 'FALSE', null, ismodifiable ) ismodifiable,
       decode( isadjusted, 'FALSE', null, isadjusted ) isadjusted,
       description
  from x$system_parameters
 where name like '&name'
 order by name;

Non-Interactive Version

Create a version that doesn’t prompt:

-- Save as xparm_search.sql
DEFINE search_pattern = '&1'

SELECT name,
       decode(isdefault, 'TRUE', ' ', 'FALSE', '*', '?') as modified,
       value
FROM x$system_parameters
WHERE UPPER(name) LIKE UPPER('%&search_pattern%')
ORDER BY name;

-- Usage: @xparm_search.sql optimizer

Practical Examples

1. Performance Tuning Parameters

-- Search for key performance parameters
@xparm80.sql
-- Enter: %sga%

@xparm80.sql
-- Enter: %pga%

@xparm80.sql
-- Enter: %cpu%

2. Find Modified Parameters

-- To find all modified parameters, use this query:
SELECT name, value, description
FROM x$system_parameters
WHERE isdefault = 'FALSE'
ORDER BY name;

3. Security-Related Parameters

@xparm80.sql
-- Enter: %audit%

@xparm80.sql
-- Enter: %encrypt%

@xparm80.sql
-- Enter: %secure%

4. Database Feature Parameters

@xparm80.sql
-- Enter: %compatible%

@xparm80.sql
-- Enter: %enable%

@xparm80.sql
-- Enter: %disable%

Integration with Other Scripts

This script works well with:

• Advanced Parameters View (xparmall.run) - Required prerequisite
• Parameter Descriptions (xparmdef.sql) - For detailed parameter descriptions
• Database Health Check (db.sql) - For comprehensive database analysis
• System Information (vsga.sql) - For memory configuration analysis

Automation Examples

Create Parameter Documentation

-- Generate parameter documentation
SET PAGESIZE 1000
SET LINESIZE 150
SPOOL parameter_documentation.txt

SELECT name, value, isdefault, description
FROM x$system_parameters
WHERE isdefault = 'FALSE'
ORDER BY name;

SPOOL OFF

Parameter Change Tracking

-- Create a parameter tracking table
CREATE TABLE parameter_history (
  capture_date DATE,
  parameter_name VARCHAR2(64),
  parameter_value VARCHAR2(512),
  is_default VARCHAR2(5),
  is_modified VARCHAR2(8)
);

-- Insert current non-default parameters
INSERT INTO parameter_history
SELECT SYSDATE, name, value, isdefault, ismodifiable
FROM x$system_parameters
WHERE isdefault = 'FALSE';

Troubleshooting

Script Fails with “Table or View Does Not Exist”

-- Verify the prerequisite view exists
SELECT COUNT(*) FROM x$system_parameters WHERE rownum = 1;

-- If it fails, run the prerequisite script:
@xparmall.run

No Parameters Found

-- Check if the pattern is correct
SELECT name FROM x$system_parameters
WHERE LOWER(name) LIKE LOWER('%your_pattern%');

-- Verify view has data
SELECT COUNT(*) FROM x$system_parameters;

Performance Issues

-- For better performance on large parameter sets, add indexes:
-- (This would require DBA privileges on the underlying tables)

Best Practices

1. Use Wildcards Effectively: Learn common parameter prefixes for efficient searches
2. Review Modified Parameters: Regularly check what’s been changed from defaults
3. Document Changes: Keep track of parameter modifications and their reasons
4. Test First: Always test parameter changes in development environments
5. Coordinate with xparmall: Use this script in conjunction with the full parameter view

This tool provides an efficient way to explore Oracle’s vast parameter system and quickly find the settings you need to analyze or modify.