Unable to read oracle data

[ArndW]

While I am not sure what issue you are having, did you mean 'partitioning' instead of 'portioning' in the error message?

[surajkumar]

error is

TDOR 000192

'Could not determine the oracle version for partitioning'

[surajkumar]

error is

TDOR 000192

'Could not determine the oracle version for partitioning'

[ArndW]

Did you ensure that all of the access grants were given:

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;

And then ensuring that your user has that role?

[surajkumar]

we have a oracle schema called NSO_REF

The following select privileges are given by dba

GRANT SELECT on sys.dba_extents to NSO_REF;
GRANT SELECT on sys.dba_data_files to NSO_REF;
GRANT SELECT on sys.dba_tab_partitions to NSO_REF;
GRANT SELECT on sys.dba_tab_subpartitions to NSO_REF;
GRANT SELECT on sys.dba_objects to NSO_REF;
GRANT SELECT on sys.all_part_indexes to NSO_REF;
GRANT SELECT on sys.all_part_tables to NSO_REF;
GRANT SELECT on sys.all_indexes to NSO_REF;

[ArndW]

The grants are given to a user (or role). Try logging into Oracle using your favorite tool (sql developer, TOAD, or the like) and the user that you are using from DataStage. Then do a 'SELECT COUNT(*) FROM sys.dba_tab_partitions;'. What happens?

[surajkumar]

my schema name and user name is same that is NSO_REF

the grants are given to user NSO_REF only

[ArndW]

Did the SELECT work?

[surajkumar]

As I am not a premium member I am unable to read your select query given in previous massage
Could you please mention query alone in you reply?

[ArndW]

I've marked the previous posts as non-premium, just this once since the information is available elsewhere.

[surajkumar]

Thanks lot

i got count is 259

[ArndW]

Very strange, I would have bet that you would have gotten an error message. Could you do the same select on:

DBA_EXTENTS
DBA_DATA_FILES
DBA_TAB_PARTITONS
DBA_TAB_SUBPARTITONS
DBA_OBJECTS
ALL_PART_INDEXES
ALL_PART_TABLES
ALL_INDEXES


This will ensure that all necessary grants are actually working. Also, is Oracle Parallel Server installed, if so, then add "Select count(*) from SYS.GV_$INSTANCE;"

[surajkumar]

Please find output

SELECT count(*) from sys.dba_extents;
SELECT count(*) from sys.dba_data_files;
SELECT count(*) from sys.dba_tab_partitions ;
SELECT count(*) from sys.dba_tab_subpartitions;
SELECT count(*) from sys.dba_objects;
SELECT count(*) from sys.all_part_indexes;
SELECT count(*) from sys.all_part_tables;
SELECT count(*) from sys.all_indexes;
Select count(*) from SYS.GV_$INSTANCE;
-----------------------------------------------------------------------------------------------------------------------------------------
COUNT(*)
----------------------
20685
1 rows selected
COUNT(*)
----------------------
28
1 rows selected
COUNT(*)
----------------------
259
1 rows selected
COUNT(*)
----------------------
0
1 rows selected
COUNT(*)
----------------------
84090
1 rows selected
COUNT(*)
----------------------
0
1 rows selected
COUNT(*)
----------------------
0
1 rows selected
COUNT(*)
----------------------
84
1 rows selected
COUNT(*)
----------------------
1
1 rows selected

I did not get any error

[chulett]

What exact flavor/version of Oracle is this? What client version are you using to connect to it?

[ArndW]

A very odd problem indeed - are you 100% certain that your job uses the Oracle userid 'NSO_REF' - if passed as a parameter then check the runtime values in the director log.
Everything else looks perfectly OK. Perhaps you could try to create your own dummy table and see if you can connect to that one; but I'm just grasping at straws.