73 lines
2.1 KiB
SQL
73 lines
2.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display valid values for init.ora parameters
|
|
* Parameters : 1 - Parameter Name (Use % as wild card and \ as escape character)
|
|
* 2 - Where Clause ( Default NULL)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ ------------------------------------------------------
|
|
* 22-Jan-14 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE PARAM_NAME
|
|
UNDEFINE WHERE_CLAUSE
|
|
|
|
DEFINE PARAM_NAME="&&1"
|
|
DEFINE WHERE_CLAUSE="&2"
|
|
|
|
|
|
|
|
COLUMN _PARAM_NAME NEW_VALUE PARAM_NAME NOPRINT
|
|
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&PARAM_NAME','','%','&&PARAM_NAME') "_PARAM_NAME"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT **************************************************************
|
|
PROMPT * Valid values for init.ora parameters
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Parameter Name = "&&PARAM_NAME"
|
|
PROMPT * - Where Clause = "&&WHERE_CLAUSE"
|
|
PROMPT **************************************************************
|
|
|
|
|
|
COLUMN parno_kspvld_values HEADING "Parameter|Number" FORMAT 9999999
|
|
COLUMN name_kspvld_values HEADING "Parameter|Name" FORMAT a30
|
|
COLUMN ordinal_kspvld_values HEADING "Ordinal|Name" FORMAT 999999
|
|
COLUMN value_kspvld_values HEADING "Valid Values" FORMAT a20
|
|
COLUMN isdefault_kspvld_values HEADING "ISDEFAULT" FORMAT a5
|
|
|
|
BREAK ON parno_kspvld_values ON name_kspvld_values SKIP 1
|
|
|
|
SELECT parno_kspvld_values
|
|
, name_kspvld_values
|
|
, ordinal_kspvld_values
|
|
, value_kspvld_values
|
|
, isdefault_kspvld_values
|
|
FROM sys.x$kspvld_values p
|
|
JOIN v$instance i ON i.instance_number = p.inst_id
|
|
WHERE 1=1
|
|
-- AND TRANSLATE(name_kspvld_values,'_','#') NOT LIKE '#%' /* Exclude hidden parameters */
|
|
AND UPPER(name_kspvld_values) LIKE UPPER('&&PARAM_NAME')
|
|
ORDER BY name_kspvld_values
|
|
, ordinal_kspvld_values
|
|
;
|
|
|
|
@@footer
|