Creating database objects for Oracle
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_USER | ds_db_usr |
ADMIN_USER | ds_db_schema |
APP_PASS | ds_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 datatbls_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 dataADMIN_USER
: new username of the owner of objects (tables, views, …).ADMIN_PASS
: password for the new owner of objectsAPP_USER
: username used by the application to connect to the databaseAPP_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 11 months ago