sys.dba_extent

[travissolt]

Ok I have tried to follow the postings on this issue but I am perplexed as I am not a backend user. I am unsure if all the below steps need to take place on my user ID or the datastage box as a whole.

I can pull my table over using the orchestrate import tool (so no issues with getting the metadata). If I try and use the table within a parallel job it will not allow me to view data (get dba_extent error). I can use the table within a sever job.

I found the below on the postings and in the help guide. I sent it to the DBA and asked for him to establish the role as described. The DBA granted the role to my user ID in the database I am trying to access versus my personal datastage ID (still got an error).

Do the selects need to be granted to both ID's?

Also as far as the user defined stuff ($ORACLE...) is that for datastage or my user account. Does anyone have an example in case I need to send it to the DBA.

Thanks




You need to be running Oracle 8 or better, Enterprise Edition in order to use the Oracle Enterprise stage.
You must also do the following:
Create the user defined environment variable ORACLE_HOME and set this to the $ORACLE_HOME path (e.g., /disk3/oracle9i)
Create the user defined environment variable ORACLE_SID and set this to the correct service name (e.g., ODBCSOL).
Add ORACLE_HOME/bin to your PATH and ORACLE_HOME/lib to your LIBPATH, LD_LIBRARY_PATH, or SHLIB_PATH. (APT_ORCHHOME/bin must appear before ORACLE_HOME/bin in your PATH.)
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.
Have SELECT privilege on:

DBA_EXTENTS
DBA_DATA_FILES
DBA_TAB_PARTITONS
DBA_OBJECTS
ALL_PART_INDEXES
ALL_PART_TABLES
ALL_INDEXES
SYS.GV_$INSTANCE (Only if Oracle Parallel Server is used)
We suggest that you 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_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 DataStage jobs, as follows:
GRANT DSXE to <oracle userid>;

[lstsaur]

Your datastage ID needs to be granted with the following:
SELECT on sys.dba_extents to DSXE;
SELECT on sys.dba_data_files to DSXE;
SELECT on sys.dba_tab_partitions to DSXE;
SELECT on sys.dba_objects to DSXE;
SELECT on sys.all_part_indexes to DSXE;
SELECT on sys.all_part_tables to DSXE;
SELECT on sys.all_indexes to DSXE.

[lstsaur]

Correction.
Your datastage ID need to be granted with the following:
SELECT on sys.dba_extents
SELECT on sys.dba_data_files
SELECT on sys.dba_tab_partitions
SELECT on sys.dba_objects
SELECT on sys.all_part_indexes
SELECT on sys.all_part_tables
SELECT on sys.all_indexes

[ray.wurlod]

It's perfectly valid if the grants are made through a role.