How to View Current database parameters in Oracle

To view current database parameters in Oracle, you can use the following methods:

---

🔹 1️⃣ View All Parameters Using V$PARAMETER

Run the following SQL query to see all current database parameters:

SELECT name, value FROM v$parameter ORDER BY name;

✅ To find a specific parameter, use:

SELECT name, value FROM v$parameter WHERE name = 'db_cache_size';

✅ To view only modified (non-default) parameters, use:

SELECT name, value FROM v$parameter WHERE isdefault = 'FALSE';

---

🔹 2️⃣ View Parameters Using V$SYSTEM_PARAMETER

The V$SYSTEM_PARAMETER view shows instance-wide parameters:

SELECT name, value, issys_modifiable FROM v$system_parameter ORDER BY name;

✅ To check if a parameter is modifiable at runtime, use:

SELECT name, value, issys_modifiable FROM v$system_parameter WHERE name = 'sga_target';

• IMMEDIATE → Can be changed dynamically without restart.
• DEFERRED → Changes take effect after reconnecting sessions.
• FALSE → Requires database restart.

---

🔹 3️⃣ View Parameters Using SHOW PARAMETER (SQL*Plus)

If using SQL*Plus, you can run:

SHOW PARAMETER memory;

This will show all parameters related to "memory".

✅ To find a specific parameter:

SHOW PARAMETER sga_target;

---

🔹 4️⃣ View Parameters with Default & Session-Level Values

If you want to check the default values for parameters:

SELECT name, value FROM v$parameter2 ORDER BY name;

To check session-level parameters:

SELECT name, value FROM v$ses_optimizer_env WHERE sid = SYS_CONTEXT('USERENV', 'SID');

---

🔹 5️⃣ View SPFILE & PFILE Parameters

To check whether a parameter is stored in the SPFILE:

SELECT name, value, isspecified FROM v$spparameter WHERE isspecified = 'TRUE';

If you need to export the PFILE from an SPFILE:

CREATE PFILE FROM SPFILE;

---

🔹 6️⃣ View Hidden Parameters

To check hidden parameters (underscore parameters):

SELECT name, value FROM v$parameter WHERE name LIKE '_%';

Example:

SELECT name, value FROM v$parameter WHERE name = '_optimizer_adaptive_features';

---

🔹 Summary Table

Method	Command	Description
Basic Parameters	SELECT name, value FROM v$parameter;	Shows all database parameters
Modified Parameters	SELECT name, value FROM v$parameter WHERE isdefault = 'FALSE';	Shows only changed parameters
System Parameters	SELECT name, value FROM v$system_parameter;	Instance-wide parameters
SQL*Plus Method	SHOW PARAMETER sga_target;	Quick parameter check in SQL*Plus
SPFILE Parameters	SELECT name, value FROM v$spparameter WHERE isspecified = 'TRUE';	Checks stored parameters in SPFILE
Hidden Parameters	SELECT name, value FROM v$parameter WHERE name LIKE '_%';	Lists Oracle’s hidden parameters