88 lines
2.5 KiB
SQL
88 lines
2.5 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display all parameters from spfile
|
|
* Parameters : 1 - Parameter Name (Use % as wild card and \ as escape character)
|
|
* 2 - Instance Number ( Use % to query for all instances)
|
|
* 3 - Where Clause ( Default NULL)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 12-Oct-15 Vishal Gupta Increased Value column width
|
|
* 05-Aug-06 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE PARAM_NAME
|
|
UNDEFINE INST_ID
|
|
UNDEFINE WHERE_CLAUSE
|
|
|
|
DEFINE PARAM_NAME="&&1"
|
|
DEFINE INST_ID="&2"
|
|
DEFINE WHERE_CLAUSE="&3"
|
|
|
|
|
|
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"
|
|
, DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT **************************************************************
|
|
PROMPT * D A T A B A S E P A R A M E T E R S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Parameter Name = "&&PARAM_NAME"
|
|
PROMPT * - Instance# = "&&INST_ID"
|
|
PROMPT * - Where Clause = "&&WHERE_CLAUSE"
|
|
PROMPT **************************************************************
|
|
|
|
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN sid HEADING "SID" FORMAT a10
|
|
COLUMN name HEADING "Name" FORMAT a36
|
|
COLUMN type HEADING "Type" FORMAT a11
|
|
COLUMN ordinal HEADING "Ordinal" FORMAT 99
|
|
COLUMN value HEADING "Value" FORMAT a80
|
|
COLUMN isspecified HEADING "Specified?" FORMAT a10
|
|
COLUMN update_comment HEADING "Comment" FORMAT a40
|
|
|
|
SELECT /*
|
|
x.inst_id Instance_id
|
|
, x.indx + 1 num
|
|
,
|
|
*/
|
|
p.SID
|
|
, p.name NAME
|
|
, p.type
|
|
, p.ordinal
|
|
, p.display_value value
|
|
--, p.isspecified
|
|
, p.update_comment
|
|
FROM v$spparameter p
|
|
WHERE ( LOWER(p.name) LIKE LOWER('&&PARAM_NAME') ESCAPE '\'
|
|
)
|
|
--AND p.inst_id like DECODE('&&INST_ID',NULL,'%','','%','&&INST_ID')
|
|
AND p.isspecified = 'TRUE'
|
|
ORDER BY LOWER(REPLACE ( p.name, '_', '')) asc
|
|
, p.SID
|
|
, p.ordinal
|
|
/
|
|
|
|
|
|
@@footer
|