This discussion is archived
13 Replies Latest reply: Mar 5, 2013 8:38 AM by alwu RSS

Dropping a semantic model

957861 Newbie
Currently Being Moderated
We use a third-party tool (TopBraid) for creating semantic data models in our Oracle 11.1.0.7.0 database (we will be upgrading to 11.2 soon). As such, aside from the set-up of the Semantic support in our database, most of the interaction with the database is abstracted from us. A few months ago, I consulted some of the documentation for how to completely eliminate all remnants of a model from the database. I thought I was successful at the time, but I noticed that it appears that I'm not truly removing everything. Because of this, our database, particularly in our development environment, has become cluttered with tables and references to models that we don't want anymore. I'm concerned this could lead to other issues. I don't think there will be much use to me telling you what our current procedure for deleting these models. I can't find any straight answers for this in any of the documentation, so I'm hoping someone can outline it for me:

Given that I only have a model name (for example, 'TESTMODEL'), what are all the steps I need to do to completely eradicate it from the database? Ideally, I'd like to be able to get it back to a state where I can re-use the model name if I wanted, although I have no plans to do that. I understand there may be additional requirements that our third-party tool is placing on it, but I'd like to know the requirements from the Oracle perspective so I can determine if they are doing anything extra.

Thanks!

Edited by: 954858 on Jan 9, 2013 11:47 AM
  • 1. Re: Dropping a semantic model
    alwu Pro
    Currently Being Moderated
    Hi,

    Give the model name TESTMODEL, you should be able to find the application table associated with it by checking MDSYS.RDF_MODEL$.

    SQL> select owner,table_name from mdsys.rdf_model$ where model_name='TESTMODEL';

    You can first drop the model by using

    SQL> exec sem_apis.drop_sem_model('TESTMODEL');

    Then you can drop the corresponding application table (based on the result of the above SELECT query).

    There may be another table (for storing namespaces) you need to drop. By default, the name
    of this table is model name followed by "_NS"

    SQL> drop table TESTMODEL_NS;

    Hope it helps,

    Zhe
  • 2. Re: Dropping a semantic model
    957861 Newbie
    Currently Being Moderated
    I think that is exactly what I am doing now. I was asking because I see model-specific views in the MDSYS schema (RDFM_<model> and SEMM_<model>) and I wasn't sure if those were getting cleaned up. I also didn't know if dropping the TPL and NS tables truly got rid of all the data. We also see RDFB_<model> and RDFC_<model> tables in the same schema with the TPL and NS tables (although it doesn't appear to be for all models; I can't tell why some have them and some don't. There's a chance they belong to our third-party tool. It would also help to get a clearer view of what tables are owned by Oracle. From your response, it sounds like ONLY the TPL and NS tables are Oracle-owned? What about the views?) Do you have any advice on inspecting the health of a semantic schema? Are there any means by which you can test the validity of a model in the RDF_MODEL$ view? We are seeing some issues in one environment that are not happening in others; I suspected there was some bad data or some other issue with the schema that was causing it. Any advice you can give for debugging the problem would be appreciated.

    Edited by: 954858 on Jan 15, 2013 7:11 AM

    Edited by: 954858 on Jan 15, 2013 7:20 AM
  • 3. Re: Dropping a semantic model
    957861 Newbie
    Currently Being Moderated
    I started attempting to delete a few models manually; the SEM_APIS.DROP_SEM_MODEL call is hanging. Eventually I killed the call, and when I attempt to re-run, I get an error:

    *
    ERROR at line 1:
    ORA-55300: model TESTMODEL does not exist
    ORA-06512: at "MDSYS.MD", line 1723
    ORA-06512: at "MDSYS.MDERR", line 17
    ORA-06512: at "MDSYS.SDO_RDF_INTERNAL", line 7177
    ORA-06512: at "MDSYS.SDO_RDF", line 1010
    ORA-06512: at "MDSYS.SDO_RDF", line 1033
    ORA-06512: at "MDSYS.RDF_APIS", line 755
    ORA-06512: at line 1

    Is there anything I can do to investigate why it is hanging in the first place?
  • 4. Re: Dropping a semantic model
    alwu Pro
    Currently Being Moderated
    RDFB_ and RDFC_ related tables are created to help data loading. You can drop those tables if you decide to drop the semantic model.

    Looks like there was something wrong with dropping TESTMODEL. Maybe you can try re-creating a semantic model called TESTMODEL. Please let me know if that can succeed.

    Thanks,

    Zhe
  • 5. Re: Dropping a semantic model
    957861 Newbie
    Currently Being Moderated
    I should note that I put TESTMODEL in there as an example; I've been trying this on a few different models we have in our database.

    I attempted a call to SEM_APIS.CREATE_SEM_MODEL on a model I had called DROP_SEM_MODEL on, but had not yet dropped the TPL and NS tables for, and it failed (ADAMSPEEDTEST2 is the name of the model):
    ERROR at line 1:
    ORA-55318: column TRIPLE in table ADAMSPEEDTEST2_TPL already contains data
    ORA-06512: at "MDSYS.MD", line 1723
    ORA-06512: at "MDSYS.MDERR", line 27
    ORA-06512: at "MDSYS.SDO_RDF", line 985
    ORA-06512: at "MDSYS.SDO_RDF", line 1023
    ORA-06512: at "MDSYS.RDF_APIS", line 749
    ORA-06512: at line 1

    Thinking I could get around this by dropping the data from the _TPL table manually, that failed as well:
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "MDSYS.RDF_APIS_INTERNAL", line 1715
    ORA-06512: at "MDSYS.ADAMSPEEDTEST2_DML", line 3
    ORA-04088: error during execution of trigger 'MDSYS.ADAMSPEEDTEST2_DML'

    I would attempt to drop the tables manually and start from scratch, but part of the issue is that we don't create models that way in practice; all of our models are (I assume) created through the Oracle Jena adapter via TopBraid, so I would have no idea of how to recreate them in the same manner. Our current methods of creating new models are not currently working in this environment (the write to Oracle step is hanging; attempting to debug why with our DBA has not turned up any answers). Any other tests I can do, or things I can the DBA look for would be helpful.

    Thanks!
  • 6. Re: Dropping a semantic model
    alwu Pro
    Currently Being Moderated
    Can you truncate table ADAMSPEEDTEST2_TPL and retry the create_sem_model? It sounds like there's some bad configuration causing all these headaches. Are there any error messages in the database's trace directory?
  • 7. Re: Dropping a semantic model
    957861 Newbie
    Currently Being Moderated
    Truncating before re-creating got rid of the original error, but now running CREATE_SEM_MODEL just hangs. I will have the DBA check if there are any errors in the trace directory.
  • 8. Re: Dropping a semantic model
    957861 Newbie
    Currently Being Moderated
    Our DBA ended up opening a support ticket for this; let me know if you'd like the #.
  • 9. Re: Dropping a semantic model
    alwu Pro
    Currently Being Moderated
    Hi,

    You can email me (alan dot wu at oracle dot com) the SR # if you like.

    Thanks,

    Zhe
  • 10. Re: Dropping a semantic model
    957861 Newbie
    Currently Being Moderated
    Thanks to your team for helping resolve the issues we were seeing in our Semantic schema. I'm back on trying to clean up some of the models we having sitting in our development environment, and I still had a few questions with regards to deleting the models completely. Hopefully I don't come across anything else, but I do see some other artifacts in the database that you hadn't mentioned at least. I have a number of models that I had run SEM_SPIS.DROP_SEM_MODEL for, and dropped the <MODELNAME>TPL, <MODELNAME>NS, RDFB_<MODELNAME>, RDFC_<MODELNAME> tables from our schema, but I still see several views in the MDSYS schema that are still there after everything is delete. I see a RDFM_<MODELNAME> and SEMM_<MODELNAME> view for each model we have created (including all the previously deleted models). Are these something I need to clean up as well? Having to modify a system schema worries me a bit if that's the case. It's also leading me to believe there may be other artifacts of these deleted models I can't easily identify. Thanks for any help you can give.

    Adam
  • 11. Re: Dropping a semantic model
    alwu Pro
    Currently Being Moderated
    Hi Adam,

    Hmmm. It sounds strange. The following two views (in MDSYS) should be dropped once the RDF model <MODELNAME> is dropped.
    RDFM_<MODELNAME> and SEMM_<MODELNAME>

    If a model owner drops a model successfully and those two views remain in the MDSYS schema, then please file a SR.
    We need to get in your database and see what's going on. It's not supposed to happen.

    Thanks,

    Zhe Wu
  • 12. Re: Dropping a semantic model
    957861 Newbie
    Currently Being Moderated
    I did a couple more tests to see what was going on when I ran DROP_SEM_MODEL; for the few that I did, it looks like everything was dropped correctly. However, it looks like a bunch of them must have gotten into a bad state for one reason or another, and the views hung around. Is it ok to clean these up manually? If so, is there anything else I should look at to see if there was anything else that didn't get cleaned up right?
  • 13. Re: Dropping a semantic model
    alwu Pro
    Currently Being Moderated
    Hi,

    If you are absolutely sure that you don't need those views (or those views were not created by others), then you can drop those views.

    To find all relevant objects for an RDF model MODEL_XYZ, you can do something like the following (as SYS):

    SQL> select owner, object_name,object_type from dba_objects where object_name like '%MODEL_XYZ%'

    You should see views, tables, and triggers either under MDSYS or user's schemas.

    I recommend you to perform a full backup before dropping any internal tables/triggers/views.

    Thanks,

    Zhe Wu

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points