All Products
Search
Document Center

DataWorks:Oracle data source

Last Updated:Oct 08, 2026

The Oracle data source lets you read data from and write data to Oracle. You can configure synchronization tasks in wizard or script mode. This topic describes Oracle synchronization capabilities supported by DataWorks.

Supported versions

Batch reads and writes

Version

Batch read (Oracle Reader)

Batch write (Oracle Writer)

Oracle 11.2 or 11gR2

Supported

Supported

Oracle 12.1 or 12cR1

Supported

Supported

Oracle 12.2 or 12cR2

Supported (new-version features are unsupported)

Supported (new-version features are unsupported)

Oracle 18.3

Supported (new-version features are unsupported)

Supported (new-version features are unsupported)

Oracle 19.x

Supported (new-version features are unsupported)

Supported (new-version features are unsupported)

Oracle 21.1

Supported (new-version features are unsupported)

Supported (new-version features are unsupported)

Important

Oracle Writer uses the ojdbc6-12.1.1.jar driver.

Real-time reads

  • Supported:

    11g R2 and later, 12c non cdb, 18c non cdb, 19c non cdb, or 19c cdb databases.

  • Unsupported:

    12c cbd and 18c cbd databases.

  • When Oracle is the source of a full-database real-time task, enable archive logging. For details, see Prepare the Oracle environment.

Note

A container database (CDB) is an Oracle 12c and later feature that contains multiple pluggable databases (PDBs).

Limits

  • NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET for the Oracle instance must be one of AL32UTF8, AL16UTF16, ZHS16GBK, or UTF8.

  • An Oracle instance supports at most 500 GB of incremental data synchronized in real time per day.

  • DataWorks real-time reads from an Oracle physical standby only. It reads archived logs, so lag is greater than when reading the primary and depends on how fast archive logs are generated.

  • Real-time synchronization does not support views; batch synchronization supports reading views.

  • DataWorks real-time Oracle reads use Oracle LogMiner. Enable supplemental and archive logging on the primary for either primary or standby reads.

  • Index-organized tables are not supported.

  • Tables whose columns are all LOB types (BLOB, CLOB, or NCLOB) are not supported.

  • Chinese characters in Oracle table or column names are not supported.

  • Oracle table names longer than 30 bytes are not supported.

Supported field types

Field type

Batch read (Oracle Reader)

Batch write (Oracle Writer)

Real-time reads

NUMBER

Supported

Supported

Supported

BINARY FLOAT

Supported

Supported

Supported

BINARY DOUBLE

Supported

Supported

Supported

CHAR

Supported

Supported

Supported

NCHAR

Supported

Supported

Supported

VARCHAR2

Supported

Supported

Supported

NVARCHAR2

Supported

Supported

Supported

DATE

Supported

Supported

Supported

TIMESTAMP

Supported

Supported

Supported

TIMESTAMP WITH TIME ZONE

Supported

Supported

Unsupported

TIMESTAMP WITH LOCAL TIME ZONE

Supported

Supported

Unsupported

CLOB

Supported

Supported

Supported

BLOB

Supported

Supported

Supported

RAW

Supported

Supported

Supported

ROWID

Unsupported

Unsupported

Supported

UROWID

Unsupported

Unsupported

Supported

FLOAT

Supported

Supported

Supported

INTERVAL DAY TO SECOND

Unsupported

Unsupported

Supported

INTERVAL YEAR TO MONTH

Unsupported

Unsupported

Supported

BFILE

Unsupported

Unsupported

Unsupported

LONG

Unsupported

Unsupported

Unsupported

LONG RAW

Unsupported

Unsupported

Unsupported

NCLOB

Supported

Supported

Unsupported

STRUCT

Supported

Supported

Unsupported

User-Defined Types

Unsupported

Unsupported

Unsupported

AnyType

Unsupported

Unsupported

Unsupported

AnyData

Unsupported

Unsupported

Unsupported

AnyDataSet

Unsupported

Unsupported

Unsupported

XmlType

Unsupported

Unsupported

Unsupported

Spatial Types

Unsupported

Unsupported

Unsupported

Media Types

Unsupported

Unsupported

Unsupported

The following table lists Oracle type mappings for Oracle Reader.

Type category

Oracle data type

Integer

NUMBER, RAWID, INTEGER, INT, and SMALLINT

Floating-point

NUMERIC, DECIMAL, FLOAT, DOUBLE PRECISIOON, and REAL

String

LONG, CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2, CLOB, NCLOB, CHARACTER, CHARACTER VARYING, CHAR VARYING, NATIONAL CHARACTER, NATIONAL CHAR, NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, and NCHAR VARYING

Date and time

TIMESTAMP and DATE

Boolean

BIT and BOOL

Binary

BLOB, BFILE, RAW, and LONG RAW

Before synchronization: Prepare the Oracle environment

Before synchronizing Oracle data in DataWorks, prepare the Oracle environment so that tasks can be configured and run normally.

Check the Oracle database version

Supported Oracle versions vary by synchronization scenario. Check that your version is supported.

  1. Use either of these statements to check the version:

    • Statement 1:

      select * from v$version;
    • Statement 2:

      select version from v$instance;
  2. If the Oracle version is 12c, or 18c, use this statement to check whether it is a cdb database. Data Integration real-time synchronization does not currently support 12c and 18c versions of cdb Oracle databases.

    select name,cdb,open_mode,con_id from v$database;

Create an account and grant permissions

Plan a database login account for later operations. It needs the relevant Oracle permissions.

  1. Create an account. For the procedure, see Create an Oracle account.

  2. Grant permissions.

    Use these commands to grant permissions. Replace 'sync_user' with the account created above.

    grant create session to 'sync_user';  //Allow the synchronization account to log on to the database.
    grant connect to 'sync_user';  //Allow the synchronization account to connect to the database.
    grant select on nls_database_parameters to 'sync_user';  //Allow the synchronization account to query the nls_database_parameters system settings.
    grant select on all_users to 'sync_user';  //Allow the synchronization account to query all database users.
    grant select on all_objects to 'sync_user';  //Allow the synchronization account to query all database objects.
    grant select on DBA_MVIEWS to 'sync_user';  //Allow the synchronization account to view materialized views.
    grant select on DBA_MVIEW_LOGS to 'sync_user';  //Allow the synchronization account to view materialized view logs.
    grant select on DBA_CONSTRAINTS to 'sync_user';  //Allow the synchronization account to view constraints on all database tables.
    grant select on DBA_CONS_COLUMNS to 'sync_user';  //Allow the synchronization account to view columns in specified constraints on all database tables.
    grant select on all_tab_cols to 'sync_user';  //Allow the synchronization account to view column information for tables, views, and clusters.
    grant select on sys.obj$ to 'sync_user';  //Allow the synchronization account to view objects. sys.obj$ is the Oracle dictionary base table containing all objects.
    grant select on SYS.COL$ to 'sync_user';  //Allow the synchronization account to view column definitions. SYS.COL$ stores table column definitions.
    grant select on sys.USER$ to 'sync_user';  //Allow the synchronization account to view system tables. sys.USER$ is the default service for user sessions.
    grant select on sys.cdef$ to 'sync_user';  //Allow the synchronization account to view system tables.
    grant select on sys.con$ to 'sync_user';  //Allow the synchronization account to view constraints. sys.con$ records Oracle constraint information.
    grant select on all_indexes to 'sync_user';  //Allow the synchronization account to view all indexes.
    grant select on v_$database to 'sync_user';  //Allow the synchronization account to view v_$database.
    grant select on V_$ARCHIVE_DEST to 'sync_user';  //Allow the synchronization account to view V_$ARCHIVE_DEST.
    grant select on v_$log to 'sync_user';  //Allow the synchronization account to view v_$log, which shows log file information in the control file.
    grant select on v_$logfile to 'sync_user';  //Allow the synchronization account to view v_$logfile, which contains redo log file information.
    grant select on v_$archived_log to 'sync_user';  //Allow the synchronization account to view v$archived_log, which contains archive log information.
    grant select on V_$LOGMNR_CONTENTS to 'sync_user';  //Allow the synchronization account to view V_$LOGMNR_CONTENTS.
    grant select on DUAL to 'sync_user';   //Allow the synchronization account to view DUAL, a one-row virtual table used in SELECT syntax.
    grant select on v_$parameter to 'sync_user';  //Allow the synchronization account to view v_$parameter, a dynamic dictionary view of database parameter values.
    grant select any transaction to 'sync_user';  //Allow the synchronization account to view any transaction.
    grant execute on SYS.DBMS_LOGMNR to 'sync_user';  //Allow the synchronization account to use LogMiner for transaction analysis and data recovery.
    grant alter session to 'sync_user';  //Allow the synchronization account to alter the database session.
    grant select on dba_objects to 'sync_user';  //Allow the synchronization account to view all database objects.
    grant select on v_$standby_log to 'sync_user';  //Allow the synchronization account to view v_$standby_log, which contains standby archive log information.
    grant select on v_$ARCHIVE_GAP to 'sync_user';  //Allow the synchronization account to query missing archive logs.
    grant select on sys.ICOL$ to 'sync_user';  //Allow the synchronization account to view objects. sys.obj$ is the Oracle dictionary base table containing all objects.
    grant select on V_$THREAD to 'sync_user';  //Allow the synchronization account to view instance thread information. V_$THREAD is an Oracle dynamic performance view of RAC instance thread status used to identify instance state and log sources for incremental parsing.

    For batch synchronization of full data, also grant the account permission to query all tables:

    grant select any table to 'sync_user';

    Oracle 12c and later require this command to let the account use LogMiner. Earlier versions provide built-in LogMiner and do not require it.

    grant LOGMINING TO 'sync_user';

Enable archive and supplemental logging and switch redo log files

  1. Enable archive logging:

    shutdown immediate;
    startup mount;
    alter database archivelog;
    alter database open;
  2. Enable supplemental logging.

    Choose supplemental logging as needed. Example SQL:

    //Enable supplemental logging to capture complete changes for real-time synchronization. For database-level logging, run:
    alter database add supplemental log data(primary key) columns; //Enable supplemental logging for primary key columns in the database.
    alter database add supplemental log data(unique) columns; //Enable supplemental logging for unique-index columns in the database.
    
    //You can instead enable it at the table level:
    alter schema.table add supplemental log data(primary key) columns; //Enable supplemental logging for primary key columns in the specified table.
    alter schema.table add supplemental log data(unique) columns; //Enable supplemental logging for unique-index columns in the specified table.
  3. Switch redo log files.

    After enabling supplemental logging, run the following command multiple times (five times is generally recommended) to switch redo log files.

    alter system switch logfile;
    Note

    Switching multiple times ensures that the current log file can roll over after filling up, preserving operation records for later recovery.

Check database character encoding

Run the following command in the database to check its character encoding:

select * from v$nls_parameters where PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
  • v$nls_parameters stores database parameter settings.

  • NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET are the database and national character sets, indicating storage formats for two categories of Oracle character data.

Note

Data Integration currently supports UTF8, AL32UTF8, AL16UTF16, and ZHS16GBK when synchronizing data. Change unsupported character encodings before synchronization.

Check table data types

Use a SQL SELECT statement to query the types of table columns. To inspect 'tablename', run:

select COLUMN_NAME,DATA_TYPE from all_tab_columns where TABLE_NAME='tablename'; 
  • COLUMN_NAME: Table column name.

  • DATA_TYPE: Data type of the column.

  • all_tab_columns: View containing information about all table columns.

  • TABLE_NAME: Name of the target table. Replace 'tablename' with the actual table name in the statement.

You can also run select * from 'tablename'; to query all data and obtain types for the target table.

Add a data source

Before you develop a synchronization task in DataWorks, you must add the required data source to DataWorks by following the instructions in Data source configuration. You can view parameter descriptions in the DataWorks console to understand the meanings of the parameters when you add a data source.

Develop synchronization tasks: Oracle workflow

For information about the entry point for and the procedure of configuring a synchronization task, see the following configuration guides.

Configure a single-table batch synchronization task

Configure a single-table real-time synchronization task

For the procedure, see Configure a real-time synchronization task (legacy).

Configure full-database batch, full and incremental real-time, and sharded-table real-time synchronization

For the procedure, see Configure real-time full-database synchronization tasks.

FAQs

  • Real-time synchronization tasks for Oracle, PolarDB, and MySQL repeatedly report errors

  • Data recovery on a primary-standby setup

    In a primary-standby disaster recovery setup, after the primary fails over to a standby, the standby continuously recovers data from the primary through binlogs. Replication can lag, especially because of network delay. Therefore, standby data can differ significantly from the primary and may not be a complete snapshot at the current time.

  • Consistency constraints

    Oracle is an RDBMS that can provide strongly consistent query interfaces. For example, if another writer updates the database after a synchronization task starts, Oracle Reader does not capture the new data because of the database snapshot behavior.

    This consistency behavior applies to a single-threaded Oracle Reader. Concurrent extraction based on your configuration cannot strictly guarantee consistency.

    When Oracle Reader splits data by splitPk, it starts multiple concurrent tasks. They do not share a read transaction and start at different times, so the resulting data is not a complete, consistent snapshot.

    A consistent snapshot across multiple threads is not currently technically achievable. The following engineering options involve trade-offs; choose one based on your needs.

    • Use single-thread synchronization without data splitting. It is slower but provides good consistency.

    • Stop other writers so the data remains static, for example by locking the table or stopping standby replication. This may affect online services.

  • Database encoding

    Oracle Reader uses JDBC to extract data. JDBC handles and converts different encodings internally, so you need not specify an encoding.

  • Incremental synchronization

    Oracle Reader extracts data with JDBC SELECT statements. You can use SELECT…WHERE… for incremental extraction:

    • When an online application writes to the database, it sets modify to the change timestamp for inserts, updates, and logical deletes. Oracle Reader can use a WHERE condition after the prior synchronization timestamp.

    • For append-only records, use a WHERE condition based on the maximum auto-increment ID from the previous stage.

    If no business field distinguishes inserts and updates, Oracle Reader cannot synchronize incrementally and must synchronize all data.

  • SQL security

    Oracle Reader provides querySql so that you can write a SELECT extraction statement. Oracle Reader does not validate querySql for security.

Appendix: Sample scripts and parameters

Configure a batch synchronization task by using the code editor

If you want to configure a batch synchronization task by using the code editor, you must configure the related parameters in the script based on the unified script format requirements. For more information, see Script mode configuration. The following information describes the parameters that you must configure for data sources when you configure a batch synchronization task by using the code editor.

Reader sample script

{
    "type": "job",
    "version": "2.0",
    "steps": [
        {
            "stepType": "oracle",
            "parameter": {
                "selectedDatabase": "AUTOTEST",
                "indexes": [],
                "datasource": "oracle_test",
                "envType": 0,
                "useSpecialSecret": true,
                "column": [
                    "id"
                ],
                "where": "",
                "splitPk": "id",
                "encoding": "UTF-8",
                "table": "AUTOTEST.table01"
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "odps",
            "parameter": {
            },
            "name": "Writer",
            "category": "writer"
        },
        {
            "name": "Processor",
            "stepType": null,
            "category": "processor",
            "copies": 1,
            "parameter": {
                "nodes": [],
                "edges": [],
                "groups": [],
                "version": "2.0"
            }
        }
    ],
    "setting": {
        "executeMode": null,
        "errorLimit": {
            "record": ""
        },
        "speed": {
            "concurrent": 2,
            "throttle": false
        }
    },
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    }
}

Reader script parameters

Parameter

Description

Required

Default value

datasource

Data source name. In script mode, you can add a data source. The value must match the name of the added data source.

Yes

None

selectedDatabase

Schema of the database to synchronize.

Yes

None

table

Name of the table to synchronize, in the form schema.tableName.

Note

For example, if selectedDatabase is AUTOTEST and the table name is table01, configure table as AUTOTEST.table01.

Yes

None

column

Columns to synchronize from the configured table, described as a JSON array. By default, all columns are selected, for example, ["*"].

  • You can export only selected columns.

  • You can export columns in an order different from the table schema.

  • You can configure constants in JSON format.

    ["id", "1", "'mingya.wmy'", "null", "to_char(a + 1)", "2.3" , "true"]
    • id is a regular column name.

    • 1 is an integer constant.

    • 'mingya.wmy' is a string constant. Include the pair of single quotation marks.

    • null is a null pointer.

    • to_char(a + 1) is an expression.

    • 2.3 is a floating-point number.

    • true is a Boolean value.

  • columnExplicitly specify a nonempty list.

Yes

None

splitFactor

A split factor that sets the number of data shards. With concurrent tasks, data is split into concurrency × splitFactor shards. For example, concurrency = 5 and splitFactor = 5 produce 5 × 5 = 25 shards across five concurrent threads.

Note

Recommended range: 1 to 100. An excessively large value can cause an out-of-memory error.

No

5

splitMode

Split mode options:

  • averageInterval: Average sampling. Use splitPK to find the maximum and minimum values, then split evenly into the configured number of shards.

  • randomSampling: Random sampling. Choose random values from all data as split points.

Note

Use splitMode together with splitPk.

  • If splitPk is numeric, set splitMode to averageInterval.

  • If splitPk is a string, set splitMode to randomSampling.

No

randomSampling

splitPk

When Oracle Reader extracts data, specifying splitPk means you want to split it by the field represented by splitPk. Synchronization starts concurrent tasks to improve efficiency.

  • We recommend the table primary key for splitPk because primary key values are usually evenly distributed, reducing data hotspots.

  • splitPkOnly numeric or string values are supported. Use splitMode with splitPk.

    • If splitPk is numeric, set splitMode to averageInterval.

    • If splitPk is a string, set splitMode to randomSampling.

  • If splitPk is omitted, Oracle Reader does not split the table and synchronizes all data through one channel.

Note

ROWID cannot be used as splitPk for a view.

No

None

where

A filter condition. Oracle Reader assembles SQL based on the specified column, table and where condition and extracts data with that SQL. For testing, set WHERE to row_number().

  • A WHERE condition can enable incremental synchronization.

  • If WHERE is absent or empty, all table data is synchronized.

No

None

querySql (advanced mode only; wizard mode unsupported)

When a WHERE condition cannot describe your filter, use querySql to write custom SQL. Data Integration then ignores table and column and other settings, filtering data directly with querySql. For example, to join multiple tables, use select a,b from table_a join table_b on table_a.id = table_b.id. When you configure querySql, Oracle Reader ignores the table, column and where condition.

No

None

fetchSize

Number of records retrieved in each batch by the plug-in and database server. This controls network interactions and can substantially improve extraction performance.

Note

An excessively large fetchSize (> 2,048) can cause an out-of-memory (OOM) error during synchronization.

No

1,024

Writer sample script

{
    "type":"job",
    "version":"2.0",//Version number.
    "steps":[
        { 
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"oracle",//Plug-in name.
            "parameter":{
                "postSql":[],//SQL statements to run after synchronization.
                "datasource":"",
                "session":[],//Database connection session parameters.
                "column":[//Fields.
                    "id",
                    "name"
                ],
                "encoding":"UTF-8",//Encoding format.
                "batchSize":1024,//Number of records submitted in one batch.
                "table":"",//Table name.
                "preSql":[]//SQL statements to run before synchronization.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"//Number of error records.
        },
        "speed":{
            "throttle":true,//When throttle is false, mbps is ignored and throughput is unlimited; when true, throughput is limited.
            "concurrent":1, //Job concurrency.
            "mbps":"12"//Rate limit. Here, 1 mbps = 1 MB/s.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Writer script parameters

Parameter

Description

Required

Default value

datasource

Data source name. In script mode, you can add a data source. The value must match the name of the added data source.

Yes

None

table

Destination table name. If the table schema differs from the configured username, use schema.table as the table format.

Yes

None

writeMode

Choose the import mode. Only INSERT INTO is supported. On a primary key or unique index conflict, the row is not written and is reported as dirty data.

No

insert into

column

Destination table fields to write, separated by commas. Example:"column": ["id","name","age"]. To write all columns in order, use *, for example, "column":["*"].

Yes

None

preSql

SQL statements to run before the synchronization task. Wizard mode allows one statement; script mode supports multiple statements, such as clearing old data.

No

None

postSql

SQL statements to run after the synchronization task. Wizard mode allows one statement; script mode supports multiple statements, such as adding a timestamp.

No

None

batchSize

Number of records submitted in one batch. This greatly reduces network interactions between Data Integration and Oracle and improves throughput. An excessively large value can cause an out-of-memory (OOM) error during synchronization.

No

1,024