Configuring Oracle for Streaming Components

If you intend to use Oracle to store the metadata for Streams Messaging Manager or Schema Registry, you must configure the Oracle database.

After you install Oracle, configure the database to store:

Schema Registry data such as the schemas and their metadata, all the versions and branches.
SMM data such as Kafka metadata, stores metrics, and alert definitions.

You must have a DBA role to perform the following steps.

Log in to the Oracle database.
Create a new user for Schema Registry or SMM.
For Schema Registry,
```
create user registry identified by 123456;
```
For SMM,
```
create user streamsmsgmgr identified by 123456;
```

Create a new tablespace.

For Schema Registry,

create tablespace schemaregistry ONLINE;

For SMM,

create tablespace streamsmsgmgr ONLINE;

Alter the default tablespace of the user to use the newly created one.
For Schema Registry,
```
alter user registry default tablespace schemaregistry;
```
For SMM,
```
alter user streamsmsgmgr default tablespace streamsmsgmgr;
```
Grant access to the database for the newly created user.
For Schema Registry,
```
grant connect to registry;
```
For SMM,
```
grant connect to streamsmsgmgr;
```
Grant other privileges to the user including create tables, views, stored procedures, sequences, and drop tables, select queries, and so on.
For Schema Registry,
```
grant all privileges to registry;
```
For SMM,
```
grant all privileges to streamsmsgmgr;
```
If you cannot grant all privileges, grant the following privileges that SMM and Schema Registry require at a minimum:
- CREATE/ALTER/DROP TABLE
- CREATE/ALTER/DROP INDEX
- CREATE/ALTER/DROP SEQUENCE
- CREATE/ALTER/DROP PROCEDURE
You can find the list of privileges by running the following SQL:
```
SELECT * FROM session_privs
ORDER BY privilege;
```

The new user needs to have permissions to create or drop tables and other database structures because when the Schema Registry or SMM service starts, it automatically creates the database structure. If the database structure already exists, then the service alters it as necessary.