Oracle Infrastructure Requirements
Relevant source files
The following files were used as context for generating this wiki page:
Introduction
The flext-target-oracle project requires a specific Oracle Database environment to function as a Singer Target. The infrastructure must support advanced data loading patterns, including bulk operations, schema evolution, and flattened or JSON storage modes. The system is designed to interface with Oracle XE or enterprise editions, leveraging flext-db-oracle for DDL and DML operations.
The infrastructure requirements span connectivity, user permissions, and storage structures necessary for high-performance ETL workflows. This includes support for specific data types such as CLOB for JSON data and TIMESTAMP for audit columns, as well as the ability to manage custom indexes and sequences.
Sources: README.md, src/flext_target_oracle/_utilities/loader.py:1-25, CONTRIBUTING.md:12-25
Core Database Requirements
Connectivity and Versioning
The target is validated against Oracle 21c (XE edition) using Python 3.13+. The connection requires a valid Host, Port (default 1521), and Service Name (e.g., XE or FLEXTDB).
Sources: CONTRIBUTING.md:46-55, tests/conftest.py:75-85, examples/run_example.sh:25-35
Storage Structures
The infrastructure must provide adequate tablespace for data and temporary operations. The test environment utilizes a dedicated tablespace with auto-extension capabilities.
CREATE TABLESPACE test_data
DATAFILE '/opt/oracle/oradata/XEPDB1/test_data01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;
Sources: tests/artifacts/oracle-init/01-create-test-schema.sql:5-8
User Permissions and Security
The Oracle user assigned to the target requires specific system and object privileges to manage the lifecycle of target tables and data.
Required Privileges
| Privilege Type | Purpose |
|---|---|
CONNECT, RESOURCE, CREATE SESSION | Basic database access and session management. |
CREATE TABLE, CREATE VIEW | Needed for target table creation and reporting views. |
CREATE SEQUENCE, CREATE TRIGGER | Used for auto-incrementing primary keys. |
UNLIMITED TABLESPACE | Allows the user to utilize the allocated tablespace fully. |
SELECT_CATALOG_ROLE | Required for schema discovery and table metadata fetching. |
Sources: tests/artifacts/oracle-init/01-create-test-schema.sql:15-25, tests/conftest.py:95-105
User Identity Setup
The infrastructure expects a dedicated schema for the target data, typically defaulting to SINGER_DATA or a user-defined schema like FLEXT_TEST.
The diagram shows the hierarchy of database objects required for the target environment.
Sources: tests/artifacts/oracle-init/01-create-test-schema.sql:10-40
Data Modeling and Schema Support
The Oracle environment must support specific data structures derived from Singer schemas. The OracleLoader maps Singer types to Oracle-native types.
Schema Components
- Audit Columns: Every table requires
_SDC_EXTRACTED_ATand_SDC_LOADED_ATcolumns of typeTIMESTAMP. - JSON Storage: If
storage_modeis set tojsonorhybrid, aCLOBcolumn (default nameJSON_DATA) is required. - Primary Keys: The infrastructure must support
NUMBERprimary keys often managed via sequences.
Sources: src/flext_target_oracle/_utilities/loader.py:218-232, src/flext_target_oracle/_utilities/loader.py:126-140
Entity Relationship Pattern
The target often handles relational data patterns, such as Customers and Orders, requiring foreign key support and indexing.
The ER diagram illustrates the typical table structure and relationships created during initialization.
Sources: tests/artifacts/oracle-init/01-create-test-schema.sql:35-70
Performance Infrastructure
To support high-volume data loading, the Oracle environment and target configuration must account for bulk operations.
Bulk Loading Requirements
- Batch Size: Infrastructure must handle batches (default 1000 records).
- Parallel Degree: Supports parallel execution for DDL/DML (configurable via
parallel_degree). - Indexes: The target creates indexes for primary keys and custom indexes for performance. Foreign key indexes are often recommended to prevent locking issues.
| Configuration | Default | Description |
|---|---|---|
batch_size | 1000 | Records per batch for processing. |
use_bulk_operations | True | Enables Oracle bulk operations. |
parallel_degree | 1 | Degree of parallelism for DB operations. |
truncate_before_load | False | Clears table before fresh data load. |
Sources: tests/unit/test_config.py:18-25, tests/conftest.py:145-160, src/flext_target_oracle/_utilities/loader.py:433-450
Environment Configuration
The target is configured using environment variables or a settings object.
FLEXT_TARGET_ORACLE_HOST=localhost
FLEXT_TARGET_ORACLE_PORT=1521
FLEXT_TARGET_ORACLE_SERVICE=XE
FLEXT_TARGET_ORACLE_USER=FLEXT_TEST
FLEXT_TARGET_ORACLE_PASSWORD=test_password
Sources: tests/README.md:95-105
Conclusion
The Oracle infrastructure for flext-target-oracle must be robust, providing a high-performance environment with specific privileges for DDL/DML operations. Requirements include a dedicated schema, specific tablespace configurations, and support for Oracle-specific types like CLOB and TIMESTAMP. These elements ensure the target can maintain data integrity and performance during large-scale Singer tap synchronizations.