66 lines
2.9 KiB
SQL
66 lines
2.9 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display resource plan details
|
|
* Parameters : 1 - Resource Plan Name (Use % as wildcard escape with '\', Default value '%')
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 02-Jul-12 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE PLAN_NAME
|
|
DEFINE PLAN_NAME="&&1"
|
|
|
|
set term off
|
|
COLUMN _PLAN_NAME NEW_VALUE PLAN_NAME NOPRINT
|
|
SELECT DECODE('&&PLAN_NAME','','%','&&PLAN_NAME') "_PLAN_NAME"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
COLUMN plan HEADING "PlanName" FORMAT a30
|
|
COLUMN sub_plan HEADING "Sub|Plan" FORMAT a4
|
|
COLUMN status HEADING "Status" FORMAT a5
|
|
COLUMN created HEADING "Created" FORMAT a18
|
|
COLUMN num_plan_directives HEADING "Plan|Direct|ives|(#)" FORMAT 99
|
|
COLUMN cpu_method HEADING "CPU|Method" FORMAT a10
|
|
COLUMN mgmt_method HEADING "MGMT|Method" FORMAT a10
|
|
COLUMN queueing_mth HEADING "Queueing|Method" FORMAT a12
|
|
COLUMN comments HEADING "Comments" FORMAT a40
|
|
COLUMN Details HEADING "Details" FORMAT a120
|
|
|
|
SELECT p.plan
|
|
, 'SubPlan? - ' || p.sub_plan || chr(10) ||
|
|
'Created - ' || to_char(o.ctime,'DD-MON-YY HH24:MI:SS') || chr(10) ||
|
|
'Modified - ' || to_char(o.mtime,'DD-MON-YY HH24:MI:SS') || chr(10) ||
|
|
'Status - ' || p.status || chr(10) ||
|
|
'Mandatory - ' || p.mandatory || chr(10) ||
|
|
'Number of Plan Directives - ' || p.num_plan_directives || chr(10) ||
|
|
'CPU Method - ' || p.cpu_method || chr(10) ||
|
|
'MGMT Method - ' || p.mgmt_method || chr(10) ||
|
|
'Active Session Pool Method - ' || p.active_sess_pool_mth || chr(10) ||
|
|
'Parallel Degree Limit Method - ' || p.parallel_degree_limit_mth || chr(10) ||
|
|
'Queueing Method - ' || p.queueing_mth || chr(10) ||
|
|
'Comments - ' || p.comments Details
|
|
FROM dba_rsrc_plans p
|
|
, sys.obj$ o
|
|
WHERE o.obj# = p.plan_id
|
|
AND upper(p.plan) like upper('&&PLAN_NAME') ESCAPE '\'
|
|
ORDER BY p.plan
|
|
;
|
|
|
|
|
|
@@footer
|