QUERYING ORACLE HIDDEN PARAMETERS
Spfile
0
QUESTIONS
How to query the hidden initialization parameters?
How to view list of hidden parameters?
How to view hidden parameters in Oracle?
How to find oracle hidden parameters?
List all hidden database parameters.
View hidden initialization parameters.
See all initialization parameters.
How to view list of hidden parameters?
How to view hidden parameters in Oracle?
How to find oracle hidden parameters?
List all hidden database parameters.
View hidden initialization parameters.
See all initialization parameters.
REQUEST
A user wants to know a value of specific hidden parameter in Oracle database.
SOLUTION
The following query retrieves all hidden parameters that are in Oracle database.
COL "PARAMETER" FOR A50
COL "IS_DEFAULT" FOR A10
COL "SESSION" FOR A10
COL "INSTANCE" FOR A10
COL "IS_SYSTEM_MODIFIABLE" FOR A20
SELECT a.KSPPINM "PARAMETER",
b.KSPPSTDF "IS_DEFAULT",
b.KSPPSTVL "SESSION",
c.KSPPSTVL "INSTANCE",
DECODE(BITAND(a.KSPPIFLG/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
DECODE(BITAND(a.KSPPIFLG/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM X$KSPPI a,
X$KSPPCV b,
X$KSPPSV c
WHERE a.INDX = b.INDX
AND a.INDX = c.INDX
AND a.KSPPINM LIKE '/_%' escape '/'
/
To get a specific hidden parameter
COL "PARAMETER" FOR A30
COL "IS_DEFAULT" FOR A10
COL "SESSION" FOR A10
COL "INSTANCE" FOR A10
COL "IS_SYSTEM_MODIFIABLE" FOR A20
SELECT a.KSPPINM "PARAMETER",
b.KSPPSTDF "IS_DEFAULT",
b.KSPPSTVL "SESSION",
c.KSPPSTVL "INSTANCE",
DECODE(BITAND(a.KSPPIFLG/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
DECODE(BITAND(a.KSPPIFLG/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM X$KSPPI a,
X$KSPPCV b,
X$KSPPSV c
WHERE a.INDX = b.INDX
AND a.INDX = c.INDX
AND a.KSPPINM LIKE '/&hidden_parameter' escape '/'
/
Enter value for hidden_parameter: _gsm_region_list
old 12: AND a.KSPPINM LIKE '/&hidden_parameter' escape '/'
new 12: AND a.KSPPINM LIKE '/_gsm_region_list' escape '/'
PARAMETER IS_DEFAULT SESSION INSTANCE IS_SESSION_MODI IS_SYSTEM_MODIFIABLE
------------------------------ ---------- ---------- ---------- --------------- --------------------
_gsm_region_list TRUE FALSE IMMEDIATE
To change a hidden parameter
-- The system level
ALTER SYSTEM SET "_your_parameter_name"='desired_value' scope=spfile;
-- A session level
ALTER SESSION SET "_your_parameter_name"='desired_value';
For more details refer to How To Query And Change The Oracle Hidden Parameters In Oracle 10g ,11g and 12c (Doc ID 315631.1)
REFERENCES
How To Query And Change The Oracle Hidden Parameters In Oracle 10g ,11g and 12c (Doc ID 315631.1)
Version : 18:20 18.12.2017
Platform : Red Hat 5.11
Database : Oracle database 12.2.0.1.0
Platform : Red Hat 5.11
Database : Oracle database 12.2.0.1.0
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)
Tags
/etc/sudoers
Account
Agent
Agent 12c Deinstall
Agent 13c Deployment
Automated Maintenance Tasks
AWR
BLOCKING SESSION
Database Auditing
Database Directory
Database Performance
Datapump
DBMS_SYSTEM
Enterprise Manager
Enterprise Manager Metrics
Failed Logins (Historical) Metric
Failed Logins Metric
Gather Statistics Job
GET_ENV
High IO Load
Idle Sessions
Initialization Parameters
LOCKED(TIMED)
OEM
OEM 12.1.0.4
OEM 13.1.0.0.0
OMS
Processes
Repository
Retrieve Object DDL
ROW LOCK
Scheduler
Schema Statistics
Scripts
Sessions
spfile
SQL_ID
Standard Query
TRANSACTION
User Grants
UTL_FILE
WAITING SESSION
Window Group
Window Name
Yum