Installing semantic technologies support on a fresh Oracle 11gR2.0.1

The following sketches the prerequisites of using Oracle Database Semantic Technologies, see Semantic Technologies Center and Oracle® Database Semantic Technologies Developer’s Guide as an overview and as the developer’s guide respectively, in your databse applications.

The procedure has been executed on a fresh Oracle 11R2.0.1 running on x64 Windows. The semantic technology stack in fact requires an enterprise edition of Oracle, featuring the partitioning and the spatial options as well as the underlying components XDB, JAVAVM and ORDIM to be installed … and licenced. Do check in advance whether this basement is affordable against your business budget because according to the current Oracle global price list by processor, an enterprise edition is around k$ 47, adding partioning by k$ 11 and spatial by k$ 17 plus updates and support. Do note also, that the standard and the standard one editions of Oracle, offering a subset of spatial called Oracle locator is not not sufficient to run the semantic technology layer. That is, it takes a couple of bucks to even create your first simple triple of sematic data with Oracle.

Checking the Oracle base software installation (anything executed as sysdba)

This section of the article, checking the Oracle base software installation itself, has been proof-taken from Stanley Guan’s blog Xml and More, entry Installation of Oracle Semantic Technologies. I can’t imagine why he has skipped the actual steps involving sematic-related code and objects there, moving me to write another post. However, Overview of Oracle Database Semantic Technologies is also worth a look really.

-- verify the underlying components, namely XDB, JAVAVM and ORDIM
select comp_id,version,status from dba_registry where comp_id in ('JAVAVM','ORDIM','XDB');

COMP_ID  VERSION      STATUS
-------- ----------   -------
ORDIM    11.2.0.1.0   VALID
XDB      11.2.0.1.0   VALID
JAVAVM   11.2.0.1.0   VALID

-- assure the options partitioning and spatial are available
select parameter, VALUE from v$option where parameter in ('Partitioning', 'Spatial') order by 1;

PARAMETER     VALUE
------------  ------
Partitioning  TRUE
Spatial       TRUE

-- additionally check spatial in detail
set serveroutput on
execute validate_sdo;

select comp_id, control, schema, version, status, comp_name from dba_registry where comp_id='SDO';

COMP_ID   CONTROL   SCHEMA   VERSION      STATUS   COMP_NAME
--------  --------  -------  -----------  -------  ----------
SDO       SYS       MDSYS    11.2.0.1.0   VALID    Spatial

select object_name, object_type, status from dba_objects where owner='MDSYS' and status <> 'VALID';

Es wurden keine Zeilen ausgewõhlt

Enabling Semantic Technologies Support in a New Database Installation (anything executed as sysdba)

This topic is extensionally covered in the Oracle docs Enabling, Downgrading, or Removing Semantic Technologies Support and also discusses upgrading and downgrading the semantic technology layer.

-- run the semantic layer install script
@%oracle_home%\md\admin\catsem.sql

-- check success by the columns VALUE: (string starting with 112) and DESCRIPTION: VALID
select namespace, attribute, value, description from mdsys.rdf_parameter where namespace = 'MDSYS' and attribute = 'SEM_VERSION';

You may at this time also enable workspace manager support for your semantic data, see the docs at Workspace Manager Support for RDF Data. Essentially, workspace manager provides data versioning over a single segment (table) of data such that model structures (and data) can evolve in iterative steps. I just enabled workspace manager because this step does not actually put a dedicated semantic model into a versioning scheme. Another step is nessecary to do so using dbms_wm.enableVersioning();.

-- enabling Workspace Manager Support for RDF Data
@%oracle_home%\md\admin\sdordfwm.sql

-- check the success by the column VALUE: INSTALLED
select value from mdsys.rdf_parameter where namespace = 'COMPONENT' and attribute = 'RDFOWM';

Oracle recommends to store all sematic data in a separate tablespace which uses up-to-date parametrization. This tablespace is then used to set up the one and only definition of a semantic network with the database. See also Quick Start for Using Semantic Data.

-- create a tablespace for the system tables
create tablespace rdf
 datafile 'e:\oracle\oradata\lucene\rdf01.dat' size 128m reuse
 autoextend on next 128m maxsize 8192m segment space management auto;

-- create a semantic data network on the new tablespace, do it only once for an Oracle database
execute sem_apis.create_sem_network('rdf');

Writing your first triples

This is just a copy of the docs at Example: Journal Article Information and being given to conclude this article to completeness. You know, only when the last statement in an example given executes as expected, the last doubts about some hidden quirk in the system may vanish. Paranoid, huh?

-- For each desired model, create a table to hold its data, must contain a column of type SDO_RDF_TRIPLE_S
create table articles_rdf_data (id number, triple sdo_rdf_triple_s);
 
-- For each model, create a model with model name, model table and model column
execute sem_apis.create_rdf_model('articles', 'articles_rdf_data', 'triple');

-- For each table to hold semantic data, insert data into the table, use a sequence
create sequence seq_articles_rdf_data increment by 1 nomaxvalue start with 1 minvalue 1 nocache nocycle order;

-- Article1 has the title "All about XYZ".
INSERT INTO articles_rdf_data VALUES (seq_articles_rdf_data.NEXTVAL,
  SDO_RDF_TRIPLE_S ('articles',
    'http://nature.example.com/Article1', 'http://purl.org/dc/elements/1.1/title','All about XYZ'));
-- Article1 was created (written) by Jane Smith.
INSERT INTO articles_rdf_data VALUES (seq_articles_rdf_data.NEXTVAL,
  SDO_RDF_TRIPLE_S ('articles',
    'http://nature.example.com/Article1', 'http://purl.org/dc/elements/1.1/creator', 'Jane Smith'));
-- Article1 references (refers to) Article2.
INSERT INTO articles_rdf_data VALUES (seq_articles_rdf_data.NEXTVAL,
  SDO_RDF_TRIPLE_S ('articles',
    'http://nature.example.com/Article1', 'http://purl.org/dc/terms/references', 'http://nature.example.com/Article2'));
-- Article1 references (refers to) Article3.
INSERT INTO articles_rdf_data VALUES (seq_articles_rdf_data.NEXTVAL,
  SDO_RDF_TRIPLE_S ('articles',
    'http://nature.example.com/Article1', 'http://purl.org/dc/terms/references', 'http://nature.example.com/Article3'));
-- Article2 has the title "A review of ABC".
INSERT INTO articles_rdf_data VALUES (seq_articles_rdf_data.NEXTVAL,
  SDO_RDF_TRIPLE_S ('articles',
    'http://nature.example.com/Article2', 'http://purl.org/dc/elements/1.1/title', 'A review of ABC'));
-- Article2 was created (written) by Joe Bloggs.
INSERT INTO articles_rdf_data VALUES (seq_articles_rdf_data.NEXTVAL,
  SDO_RDF_TRIPLE_S ('articles',
    'http://nature.example.com/Article2', 'http://purl.org/dc/elements/1.1/creator', 'Joe Bloggs'));
-- Article2 references (refers to) Article3.
INSERT INTO articles_rdf_data VALUES (seq_articles_rdf_data.NEXTVAL,
  SDO_RDF_TRIPLE_S ('articles',
    'http://nature.example.com/Article2', 'http://purl.org/dc/terms/references', 'http://nature.example.com/Article3'));
COMMIT;

-- Query semantic data
SELECT SEM_APIS.GET_MODEL_ID('articles') AS model_id FROM DUAL;

MODEL_ID
----------
         1

SELECT SEM_APIS.GET_TRIPLE_ID('articles',
  'http://nature.example.com/Article2',
  'http://purl.org/dc/terms/references',
  'http://nature.example.com/Article3') AS RDF_triple_id FROM DUAL;

RDF_TRIPLE_ID
-------------------------------------------------------
1_622D322515BDF6E1_90D25A8B08C653A_5643305B015FBF65

SELECT SEM_APIS.IS_TRIPLE('articles',
  'http://nature.example.com/Article2',
  'http://purl.org/dc/terms/references',
  'http://nature.example.com/Article3') AS is_triple FROM DUAL;

IS_TRIPLE
-----------------
TRUE (EXACT)

-- Use SDO_RDF_TRIPLE_S member functions in queries
SELECT a.triple.GET_TRIPLE() AS triple FROM articles_rdf_data a WHERE a.id = 1;
SELECT a.triple.GET_SUBJECT() AS subject FROM articles_rdf_data a WHERE a.id = 1;
SELECT a.triple.GET_PROPERTY() AS property FROM articles_rdf_data a WHERE a.id = 1;
SELECT a.triple.GET_OBJECT() AS object FROM articles_rdf_data a WHERE a.id = 1;

Have fun!