Ora 12012 ошибка при автоисполнение задания

In alert log we are getting the error, So we checked the job number which is causing the error. If it is not needed then drop it or disable it by dbms_scheduler procedure.

For checking the status of job when its last run and owner and inform to application team if its belong to application job:

select
d.job_name,
d.job_action,d.NEXT_RUN_DATE,d.OWNER
from
dba_scheduler_jobs d,
sys.scheduler$_job s
where
d.job_action = s.program_action
and
s.obj# = 55536

If error related to Auto Gather stats jobs:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_nn"

Error:

ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_”

Solution
1. Run the following query for checking advisory package:

col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

No rows selected

2. If no row found then initialize the package with following commands.

EXEC dbms_stats.init_package();

3. Verify the package create. It will fixed the issue.

col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

NAME                           CTIME     HOW_CREATED                            
------------------------------ --------- --------------       
AUTO_STATS_ADVISOR_TASK        05-DEC-18 CMD                                    
INDIVIDUAL_STATS_ADVISOR_TASK  05-DEC-18 CMD

Источник

Here is my code.

update netz98.order_data
set usergroup = 'DISTR'
where 
modelcode||optcode||interiortrimcode in ('CW1WXJXFZLAA6016E','CW7WXMXFZLAA6014E')
and committed = 0
;
commit;

skaffman's user avatar

skaffman

399k96 gold badges819 silver badges770 bronze badges

asked Feb 14, 2011 at 9:15

prashanth's user avatar

0

ORA-12012 is always followed by a list of error codes/descriptions causing the failure. What you must do is to check your log files and by the errors next to the 12012 itself you will realize what’s going on.

The error is not necessarily related with the sentences the JOB is running, so the provided code not necessarily tell the cause, as stated by the referenced documentation:

ORA-12012: error on auto execute of job string
Cause: An error was caught while doing an automatic execution of a job.
Action: Look at the accompanying errors for details on why the execute failed.

Emphasis is mine.

answered Feb 14, 2011 at 17:30

jachguate's user avatar

jachguatejachguate

17k3 gold badges57 silver badges98 bronze badges

Источник
  • Home
  • Forum
  • Oracle Forums
  • Oracle Development
  • DBMS_JOB errors: PLS-00201: identifier ‘package.proc_name’ must be declared

  1. DBMS_JOB errors: PLS-00201: identifier ‘package.proc_name’ must be declared

    Hi all,

    I am new to writing packages and procedures. I have 2 boxes a live box and a test box. I have a job which runs a procedure which is part of a package via dbms_job and another which deletes rows from a certain table . Now i have exported the schema to the test box and set up the jobs but am finding these errors when it tries to execute the jobs:

    ORA-12012: error on auto execute of job 22
    ORA-06550: line 1, column 96:
    PLS-00201: identifier ‘package_name.proc_name’ must be declared
    ORA-06550: line 1, column 96:

    PL/SQL: Statement ignored

    ORA-12012: error on auto execute of job 7
    ORA-06550: line 1, column 117:
    PL/SQL: ORA-01031: insufficient privileges
    ORA-06550: line 1, column 96:
    PL/SQL: SQL Statement ignored

    Can anyone shed some light on this?

    Thanks in advance,
    Chucks

  2. It sounds like you need to grant execute on the package from one user to another. Grants must be explicit and not through a role.

  3. Hi,

    Thats what i thought as well. Hence i gave execute persmission on (grant execute on package to sys) the package to sys. Then as sys i tried running the procedure as such:

    exec package.proc

    I still got the same error:

    PLS-00201: identifier ‘procedure.package’ must be declared

    Any ideas will be greatly appreciated.

  4. Quote Originally Posted by Chucks_k

    Hi,

    Thats what i thought as well. Hence i gave execute persmission on (grant execute on package to sys) the package to sys. Then as sys i tried running the procedure as such:

    exec package.proc

    I still got the same error:

    PLS-00201: identifier ‘procedure.package’ must be declared

    Any ideas will be greatly appreciated.

    Are you passing in the wrong datatype for the parameter?
    Are you familiar with overloading procedures in a package?

  5. I have this same problem

    The package runs fine when I am logged in as Admin (the creator of the package), but it fails when I log in as USER. Even after I grant USER all permissions on the package it fails. I believe the user also needs permission to the procedure inside the package; but I don’t see any options in the admin tool to grant such a permission.

  6. Never mind. I needed to create a synonym for my package.

  7. Thanks

    Thanks for the Synonym suggestion. I had a Synonym already, but dropping it and creating again resolved the issue. Never realized that synonym could be an issue with this error message.

    Quote Originally Posted by RobKraft

    Never mind. I needed to create a synonym for my package.

  8. another cause of this error

    I also had this error.
    It actually turned out to be something simple. In Oracle, when you declare a package name with double quotes, it defaults to upper case. Object names are case sensitive.
    So, CREATE OR REPLACE PACKAGE «DBO».»PkgName» would create
    DBO.PKGNAME. However, depending on the editor, it may (in my case it did) display PkgName, but will register PKGNAME in the database.

    This means a call to PkgName.procedure would raise PLS-00201: identifier PkgName must be declared.

    The answer? Either use caps as a standard naming convention or don’t use the double quotes. The quotes are only needed if there is a space in the object name.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
  • BB code is On
  • Smilies are On
  • [IMG] code is On
  • [VIDEO] code is On
  • HTML code is On

Forum Rules


Click Here to Expand Forum to Full Width

Источник

I see Errors in my alert log , and these errors are generated every 5 minutes. It seems to be that some PL/SQL Block under SYSTEM is throwing these errors.

here is the error message in alert log.
++++++++++++++++++++++++++++++++++
Fri Feb 27 10:04:17 2009
Errors in file /u10/app/ORACLE/admin/dss/bdump/dss_j000_12401.trc:
ORA-12012: error on auto execute of job 144338
ORA-06550: line ORA-06550: line 1, column 531:
PL/SQL: ORA-00917: missing comma
ORA-06550: line 1, column 439:
PL/SQL: SQL Statement ignored
, column :
Fri Feb 27 10:09:17 2009
Errors in file /u10/app/ORACLE/admin/dss/bdump/dss_j000_12515.trc:
ORA-12012: error on auto execute of job 144338
ORA-06550: line ORA-06550: line 1, column 531:
PL/SQL: ORA-00917: missing comma
ORA-06550: line 1, column 439:
PL/SQL: SQL Statement ignored
, column :
+++++++++++++++++++++++++++++++++++++

I looked up into one of those TRACE file that it refers in ALERT LOG
————————————-———-———-———-———-———-——-
/u10/app/ORACLE/admin/dss/bdump/dss_j000_12515.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 — 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u10/app/ORACLE/product/10.2.0.1
System name:    SunOS
Node name:      oscora02z
Release:        5.10
Version:        Generic_127127-11
Machine:        sun4u
Instance name: dss
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 12515, image: oracle@oscora02z (J000)

*** ACTION NAME:(JOB_SNAP_TEMP_USAGE) 2009-02-27 10:09:17.131
*** MODULE NAME:(DBMS_SCHEDULER) 2009-02-27 10:09:17.131
*** SERVICE NAME:(SYS$USERS) 2009-02-27 10:09:17.131
*** SESSION ID:(312.17680) 2009-02-27 10:09:17.131
*** 2009-02-27 10:09:17.131
ORA-12012: error on auto execute of job 144338
ORA-06550: line ORA-06550: line 1, column 531:
PL/SQL: ORA-00917: missing comma
ORA-06550: line 1, column 439:
————————————-———-———-———-———-———-———

So my question is , why is this ‘JOB_SNAP_TEMP_USAGE’ runnign every 5 minutes. How do i disable it or what happens, if i delete this JOB ?

Thanks
Ram K

I did some research and looked up under DBA_SCHEDULER_JOBS and JOB NAME is

Источник

To resolve the issue, consider rewriting your statement as follows: Alternatively, you can specify the view list using a string, using single quotes in the Java code. Additionally, you may need to check the Oracle procedure and Oracle logs for any potential solutions.

Table of contents

  • Can’t open PDB in Oracle 12c R2 for Windows 10
  • DBMS Job scheduler for materialized view
  • JSON String to CLOB object
  • How to avoid the error ora-00942?
  • What does error ora-04098 mean?
  • What is error code ora-04045?

Can’t open PDB in Oracle 12c R2 for Windows 10


Question:

I have recently installed Oracle 12cR2 on my Windows 10 laptop. Upon attempting to open the sample ORCLPDB for utilization, I encountered the error displayed in the SQL*Plus screenshot. After conducting extensive research through various resources such as videos, Oracle documentation, and forums, it appears that others have also faced this issue. However, no definitive solution has been found thus far.

Here are the steps that I’ve taken.

Screenshot of SQL*Plus as sys

Below are the entries in my tnsnames.ora file. 

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb)
    )
  )

These are the entries in my Listener file. 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\OracleHomeUser1\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\OracleHomeUser1\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC = 
      (SID_NAME = ORCLPDB)
      (ORACLE_HOME = D:\app\OracleHomeUser1\product\12.2.0\dbhome_1)
    )  
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Please be aware that the Oracle 12R2 has been installed on Windows 10 Home edition version 1903, with an OS build of 18362.418.

I would greatly appreciate your assistance as I have been facing difficulties for a few weeks. Thank you so much for your support!

The alert_orcl file contains the content from the last 48 hours. 

rs in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_8292.trc:
2019-11-10T17:48:30.952951-05:00
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_347"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T17:58:28.042968-05:00
Resize operation completed for file# 3, old size 655360K, new size 665600K
2019-11-10T17:58:30.685688-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_4476.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_349"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T18:00:00.562652-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_13736.trc:
ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_WS_NOTIFICATIONS"
ORA-04063: package body "APEX_050000.WWV_FLOW_WORKSHEET" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APEX_050000.WWV_FLOW_WORKSHEET"
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_API", line 4898
2019-11-10T18:08:33.989588-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_19036.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_351"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T18:18:31.552426-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_18668.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_353"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T18:28:33.035473-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_11084.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_355"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T18:30:00.624114-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_5216.trc:
ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_WS_NOTIFICATIONS"
ORA-04063: package body "APEX_050000.WWV_FLOW_WORKSHEET" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APEX_050000.WWV_FLOW_WORKSHEET"
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_API", line 4898
2019-11-10T18:38:34.705198-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_12012.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_357"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T18:48:35.891078-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_2552.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_359"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T21:51:18.519574-05:00
Warning: VKTM detected a forward time drift. 
Time drifts can result in unexpected behavior such as time-outs. 
Please see the VKTM trace file for more details:
D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_vktm_10132.trc
2019-11-10T21:51:24.604745-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_16664.trc:
ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_WS_NOTIFICATIONS"
ORA-04063: package body "APEX_050000.WWV_FLOW_WORKSHEET" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APEX_050000.WWV_FLOW_WORKSHEET"
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_API", line 4898
2019-11-10T21:51:37.564979-05:00
Thread 1 cannot allocate new log, sequence 12
Private strand flush not complete
  Current log# 2 seq# 11 mem# 0: D:\APP\ORACLEHOMEUSER1\ORADATA\ORCL\REDO02.LOG
2019-11-10T21:51:37.647758-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j001_16608.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_361"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T21:51:40.643657-05:00
Thread 1 advanced to log sequence 12 (LGWR switch)
  Current log# 3 seq# 12 mem# 0: D:\APP\ORACLEHOMEUSER1\ORADATA\ORCL\REDO03.LOG
2019-11-10T22:00:00.764705-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_19288.trc:
ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_WS_NOTIFICATIONS"
ORA-04063: package body "APEX_050000.WWV_FLOW_WORKSHEET" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APEX_050000.WWV_FLOW_WORKSHEET"
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_API", line 4898
2019-11-10T22:01:24.387425-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_12440.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_363"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T23:24:02.204591-05:00
Warning: VKTM detected a forward time drift. 
Please see the VKTM trace file for more details:
D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_vktm_10132.trc
2019-11-10T23:24:03.743617-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j001_19172.trc:
ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_WS_NOTIFICATIONS"
ORA-04063: package body "APEX_050000.WWV_FLOW_WORKSHEET" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APEX_050000.WWV_FLOW_WORKSHEET"
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_API", line 4898
2019-11-10T23:24:07.893235-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_14616.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_365"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T23:30:00.352162-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_15996.trc:
ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_WS_NOTIFICATIONS"
ORA-04063: package body "APEX_050000.WWV_FLOW_WORKSHEET" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APEX_050000.WWV_FLOW_WORKSHEET"
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_API", line 4898
2019-11-10T23:34:07.321600-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_17628.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_367"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T23:44:05.942664-05:00
Resize operation completed for file# 3, old size 665600K, new size 675840K
2019-11-10T23:44:07.250777-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_18984.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_369"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-10T23:54:08.116856-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_17168.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_371"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-11T00:00:00.491208-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_13364.trc:
ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_WS_NOTIFICATIONS"
ORA-04063: package body "APEX_050000.WWV_FLOW_WORKSHEET" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APEX_050000.WWV_FLOW_WORKSHEET"
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_API", line 4898
2019-11-11T00:04:08.269812-05:00
TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED INTERVAL PARTITION SYS_P457 (43779) VALUES LESS THAN (TO_DATE(' 2019-11-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE SYS.WRI$_OPTSTAT_HISTGRM_HISTORY: ADDED INTERVAL PARTITION SYS_P460 (43779) VALUES LESS THAN (TO_DATE(' 2019-11-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
2019-11-11T00:04:10.242413-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_12576.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_373"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-11T00:14:11.060275-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_17152.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_375"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-11T00:24:11.498403-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_7156.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_377"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-11T00:25:10.116868-05:00
TABLE SYS.WRP$_REPORTS_TIME_BANDS: ADDED INTERVAL PARTITION SYS_P461 (3601) VALUES LESS THAN (TO_DATE(' 2019-11-11 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
2019-11-11T00:30:00.608764-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_10260.trc:
ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_WS_NOTIFICATIONS"
ORA-04063: package body "APEX_050000.WWV_FLOW_WORKSHEET" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APEX_050000.WWV_FLOW_WORKSHEET"
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_API", line 4898
2019-11-11T00:34:13.016562-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_11772.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_379"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2019-11-11T11:13:12.219241-05:00
Warning: VKTM detected a forward time drift. 
Please see the VKTM trace file for more details:
D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_vktm_10132.trc
2019-11-11T11:13:12.937564-05:00
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager CDB plan via parameter
2019-11-11T11:13:16.063522-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j002_8140.trc:
ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_WS_NOTIFICATIONS"
ORA-04063: package body "APEX_050000.WWV_FLOW_WORKSHEET" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APEX_050000.WWV_FLOW_WORKSHEET"
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_API", line 4898
2019-11-11T11:13:59.803724-05:00
TABLE SYS.WRP$_REPORTS: ADDED INTERVAL PARTITION SYS_P462 (3602) VALUES LESS THAN (TO_DATE(' 2019-11-12 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE SYS.WRP$_REPORTS_DETAILS: ADDED INTERVAL PARTITION SYS_P463 (3602) VALUES LESS THAN (TO_DATE(' 2019-11-12 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
2019-11-11T11:30:00.266486-05:00
Errors in file D:\APP\ORACLEHOMEUSER1\diag\rdbms\orcl\orcl\trace\orcl_j000_15252.trc:
ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_WS_NOTIFICATIONS"
ORA-04063: package body "APEX_050000.WWV_FLOW_WORKSHEET" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APEX_050000.WWV_FLOW_WORKSHEET"
ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_API", line 4898


Solution:

The Windows 10 Home edition has the Oracle 12R2 installed.

According to the Installation Guide, the Oracle database server is certified for Pro, Enterprise, and Education editions. However, there is significantly less information available for troubleshooting installations on the Home edition.

Is it necessary for me to upgrade to either Windows Pro or Education?

The Oracle database is designed for enterprise use and may rely on operating system features that are not available in the Home edition. However, I cannot assure you that upgrading your operating system will definitely resolve this issue. By the way, I am a Mac user :)

Docker is a platform that enables the deployment of containers, which are essentially streamlined virtual machines. With Docker, it is possible to run Oracle 12c EE on Linux without the need for a dual-boot PC. This is how I am able to run an Oracle database on my Mac. However, Docker’s functionality on Win10 Home is not as simple.

To achieve success swiftly, I suggest installing Oracle VirtualBox, along with the Extensions pack. Afterward, you can proceed to download and run a vbox image of your choice from Oracle’s selection of numerous options available on their page. This was the method I employed when I last needed to run an Oracle database on Windows.

ORA-12012: error on auto execute of job 1161, 2 ORA-12012 is always followed by a list of error codes/descriptions causing the failure. What you must do is to check your log files and by the errors next to the 12012 itself you will realize what’s going on.

DBMS Job scheduler for materialized view


Question:

Every night, I have been attempting to simultaneously schedule the refresh of three materialized views using the code provided below. 

BEGIN
DBMS_SCHEDULER.CREATE_JOB 
(
JOB_NAME            => 'REFRESH_MVIEW',
JOB_TYPE            => 'PLSQL_BLOCK',
JOB_ACTION          => 'BEGIN DBMS_MVIEW.REFRESH("m_view1, m_view2, m_view3",''C''); END;',
NUMBER_OF_ARGUMENTS => 0,
START_DATE          => SYSTIMESTAMP,
REPEAT_INTERVAL     => 'FREQ=DAILY; BYHOUR=0',
END_DATE            => NULL,
ENABLED             => TRUE,
AUTO_DROP           => FALSE,
COMMENTS            => 'JOB TO REFRESH'
);
END;

However, I encounter the error message once the job is executed.

An error occurred (ORA-12012) during the execution of job 57179. This error (ORA-06550) was encountered on line 1, column 495. The identifier ‘m_view1, m_view2’ is too long (PLS-00114).

I’m aware that the procedure name has a limit of 30 characters. Does this imply that I need to divide the task into three separate jobs? Can you please inform me of any mistakes I may have made?

Thanks in Advance !


Solution:

Would you kindly inform me of my mistakes?

Consider composing your statement in a similar manner: 

BEGIN DBMS_MVIEW.REFRESH(''m_view1,m_view2,m_view3'',''C'')

Or 

BEGIN DBMS_MVIEW.REFRESH(''m_view1,m_view2,m_view3'',''CCC'')

The specified view list is represented by a string. To denote the string, single quotes are used. It is unclear whether spaces hold any significance in the view list. To err on the side of caution, I have also eliminated them.

In the first scenario, it is important to mention that there is a default refresh for the last two views. However, if you require a full refresh for all of your views, you must specify CCC as the refresh method.

Please refer to the link provided: https://docs.oracle.com/cd/A97630_01/server.920/a96568/rarmviea.htm#94135.

Newest ‘database-link’ Questions, I work for a small company that currently use Apex to run reports on our accounts system. Oracle is installed on our local server and part of the data is gained via a database link to an access oracle ms-access remote database-link oracle-apex. Andrew. 3; asked Mar 14, 2020 at 7:17. 1 vote. 1 answer. 317 views. …

JSON String to CLOB object


Question:

I am successfully creating a new dbms_job using Java code. However, I am encountering a failure when trying to process it and call the job_action. In the job_action, I am invoking an Oracle loaded Java code procedure. The issue seems to be caused by the json format in the oracle.sql.Clob object.

I’m uncertain if there’s an issue with converting JSON to CLOB and back to JSON. Why is it failing in this case?

java code: 

connection = DBBroker.getConnection();
            stmt = (CallableStatement) connection.prepareCall("begin ? := PKGRESTASSIGNBLOCK.CREATEJOB(?, ?, ?); end;");
            CLOB clob = null;
            clob = CLOB.createTemporary(connection, false, CLOB.MODE_READWRITE);
            clob.open(CLOB.MODE_READWRITE);
            clob.setString(1, requestString);
            stmt.registerOutParameter(1, java.sql.Types.VARCHAR);
            stmt.setClob(2, clob);
            stmt.setString(3, requestID);
            stmt.setInt(4, requestType);
            stmt.execute();

oracle procedure : 

CREATE OR REPLACE
PACKAGE BODY      PKGRESTASSIGNBLOCK
AS
FUNCTION createjob(request IN CLOB, requestID IN VARCHAR2, type IN NUMBER) RETURN VARCHAR2
IS
                    l_jobname  varchar2(1044);
BEGIN
      BEGIN
      l_jobname := NULL;
      l_jobname := SUBSTR('JOB_IPBlk_' || requestID ||'_'|| to_char(SYSTIMESTAMP, 'yymmddhhmmssFF'), 0, 30);
      DBMS_SCHEDULER.create_job(
                                job_name => l_jobname,
                                job_type => 'PLSQL_BLOCK',
                                job_action => 'begin pkgrestassignblock.assignblock ('|| request || ', ' || requestID || ', '|| type||'); end ;',
                                start_date => SYSTIMESTAMP,
                                repeat_interval      => NULL,
                                end_date             => NULL,
                                enabled              => TRUE,
                                comments             => 'Job defined entirely by the CREATE JOB procedure.');
      END;
      DBMS_OUTPUT.put_line ('l_jobname : ' ||l_jobname);
      RETURN l_jobname;
END createjob;
PROCEDURE assignblock (request IN CLOB, requestID IN VARCHAR2, type IN NUMBER)
AS
LANGUAGE JAVA
NAME 'com.abcd.efgh.AssignBlock.handleRequest(java.sql.Clob, java.lang.String, java.lang.String, int)';
END PKGRESTASSIGNBLOCK;

oracle logs: 

logs
ORA-12012: error on auto execute of job "JOB_BLK_20022019_19022008020"
ORA-06550: line 1, column 799:
PLS-00103: Encountered the symbol "{" when expecting one of the following:
   ( ) - + case mod new not null 
    
   table continue avg count current exists max min prior sql
   stddev sum variance execute multiset the both leading
   trailing forall merge year month day hour minute second
   timezone_hour timezone_minute timezone_region timezone_abbr
   time timestamp interval date
   
Wed Feb 20 20:55:03 2019
Errors in file path/trace/CMM_j002_30847.trc:
ORA-12012: error on auto execute of job ""JOB_BLK_20022019_19022008020"
ORA-06550: line 1, column 756:
PLS-00306: wrong number or types of arguments in call to 'ASSIGNBLOCK'
ORA-06550: line 1, column 756:
PL/SQL: Statement ignored


Solution:

There might be an issue if
job_action
 is a
VARCHAR2
. I am uncertain about the character limit, whether it is 4000 or 32767, but in any case, if your JSON is lengthy, it has the potential to cause a malfunction.

The primary issue lies in the absence of quotation marks around your strings. Consider modifying the specified line. 

job_action => 'pkgrestassignblock.assignblock('''|| request || ''', ''' || requestID || ''', '|| type||');',

In addition, I eliminated the need for
begin..end;
 as the job already encapsulates the action within its own begin/end block.

Newest ‘jobs’ Questions, This is a job that runs a full backup from our backup utility DDBoost. When I execute the job, the backup completes in 5 mins for all databases. However, sql-server jobs. user134251. 21; asked Sep 8, 2017 at 18:43. 1 vote. 0 answers. 178 views. How to verify DBCC CHECKDB. I am logging the DBCC CHECKDB job …


Источник

Понравилась статья? Поделить с друзьями:
  • Ora 04063 ошибка
  • Ora 06512 ошибка oracle
  • Origin ошибка 327683 0
  • Ora 06512 описание ошибки
  • Origin код ошибки 20 99