Freitag, 7. Juni 2019

ORA-21700 in data dictionary


This whole story started with a nice little ticket:

When I try to edit a scheduler job in <DB>  with user <USER> I am getting error:
ORA-21700: object does not exist or is marked for delete
even I can see the object and the object is not deleted or marked as delete.

At first I could not reproduce the error, so I asked for something more specific, and I got 2 screenshots. (one shown here)
Unfortunately (for me) the screenshots were from Toad and PL/SQL Developer - 2 tools I don't have install or licensed or know anything about.






So, to give it a try I connected to the DB with SQL Developer and randomly clicked around several scheduler related tabs. I was lucky to generate ORA-21700 there also. And the big value of SQL Developer in this case is the log it has for all SQLs it's sending to the DB.




It shows the statement which failed - together with it's arguments.


select job_name, argument_name, argument_position, argument_type, value, out_argument 
from SYS.Dba_SCHEDULER_JOB_ARGS  
WHERE "JOB_NAME" = :OBJECT_NAME  AND "OWNER" = :OBJECT_OWNER
order by argument_position

With this query it's much easier to analyse the problem. Executing the quera ys the specific user really created an ORA-21700. But where does it come from? DBMS_UTILITY.EXPAND_SQL_TEXT is very handy to get the "real" sql for a query with views. Of course it's possible to see the views text, but if it's using views again, this can be real work (which I try to avoid).

Trying all the tables in the expanded query the culprit was found easily: sys.SCHEDULER$_JOB_ARGUMENT
The table is not complicated:
NAME            DATA TYPE            NULL  DEFAULT    COMMENTS
*OID            NUMBER               No               
*POSITION       NUMBER               No               
 NAME           VARCHAR2(128 BYTE)   Yes              
 TYPE_NUMBER    NUMBER               Yes              
 USER_TYPE_NUM  NUMBER               Yes              
 VALUE          ANYDATA              Yes              
 FLAGS          NUMBER               Yes              

And with some more trying the problem was tracked down to 1 column in 1 row:
select VALUE from sys.SCHEDULER$_JOB_ARGUMENT where oid=2736824 

This shows a little misunderstanding in the first request, the error code and the problem. The full error description is
ORA-21700: object does not exist or is marked for delete
21700. 00000 -  "object does not exist or is marked for delete"
*Cause:    User attempted to perform an inappropriate operation to
           an object that is non-existent or marked for delete.
           Operations such as pinning, deleting and updating cannot be
           applied to an object that is non-existent or marked for delete.
*Action:   User needs to re-initialize the reference to reference an
           existent object or the user needs to unmark the object.

The objects it is referring to are things like a TYPE, whereas in the original content it was interpreted as a SCHEDULER JOB.

But why is there a problem with an anydata column at all? couldn't it hold simply ANY data?
there is a Note at MOS which describes the situation quite good: Used Type Can Be Dropped Causing ORA-21700 When Querying A Table With SYS.ANYDATA Column ( Doc ID 1503628.1 )
When selecting an anydata column which contains atype, the given TYPE must be known. If the TYPE was deleted in the meantime, the column (and related functions like anydata.gettypename) will fail.
The block or row itself is not corrupted and it can be queried with dump().
Typ=58 Len=630: 0,1,0,0,0,0,0,1,0,0,5,189,61,90,2,96,9,0,0,0,0,0,2,80,0,0,0,0,0,...
Unfortunately I don't know the internal structure of this Type. Datatype 58 is opaque (DTYOPQ) which can hold ANYDATA, ANYTYPE, XMLTYPE, ANYDATASET (and maybe others). But how to distinguish which exactly, or how to interpred the bytes I got from dump() I couldn't find somewhere.

The problem in this particular database is this one row in sys.SCHEDULER$_JOB_ARGUMENT. To be visible in Dba_SCHEDULER_JOB_ARGS, there is a join of SCHEDULER$_JOB_ARGUMENT and SCHEDULER$_JOB on OID = OBJ#.
In my particular case there is no row anymore on SCHEDULER$_JOB. So no row is visible and there is nothing which can be dropped with DBMS_SCHEDULER.DROP_JOB. I have no idea how this happened. But it's there.

That's the situation to create a SR at MOS and ask for assistance.

Even a testcase is simply done:
CREATE OR REPLACE TYPE random_type AS OBJECT (
    n NUMBER
);
/

CREATE OR REPLACE PROCEDURE do_nothing (
    param1 IN random_type
) AS
BEGIN
    NULL;
END do_nothing;
/

DECLARE
    t random_type;
BEGIN
    t := random_type(NULL);
    t.n := 1;
    do_nothing(t);
END;
/

BEGIN
    dbms_scheduler.create_job(job_name => 'TEST_NOTHING', job_type => 'STORED_PROCEDURE', job_action => 'DO_NOTHING', number_of_arguments
    => 1, start_date => SYSDATE + 1, repeat_interval => NULL, end_date => NULL, enabled => false, 
auto_drop => true, 
comments => 'job to test parameter of ANYDATA'
    );
END;
/

DECLARE
    t random_type;
BEGIN
    t := random_type(NULL);
    t.n := 1;
    dbms_scheduler.set_job_anydata_value(job_name => 'TEST_NOTHING', argument_position => 1, argument_value => SYS.anydata.convertobject
    (t) );

END;
/

SELECT
    sys.anydata.gettypename(value) d
FROM
    sys.scheduler$_job_argument;

DROP TYPE random_type;

SELECT
    sys.anydata.gettypename(value) d
FROM
    sys.scheduler$_job_argument;

That's exactly where the ORA-21700 occurs.
In the testcase, a simple cleanup can be done by (trying to) execute this job, so it's cleared:

PAUSE "let's initiate some cleanup" 

begin
    dbms_scheduler.set_attribute(name => '"BERX"."TEST_NOTHING"', attribute => 'logging_level', value => dbms_scheduler.logging_full);
    dbms_scheduler.set_attribute_null(name => '"BERX"."TEST_NOTHING"', attribute => 'start_date');
    dbms_scheduler.enable(name => '"BERX"."TEST_NOTHING"');
end;
/

PAUSE "sleep for some time until the job is executed"

SELECT
    sys.anydata.gettypename(value) d
FROM
    sys.scheduler$_job_argument;

SELECT
    *
FROM
    dba_scheduler_job_log
WHERE
    owner = user;

All I can do now is to convince an MOS to approve me deleting the problematic row.
Update: 2019-06-13
Oracle Support approved to fix this situation by running
delete from SCHEDULER$_JOB_ARGUMENT WHERE OID=2736824; 
commit
So my issue is fixed.

I do NOT recommend anyone to delete rows in data dictionary without asking Oracle Support for approval first!

Keine Kommentare: