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 (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 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx