140 lines
4.9 KiB
SQL
140 lines
4.9 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display all parameters modified at runtime but not specified in 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
|
|
* --------- ------------ -----------------------------------------
|
|
* 31-MAR-14 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 * (Modified at runtime, but not specified in spfile)
|
|
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 name HEADING "Name" FORMAT a36
|
|
COLUMN value HEADING "Value" FORMAT a25
|
|
COLUMN type HEADING "Type" FORMAT a10
|
|
COLUMN ordinal HEADING "Ordinal" FORMAT 99
|
|
COLUMN isdefault HEADING "Def|ault?" FORMAT a5
|
|
COLUMN ismodified HEADING "Modi|fied?" FORMAT a8
|
|
COLUMN isadjusted HEADING "Adjus|ted?" FORMAT a5
|
|
COLUMN isses_modifiable HEADING "Sess|Modif|iable?" FORMAT a9
|
|
COLUMN issys_modifiable HEADING "Sys|Modif|iable?" FORMAT a9
|
|
COLUMN isinstance_modifiable HEADING "Inst|Modif|iable?" FORMAT a9
|
|
COLUMN isinstance_modifiable HEADING "Inst|Modif|iable?" FORMAT a9
|
|
COLUMN isbasic HEADING "Basic" FORMAT a5
|
|
COLUMN description HEADING "Desc" FORMAT a70
|
|
COLUMN update_comment HEADING "Comment" FORMAT a20
|
|
|
|
COLUMN display_value HEADING "RunTime Value" FORMAT a25
|
|
COLUMN spfile_display_value HEADING "SP Value" FORMAT a25
|
|
|
|
/*
|
|
SELECT p.SID
|
|
, p.name NAME
|
|
, p.type
|
|
, p.ordinal
|
|
, p.display_value value
|
|
--, p.isspecified
|
|
, p.update_comment
|
|
FROM gv$system_parameter2 p
|
|
, gv$instance i
|
|
, v$spparameter sp
|
|
WHERE p.inst_id = i.inst_id
|
|
AND p.isdefault <> 'TRUE'
|
|
AND LOWER(p.name) = LOWER(sp.name (+))
|
|
AND CASE WHEN sp.sid = '*' THEN TRUE
|
|
ELSE LOWER(sp.sid) = LOWER(p.instance_name || p.instance_number (+) )
|
|
END
|
|
AND sp.isspecified (+) = 'TRUE'
|
|
ORDER BY LOWER(REPLACE ( p.name, '_', '')) asc
|
|
, p.SID
|
|
, p.ordinal
|
|
/
|
|
*/
|
|
|
|
SELECT p.inst_id
|
|
, p.name NAME
|
|
, DECODE(p.type
|
|
, 1, 'Boolean'
|
|
, 2, 'String'
|
|
, 3, 'Integer'
|
|
, 4, 'Parameter File'
|
|
, 5, 'Reserved'
|
|
, 6, 'Big Int'
|
|
, p.type
|
|
) TYPE
|
|
, p.ordinal
|
|
, p.isdefault
|
|
, p.ismodified
|
|
, p.isadjusted
|
|
, p.display_value
|
|
, sp.display_value spfile_display_value
|
|
--, p.isses_modifiable
|
|
--, p.issys_modifiable
|
|
--, p.isinstance_modifiable
|
|
--, p.isbasic
|
|
--, p.description
|
|
FROM gv$system_parameter2 p
|
|
JOIN gv$instance i ON p.inst_id = i.inst_id
|
|
LEFT OUTER JOIN v$spparameter sp ON sp.isspecified = 'TRUE'
|
|
AND LOWER(p.name) = LOWER(sp.name)
|
|
AND p.ordinal = sp.ordinal
|
|
AND CASE WHEN sp.sid = '*' THEN LOWER(i.instance_name ) ELSE LOWER(sp.sid) END = LOWER(i.instance_name)
|
|
WHERE 1=1
|
|
AND ( LOWER(p.name) LIKE LOWER('&&PARAM_NAME') ESCAPE '\'
|
|
OR LOWER(p.description) LIKE LOWER('&&PARAM_NAME') ESCAPE '\'
|
|
)
|
|
AND p.inst_id like DECODE('&&INST_ID',NULL,'%','','%','&&INST_ID')
|
|
AND ( ( p.ismodified = 'MODIFIED' AND NVL(p.display_value,'x') <> NVL(sp.display_value,'x'))
|
|
OR ( p.ismodified = 'FALSE' AND p.isdefault = 'FALSE' AND NVL(p.display_value,'x') <> NVL(sp.display_value,'x') )
|
|
)
|
|
ORDER BY LOWER(REPLACE ( p.name, '_', '')) asc, p.inst_id, p.ordinal
|
|
/
|
|
|
|
|
|
@@footer
|