109 lines
4.2 KiB
SQL
109 lines
4.2 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display all parameters
|
|
* 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
|
|
* --------- ------------ ------------------------------------------------------
|
|
* 26-Jan-15 Vishal Gupta Added where clause input parameter
|
|
* 11-Dec-14 Vishal Gupta Added conditional column display based on DB version
|
|
* 05-Aug-06 Vishal Gupta First Draft
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* 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 name HEADING "Name" FORMAT a36
|
|
COLUMN value HEADING "Value" FORMAT a25
|
|
COLUMN Display_value HEADING "Value" FORMAT a25
|
|
COLUMN type HEADING "Type" FORMAT a10
|
|
COLUMN ordinal HEADING "Ord" 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 isdeprecated HEADING "Depre|cated?" FORMAT a5
|
|
COLUMN description HEADING "Desc" FORMAT a70
|
|
COLUMN update_comment HEADING "Comment" FORMAT a20
|
|
|
|
SELECT p.inst_id
|
|
, p.name
|
|
&&_IF_ORA_10gR1_OR_HIGHER , p.isdeprecated
|
|
, DECODE(p.type
|
|
, 1, 'Boolean'
|
|
, 2, 'String'
|
|
, 3, 'Integer'
|
|
, 4, 'Parameter File'
|
|
, 5, 'Reserved'
|
|
, 6, 'Big Int'
|
|
, p.type
|
|
) TYPE
|
|
, p.ordinal
|
|
&&_IF_ORA_9iR2_OR_LOWER , p.value
|
|
&&_IF_ORA_10gR1_OR_HIGHER , p.display_value
|
|
, p.isdefault
|
|
, p.ismodified
|
|
, p.isadjusted
|
|
, p.isses_modifiable
|
|
, p.issys_modifiable
|
|
&&_IF_ORA_10gR1_OR_HIGHER , p.isinstance_modifiable
|
|
&&_IF_ORA_11gR1_OR_HIGHER , p.isbasic
|
|
, p.description
|
|
--, p.update_comment
|
|
FROM gv$system_parameter2 p
|
|
WHERE ( 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')
|
|
&&WHERE_CLAUSE
|
|
ORDER BY LOWER(REPLACE ( p.name, '_', '')) asc, p.inst_id, p.ordinal
|
|
/
|
|
|
|
@@footer
|