Finding the deleted TYPE when ANYDATA raises ORA-21700: object does not exist or is marked for delete
The current message about Oracle Database is: multi-model database. That’s not new. At the time of Oracle 9i, Object Oriented was the trend, with all the flexibility of polymorphism, but without the mess of unstructured data and without the inconsistency of NoSQL. Oracle added a datatype that can contain any datatype: SYS.ANYDATA. In the same column, you can put a number in row 1, a varchar2 in row 2, a record in row 3, andy object in row 4… Any arbitrary object can be stored, but, unlike a RAW or a BLOB (or XML or JSON), each object is structured and references a known datatype or a user-created TYPE.
However, it is impossible to enforce the dependency for each row and it can happen that you DROP a TYPE that is used by an ANYDATA object.
Example
I create two types. Very simple ones, and similar for this example, but it can be any complex object definition:
DEMO@//localhost/pdb1> create type DEMO1 as object(a number);
2 /Type created.
DEMO@//localhost/pdb1> create type DEMO2 as object(a number);
2 /Type created.
I create a table with a key (NUMBER) and value (ANYDATA):
DEMO@//localhost/pdb1> create table DEMO ( k number, v anydata );Table created.
I insert two instances of DEMO1
DEMO@//localhost/pdb1> insert into DEMO values(1,
anydata.convertobject( DEMO1(1)) );1 row created.DEMO@//localhost/pdb1> insert into DEMO values(2,
anydata.convertobject( DEMO1(1)) );1 row created.
and two instances of DEMO2
DEMO@//localhost/pdb1> insert into DEMO values(3,
anydata.convertobject( DEMO2(1)) );1 row created.DEMO@//localhost/pdb1> insert into DEMO values(4,
anydata.convertobject( DEMO2(1)) );1 row created.
Type name and Dump
I query the table. SQL Developer displays the type but I can also get it with ANYDATA.GETTYPENAME()
select k,v,anydata.getTypeName(v) from demo;
By curiosity, I look at the binary storage:
select k,anydata.getTypeName(v),substr(dump(v,16),1,145) from demo;
This contains the Type Object ID. Here are my types from USER_TYPES:
select * from user_types;
On this example it is clear that the TYPE_OID is there:
99ED99CFEAB04E7FE0531103000A3EA6 is contained in Typ=58 Len=74: 0,1,0,0,0,0,0,1,0,0,0,19,83,df,0,34,48,90,0,2e,0,0,2a,1,85,1,2a,1,1,2,4,0,6c,99,ed,99,cf,ea,b0,4e,7f,e0,53,11,3,0,a,3e,a6,0,1,0,0,
99ED99CFEAB44E7FE0531103000A3EA6 is contained in Typ=58 Len=74: 0,1,0,0,0,0,0,1,0,0,0,19,83,e2,0,34,48,90,0,2e,0,0,2a,1,85,1,2a,1,1,2,4,0,6c,99,ed,99,cf,ea,b4,4e,7f,e0,53,11,3,0,a,3e,a6,0,1,0,0,
Drop the TYPE
Now, I can drop the TYPE without having any error:
drop type DEMO2;This is not a bug (Bug 14828165 : TYPE IS ALLOWED TO BE DROPPED closed in status 92). With ANYDATA you want flexibility, right?
However, I cannot query a value that references this dropped TYPE:
select * from demo
*
ERROR at line 1:
ORA-21700: object does not exist or is marked for deleteAnd the problem is that I cannot even know the type name:
select k,anydata.getTypeName(v) from demo;
The only thing that I can see is the Type OID from the dump of the ANYDATA value:
But as the TYPE was dropped, I cannot get the name from USER_TYPES.
Flashback query
Ideally, you can get this metadata information from a Data Pump export (OID is visible in the DDL sqlfile) or from a backup. Here, as the DROP was recent, I’ll simply use Flashback Query.
I cannot “versions between” on a view so I query first the SCN from TYPE$
select toid,versions_endscn,versions_operation
from sys.type$ versions between scn minvalue and maxvalue
where ',99,ed,99,cf,ea,b4,4e,7f,e0,53,11,3,0,a,3e,a6,0,1,0,0,'
like '%,'||regexp_replace(dump(type$.toid,16),'^.* ')||',%'
;
(I passed through a regexp because SQL Developer adds thousand separators which made their way to the substitution variable)
And then I query “as of” the DBA_TYPES for this SCN to get all information:
select *
from dba_types as of scn ( 11980082 -1)
where rawtohex(type_oid)= '99ED99CFEAB44E7FE0531103000A3EA6'
Here I have it: the dropped type referenced by this ANYDATA value is DEMO.DEMO2 and that can help me understand what it was and when it has been dropped. As long as I am in the UNDO retention I can find all information to recreate it (mentioning the OID).
I’ve put all that in a function which takes the ANYDATA value and DUMP() to find the OID and name when the ORA-21700 is encountered:
with function try(x anydata,d varchar2) return varchar2 as
l_toid varchar2(1000); l_scn number; l_name varchar2(1000); begin return anydata.getTypeName(x); exception when others then select rawtohex(toid),versions_endscn into l_toid,l_scn from sys.type$ versions between scn minvalue and maxvalue where d like '%,'||regexp_replace(dump(type$.toid,16),'^.* ')||',%' order by versions_endscn fetch first 1 rows only; select owner||'.'||type_name into l_name from dba_types as of scn (l_scn -1) where rawtohex(type_oid)=l_toid; return sqlerrm||' -> '||l_name; end; select k,try(v,dump(v,16)) from demo.demo /
Basically, ANYDATA stores all known datatypes in their own format, in a record, with an OID to reference the structure metadata. Here is an example where the NUMBER format is visible inside:
Who says that there is an impedance mismatch between Relational Databases and Object Oriented models? There are not. You can store objects in a relational database. But there are only a few use cases where you want a column with a generic datatype where you can store ANYDATA. For example, Advanced Queuing uses that for queued messages: you know what you put. You know what you read. But the table can store heterogeneous data without having to define one table queue for each type. Yes, this looks like inheritance and abstract class, in a relational table.
