Ora-27466 Internal Scheduler Error 1870
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Not able to stop dbms_scheduler job up vote 0 down vote favorite SQL> Exec Dbms_Scheduler.stop_job('US_ALERT',true); begin Dbms_Scheduler.stop_job('US_ALERT',true); end; ORA-27466: internal scheduler error: 1870 ORA-06512: at "SYS.DBMS_ISCHED", line 227 ORA-06512: at "SYS.DBMS_SCHEDULER", line 674 ORA-06512: at line 1 Does anyone has any idea of what's going wrong here ? Database : Oracle Version : 12c oracle plsql dbms-scheduler share|improve this question asked Apr 13 at 12:17 Prashant Mishra 381317 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote This error is caused by the stack being unwound by unhandled exceptions in pl/SQL code. Most likely the scheduler is executing a procedure with some bad code in it. (See http://www.techonthenet.com/oracle/errors/ora06512.php) The first thing to do is drop the job BEGIN DBMS_SCHEDULER.DROP_JOB('myjob1'); END; / Then start investigating your stored procedure(s) and add in some exception handling. For example you might have some pl/sql code as follows DECLARE pe_ratio NUMBER(3,1); BEGIN SELECT price / earnings INTO pe_ratio FROM stocks WHERE symbol = 'XYZ'; -- might cause division-by-zero error INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio); COMMIT; End; If this was called by your job it could result in a ora 06512. (You probably will need to add in some logging/traces into to your pl/SQL to narrow this down, the line numbers in the error messages you reported may also help) Replace it with DECLARE pe_ratio NUMBER(3,1); BEGIN SELECT price / earnings INTO pe_ratio FROM stocks WHERE symbol = 'XYZ'; -- might cause division-by-zero error INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio); COMMIT; EXCEPTION -- exception handlers begin WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL); COMMIT; ... WHEN
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
Oracle Scheduling Implementing Error Checking Routines Oracle Tips by Burleson Consulting Implementing Error Checking Routines Proper error handling is an import part of implementing robust job scheduling. Depending on http://stackoverflow.com/questions/36598111/not-able-to-stop-dbms-scheduler-job the scheduling mechanism job failures can have differing effects. For jobs scheduled using the dbms_job package, 16 consecutive failures will result in the job being marked as broken. The following definition creates a job that will fail on every run. * create_job_failure.sql BEGIN DBMS_JOB.isubmit (1000, 'BEGIN RAISE_APPLICATION_ERROR(-20000, ''Error''); END;' , http://www.dba-oracle.com/job_scheduling/error_checking_routines.htm SYSDATE, 'SYSDATE + INTERVAL ''1'' SECOND'); COMMIT; END; / The job_failures.sql script queries the dba_jobs view allowing the progress of the job to be monitored. * job_failures.sql -- ************************************************* -- Copyright ? 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select job, broken, failures from dba_jobs where job = DECODE(UPPER('&1'), 'ALL', job, &1) ; The output of this query is displayed below. SQL> @job_failures.sql 1000 JOB B FAILURES ---------- - ---------- 1000 N 14 1 row selected. After 16 failures, the broken flag has been set. SQL> @job_failures.sql 1000 JOB B FAILURES ---------- - ---------- 1000 Y 16 1 row selected. Once the problem with the job is rectified, it could be restarted using tна форуме
use of cookies. For further details on cookies, please see our cookies policy. Hide this message ProductsCustomer ServiceCustomer ServiceNetwork ManagementEnterprise Operations Console (EOC)Failover Engine (FoE)IP Address Manager (IPAM)Netflow Traffic Analyzer (NTA)Network Configuration Manager (NCM)Network Performance Monitor (NPM)Network Topology Mapper (NTM)User Device Tracker (UDT)VoIP & Network Quality Manager (VNQM)Applications & SystemsDatabase Performance Analyzer (DPA)Server & Application Monitor (SAM)Storage Manager (STM)Storage Resource Monitor (SRM)Virtualization Manager (VMAN)Web Performance Monitor (WPM)Security & ComplianceFirewall Security Manager (FSM)Log & Event Manager (LEM)Patch ManagerToolsAlert CentralDameWare Remote Support & Mini Remote ControlEngineer's ToolSet (ETS)ipMonitorKiwi CatToolsKiwi Syslog ServerLicense ManagerMobile AdminServ-U Managed File Transfer & Serv-U FTP ServerWeb Help Desk (WHD) thwack communityCustomer PortalSubmit a ticketCall usGet Started with Product Getting Started GuidesNew to Network Configuration Manager? Check out the NCM Getting Started Guide. Success CenterAssetsSearchSuccess CenterDatabase Performance Analyzer (DPA)Alert CentralCustomer ServiceDameWare Remote Support & Mini Remote ControlDatabase Performance Analyzer (DPA)Engineer's ToolSet (ETS)Enterprise Operations Console (EOC)Failover Engine (FoE)Firewall Security Manager (FSM)Free Tools Knowledge BaseipMonitorIP Address Manager (IPAM)Kiwi CatToolsKiwi Syslog ServerLANsurveyorLicense ManagerLog & Event Manager (LEM)Mobile AdminNetflow Traffic Analyzer (NTA)Network Automation Manager (NAM)Network Configuration Manager (NCM)Network ManagementNetwork Operations Manager (NOM)Network Performance Monitor (NPM)Network Performance MonitorNetwork Topology Mapper (NTM)Patch ManagerServ-U Managed File Transfer & Serv-U FTP ServerServer & Application Monitor (SAM)SolarWinds Cookie PolicyStorage Manager (STM)Storage Resource Monitor (SRM)Third-Party Software ListUser Device Tracker (UDT)Virtualization