In my last post, Deploying Data Mining Models using Model Export and Import, we explored using DBMS_DATA_MINING.EXPORT_MODEL and DBMS_DATA_MINING.IMPORT_MODEL to enable moving a model from one system to another.

In this post, we'll look at two distributed scenarios that make use of this capability and a tip for easily moving models from one machine to another using only Oracle Database, not an external file transport mechanism, such as FTP.


In the second scenario, consider multiple hospitals that collect data on patients with certain types of cancer. The data collection is standardized, so each hospital collects the same patient demographic and other health / tumor data, along with the clinical diagnosis. Instead of each hospital building it's own models, the data is pooled at a central data analysis lab where a predictive model is built. Once completed, the model is distributed to hospitals, clinics, and doctor offices who can score patient data locally.


Since this blog focuses on model export and import, we'll only discuss what is necessary to move a model from one database to another. Here, we use the package DBMS_FILE_TRANSFER, which can move files between Oracle databases.

The script is fairly straightforward, but requires setting up a database link and directory objects. We saw how to create directory objects in the previous post.

To create a database link to the source database from the target, we can use, for example:

create database link SOURCE1_LINK connect to <schema> identified by <password> using 'SOURCE1';

Note that 'SOURCE1' refers to the service name of the remote database entry in your tnsnames.ora file.

From SQL*Plus, first connect to the remote database and export the model. Note that the model_file_name does not include the .dmp extension. This is because export_model appends "01" to this name.  Next, connect to the local database and invoke DBMS_FILE_TRANSFER.GET_FILE and import the model. Note that "01" is eliminated in the target system file name. 

connect <source_schema>/<password>@SOURCE1_LINK;
BEGIN
 DBMS_DATA_MINING.EXPORT_MODEL ('EXPORT_FILE_NAME' || '.dmp',
                                'MY_SOURCE_DIR_OBJECT',
                                'name =''MY_MINING_MODEL''');
END;

connect <target_schema>/<password>;
BEGIN
 DBMS_FILE_TRANSFER.GET_FILE ('MY_SOURCE_DIR_OBJECT',
                              'EXPORT_FILE_NAME' || '01.dmp',
                              'SOURCE1_LINK',
                              'MY_TARGET_DIR_OBJECT',
                              'EXPORT_FILE_NAME' || '.dmp' );
 DBMS_DATA_MINING.IMPORT_MODEL ('EXPORT_FILE_NAME' || '.dmp',
                                'MY_TARGET_DIR_OBJECT');
END;

To clean up afterward, you may want to drop the exported .dmp file at the source and the transferred file at the target. For example,

utl_file.fremove('&directory_name', '&model_file_name' || '.dmp');