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:

I create a table with a key (NUMBER) and value (ANYDATA):

I insert two instances of DEMO1

and two instances of DEMO2

Type name and Dump

I query the table. SQL Developer displays the type but I can also get it with ANYDATA.GETTYPENAME()

By curiosity, I look at the binary storage:

This contains the Type Object ID. Here are my types 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:

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:

And the problem is that I cannot even know the type name:

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$

(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:

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:

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.

https://twitter.com/FranckPachot Passionate about all databases. Oak Table member, Oracle ACE Director & OCM 12c. Other blog posts: http://blog.dbi.pachot.net

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store