---
title: Oracle - Target environment
description: An Oracle target environment needs PowerShell 7 and SQL*Plus on the machine that deploys, and one database user per layer plus one load-control user.
---

[Skip to content](https://knowledge.bigenius-x.com/oracle-target-environment#main-content)

[Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en)

[![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/)

Open main navigation

Close main navigation

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new)
- [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal)

 How can we help you?

- There are no suggestions because the search field is empty.

1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en)
2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en)
3. [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment)

# Oracle - Target environment

**Applies to**  
Platform: Oracle

An Oracle target environment needs PowerShell 7 and SQL\*Plus on the machine that deploys, and one database user per layer plus one load-control user. Prepare it before you replace the placeholders and deploy the artifacts of an Oracle generator. Afterwards each user can connect and holds the privileges that deployment and loading need.

The steps apply to all Oracle generator configurations: Stage, Data Store, Data Vault, Data Mart and their combination. biGENIUS support does not cover the installation and configuration of Oracle beyond the example in this article.

### Prerequisites

- An Oracle database that the deploying machine reaches by host name, port and service name.
- An administrator account that can create users and grant privileges, or a database administrator who creates the users for you.
- For the helper script: the artifacts are [generated](https://knowledge.bigenius-x.com/generate-artifacts?hsLang=en) and [downloaded](https://knowledge.bigenius-x.com/download-generated-artifacts?hsLang=en), and their [placeholders are replaced](https://knowledge.bigenius-x.com/replace-the-placeholders-in-the-artifacts?hsLang=en).

### Install the tools

1. Install [PowerShell 7](https://learn.microsoft.com/powershell/scripting/install/installing-powershell-on-windows). The scripts `deploy.ps1` and `execute.ps1` do not run in Windows PowerShell 5.1.
2. Install SQL\*Plus, for example the Basic and SQL\*Plus packages of [Oracle Instant Client](https://www.oracle.com/database/technologies/instant-client.html).
3. Add the SQL\*Plus folder to the `PATH` environment variable and open a new PowerShell 7 window.
4. Run `sqlplus -v`. SQL\*Plus prints its release, for example `SQL*Plus: Release 23.26.1.0.0 - Production`.

### Create the database users

The artifacts connect as one user per layer, for example Stage, Raw Vault and Business Vault, and with Native Load Control as one load-control user. Each user owns the objects of its layer, so its user name is also the schema name in the [placeholder values](https://knowledge.bigenius-x.com/oracle-placeholder-values?hsLang=en). Deployment and native loading need at least these privileges:

- Every user: `CREATE SESSION`, `CREATE TABLE`, `CREATE VIEW`, `CREATE PROCEDURE` and `CREATE SEQUENCE`, and quota on each tablespace its tables and indexes use.
- Stage user with [Database Links for Source Systems](https://knowledge.bigenius-x.com/database-links-for-source-systems-project?hsLang=en) set to `Create Database Links`: `CREATE DATABASE LINK`.
- Stage user with `Do Not Use Database Links`: `SELECT` on each source table or view.
- Load-control user: `EXECUTE` on `SYS.DBMS_LOCK`.

`CREATE SEQUENCE` is needed because the load-control tables and some vault tables have identity columns. In Oracle AI Database 26ai (release 23.26), the roles `CONNECT` and `RESOURCE` together contain the five privileges of the first item, but no tablespace quota, no `CREATE DATABASE LINK` and no `EXECUTE` on `SYS.DBMS_LOCK`.

#### Create the users with the helper script

The artifacts contain the example script `Helpers\create_development_user.sql`, available since Generator 2.2. It creates each layer user and the load-control user with the default tablespace `USERS` and the temporary tablespace `TEMP`. It grants the roles `RESOURCE` and `CONNECT`, `CREATE VIEW` and unlimited quota on `USERS`. The Stage user also gets `CREATE DATABASE LINK` and `CREATE ANY DIRECTORY`. The load-control user also gets `EXECUTE` on `DBMS_LOCK`, `CREATE ANY CONTEXT` and `CREATE PUBLIC SYNONYM`.

1. Open `Helpers\create_development_user.sql` of the replaced artifacts in a text editor.
2. Replace each password after `IDENTIFIED BY` with the password of that user in `replacement_config.json` and save the file.
   
   The script sets each password to the user name. Replace the passwords before you run it.
3. Open PowerShell 7 in the artifacts folder and connect as an administrator, for example `sqlplus sys@//<host name>:<port>/<service name> as sysdba`. Here `<host name>`, `<port>` and `<service name>` identify your database.
4. Run `@Helpers/create_development_user.sql`. SQL\*Plus prints `User created.`, `User altered.` and `Grant succeeded.` for each user.

The script grants quota only on `USERS`. A model object whose [Tablespace Table or Tablespace Index](https://knowledge.bigenius-x.com/partition-clause-and-table-storage-model-object?hsLang=en) names another tablespace needs quota there as well. Without it, the deployment of that object fails with `ORA-01950`.

### Provide the source data

The Stage layer reads the source systems through database links or directly, as set in [Database Links for Source Systems (Project)](https://knowledge.bigenius-x.com/database-links-for-source-systems-project?hsLang=en). To create source model objects from an Oracle schema, see [Discover an Oracle database](https://knowledge.bigenius-x.com/discover-an-oracle-database?hsLang=en). The source system types each generator configuration accepts are listed in [Source Systems per Generator Configurations](https://knowledge.bigenius-x.com/source-systems-per-generator-configurations?hsLang=en).

### Example

A Data Vault project with the layers Stage, Raw Vault and Business Vault uses the example users `DWH_STG`, `DWH_RV`, `DWH_BV` and `LC`. After the replacement, the helper script creates the load-control user like this:

```
CREATE USER LC IDENTIFIED BY LC;
ALTER USER LC
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    ACCOUNT UNLOCK ;
GRANT RESOURCE, CONNECT TO LC;
ALTER USER LC DEFAULT ROLE "RESOURCE","CONNECT";
ALTER USER LC QUOTA UNLIMITED ON USERS;
GRANT CREATE VIEW TO LC;
GRANT EXECUTE ON DBMS_LOCK TO LC;
GRANT CREATE ANY CONTEXT TO LC;
GRANT CREATE PUBLIC SYNONYM TO LC;
```

Before you run it, replace `IDENTIFIED BY LC` with `IDENTIFIED BY <password>`, where `<password>` is the password of the load-control user in `replacement_config.json`.

### Check the result

Connect as each user, for example `sqlplus DWH_STG@//<host name>:<port>/<service name>`. SQL\*Plus prints `Connected to:` and the database release. Then run `SELECT * FROM SESSION_PRIVS;` and check that the `CREATE` privileges listed above appear.

### Failure symptoms and recovery

#### The sqlplus command is not found

Message: `SqlPlus is not installed, not recognized or the PATH environment variable is not properly configured`  
Cause: The SQL\*Plus folder is not in `PATH` of the PowerShell window.  
Fix: Add the folder to `PATH`, open a new PowerShell 7 window and check `sqlplus -v`.

#### The helper script cannot create the load-control user

Message: `ORA-01920`  
Cause: The load-control user exists already, for example because the helper script of another project created it.  
Fix: None needed. The script continues and creates the other users.

### Related articles

- Next: [Oracle - Placeholder values](https://knowledge.bigenius-x.com/oracle-placeholder-values?hsLang=en), [Oracle - Deployment](https://knowledge.bigenius-x.com/oracle-deployment?hsLang=en)
- Reference: [Database Links for Source Systems (Project)](https://knowledge.bigenius-x.com/database-links-for-source-systems-project?hsLang=en), [Partition Clause and table storage (Model Object)](https://knowledge.bigenius-x.com/partition-clause-and-table-storage-model-object?hsLang=en), Oracle generator profiles (no article yet)
- See also: [Oracle - Artifacts](https://knowledge.bigenius-x.com/oracle-artifacts?hsLang=en), [Discover an Oracle database](https://knowledge.bigenius-x.com/discover-an-oracle-database?hsLang=en), [Native - Load control environment](https://knowledge.bigenius-x.com/native-load-control-environment?hsLang=en), [Apache Airflow - Load control environment](https://knowledge.bigenius-x.com/apache-airflow-load-control-environment?hsLang=en)

- [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content)

    - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines)
    - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working)
    - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account)
- [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en)
- [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en)
- [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content)

    - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration)
    - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions)
    - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features)
    - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects)
    - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches)
    - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections)
    - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview)
    - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps)
    - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms)
    - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping)
    - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships)
    - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality)
    - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes)
    - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling)
    - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts)
    - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings)
    - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace)
- [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content)

    - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse)
    - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse)
    - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks)
    - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake)
    - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server)
    - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts)
    - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders)
    - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment)
    - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment)
    - [Deployment with an Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-devops-pipeline)
    - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment)
    - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment)
    - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control)
    - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow)
    - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type)
    - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties)
    - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms)
- [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content)

    - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations)
- [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content)

    - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches)
    - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases)
    - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules)
    - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules)
- [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en)
- [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content)

    - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application)
    - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application)
    - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators)
- [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content)

    - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs)
    - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits)

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en)
- [Customer Portal](https://knowledge.bigenius-x.com/customer-portal)
- [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login)

[![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com)

<https://www.linkedin.com/company/bigenius> <https://www.youtube.com/@biGENIUS_DWA>

All rights reserved © 2025, biGENIUS AG