Oracle | DBMS_MAINTPLAN

Oracle DBMS_MAINTPLAN Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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