0

While I was trying to drop a table, it throws following error in Oracle SQL Developer:

ORA-40341: Access violation on model storage object

The tables are temporary tables created while pushing into the database using Oracle R Enterprise. The names of the tables are: DM$PRORE$21_473, DM$PGORE$21_473, ...

I need to drop all these tables as these tables have occupied large space of my database. While googling, I found this link but it provides no solution clues.

  • 1
    The error message advises you to contact your DBA or Oracle Support. Have you done either? – APC Jul 9 '13 at 9:33
  • 1
    Cause: An attempt was made to directly access/modify a schema object that stores model metadata and content.Action: Perform all mining operations (create,drop,alter, and select using data mining functions) against named model objects. Contact your DBA or Oracle Support if you suspect that an orphaned schema object. – Gaurav Soni Jul 9 '13 at 9:42
  • Should I contact to DBA? As we donot have a dedicated DBA, I would like to do using some PL/SQL queries. – Zane Jul 9 '13 at 9:44
  • So you don't have a DBA. And presumably you don't have an Oracle Support contract either. That's tough. Oracle R is a very new technology, so there's really not that many people who know it. – APC Jul 9 '13 at 10:11
  • It sounds like these tables are "owned" by a parent table, and they store various meta-data / structures to improve access performance. Try finding their "parent". – beiller Jul 9 '13 at 15:34
1

Database tables created using ore.push will have the prefix ORE$. Normally, these are automatically cleaned up when the R session ends or the ORE db connection terminated (unless you save the ore.frame objects in an ORE datastore).

In-database models from Oracle Data Mining (ODM) as created from ore.odmSVM will also normally be deleted automatically and the end of the R session or ORE db connection termination. You should not attempt to remove DM$ tables as they are part of the in-database model. If you do delete any of these, their corresponding models will be corrupted/invalid.

You can invoke

SELECT name FROM dm_user_models;

to see the list of models in the database for your schema.

You can drop ODM models using the following in SQL*Plus or SQL Dev:

BEGIN 
  DBMS_DATA_MINING.DROP_MODEL('MY_MODEL'); 
END;

Lots of thanks goes to Mark Hornick, Senior Manager, Oracle Advanced Analytics for his constant support in ORE implementation in my project.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.