Oracle DBMS_ISCHED
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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_EVALUATION_CONTEXT_VARS DBMS_OUTPUT SCHEDULER$_CHAIN
ALL_RULE_SETS DBMS_REFRESH SCHEDULER$_CHAIN_LINK
ALL_RULE_SET_RULES DBMS_RULEADM_INTERNAL SCHEDULER$_CHAIN_LINK_LIST
ALL_SCHEDULER_GLOBAL_ATTRIBUTE DBMS_RULE_ADM SCHEDULER$_CLASS
ANYDATA DBMS_RULE_INTERNAL SCHEDULER$_CREDENTIAL
AQ$_AGENT DBMS_SCHEDULER SCHEDULER$_DESTINATIONS
AQ$_REG_INFO DBMS_SCHEDULER_LIB SCHEDULER$_EVENT_LOG
AQ$_REG_INFO_LIST DBMS_SNAPSHOT_KKXRCA SCHEDULER$_GLOBAL_ATTRIBUTE
DBA_DIRECTORIES DBMS_STANDARD SCHEDULER$_INSTANCE_S
DBA_RULES DBMS_SYNC_REFRESH_INTERNAL SCHEDULER$_JOB
DBA_RULE_SETS DBMS_SYSTEM SCHEDULER$_JOB_OUTPUT
DBA_SCHEDULER_CHAIN_RULES DBMS_SYS_ERROR SCHEDULER$_JOB_RUN_DETAILS
DBA_SCHEDULER_CHAIN_STEPS DBMS_TRANSACTION SCHEDULER$_JOB_STEP_TYPE
DBA_SCHEDULER_DB_DESTS DBMS_UTILITY SCHEDULER$_LWJOB_OBJ
DBA_SCHEDULER_EXTERNAL_DESTS DUAL SCHEDULER$_NOTIFICATION
DBA_SCHEDULER_FILE_WATCHERS GLOBAL_NAME SCHEDULER$_STEP
DBA_SCHEDULER_JOBS GV$INSTANCE SCHEDULER$_STEP_STATE
DBA_SCHEDULER_RUNNING_CHAINS JOB$ SCHEDULER$_STEP_TYPE
DBMSOUTPUT_LINESARRAY JOBATTR_ARRAY SCHEDULER$_STEP_TYPE_LIST
DBMS_AQ JOB_DEFINITION_ARRAY SCHEDULER$_VARIABLE_VALUE
DBMS_AQADM OBJ$ SCHEDULER$_VAR_VALUE_LIST
DBMS_AQADM_SYS ODCIVARCHAR2LIST SCHEDULER$_WINDOW_DETAILS
DBMS_ASSERT PLITBLM SCHEDULER_FILEWATCHER_REQUEST
DBMS_CRYPTO RE$ATTRIBUTE_VALUE_LIST SCHEDULER_FILEWATCHER_RESULT
DBMS_IJOB RE$COLUMN_VALUE_LIST USER$
DBMS_IREFRESH RE$NV_ARRAY UTL_ENCODE
DBMS_ISCHED_CHAIN_CONDITION RE$NV_LIST UTL_FILE
DBMS_ISCHED_REMDB_JOB RE$NV_NODE UTL_HTTP
DBMS_ISCHED_UTL RE$RULE_HIT UTL_I18N
DBMS_ISNAPSHOT RE$RULE_HIT_LIST UTL_INADDR
DBMS_JOB RE$TABLE_VALUE_LIST UTL_RAW
DBMS_LOB RE$VARIABLE_TYPE UTL_SMTP
DBMS_LOCK RE$VARIABLE_TYPE_LIST UTL_TCP
DBMS_MVIEW_STATS RE$VARIABLE_VALUE UTL_URL
DBMS_MVIEW_STATS_INTERNAL SCHEDULER$_BATCHERR_ARRAY V$PARAMETER
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_AGENT_DESTINATION NEW_LOG_ID
ADD_EVENT_QUEUE_SUBSCRIBER DROP_CHAIN NORMALIZE_HOST_NAME
ADD_GROUP_MEMBER DROP_CHAIN_RULE NTFY_SVC_METRICS_UPDT
ADD_JOB_EMAIL_NOTIFICATION DROP_CHAIN_STEP OBFUSCATE_CREDENTIAL_PASSWORD
ADD_OUTPUT_BLOB DROP_CREDENTIAL OPEN_WINDOW
ADD_WINDOW_GROUP_MEMBER DROP_DATABASE_DESTINATION PARSE_EMAIL_ADDRESSES
AGENT_INSTALL_POST_STEPS DROP_FILE_WATCHER PRE_ALTER_CHAIN
AGENT_INSTALL_PRE_STEPS DROP_GROUP PRE_CREATE_CHAIN
ALTER_CHAIN DROP_JOB PRE_DROP_CHAIN
ALTER_CHAIN_STEP DROP_JOB_CLASS PURGE_LOG
ALTER_RUNNING_CHAIN DROP_PROGRAM PUT_FILE
AUDIT_SYS_PRIV DROP_PROGRAM_ARGUMENT RAISE_ORACLE_ERROR
BASE64ENCODENONEWLINES DROP_RESOURCE RAISE_SCHLIM_EVT
BATCH_JOB_OPS DROP_SCHEDULE RECORD_RESEND_REQUEST
CHAIN_END DROP_SCHEDULER_ATTRIBUTE REGISTER_CALLBACK
CHAIN_EVAL DROP_SCHEDULER_ATT_INT REMOTE_KILL
CHAIN_EVAL_UPDATE_STEP_STATE DROP_WINDOW REMOVE_EVENT_QUEUE_SUBSCRIBER
CHAIN_KILL DROP_WINDOW_GROUP REMOVE_GROUP_MEMBER
CHAIN_LOG ENABLE REMOVE_JOB_EMAIL_NOTIFICATION
CHAIN_PARSE_STRING ENQ_END_CHAIN_JOB REMOVE_WINDOW_GROUP_MEMBER
CHAIN_START EVALUATE_RULESET RESET_JOBSUFFIX_SEQ
CHAIN_STOP EVALUATE_RUNNING_CHAIN RESET_JOB_ARGUMENT_VALUE
CHECK_AGENT_ACTION_STATUS EVENT_COND_FILTER RESOLVE3_NAME
CHECK_AQ_CBK_PRIVS EXEC_JOB_RUN_LSA RESOLVE_IF_NAMED_DEST
CHECK_COMPAT EXPAND_FILENAME RESOLVE_NAME
CHECK_CREDENTIAL FILE_TRANSFER RETRIEVE_JOB_OUTPUT
CHECK_LOCAL_CREDENTIAL FILE_WATCH_FILTER RUN_CHAIN
CHECK_REQUEST_PRIVS GENERATE_OBJECT_NAME RUN_JOB
CLOSE_WINDOW GEN_AGENT_PASS_SALT SEND_EVENT_EMAIL
COMPLETE_JOB_RUN GET_AGENT_INFO SET_AGENT_REGISTRATION_INFO
CONVERT_DBMS_JOB GET_AGENT_PASS_VERIFIER SET_AGENT_REGISTRATION_PASS
COPY_JOB GET_AGENT_REGISTRATION_INFO SET_BOOL_ATTRIBUTE
CREATE_AGENT_DESTINATION GET_AGENT_VERSION SET_CHAR_ATTRIBUTE
CREATE_CHAIN GET_AGENT_WALLET_LOCATION SET_DATE_ATTRIBUTE
CREATE_CHAIN_STEP GET_BOOL_ATTRIBUTE SET_EVTMSG_ARG
CREATE_CREDENTIAL GET_CHAIN_EVAL_LOCK SET_INTERVAL_ATTRIBUTE
CREATE_DATABASE_DESTINATION GET_CHAIN_RULESET SET_INT_ATTRIBUTE
CREATE_FILE_WATCHER GET_CHAR_ATTRIBUTE SET_JOB_ARGUMENT_VALUE
CREATE_GROUP GET_DATE_ATTRIBUTE SET_JOB_ATTRIBUTES
CREATE_JOB (2) GET_FILE SET_LAST_RUN_TIME
CREATE_JOBS GET_GLOBAL_DB_NAME SET_LIST_ATTRIBUTE
CREATE_JOB_CLASS GET_INTERVAL_ATTRIBUTE SET_RESOURCE_CONSTRAINT
CREATE_LOG_DIR GET_INT_ATTRIBUTE SET_SCHEDULER_ATTRIBUTE
CREATE_PROGRAM GET_LAST_RUN_TIME SET_SECURITY_HEADERS
CREATE_RESOURCE GET_NOTIFICATIONS SHOW_ERRORS
CREATE_SCHEDULE GET_RULE_LINKS STIME
CREATE_WINDOW GET_SCHEDULER_ATTRIBUTE STOP_JOB
CREATE_WINDOW_GROUP GET_STEP_STATE SUBMIT_REMOTE_EXTERNAL_JOB
DEFINE_CHAIN_RULE GET_STEP_STATE_CF SUBMIT_REMOTE_FILE_WATCH
DEFINE_CHAIN_STEP GET_SYS_TIME_ZONE_NAME TRACE_EMAIL
DEFINE_METADATA_ARGUMENT GET_TNS_NVPAIR TRANSLATE_JSSU_ERROR_CODE (2)
DEFINE_PROGRAM_ARGUMENT IS_SCHEDULER_CREATED_AGENT VALIDATE_DEST
DELETE_FILE LOG_DBMS_OUTPUT VALIDATE_EMAIL_ADDRESSES
DISABLE LOG_LOCAL_EXTERNAL_OUTPUT WRITE_FILE_WATCH_TRACE
DISABLE1_CALENDAR_CHECK    
 
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;
/
 
EXPAND_FILENAME
The name is self-explanatory but I can not determine what, specifically, it does at this time 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_GLOBAL_DB_NAME
Returns the global database name dbms_isched.check_compat;
SELECT dbms_isched.get_global_db_name
FROM dual;
 
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_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 connction string dbms_isched.get_tns_nvpair(tns_entry IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_isched.get_tns_nvpair('PDBDEV')
FROM dual;
 
NORMALIZE_HOST_NAME
Returns fully qualified host name dbms_isched.normalize_host_name(hostname IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_isched.normalize_host_name('PERRITO4')
FROM dual;
 
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;
/
 
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;
/
 
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;
 
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.raise_oracle_error(
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
 
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 18cR3
What's New In 19cR3

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