When working with ORE you will end up creating a number of different data stores in the database. Also as your data science team increases the number of data stores can grow to a very large number.
When you install Oracle R Enterprise you will get a number of views that are made available to ORE users to see what ORE Data stores they have and what objects exist in them. All using SQL.
Perhaps some of the time the ORE developers and data analysts will use the set of ORE functions to manage the in-database ORE Data stores. These include:
When using these ORE function the schema user/data scientist can see what ORE Data stores they have. You can use the ore.delete to delete an ORE Data store when it is no longer needed.
But the problem here is that over time your schemas can get a bit clogged up with ORE Data stores. Particularly when the data scientist is not longer working on the project or there is no need to maintain ORE Data stores. This is common on data science projects when you might have a number of data scientists work in/sharing the one database schema.
For a DBA, who's role will be to clean up the ORE Data store that are no longer needed, you have 4 options.
The first of these, is if all the ORE Data stores exist in the data scientists schema and nothing else in the schema is needed then you can just go ahead and drop the schema.
The second option is to log into the schema using SQL and drop the ORE Data stores. See an example of this below.
The third option is to connect to the Oracle schema using R and ORE and then use the ore.delete function to drop the ORE Data stores.
The fourth option is to connect to the RQSYS schema. This schema is the owner of the views used to query the ORE Data stores in each schema. After the RQSYS schema was created it was locked as part of the ORE installation. You as the DBA will need to unlock and then connect.
The following SQL lists the ORE Data stores that were created for that schema.
column dsname format a20
column description format a35
SELECT * FROM rquser_DataStoreList;
DSNAME NOBJ DSSIZE CDATE DESCRIPTION
-------------------- ---------- ---------- --------- -----------------------------------
ORE_DS 2 5104 04-AUG-15 Example of ORE Datastore
ORE_FOR_DELETION 1 1675 14-AUG-15 Need to Delete this ORE Datastore
ORE_DS2 5 51466509 04-AUG-15 DS for all R Env Data
You can also view what objects have saved in the ORE Data store.
column objname format a15
column class format a15
SELECT * FROM rquser_DataStoreContents;
DSNAME OBJNAME CLASS OBJSIZE LENGTH NROW NCOL
-------------------- --------------- --------------- ---------- ---------- ---------- ----------
ORE_DS CARS_DATA ore.frame 1306 11 32 11
ORE_DS cars_ds data.frame 3798 11 32 11
ORE_DS2 cars_ds data.frame 3798 11 32 11
ORE_DS2 cars_ore_ds ore.frame 1675 11 32 11
ORE_DS2 sales_ds data.frame 51455575 7 918843 7
ORE_DS2 usa_ds ore.frame 2749 23 18520 23
ORE_DS2 usa_ds2 ore.frame 2712 23 18520 23
ORE_FOR_DELETION cars_ore_ds ore.frame 1675 11 32 11
To drop an ORE Data store for you current schema you can use the rqDropDataStore SQL function.
BEGIN
rqDropDataStore('ORE_FOR_DELETION');
END;
/
For the DBA when you unlock and connect to the RQSYS schema you will be able to see all the ORE Data stores in the data. The views will contain an additional column.
But if you use the above SQL function to delete an ORE Data store it will not work. This because this SQL function will only drop and ORE Data store if it exists in your schema. If we have connected to the RQSYS schema we will not have any ORE Data stores in it.
We can create a procedure that will allow use to delete/drop any ORE Data store in any schema.
create or replace PROCEDURE my_ORE_Datastore_Drop(
ds_owner in VARCHAR2,
ds_name IN VARCHAR2
)
IS
del_objIds rqNumericSet;
BEGIN
del_objIds := rq$DropDataStoreImpl(ds_owner, ds_name);
IF del_objIds IS NULL THEN
raise_application_error(-20101, 'DataStore ' ||
ds_name || ' does not exist');
END IF;
-- remove from rq$datastoreinventory
BEGIN
execute immediate
'delete from RQ$DATASTOREINVENTORY c where c.objID IN (' ||
'select column_value from table(:del_objIds))' using del_objIds;
COMMIT;
EXCEPTION WHEN others THEN null;
END;
END;
We are the DBA, logged into the RQSYS schema can now delete any ORE Data store in the database, using the following.
BEGIN
my_ORE_Datastore_Drop('ORE_USER', 'ORE_FOR_DELETION');
END;
/
