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 (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

Related Topics
Built-in Functions
Built-in Packages
Database Security
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx