Installing KDB on Oracle

All the KDB tables must be in a common schema owned by a single user, conventionally KCC, who is the only user granted update access in order to preserve referential integrity. KDB provides a mechanism to automatically grant read-only access to a role for all the tables in this schema. Other users accessing the database through ODBC or other tools should be created in that role (see _CONN_ATTR_RO_ROLE connection attribute).

Create this schema with the following script while logged in as SYSDBA:

REM First drop any existing KCC user and any tables in its schema 
DROP USER kcc CASCADE;
REM Create the new user setting default and temporary tablespaces appropriately 
CREATE USER kcc IDENTIFIED BY kcc123 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
REM Give the user connection and table creation privileges 
GRANT CONNECT TO KCC;
GRANT RESOURCE TO KCC;
REM Create view on KCML session info
CREATE OR REPLACE VIEW K$SESSIONS AS SELECT INST_ID,SID,SERIAL#,AUDSID,CLIENT_IDENTIFIER,CLIENT_INFO,CAST(MACHINE AS VARCHAR2(32)) "SERVER",OSUSER "LOGNAME",RESOURCE_CONSUMER_GROUP FROM GV$SESSION WHERE TYPE='USER' AND STATUS IN ('ACTIVE', 'INACTIVE') AND CLIENT_IDENTIFIER LIKE 'KCML:%' AND UPPER(PROGRAM) LIKE 'KC%';
GRANT SELECT ON K$SESSIONS TO KCC;

You may want to use a different default tablespace. The example kcc123 password is the default for the password cloaking scheme described in KI_CONNECT. Only the SYSDBA will ever actually use it as applications will use the hidden one. This is an example; choose your own secure password and never use kcc123 on a real database.

This script also creates a view on the system view GV$SESSION which allows KDB to see processes running on other nodes of a RAC cluster. This is exposed to the application through the KI_SESSIONS pseudotable.