Targets
Oracle
Replicate into an Oracle database, with the connection, permissions, objects and options an Oracle target uses.
An Oracle target receives the initial load and the streamed changes through the agent's database connection. SQL Flow can create the target tables for you, and it applies changes set-based or one at a time depending on what the target allows.
Requirements
| Requirement | Details |
|---|---|
| Oracle release | SQL Flow doesn't check the release of a target. It's tested on the releases listed in Oracle: Supported versions. |
| Sources | Every source and capture mode. |
| Connection | Host, Port, Service name (PDB), Username and a password or secret reference. Service name (PDB) is the service of the database or PDB you write to. |
| Encryption | With Encryption at Require (default), enter the listener's TLS port and the Server certificate DN; the agent validates the server certificate against the agent host's trust store and checks that its DN matches. Wallet path on agent is optional, for a client certificate; wallet files must be readable by the agent's service account. Prefer without any TLS setting connects over plain TCP. See Oracle: Connection encryption. |
| Target schema | The schema (Oracle user) that tables map to must already exist. SQL Flow creates tables but never creates users. Tables with no mapped schema go to the connecting user's own schema. |
| Keys | Each target table needs a primary key or a unique constraint on the source key columns. |
Permissions
SQL Flow doesn't check the login's privileges up front; a missing privilege surfaces as the Oracle error from the statement that needed it. Grant the login what the pipeline does:
| What SQL Flow does | Privileges |
|---|---|
| Connects | CREATE SESSION |
| Writes the initial load and changes | SELECT, INSERT, UPDATE and DELETE on each target table. A table in the login's own schema needs no grant. |
| Creates target tables (Prepare target schema) and its own apply tables | CREATE TABLE and a tablespace quota. To create tables in another user's schema, CREATE ANY TABLE. |
| Adds a missing key to an existing table | ALTER on that table, or ownership |
| Applies source schema changes (added and dropped columns) | Ownership of the target tables, or the ALTER ANY TABLE system privilege. See Schema changes by target. |
Creates object types for Oracle-to-Oracle tables with object or VARRAY columns | CREATE TYPE |
| Uses set-based apply (Collapse row versions) | SELECT ANY DICTIONARY, SELECT ANY TABLE or READ ANY TABLE. Without one of them, SQL Flow can't check the table's triggers, foreign keys and indexes, and applies every change in order. |
For example, for a dedicated apply user that owns its target tables:
CREATE USER <apply_user> IDENTIFIED BY "<password>" QUOTA UNLIMITED ON <tablespace>;
GRANT CREATE SESSION, CREATE TABLE TO <apply_user>;
GRANT SELECT ANY DICTIONARY TO <apply_user>;Replace <apply_user>, <password> and <tablespace> with your values. Add CREATE TYPE for Oracle-to-Oracle pipelines with object or VARRAY columns.
What SQL Flow creates on the target
| Object | When | Where |
|---|---|---|
| Target tables, with the source key as the primary key | Only after you confirm Prepare target schema in the wizard | The mapped schema, or the login's schema. Names are uppercase and quoted. |
| A primary key, or a unique constraint when the table already has a primary key | When an existing table has no key on the source key columns | On that table |
| Object types | Oracle-to-Oracle tables with object or VARRAY columns | The mapped schema |
CDC_APPLY_CHECKPOINT, CDC_PARTIAL_APPLY, and CDC_STG_<hash>_<hash> temporary tables | Set-based apply (Collapse row versions) is in use | The login's schema |
SQL Flow also keeps the pipeline's committed position on the target, so another agent can take over and resume from what the target holds. See High availability for agents.
When SQL Flow creates a table from a non-Oracle source, it chooses the Oracle type from the value type:
| Source value | Oracle column |
|---|---|
| Text | clob; varchar2(4000) in a key |
| Binary | blob; raw(2000) in a key |
| Boolean | number(1) |
| 32-bit and 64-bit integers | number(10), number(19) |
| Decimal | number |
| Double, float | binary_double, binary_float |
| GUID | raw(16) |
| Date and time; with offset | timestamp; timestamp with time zone |
| Time span | interval day(9) to second(6) |
An Oracle source keeps its native column types. See Data type mapping for each source engine.
Options
These appear on the wizard's Target step after you choose Oracle.
| Option | Default | Effect |
|---|---|---|
| Collapse row versions (faster) | On | Writes only each row's final state with set-based apply. Untick it if a target trigger or audit must see every change. |
| Allow streamed large-object loads despite triggers | Off | Lets the initial load write a row with a large value as one INSERT plus one update per large value, even when the target has triggers. Tick it only if your triggers accept that. |
| Atomic transaction limits: Maximum records per transaction | 1,000,000 (maximum 10,000,000) | Apply stops if one source transaction exceeds the limit, keeping the transaction intact for recovery. |
| Atomic transaction limits: Maximum transaction size (MiB) | 512 | The same, by size. |
Collapse row versions is a request, not a guarantee. SQL Flow applies every change in order instead when:
- the source can't guarantee complete row images: it isn't SQL Server, Azure SQL or Oracle, or it's an Oracle source without an
(ALL) COLUMNSsupplemental log group on every table; - the login can't read the data dictionary (see Permissions);
- a target table has an enabled trigger, a foreign key, another unique constraint or index, a generated or identity column, or row-level security, or isn't an ordinary non-partitioned table;
- the pipeline is bidirectional.
Limitations
- Generated type sizes: in tables SQL Flow creates, key text columns are
varchar2(4000)and key binary columns areraw(2000). Create the table yourself if a key needs a different type. - One database per connection: target tables can't be qualified with another database.
