Oracle DBMS_ISCHED
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 An undocumented internal package containing 140 separate functions and procedures. I have only documented one of them here so as not to lose work performed when this procedure was part of DBMS_SCHEDULER.
AUTHID DEFINER
Dependencies
ALL_SCHEDULER_GLOBAL_ATTRIBUTE DBMS_REDEFINITION SCHEDULER$_DBMSJOB_MAP
ANYDATA DBMS_REDEFINITION_INTERNAL SCHEDULER$_DESTINATIONS
AQ$_AGENT DBMS_SCHEDULER SCHEDULER$_EVENT_LOG
AQ$_REG_INFO DBMS_SCHEDULER_LIB SCHEDULER$_GLOBAL_ATTRIBUTE
AQ$_REG_INFO_LIST DBMS_SCHED_MAIN_EXPORT SCHEDULER$_INSTANCE_S
DBA_DIRECTORIES DBMS_SNAPSHOT_KKXRCA SCHEDULER$_JOB
DBA_RULE_SETS DBMS_STANDARD SCHEDULER$_JOB_EVENT_HANDLER
DBA_SCHEDULER_DB_DESTS DBMS_SYSTEM SCHEDULER$_JOB_OUTPUT
DBA_SCHEDULER_EXTERNAL_DESTS DBMS_SYS_ERROR SCHEDULER$_JOB_RUN_DETAILS
DBA_SCHEDULER_FILE_WATCHERS DBMS_TRANSACTION SCHEDULER$_JOB_STEP_TYPE
DBA_SCHEDULER_JOBS DBMS_UTILITY SCHEDULER$_LWJOB_OBJ
DBA_SCHEDULER_RUNNING_CHAINS DUAL SCHEDULER$_NOTIFICATION
DBMSOUTPUT_LINESARRAY GLOBAL_NAME SCHEDULER$_STEP
DBMS_AQ GV$INSTANCE SCHEDULER$_STEP_STATE
DBMS_AQADM JOB$ SCHEDULER$_STEP_TYPE
DBMS_AQADM_SYS JOBATTR_ARRAY SCHEDULER$_STEP_TYPE_LIST
DBMS_ASSERT JOBSEQ SCHEDULER$_VARIABLE_VALUE
DBMS_CHAIN_INVOKER JOB_DEFINITION SCHEDULER$_VAR_VALUE_LIST
DBMS_CREDENTIAL JOB_DEFINITION_ARRAY SCHEDULER$_WINDOW_DETAILS
DBMS_CRYPTO OBJ$ SCHEDULER_FILEWATCHER_REQUEST
DBMS_IJOB ODCIVARCHAR2LIST SCHEDULER_FILEWATCHER_RESULT
DBMS_IREFRESH PLITBLM USER$
DBMS_ISCHEDFW RE$NV_ARRAY UTL_ENCODE
DBMS_ISCHED_AGENT RE$NV_LIST UTL_FILE
DBMS_ISCHED_CHAIN_CONDITION RE$NV_NODE UTL_HTTP
DBMS_ISCHED_REMDB_JOB RE$RULE_HIT UTL_I18N
DBMS_ISCHED_REMOTE_ACCESS RE$RULE_HIT_LIST UTL_INADDR
DBMS_ISCHED_UTL RE$VARIABLE_VALUE UTL_RAW
DBMS_I_INDEX_UTL SCHEDULER$NTFY_SVC_METRICS UTL_SMTP
DBMS_JOB SCHEDULER$_BATCHERR_ARRAY UTL_TCP
DBMS_LOB SCHEDULER$_CHAIN UTL_URL
DBMS_LOCK SCHEDULER$_CHAIN_LINK V$ACTIVE_INSTANCES
DBMS_LOGREP_UTIL SCHEDULER$_CHAIN_LINK_LIST V$DATABASE
DBMS_LOGSTDBY SCHEDULER$_CLASS V$INSTANCE
DBMS_OUTPUT SCHEDULER$_CREDENTIAL V$PARAMETER
DBMS_PRVTAQIP    
Documented No
First Available Not known
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvthsch.plb
Subprograms
ADD_AGENT_CERT DROP_FILE_WATCHER NEXTVALS
ADD_EVENT_QUEUE_SUBSCRIBER DROP_GROUP NEXTVALSBY
ADD_GROUP_MEMBER DROP_JOB NORMALIZE_HOST_NAME
ADD_JOB_EMAIL_NOTIFICATION DROP_JOB_CLASS NTFY_SVC_METRICS_UPDT
ADD_JOB_EMAIL_NOTIFICATION_PH3 DROP_PROGRAM OBFUSCATE_CREDENTIAL_PASSWORD
ADD_OUTPUT_BLOB DROP_PROGRAM_ARGUMENT OPEN_WINDOW
ADD_WINDOW_GROUP_MEMBER DROP_RESOURCE PARSE_EMAIL_ADDRESSES
AGENT_INSTALL_POST_STEPS DROP_SCHEDULE PRE_ALTER_CHAIN
AGENT_INSTALL_PRE_STEPS DROP_SCHEDULER_ATTRIBUTE PURGE_LOG
ALTER_CHAIN DROP_SCHEDULER_ATT_INT PUSER_QUERY
ALTER_CHAIN_STEP DROP_WINDOW PUT_FILE
ALTER_RUNNING_CHAIN DROP_WINDOW_GROUP RAISE_ORACLE_ERROR
AUDIT_SYS_PRIV ENABLE RAISE_SCHLIM_EVT
BASE64ENCODENONEWLINES ENQ_END_CHAIN_JOB RECORD_RESEND_REQUEST
BATCH_JOB_OPS EVALUATE_RUNNING_CHAIN REGISTER_CALLBACK
CDROLE EVENT_COND_FILTER REMOTE_KILL
CHAIN_END EXEC_JOB_RUN_LSA REMOVE_EVENT_QUEUE_SUBSCRIBER
CHAIN_EVAL EXPAND_FILENAME REMOVE_GROUP_MEMBER
CHAIN_EVAL_UPDATE_STEP_STATE EXPORT_DBMS_JOB REMOVE_JOB_EMAIL_NOTIFICATION
CHAIN_KILL EXPORT_MYINST REMOVE_WINDOW_GROUP_MEMBER
CHAIN_LOG EXPORT_SCHEMA_ARGUMENTS RESET_JOBSUFFIX_SEQ
CHAIN_PARSE_STRING FILE_TRANSFER RESET_JOB_ARGUMENT_VALUE
CHAIN_START FILE_WATCH_FILTER RESET_NO_COMMIT_FLAG
CHAIN_STOP GENERATE_OBJECT_NAME RESOLVE3_NAME
CHECK_AGENT_ACTION_STATUS GEN_AGENT_PASS_SALT RESOLVE_IF_NAMED_DEST
CHECK_COMPAT GET_AGENT_INFO RESOLVE_NAME
CHECK_CREDENTIAL GET_AGENT_PASS_VERIFIER RETRIEVE_JOB_OUTPUT
CHECK_LOCAL_CREDENTIAL GET_AGENT_REGISTRATION_INFO RUN_CHAIN
CHECK_REQUEST_PRIVS GET_AGENT_VERSION RUN_DBMS_JOB
CLOSE_WINDOW GET_AGENT_WALLET_LOCATION RUN_JOB
COMPLETE_JOB_RUN GET_BOOL_ATTRIBUTE SEND_EVENT_EMAIL
CONVERT_DBMS_JOB GET_CHAIN_EVAL_LOCK SET_AGENT_REGISTRATION_INFO
COPY_JOB GET_CHAIN_OID SET_AGENT_REGISTRATION_PASS
CREATE_AGENT_DESTINATION GET_CHAIN_RULESET SET_BOOL_ATTRIBUTE
CREATE_CHAIN GET_CHAR_ATTRIBUTE SET_CHAR_ATTRIBUTE
CREATE_CHAIN_STEP GET_DATE_ATTRIBUTE SET_DATE_ATTRIBUTE
CREATE_CLOUD_CREDENTIAL GET_DB_EPOCH SET_EVTMSG_ARG
CREATE_CREDENTIAL GET_FILE SET_INTERVAL_ATTRIBUTE
CREATE_DATABASE_DESTINATION GET_FIXED_SALT_STRING SET_INT_ATTRIBUTE
CREATE_FILE_WATCHER GET_GLOBAL_DB_NAME SET_JOB_ARGUMENT_VALUE
CREATE_GROUP GET_INTERVAL_ATTRIBUTE SET_JOB_ATTRIBUTES
CREATE_JOB (2) GET_INT_ATTRIBUTE SET_LAST_RUN_TIME
CREATE_JOBS GET_JOBS_TO_STOP SET_LIST_ATTRIBUTE
CREATE_JOB_CLASS GET_JOB_OID SET_NO_COMMIT_FLAG
CREATE_LOG_DIR GET_JOB_STATUS SET_RESOURCE_CONSTRAINT
CREATE_PROGRAM GET_LAST_RUN_TIME SET_SCHEDULER_ATTRIBUTE
CREATE_RESOURCE GET_NOTIFICATIONS SET_SECURITY_HEADERS
CREATE_SCHEDULE GET_RULE_LINKS SET_STEP_STATE
CREATE_WINDOW GET_SCHEDULER_ATTRIBUTE SHOW_ERRORS
CREATE_WINDOW_GROUP GET_SEC_FROM_EPOCH_TO_YEAR STIME
DB_COMPATIBILITY GET_STEP_STATE STOP_JOB
DEFINE_METADATA_ARGUMENT GET_STEP_STATE_CF SUBMIT_REMOTE_EXTERNAL_JOB
DEFINE_PROGRAM_ARGUMENT GET_SYS_TIME_ZONE_NAME SUBMIT_REMOTE_FILE_WATCH
DELETE_DBMSJOB_MAP_ENTRY GET_TNS_NVPAIR TRACE_EMAIL
DELETE_FILE INSERT_DBMSJOB_MAP_ENTRY TRANSLATE_JSSU_ERROR_CODE (2)
DISABLE INSERT_STEP_STATE UPDATE_ALL_STEPS
DISABLE1_CALENDAR_CHECK INST_CHECK UPDATE_DBMS_JOB
DROP_AGENT_DESTINATION IS_SCHEDULER_CREATED_AGENT USER_EXPORT
DROP_CHAIN LOG_DBMS_OUTPUT VALIDATE_DEST
DROP_CREDENTIAL LOG_LOCAL_EXTERNAL_OUTPUT VALIDATE_EMAIL_ADDRESSES
DROP_DATABASE_DESTINATION NEW_LOG_ID WRITE_FILE_WATCH_TRACE
 
AGENT_INSTALL_POST_STEPS
Undocumented dbms_isched.agent_install_post_steps;
exec dbms_isched.agent_install_post_steps;
 
AGENT_INSTALL_PRE_STEPS
Undocumented dbms_isched.agent_install_pre_steps;
exec dbms_isched.agent_install_pre_steps;
 
CHECK_COMPAT
Undocumented dbms_isched.check_compat;
exec dbms_isched.check_compat;
 
CONVERT_DBMS_JOB
Convert a dbms_job api created job into a scheduler job the dbms_job equivalent job will be removed.

As this is officially undocumented I am leaving this as it is until I learn more.

Relocated from DBMS_SCHEDULER ... I think ... but when?
dbms_isched.convert_dbms_job(job_name IN VARCHAR2);
Step 1: Create a job

CREATE OR REPLACE PROCEDURE test_job IS
BEGIN
  NULL;
END test_job;
/

DECLARE
 JobNo user_jobs.job%TYPE;
BEGIN
  dbms_job.submit(JobNo, 'begin test_job; end;', SYSDATE, 'SYSDATE + 36/86400');
  COMMIT;
END;
/

SELECT job, schema_user
FROM dba_jobs;

Step 2: Convert it

-- This might actually work if DBMS_JOB created jobs had VARCHAR2 names.
-- Unfortunately they do not so I can not puzzle this one out unless I
-- assume the information in the rdbms/admin file is incorrect.
 
CREATE_AGENT_DESTINATION
Code from execsch.sql in /RDBMS/ADMIN dbms_isched.create_agent(
destination_name IN VARCHAR2,
hostname         IN VARCHAR2,
port             IN BINARY_INTEGER,
comments         IN VARCHAR2);
--create pseudo local db destination
BEGIN
  dbms_scheduler.create_database_destination(
    destination_name => 'sched$_local_pseudo_db',
    agent => 'sched$_local_pseudo_agent',
    tns_name => 'pseudo_inst',
    comments => 'Place holder for synonym LOCAL_DB dest');
EXCEPTION
  WHEN OTHERS THEN
    IF sqlcode = -27477 THEN
      NULL;
    ELSE
      RAISE;
    END IF;
END;
/
 
CREATE_LOG_DIR
Creates the scheduler logging directory: I think. dbms_isched.check_compat;
--source code from {ORACLE_HOME}/rdbms/admin/execsch.sql
BEGIN
  dbms_isched.create_log_dir;
END;
/
 
DB_COMPATIBILITY
Returns the major version number from the compatibility parameter dbms_isched.db_compatibility RETURN NUMBER;
SELECT dbms_isched.db_compatibility
FROM dual;

DB_COMPATIBILITY
----------------
              19
 
EXPAND_FILENAME
Expands the file name to include the path from the root directory dbms_isched.expand_filename(source_file IN VARCHAR2) RETURN VARCHAR2;
TBD
 
FILE_WATCH_JOB
Undocumented dbms_isched.file_watch_job;
exec dbms_isched.file_watch_job;
 
GET_AGENT_PASS_VERIFIER
Not sure for what it is intended but the output could lend itself to a number of security-related usages dbms_isched.get_agent_pass_verifier(
password IN  RAW,
iter     OUT BINARY_INTEGER,
salt     OUT RAW,
hash     OUT RAW);
DECLARE
 pwd  RAW(128) := utl_raw.cast_to_raw('Morgan');
 iter BINARY_INTEGER;
 salt RAW(128);
 hash RAW(128);
BEGIN
  dbms_isched.get_agent_pass_verifier(pwd, iter, salt, hash);
  dbms_output.put_line(pwd);
  dbms_output.put_line(TO_CHAR(iter));
  dbms_output.put_line(salt);
  dbms_output.put_line(hash);
END;
/
 
GET_DB_EPOCH
Undocumented dbms_isched.get_db_epoch RETURN NUMBER;
SELECT dbms_isched.get_db_epoch
FROM dual;

GET_DB_EPOCH
------------
1.6011E+12

col get_db_epoch format 99999999999999

SQL> col get_db_epoch format 99999999999999
SQL> /

   GET_DB_EPOCH
---------------
  1601095689728
 
GET_FIXED_SALT_STRING
Undocumented dbms_isched.get_fixed_salt_string RETURN VARCHAR2;
SELECT dbms_isched.get_fixed_salt_string
FROM dual;

GET_FIXED_SALT_STRING
----------------------
1576800000
 
GET_GLOBAL_DB_NAME
Returns the global database name dbms_isched.check_compat;
SELECT dbms_isched.get_global_db_name
FROM dual;

GET_GLOBAL_DB_NAME
-------------------
ORABASEX
 
GET_LAST_RUN_TIME
Undocumented dbms_isched.get_last_run_time RETURN TIMESTAMP WITH TIME ZONE;
SELECT dbms_isched.get_last_run_time
FROM dual;

GET_LAST_RUN_TIME
------------------

 
GET_SYS_TIME_ZONE_NAME
Returns the servers current time zone name dbms_isched.get_sys_time_zone_name(tzname OUT VARCHAR2);
DECLARE
  tz VARCHAR2(20);
BEGIN
  dbms_isched.get_time_zone_name(tz);
  dbms_output.put_line(tz);
END;
/
dbms_isched.check_compat;
 
GET_TNS_NVPAIR
Returns the TNSNAMES.ORA connection string Name Value (NV) pair dbms_isched.get_tns_nvpair(tns_entry IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_isched.get_tns_nvpair('PDBDEV')
FROM dual;

DBMS_ISCHED.GET_TNS_NVPAIR('PDBDEV')
------------------------------------------------------------------------------------
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbdev)))
 
INST_CHECK
Undocumented dbms_isched.inst_check(
instance IN NUMBER,
force    IN BOOLEAN);
exec dbms_isched.inst_check(1, FALSE);
 
NORMALIZE_HOST_NAME
Returns fully qualified host name dbms_isched.normalize_host_name(hostname IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_isched.normalize_host_name('PERRITO5')
FROM dual;

DBMS_ISCHED.NORMALIZE_HOST_NAME('PERRITO5')
--------------------------------------------
PERRITO5.hsd1.mn.comcast.net
 
OBFUSCATE_CREDENTIAL_PASSWORD
Every time this procedure is executed it generates a different password obfuscating the input string dbms_isched.obfuscate_credential_password(
credential_name IN  VARCHAR2,
password        IN  VARCHAR2,
opass           OUT VARCHAR2,
cur_schema      IN  VARCHAR2);
DECLARE
 opassVal VARCHAR2(120);
BEGIN
  dbms_isched.obfuscate_credential_password('UWCred', 'ZZYZX', opassVal, USER);
  dbms_output.put_line(opassVal);
END;
/
FR0iwm6HnOuuFLFMYaiiNjPmPZpB7l+RP1cIUPJ3nrMJ

PL/SQL procedure successfully completed.
 
PARSE_EMAIL_ADDRESSES
Turns a comma delimited list of email addresses into a VARCHAR2 table dbms_isched.parse_email_addresses(
comma_separated_list IN  VARCHAR2,
addresses_list       OUT dbms_utility.lname_array);
DECLARE
 retTab dbms_utility.lname_array;
BEGIN
  dbms_isched.parse_email_addresses('larry@orcl.com,mark@orcl.com,safra@orcl.com', retTab)
  FOR i IN 1 .. 3 LOOP
    dbms_output.put_line(retTab(i));
  END LOOP;
END;
/
 
RAISE_ORACLE_ERROR
Raises a DBMS_SCHEDULER specific error dbms_isched.raise_oracle_error(
error_number IN BINARY_INTEGER,
params_table IN dbms_utility.lname_array);
DECLARE
 paramArr dbms_utility.lname_array;
BEGIN
  paramArr(1) := 'operation not permitted on lightweight and in-memory jobs';
  dbms_isched.raise_oracle_error(27494, ParamArr);
END;
/
 
REMOTE_KILL
Undocumented but I really hope it proves to do what the name and parameters imply dbms_isched.remote_kill(
job_name         IN VARCHAR2,
job_subname      IN VARCHAR2,
job_owner        IN VARCHAR2,
credential_name  IN VARCHAR2,
credential_owner IN VARCHAR2,
destination      IN VARCHAR2,
hard_kill        IN BINARY_INTEGER);
TBD
 
RESET_JOBSUFFIX_SEQ
Undocumented dbms_isched.reset_jobsuffix_seq;
exec dbms_isched.reset_jobsuffix_seq;
 
RESET_NO_COMMIT_FLAG
Undocumented dbms_isched.reset_no_commit_flag;
exec dbms_isched.reset_no_commit_flag;
 
RESOLVE_NAME
Either separates owner and object name from a fully qualified object name or, if owner is not supplied provides a substitute. dbms_isched.resolve_name(
full_name     IN  VARCHAR2,
object_name   OUT VARCHAR2,
owner_name    OUT VARCHAR2,
default_owner IN  VARCHAR2);
DECLARE
 objName VARCHAR2(30);
 ownName VARCHAR2(30);
BEGIN
  dbms_isched.resolve_name('UWCLASS.SERVERS', objName, ownName, 'UNK');
  dbms_output.put_line('Object: ' || objName);
  dbms_output.put_line('Owner: ' || ownName);
END;
/

DECLARE
 objName VARCHAR2(30);
 ownName VARCHAR2(30);
BEGIN
  dbms_isched.resolve_name('SERVERS', objName, ownName, 'UNK');
  dbms_output.put_line('Object: ' || objName);
  dbms_output.put_line('Owner: ' || ownName);
END;
/
 
SEND_EVENT_EMAIL
Undocumented dbms_isched.raise_oracle_error(
email_server_host IN VARCHAR2,
email_server_port IN BINARY_INTEGER,
send              IN VARCHAR2,
recipient         IN VARCHAR2,
subject           IN VARCHAR2,
body              IN VARCHAR2,
wallet_path       IN VARCHAR2,
email_server_cred IN VARCHAR2,
email_server_end  IN VARCHAR2);
TBD
 
SET_NO_COMMIT_FLAG
Undocumented dbms_isched.set_no_commit_flag;
exec dbms_isched.set_no_commit_flag;
 
STIME
Undocumented dbms_isched.stime(
follows_default_timezone IN  BOOLEAN,
outs                     OUT TIMESTAMP WITH TIME ZONE);
DECLARE
 outVal TIMESTAMP WITH TIME ZONE;
BEGIN
  dbms_isched.stime(TRUE, outVal);
  dbms_output.put_line(outVal);
END;
/
26-SEP-20 09.41.34.928000 AM US/CENTRAL

PL/SQL procedure successfully completed.
 
VALIDATE_EMAIL_ADDRESSES
Undocumented dbms_isched.validate_email_addresses(
single_address IN VARCHAR2,
addresses_list IN dbms_utility.lname_array);
-- note the missing "@" in the following demo
DECLARE
 addrList dbms_utility.lname_array;
BEGIN
  dbms_isched.validate_email_addresses('damorgan_oracle.com', addrList);
END;
/
*
ERROR at line 1:
ORA-24098: invalid value damorgan_yahoo.com for EMAIL_ADDRESS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_ISCHED", line 8931
ORA-06512: at line 4


-- with the "@" in place
DECLARE
 addrList dbms_utility.lname_array;
BEGIN
  dbms_isched.validate_email_addresses('damorgan@oracle.com', addrList);
END;
/

DECLARE
 addrList dbms_utility.lname_array;
BEGIN
  addrList(1) := 'a@b.com';
  addrList(2) := 'b@c.com';
  addrList(3) := 'c@d.com';
  dbms_isched.validate_email_addresses(NULL, addrList);
END;
/
 
WRITE_FILE_WATCH_TRACE
Undocumented but writes a file watcher trace

-- demo from initscfw.sql
dbms_isched.remote_kill(
job_name         IN VARCHAR2,
job_subname      IN VARCHAR2,
job_owner        IN VARCHAR2,
credential_name  IN VARCHAR2,
credential_owner IN VARCHAR2,
destination      IN VARCHAR2,
hard_kill        IN BINARY_INTEGER);
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "dbFWTrace" AS

import oracle.scheduler.agent.fileWatchTrace;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import java.sql.Connection;

public class dbFWTrace implements fileWatchTrace
{
  public void writeTrace(String do_trc, String trc_string)
  {
    if (do_trc.equals("Y"))
    {
      try
      {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:default:connection");
        Connection conn = ods.getConnection();
        OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall(
            "{call dbms_isched.write_file_watch_trace(?, ?)}");
        ocs.setString(1, do_trc);
        ocs.setString(2, trc_string);
        ocs.executeUpdate();
        ocs.close();
      }
      catch (java.sql.SQLException sqlexception)
      {
        // ignore for now
      }
    }
  }
}
/

Related Topics
Built-in Functions
Built-in Packages
DBMS_ISCHEDFW
DBMS_ISCHED_AGENT
DBMS_ISCHED_CHAIN_CONDITION
DBMS_SCHEDULER
DBMS_SCHED_ARGUMENT_IMPORT
DBMS_SCHED_ATTRIBUTE_EXPORT
DBMS_SCHED_FILE_WATCHER_EXPORT
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