Source and Target Database Setup in Informatica

An Informatica mapping reads data from a source and writes the processed data to a target. Before creating mappings and workflows, configure the required database users, schemas, tables, Oracle connectivity, and Informatica connection objects.

This example uses Oracle as both the source and target database. The steps demonstrate how to create a target schema and a sample target table. Database names, service names, ports, usernames, and privileges must be adjusted for your environment.

Source and Target Database Requirements for Informatica

  • An accessible Oracle database instance
  • Separate source and target schemas when isolation is required
  • A source account with permission to read the required tables or views
  • A target account with permission to insert, update, delete, or create objects as required by the load
  • The Oracle host name, listener port, and service name or SID
  • Compatible Oracle client software on the Informatica machine when the selected connector requires it
  • Network access from the Informatica runtime host to the Oracle listener

Using separate schemas makes it easier to control privileges and distinguish source objects from target objects. For example, a training environment might use INFA_SOURCE for source tables and BATCH10AM for target tables.

Connect to Oracle as an Administrative User

In Oracle, a database user normally owns a schema with the same name. An administrative account can create the target user and grant the privileges required for the Informatica load.

In the Oracle installation used for this example, open SQL*Plus by navigating to Start | Programs | Oracle | Application Development | SQL Plus. On newer installations, SQL*Plus can also be started from a terminal if it is available on the system path.

  • Log on to Oracle with the administrative credentials configured for your environment.
    • Username: SYSTEM
    • Password: use the password assigned during Oracle setup

Do not place production passwords in tutorials, scripts, mappings, or shared connection files. The password shown in this legacy example is only a training value and should be replaced.

</>
Copy
Enter user-name: system
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Create the Oracle Target User for Informatica

The following statement creates the target user named Batch10am. In Oracle, this user becomes the owner of the target schema used in the example.

</>
Copy
SQL> Create user Batch10am identified by Target;
User created.

The original training sequence below grants the DBA role and then verifies that the new account can connect.

</>
Copy
SQL> Grant DBA TO Batch10am;
Grant succeeded.
SQL> show user;
USER is "SYSTEM"

SQL> Conn Batch10am/ Target;
Connected.

SQL> Show user;
USER is "BATCH10AM"

Security note: granting DBA gives the account extensive administrative privileges and is not appropriate for a normal Informatica target connection. It appears above only because it is part of the original lab example. Use least-privilege grants in a real environment.

For a simple schema-owning target account, a database administrator can grant only the privileges needed for the planned operations. The exact privileges and tablespace quota depend on whether Informatica will merely load existing tables or also create and modify database objects.

</>
Copy
GRANT CREATE SESSION TO Batch10am;
GRANT CREATE TABLE TO Batch10am;
ALTER USER Batch10am QUOTA 100M ON USERS;

If a target administrator creates all tables in advance, the Informatica account may not need CREATE TABLE. Instead, grant object-level privileges on the required target tables. Consult the Oracle database administrator before applying grants in a production environment.

Create the T_EMPLOYEE Target Table

Connect as Batch10am and create the sample T_EMPLOYEE table. This table can be imported as a target definition or selected when configuring a database ingestion target.

</>
Copy
SQL> Create table T_employee (Empno number(5),
  2  Ename varchar2(10),
  3  Job varchar2(10),
  4  Sal number(7,2),
  5  Deptno number(2));

SQL> Desc T_employee;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------

 EMPNO                                              NUMBER(5)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(10)
 SAL                                                NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

The DESC command confirms that the table exists and shows its column names and Oracle data types. Verify that these data types are compatible with the corresponding source fields before building the mapping.

Prepare the Oracle Source Schema

The source connection should use an account that can read the required source objects. If the source tables belong to another schema, grant access to the Informatica source account rather than sharing the schema owner’s password.

</>
Copy
GRANT CREATE SESSION TO INFA_SOURCE;
GRANT SELECT ON HR.EMPLOYEES TO INFA_SOURCE;

Replace INFA_SOURCE and HR.EMPLOYEES with the actual account and source object. Additional grants may be required for views, synonyms, stored procedures, or change-data-capture features.

Configure Oracle Source and Target Connections in Informatica

Create two Informatica connection objects even when the source and target are hosted in the same Oracle database. Separate connection objects allow each side to use its own username and privileges.

  1. Open the connection administration area for the Informatica product being used.
  2. Create an Oracle relational connection for the source database.
  3. Enter the source username, password, host name, listener port, and Oracle service name or SID.
  4. Test the source connection and confirm that the required tables or views are visible.
  5. Create a separate Oracle relational connection for the target database.
  6. Enter the target user credentials, such as the Batch10am account used in this example.
  7. Test the target connection and verify access to T_EMPLOYEE.
  8. Assign the source and target connections to the mapping, session, mapping task, or ingestion job as required by the Informatica product.

Menu names differ between Informatica PowerCenter, Informatica Intelligent Cloud Services, and newer cloud data integration or database ingestion interfaces. The connection still needs the same core information: the Oracle endpoint, authentication details, and the runtime environment that will execute the job.

Validate the Source-to-Target Load

After importing or selecting the source and target objects, map compatible source fields to the target columns. Run a small test load before processing the full dataset.

</>
Copy
SELECT COUNT(*) FROM T_EMPLOYEE;
SELECT * FROM T_EMPLOYEE ORDER BY EMPNO;

Review the Informatica session or job log for rejected rows, conversion errors, constraint violations, and database connection failures. A successful connection test confirms connectivity, but it does not prove that the account has every object privilege required during execution.

Troubleshooting Informatica Oracle Connections

  • Oracle listener or connection error: verify the host, port, service name, listener status, DNS resolution, and firewall rules from the Informatica runtime machine.
  • Invalid username or password: check the credentials, account status, password expiry, and whether the connection is using the intended Oracle service.
  • Table is not visible: confirm the object owner and grant SELECT or the required target privileges directly to the connection user.
  • Table creation fails: check CREATE TABLE, the user’s default tablespace, and its tablespace quota.
  • Job connects but cannot load data: verify INSERT, UPDATE, DELETE, sequence, and constraint-related permissions according to the load strategy.
  • Data type conversion fails: compare the source field precision, scale, length, date format, and character encoding with the target column definition.

Frequently Asked Questions About Informatica Database Setup

Can Informatica use the same Oracle database for the source and target?

Yes. The source and target can be in the same Oracle database, but separate schemas and connection objects are generally preferable because they provide clearer ownership and privilege control.

Does an Informatica target user require the Oracle DBA role?

No. A normal target user should receive only the privileges required by the load. Depending on the design, these may include CREATE SESSION and object-level INSERT, UPDATE, DELETE, or SELECT privileges. Schema-owning accounts may require additional privileges and a tablespace quota.

Why can Informatica connect to Oracle but not see a source table?

The connection user may not own the table or may lack permission to read it. Confirm the table’s schema and grant SELECT on the object to the Informatica source user. Also check whether the import or object-selection screen is filtering by schema.

Which Oracle connection properties are required in Informatica?

The required values normally include the Oracle host, listener port, service name or SID, username, password, and runtime environment. Some Informatica connectors also require an Oracle client, wallet, advanced security properties, or a JDBC connection string.

Source and Target Database Setup QA Checklist

  • Confirm that the Informatica runtime host can reach the Oracle listener.
  • Verify the source and target host, port, service name, and schema values.
  • Test source access with the same user configured in the Informatica source connection.
  • Test target access with the same user configured in the Informatica target connection.
  • Confirm that the target user has only the privileges required by the selected load strategy.
  • Check that source and target data types, lengths, precision, and scale are compatible.
  • Run a limited test load and compare source, target, rejected, and committed row counts.
  • Review the Informatica execution log and Oracle errors before scheduling the full load.
  • Remove sample passwords and excessive grants before using the configuration outside a lab.