Oracle | DBMS_MAINTPLAN

Oracle DBMS_MAINTPLAN Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Maintenance Plan Utilities. The objects in this package are used used to set/update/delete maintenance plans in CDB$ROOT by user with the EXECUTE_CATALOG_ROLE role.

A maintenance plan can be queried in a PDB via the DB_NOTIFICATIONS view by a user that has the SELECT_CATALOG_ROLE system privilege or the MAINTPLAN_APP system privilege.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
General: Section 1
ACTUAL_ENDTM	VARCHAR2(64)	'actualEndDate'
ACTUAL_STARTM	VARCHAR2(64)	'actualStartDate'
ADDITIONALDATA	VARCHAR2(64)	'additionalData'
DBID	VARCHAR2(64)	'DBID'
DB_UNIQUE_NAME	VARCHAR2(128)	'DB_UNIQUE_NAME'
EXPECTED_ENDTM	VARCHAR2(64)	'expectedEndDate'
EXPECTED_STARTM	VARCHAR2(64)	'expectedStartDate'
INSTANCES	VARCHAR2(64)	'instances'
MAINT_DESC	VARCHAR2(64)	'description'
MAINT_PRODUCT	VARCHAR2(64)	'maintenanceProduct'
MAINT_STATUS	VARCHAR2(64)	'maintenanceStatus'
MAINT_TYPE	VARCHAR2(64)	'maintenanceType'
PATCHID	VARCHAR2(64)	'patchID'
PDBS	VARCHAR2(64)	'pdbs'
PLAN_JSON_DOC	VARCHAR2(64)	'planJsonDoc'
UPDATE_TYPE	VARCHAR2(64)	'updateType'
General: Section 2
NOTIFICATION_TIME	VARCHAR2(64)	'notificationTime'
NOTIFICATION_TYPE	VARCHAR2(64)	'notificationType'
STATUS	VARCHAR2(64)	'status'
General: Section 3
DEFAULT_DATEFORMAT	VARCHAR2(64)	'MM/DD/YYYY HH24:MI:SS TZH:TZM'
General: Section 4
STATUS_PENDING	NUMBER	1
STATUS_RESOLVED	NUMBER	2
STATUS_OBSOLETE	NUMBER	3

Data Types SQL> desc plan_params Name Type ----------------- ------------- NOTIFICATION_TYPE VARCHAR2(64) NOTIFICATION_TIME VARCHAR2(64) STATUS VARCHAR2(64) PLANJSONTYPE CHAR(1) PLANJSONDOC CLOB METHOD ------ FINAL CONSTRUCTOR FUNCTION PLAN_PARAMS RETURNS SELF AS RESULT METHOD ------ MEMBER PROCEDURE SET_PARAM Argument Name Type In/Out ------------------------------ ----------------------- ------ PARAM VARCHAR2 IN PARAM_VAL VARCHAR2 IN METHOD ------ MEMBER FUNCTION GET_PARAM RETURNS VARCHAR2 Argument Name Type In/Out ------------------------------ ----------------------- ------ PARAM VARCHAR2 IN

Dependencies

DB_NOTIFICATIONS	DBMS_SYS_ERROR	MAINTPLAN_SEQ
DBMS_OUTPUT	JSON_ELEMENT_T	PLAN_PARAMS
DBMS_SQL	JSON_OBJECT_T	PLITBLM

Documented No

First Available 20c

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role

Related privileges include SELECT_CATALOG_ROLE and the MAINTPLAN_APP system privilege.

Source {ORACLE_HOME}/rdbms/admin/dbmsmaintplan.sql

Subprograms

DELETE_PLAN	GET_CURRENT_PDB_NAME	INSERT_PLAN_IMPL
DUMP_PLAN	GET_PLAN	UPDATE_PLAN
DUMP_PLANS	INSERT_PLAN

DELETE_PLAN (new 21c)

Deletes maintenance plan based on plan notification id or plan notification id with plan as selector dbms_maintplan.delete_plan( notification_id IN NUMBER, plan IN plan_params DEFAULT NULL); PRAGMA SUPPLEMENTAL_LOG_DATA(delete_plan, AUTO_WITH_COMMIT);

TBD

DUMP_PLAN (new 21c)

Dumps specific maintenance plan record based on the plan notification id dbms_maintplan.dump_plan(notification_id IN NUMBER); PRAGMA SUPPLEMENTAL_LOG_DATA(dump_plan, READ_ONLY);

TBD

DUMP_PLANS (new 21c)

Dumps all maintenance plan record in the table dbms_maintplan.dump_plans;

exec dbms_maintplan.dump_plans;

      

      PL/SQL procedure successfully completed.

      

      ResultSet #1

      

      no rows selected

GET_CURRENT_PDB_NAME (new 21c)

Returns the name of the current container dbms_maintplan.get_current_pdb_name RETURN VARCHAR2; PRAGMA SUPPLEMENTAL_LOG_DATA(get_current_pdb_name, READ_ONLY);

SELECT dbms_maintplan.get_current_pdb_name

      FROM dual;

      

      GET_CURRENT_PDB_NAME

      ---------------------

      TEST21P1

GET_PLAN (new 21c)

Returns maintenance plan record based on the plan notification id or plan the notification id with a plan selector

Overload 1 dbms_maintplan.get_plan( notification_id IN NUMBER, plan IN OUT plan_params); PRAGMA SUPPLEMENTAL_LOG_DATA(get_plan, READ_ONLY);

TBD

Overload 2 dbms_maintplan.get_plan( notification_id IN NUMBER, plancursor OUT SYS_REFCURSOR); PRAGMA SUPPLEMENTAL_LOG_DATA(get_plan, READ_ONLY);

TBD

INSERT_PLAN (new 21c)

Inserts a maintenance plan with details into the notification table dbms_maintplan.insert_plan( plan IN plan_params, notification_id OUT NUMBER); PRAGMA SUPPLEMENTAL_LOG_DATA(insert_plan, NONE);

TBD

INSERT_PLAN_IMPL (new 21c)

Implements inserting a maintenance plan dbms_maintplan.insert_plan_impl( plan IN plan_params, notification_id IN NUMBER); PRAGMA SUPPLEMENTAL_LOG_DATA(insert_plan_impl, AUTO_WITH_COMMIT);

TBD

UPDATE_PLAN (new 21c)

Updates a maintenance plan with details dbms_maintplan.upate_plan( notification_id IN NUMBER, plan IN plan_params); PRAGMA SUPPLEMENTAL_LOG_DATA(update_plan, AUTO_WITH_COMMIT);

TBD