Advanced System Parameters View (xparmall.run)

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.

Advanced System Parameters View

This script creates a comprehensive view that exposes Oracle’s hidden parameters along with their modifiability flags, default status, and descriptions. It provides much more detail than standard parameter queries.

Script: xparmall.run

rem  xparmall.run
rem
create or replace view x_$system_parameters as
select x.indx+1 num,
       ksppinm name,
       ksppity type,
       ksppstvl value,
       ksppstdf isdefault,
       decode( bitand(ksppiflg/256,1),
               1, 'TRUE', 'FALSE' ) isses_modifiable,
       decode( bitand(ksppiflg/65536,3),
               1, 'IMMEDIATE', 2, 'DEFERRED', 'FALSE' ) issys_modifiable,
       decode( bitand(ksppstvf,7), 1, 'MODIFIED', 'FALSE' ) ismodifiable,
       decode( bitand(ksppstvf,2), 2, 'TRUE', 'FALSE' ) isadjusted,
       ksppdesc description
  from x$ksppi x,
       x$ksppsv y
 where x.indx = y.indx;
rem
drop   public synonym x$system_parameters;
create public synonym x$system_parameters for x_$system_parameters;
grant  select on x_$system_parameters to public;

What This Script Does

This script creates a comprehensive view that exposes Oracle’s internal parameter structure, including hidden and undocumented parameters. It provides detailed metadata about each parameter including:

• Parameter modifiability at session and system levels
• Whether parameters have been modified from defaults
• Whether parameters have been adjusted by Oracle
• Complete parameter descriptions

Usage

Run as a user with DBA privileges:

-- Execute the script to create the view
@xparmall.run

-- Query all parameters
SELECT * FROM x_$system_parameters ORDER BY name;

-- Find modifiable parameters
SELECT name, value, isses_modifiable, issys_modifiable
FROM x_$system_parameters
WHERE isses_modifiable = 'TRUE' OR issys_modifiable != 'FALSE';

-- Find modified parameters
SELECT name, value, isdefault, ismodifiable
FROM x_$system_parameters
WHERE isdefault = 'FALSE' OR ismodifiable = 'MODIFIED';

Required Privileges

• DBA or SYSDBA privileges
• CREATE VIEW privilege
• CREATE PUBLIC SYNONYM privilege
• GRANT privilege

Sample Output

NUM NAME                           TYPE VALUE         ISDEFAULT ISSES_MOD ISSYS_MOD ISMODIFIED ISADJUSTED
--- ------------------------------ ---- ------------- --------- --------- --------- ---------- ----------
  1 _optimizer_mode_force          1                  TRUE      FALSE     FALSE     FALSE      FALSE
  2 _optimizer_undo_cost_change    1    10.4.1        TRUE      FALSE     FALSE     FALSE      FALSE
  3 _trace_files_public            1    FALSE         TRUE      TRUE      IMMEDIATE FALSE      FALSE
  4 sga_max_size                   2    1073741824    FALSE     FALSE     IMMEDIATE MODIFIED   TRUE
  5 processes                      3    300           FALSE     FALSE     FALSE     MODIFIED   FALSE

Key Output Columns

Column	Description
NUM	Parameter sequence number
NAME	Parameter name (including hidden parameters)
TYPE	Parameter data type (1=boolean, 2=string, 3=integer, etc.)
VALUE	Current parameter value
ISDEFAULT	TRUE if parameter is at default value
ISSES_MODIFIABLE	TRUE if modifiable at session level
ISSYS_MODIFIABLE	IMMEDIATE/DEFERRED if modifiable at system level
ISMODIFIABLE	MODIFIED if parameter has been changed
ISADJUSTED	TRUE if Oracle automatically adjusted the value
DESCRIPTION	Oracle’s internal parameter description

Understanding the Parameters

Parameter Types

• Type 1: Boolean parameters (TRUE/FALSE)
• Type 2: String parameters
• Type 3: Integer parameters
• Type 4: Parameter file parameters
• Type 5: Reserved
• Type 6: Big integer parameters

Modifiability Levels

• Session Modifiable: Can be changed with ALTER SESSION
• System Immediate: Can be changed with ALTER SYSTEM immediately
• System Deferred: Can be changed with ALTER SYSTEM but takes effect for new sessions
• Static: Requires database restart to change

Common Use Cases

1. Find All Hidden Parameters

SELECT name, value, description
FROM x_$system_parameters
WHERE name LIKE '\_%' ESCAPE '\'
ORDER BY name;

2. Review Non-Default Settings

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

3. Find Parameters Modified Since Startup

SELECT name, value, description
FROM x_$system_parameters
WHERE ismodifiable = 'MODIFIED'
ORDER BY name;

4. List Dynamically Modifiable Parameters

SELECT name, value, isses_modifiable, issys_modifiable, description
FROM x_$system_parameters
WHERE isses_modifiable = 'TRUE'
   OR issys_modifiable IN ('IMMEDIATE', 'DEFERRED')
ORDER BY issys_modifiable, isses_modifiable, name;

5. Search for Specific Parameter Patterns

SELECT name, value, description
FROM x_$system_parameters
WHERE UPPER(name) LIKE '%OPTIMIZER%'
   OR UPPER(description) LIKE '%OPTIMIZER%'
ORDER BY name;

Performance Analysis

This view is particularly useful for:

• Performance Tuning: Finding optimizer and memory-related hidden parameters
• Troubleshooting: Identifying parameters that may be causing issues
• Configuration Auditing: Reviewing all non-default settings
• Capacity Planning: Understanding memory and resource allocations

Security Considerations

• Limited Access: Grant access only to DBAs and senior developers
• Hidden Parameters: Many exposed parameters are undocumented and unsupported
• Modification Risk: Changing hidden parameters can cause instability
• Documentation: Always document any hidden parameter changes

Troubleshooting

View Creation Fails

-- Check if user has required privileges
SELECT * FROM session_privs WHERE privilege IN
('CREATE VIEW', 'CREATE PUBLIC SYNONYM');

-- Verify x$ table access
SELECT COUNT(*) FROM x$ksppi WHERE rownum = 1;

No Hidden Parameters Shown

-- Verify view is properly created
DESC x_$system_parameters;

-- Check synonym access
SELECT COUNT(*) FROM x$system_parameters WHERE rownum = 1;

Integration with Other Scripts

This view works well with:

• Parameter Query Tool (xparm80.sql) - For formatted parameter lookups
• Parameter Descriptions (xparmdef.sql) - For parameter description queries
• Database Health Check (db.sql) - For comprehensive database analysis

Related Oracle Views

• V$PARAMETER - Standard documented parameters only
• V$SYSTEM_PARAMETER - System-level parameters
• V$PARAMETER2 - Multi-value parameters
• X$KSPPI - Internal parameter names (raw)
• X$KSPPSV - Internal parameter values (raw)

Best Practices

1. Create Once: Run this script once per database to create the view
2. Regular Reviews: Periodically review non-default parameters
3. Document Changes: Track any parameter modifications
4. Test Thoroughly: Always test parameter changes in development first
5. Oracle Support: Consult Oracle Support before modifying hidden parameters

This view provides unprecedented visibility into Oracle’s parameter system and is essential for advanced database administration and troubleshooting.