Oracle 12c release introduced the concept of multi-tenant architecture for housing several databases running as service under a single database, I'll try to address the connectivity and required configuration to connect to one of the Pluggable database running in the multi-tenant architecture.
Running multiple databases under a single database installation. Oracle 12c allows user to create one database called Container Database (CDB) and then spawn several databases called Pluggable Databases (PDB) running as services under it.
Such a setup allows users to spawn a new PDB as and when needed with limited resource requirements, easily administer several PDBs just by administering the container database - since all the PDBs are contained within a single database's tablespace structure, start and stop individual PDB leading to low cost on maintaining different databases - as the resource management is limited to one CDB.
In scenarios like creating PoCs, different test environments requiring external data storage, maintaining different versions of dataset, having this run in the multi-tenant architecture could help save time, money and effort.
Creation of a Container Database (CDB) is not very different from creating a non Container Database use the attached guide Installing Oracle Database Software and Creating a Database.pdf same is accessible online.
Use the attached Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c PDF guide to create and plug a Pluggable Database into the Container Database created in previous step, same is accessible online
Using above guide I have bunch of pluggable databases as can be seen below. I'll be using TW724 for connecting to ThingWorx server as an external datasource for following example
5. Once done save the entity
Note: A PDB in a container database can be reached only as a service and not using the CDB's SID. In the above configuration TW724 is a PDB which can be connected to via it's service name i.e. TW724.PTCNET.PTC.COM
Let's head to the Services tab for TW724_PDB_Thing to query and access the PDB data
Creating Services to access the PDB as external database source for ThingWorx
Once the configuration is done the TW724_PDB_Thing is ready for use. The queries remain the same as any other SQL query needed to access the data from Oracle.
Once on the Services tab for the TW724_PDB_Thing click on Add My Service select the service handler as SQL Command to use following script to create a testTable1 in the PDB
create table testTable1 (
id NUMBER GENERATED ALWAYS AS IDENTITY primary key,
Note: GENERATED ALWAYS AS IDENTITY option is Oracle 12c specific and I included it here for the reason that with Oracle 12c the possibility to auto generate is now built in with that option simplifying the sequence generation when compared with older Oracle versions such as Oracle 11g. User creating table will need access right on creating table and sequence checkout the Oracle documentation on Identity for more on this.
Add another service with script Select * from testTable1 for getting all the data from the table
Adding another service with script insert into testTable1 (col1, col2) values ('TextValue', 123) will insert the data into the table created above
Using Select * from tab lists all the available tables in the TW724 PDB
Just a quick wrap up on how this would look visually refer to the following image. Since this is a scalable setup - given the platform having enough resources it's possible to create upto 252 PDBs under a CDB therefore 252 PDBs could be created and configured to as many things extending the OracleDBServer12 Thing.
If you observe the error something like this Unable to Invoke Service GetAllPDBTables on TW724_PDB_Thing : ORA-01033: ORACLE initialization or shutdown in progress
Ensure that the pluggable database, in this error TW724 (since this is what I created and used above in my services) is opened and accessible. If it's not opened use the command after logging in as sys/system (with admin rights) in CDB, which is ORCL in via SQL*Plus or SQL Developer or any SQL utility of your choice capable of connecting to Oracle DB and open the pluggable database using the command : alter pluggable database tw724 open;
We are an Oracle shop so I was wondering if PTC officially supports Oracle 12c as an External Data Storage for ThingWorx? I didn't see it listed anywhere in the list of comparable Database Storage Options for ThingWorx (taken from KCS Article CS257403). Can someone please advise?
Oracle is not supported as Persistence Provider for Thingworx. But you can use Oracle database for different tasks by connecting to it via JDBC driver from Thingworx.
For the moment, there is no information regarding Oracle as persistence provider.
Regarding what tasks can be done by connecting to Oracle DB with JDBC, for example you can extract data from Oracle to create a mashup/dashboard in Thingworx. Mashup could contain, for example, graphs,charts, tables, etc.
On the other hand, you can create a mashup that could help with inserting data into Oracle database (by creating fields to insert values, similar to Oracle Forms), or you could get some data from ThingWorx and you want to insert it in some Oracle table/s.
All of these operations should be put in a context of a business need, I have just provided some general examples.
If you have other questions, please let me know.