Accessing Oracle databases

Before you can use the Oracle Enterprise stage, you must set values for environment variables and ensure that you have the roles and privileges that are required.

1. Create the user defined environment variable ORACLE_HOME and set this to the $ORACLE_HOME path (for example, /disk3/oracle10g).
2. Add ORACLE_HOME/bin to your PATH and ORACLE_HOME/lib to your LIBPATH, LD_LIBRARY_PATH, or SHLIB_PATH.
3. Have login privileges to Oracle using a valid Oracle user name and corresponding password. These must be recognized by Oracle before you attempt to access it.
4. Have SELECT privilege on:
   • DBA_EXTENTS
   • DBA_DATA_FILES
   • DBA_TAB_PARTITONS
   • DBA_TAB_SUBPARTITONS
   • DBA_OBJECTS
   • ALL_PART_INDEXES
   • ALL_PART_TABLES
   • ALL_INDEXES
   • SYS.GV_$INSTANCE (Only if Oracle Parallel Server is used)
     Note: APT_ORCHHOME/bin must appear before ORACLE_HOME/bin in your PATH.

   You can create a role that has the appropriate SELECT privileges, as follows:

   CREATE ROLE DSXE;
   GRANT SELECT on sys.dba_extents to DSXE;
   GRANT SELECT on sys.dba_data_files to DSXE;
   GRANT SELECT on sys.dba_tab_partitions to DSXE;
   GRANT SELECT on sys.dba_tab_subpartitions to DSXE;
   GRANT SELECT on sys.dba_objects to DSXE;
   GRANT SELECT on sys.all_part_indexes to DSXE;
   GRANT SELECT on sys.all_part_tables to DSXE;
   GRANT SELECT on sys.all_indexes to DSXE;

   Once the role is created, grant it to users who will run the IBM® InfoSphere® DataStage® and QualityStage® jobs, as follows:

   GRANT DSXE to <oracle userid>;