Creating database objects for Oracle

Suggest Edits

Database prerequisites

  • The database instance must already exist.
  • This procedure must be performed by a DBA user

Let's start

  • Copy DOIM_HOME/pkg-repo/pkg-base/db/ghibli-distro-datashaper-db-<version>.tar.gz to the database host or to a client that can connect to the database.
  • Extract the package ghibli-distro-datashaper-db-<version>.tar.gz.
  • Run the steps described in the following sections.

Please notice that some of the SQL scripts require parameters whose value must be the same as the value specified in the DMCFG.

Here is a quick cross-reference between the two sets of parameters:

APP_USERds_db_usr
ADMIN_USERds_db_schema
APP_PASSds_db_password

1. Create tablespaces

sqlplus <dba_user>/<dba_password>@<host_address>:<host_port>/<db_name> 
@01_DS_PREPARE_TBLS.sql <space-separated script parameters>

Replace:

  • <dba_user> with the username of a DBA user
  • <dba_password> with the password of the DBA user
  • <host_address> with the IP address or the fully qualified domain name of the database host
  • <host_port> with the port where database is listening to
  • <db_name> with the database name

Here is the list of the required space-separated script parameters:

  • tbls_ds_ge_data: tablespace name for generic data
  • tbls_ds_ge_data_path: absolute datafile path for generic data tablespace

2. Create users

sqlplus <dba_user>/<dba_password>@<host_address>:<host_port>/<db_name> 
@02_DS_PREPARE_USERS.sql <space-separated script parameters>

Replace:

  • <dba_user> with the username of a DBA user
  • <dba_password> with the password of the DBA user
  • <host_address> with the IP address or the fully qualified domain name of the database host
  • <host_port> with the port where the database is listening to
  • <db_name> with the database name

Here is the list of the required space-separated script parameters:

  • tbls_ds_ge_data: tablespace name for generic data
  • ADMIN_USER: new username of the owner of objects (tables, views, …).
  • ADMIN_PASS: password for the new owner of objects
  • APP_USER: username used by the application to connect to the database
  • APP_PASS: password for the application user

3. Create schema

sqlplus <ADMIN_USER>/<ADMIN_PASS>@<host_address>:<host_port>/<db_name> 
@03_DS_CREATE_SCHEMA.sql <space-separated script parameters>

Replace:

  • <ADMIN_USER> with the username of the owner of the objects created in the previous script
  • <ADMIN_PASS> with the password of the owner of the objects
  • <host_address> with the IP address or the fully qualified domain name of the database host
  • <host_port> with the port where the database is listening to
  • <db_name> with the database name

4. Load initial data

sqlplus \<ADMIN_USER>/<ADMIN_PASSWORD>@<host_address>:<host_ port>/<db_name>
@04_DS_INITIAL_DML.sql

Replace:

  • <ADMIN_USER> username of the owner of the objects
  • <ADMIN_PASSWORD> password of the owner of the objects
  • <host_address> with the IP address or the fully qualified domain name of the database host
  • <host_port> with the port where the database is listening to
  • <db_name> with the database name

5. Assign permissions to the application user

sqlplus <dba_user>/<dba_password>@<host_address>:<host_port>/<db_name> 
@05_DS_GRANT_USER.sql <space-separated script parameters>

Replace:

  • <dba_user> with the username of a DBA user
  • <dba_password> with the password of the DBA user
  • <host_address> with the IP address or the fully qualified domain name of the database host
  • <host_port> with the port where the database is listening to
  • <db_name> with the database name

Here is the list of the required space-separated script parameters:

  • ADMIN_USER: username of the owner of objects (tables, views, …). The username must be UPPERCASE.
  • APP_USER: username used by the application to connect to the database. The username can be lowercase or UPPERCASE.

Updated 6 months ago