Oracle as Target

Fivetran HVR supports integrating changes into Oracle database. This section describes the configuration requirements for integrating changes (using Integrate and Refresh) into Oracle location.

For the list of supported Oracle versions, into which HVR can integrate changes, see Integrate changes into location in Capabilities.

HVR uses the following interfaces to write data to Oracle during Refreshing Data or Integrate:

Oracle native OCI interface, used for continuous Continuous Integrate and Row-wise Refresh.
Oracle OCI direct-path-load interface, used for Burst Integrate and Bulk Refresh.

Grants for Integrate and Refresh

This section lists the grants required for integrating changes into Oracle.

The HVR database User must be allocated a quota on the default tablespace. For this, the HVR database user must be granted the following privilege:
```
alter user username quota quotalimit on tablespacename;
```
Alternatively, unlimited tablespace quota may be granted:
```
grant unlimited tablespace to username;
```
To Integrate changes or to Refresh data into Oracle database, the HVR database User must be granted create session and create table privileges:
```
grant create session to username;

grant create table to username;
```
To create target tables using Refresh, the HVR database User must be granted create any index, drop any index, create any table, alter any table, and drop any table privileges:
```
grant create, drop any index to username;

grant create, alter, drop any table to username;
```
To change tables which are owned by other schemas (using action TableProperties with parameter Schema), the HVR database User must be granted the following select any table, insert any table, update any table, and delete any table privileges:
```
grant select, insert, update, delete any table to username;
```

To read the data dictionaries in Oracle's SYS schema for target-side metadata lookups, the HVR database User must be granted the select any dictionary privilege:

grant select any dictionary to username;

Even if you grant select any dictionary, certain target metadata lookups may require explicit select privileges on specific internal dictionary tables in the SYS schema, for example, SYS.OBJ$, SYS.USER$, and SYS.COL$. Grant the applicable SYS table privileges from the specific grants list below.

As an alternative to granting select any dictionary, you may grant the HVR database user the SELECT privilege on all of the required data dictionary objects listed below:

Click here for the specific grants

/* If select any dictionary is not granted, then grant select on the following dictionary views as required. */

grant select on sys.all_objects to username;

grant select on sys.all_tables to username;

grant select on sys.all_tab_cols to username;

grant select on sys.all_nested_tables to username;

grant select on sys.all_indexes to username;

grant select on sys.all_ind_columns to username;

grant select on sys.all_constraints to username;

grant select on sys.all_cons_columns to username;

grant select on sys.all_lobs to username;

grant select on sys.all_log_groups to username;



/* The following grant is required for table-version lookups. This must be provided explicitly even when 'select any dictionary' is granted. */

grant select on sys.obj$ to username;



/* The following grants are required only for partitioned target tables. */

grant select on sys.all_tab_partitions to username;



/* The following two grants are required only for partition statistics lookups. */

grant select on sys.all_part_tables to username;

grant select on sys.all_part_key_columns to username;



/* The following grants are required only for Index-Organized Tables with partitioned indexes or overflow partitions. These must be provided explicitly even when 'select any dictionary' is granted. */

grant select on sys.indpart$ to username;

grant select on sys.tabpart$ to username;



/* The following grants are required only for encrypted target tables, encrypted tablespaces, or wallet verification. The grants on sys.user$, sys.col$, and sys.enc$ must be provided explicitly even when 'select any dictionary' is granted. */

grant select on sys.all_encrypted_columns to username;

grant select on sys.dba_tablespaces to username;

grant select on sys.user$ to username;

grant select on sys.col$ to username;

grant select on sys.enc$ to username;



/* The following grant is required only for Oracle Database 11.2 and above when inspecting column defaults, including when AdaptDDL syncs destination tables with source schema updates. This must be provided explicitly even when 'select any dictionary' is granted. */

grant select on sys.ecol$ to username;



/* The following grant (sys.all_views) is required only when the channel includes views. */

grant select on sys.all_views to username;



/* The following grant (sys.all_sequences) is required only when action DbSequence is configured. */

grant select on sys.all_sequences to username;



/* The following two grants are required only for column statistics and histogram lookups. */

grant select on sys.all_tab_col_statistics to username;

grant select on sys.all_tab_histograms to username;



/* The following grants are required only when we inspect target schema users, procedures, or triggers during target metadata checks, including trigger-related setup. */

grant select on sys.all_users to username;

grant select on sys.all_procedures to username;

grant select on sys.all_triggers to username;

To perform Bulk Refresh of tables which are owned by other schemas, the HVR database User must be granted the following alter any table, lock any table, and drop any table privileges :
```
grant alter, lock any table to username;

/* Following grant is required for truncate statements */

grant drop any table to username;
```
To use action DbSequence with parameter Schema, the HVR database User must be granted create any sequence and drop any sequence privileges:
```
grant create, drop any sequence to username;
```
To disable/re-enable triggers in target schema, the HVR database User must be granted the create any trigger and alter any trigger privileges:
```
grant create, alter any trigger to username;
```

Intermediate Directory

This option in the HVR UI allows you to specify a directory path for storing intermediate (temporary) files generated during Compare. These files are created during both "direct file compare" and "online compare" operations.

This option is displayed in the location creation dialog when creating a new location, and in the Source and Target Properties pane on the Location Details page when editing an existing location.

Using an intermediate directory can enhance performance by ensuring that temporary files are stored in a location optimized for the system's data processing needs.

This setting is particularly relevant for target file locations, as it determines where the intermediate files are placed during the Compare operation. If this option is not enabled, the intermediate files are stored by default in the integratedir/_hvr_intermediate directory, where integratedir is the replication DIRECTORY (File_Path) defined for the target file location.

This option is equivalent to the location property Intermediate_Directory.

Intermediate Directory is Local

This option indicates that the Intermediate Directory will be created on the local drive of the location's server. This option is displayed when you select the Intermediate Directory option in the HVR UI. It is selected by default and cannot be modified.

Storing intermediate files locally is crucial for optimizing performance by reducing network latency and avoiding potential permission issues associated with remote storage. It enables HVR to process data more efficiently by leveraging the speed and reliability of local storage. This is particularly beneficial when the HVR Agent has access to ample local storage, allowing it to handle large data volumes without relying on networked storage solutions.

This option is equivalent to the location property Intermediate_Directory_Is_Local.