Wednesday, 2 April 2014

Profound - Oracle DB - Schema == User

I'm slowly getting my head around Oracle's DB product, and saw this earlier: -
  • A schema is a collection of logical structures of data, or schema objects.
  • A schema is owned by a database user and has the same name as that user.
  • Each user owns a single schema.
  • Schema objects can be created and manipulated with SQL and include the following types of objects:

This is especially useful to me with IBM Business Monitor, where I have three users: -
  • COGNOS
  • IBMBUSSP
  • MONITOR
but also have two additional, distinct schemas: -
  • MONCM00
  • MONME00
which are used to separate the tables for the two Messaging Engines ( used by the Service Integration Bus to provide cross-cell persistence )

The three user accounts need to be capable of logging on, as they're used by various elements of BAM, including Cognos BI, Business Space and, of course, the two Messaging Engines, which communicate to Oracle via JDBC data sources, using the MONITOR user ID.

Therefore, I need to ensure that I create the three user IDs: -

SQL> CREATE USER COGNOS
  IDENTIFIED BY passw0rd
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

SQL> CREATE USER IBMBUSSP
  IDENTIFIED BY passw0rd
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

SQL> CREATE USER MONITOR
  IDENTIFIED BY passw0rd
  PROFILE DEFAULT
  ACCOUNT UNLOCK; 

and that I then separately create the two schemas, using the sibDDLGenerator tool: -

/opt/IBM/WebSphere/AppServer/bin/sibDDLGenerator.sh -system oracle -version 11g -platform unix -schema MONCM00 -statementend ";" >> ~/createMESchemas.sql
/opt/IBM/WebSphere/AppServer/bin/sibDDLGenerator.sh -system oracle -version 11g -platform unix -schema MONCM00 -statementend ";" >> ~/createMESchemas.sql
sqlplus / as SYSDBA @createMESchemas.sql;


This, of course, is over and above the need to create the Monitor, Business Space and Cognos tables etc. separately.

Neat-o

No comments: