select privileges

[satya99]

Hi,

when i am using a oracle enterprise stage i am getting the following error:

"Access to sys.dba_extents required but not available. please see the your dba for select privileges"

how to resolve this issue.

thanks in advance

[DSguru2B]

As it says, see your dba. The enterprise stage needs more privileges than a regular OCI stage. Read your Oracle Enterprise Stage pdf file.

[ray.wurlod]

Or Search this forum. You are not the first to have encountered this. There are other SYS tables on which you require SELECT privilege.

[keshav0307]

before loading the oracle enterprise stage , datastage check if the table/view actually exist or not, thats the reason the database user must have select priviledge on some data dictionay tables

[johnthomas]

try disabling all the constraints on the target tables and select diasable constraints to false in the enterprise stage

[DSguru2B]

No constraint disabling is needed/advised. These constraints are there for a reason. Instead of a workaround, lets stick to what the guide says.

[johnthomas]

Try to get the select privleages as advised by dsguru2b . But in some situations we need to disable the constraints and enable the constraints later. You have to decide which is one is the best based on your specific
problem .

[sajarman]

Hi,

You can ask the DBA to create a role as below and grant that role to the user ids that use DataStage jobs.

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 user id>;

Cheers,
Sajarman.