Where to change Database datafile name and path in datastage

shalini11 · Post by **shalini11** » Wed Feb 04, 2009 3:20 am

Hi All,

The DBA has moved the oracle schema attached to my project to a different file system due to some memory issues. The schema name is the same as the old one but the data file name and path is changed. Now when I am trying to load database table from datastage job, I am getting fatal error as below:

SQL*Loader-951: Error calling once/load initialization
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/data1/app/oracle/oradata/smartdc/SMART.dbf'
ORA-27041: unable to open file

The datafile it is pointing to is SMART.dbf, but now the data filename is changed to SMART_NEW.dbf.

I am using Oracle Enterprise stage to connect to database.I want to know where in Datastage can I change the datafile path it is pointing to.

Thnx in adv

ray.wurlod · Post by **ray.wurlod** » Wed Feb 04, 2009 3:36 am

It depends where you've set the pertinent environment variables; it may be in the dsenv script, or you may have specific-to-project environment variables set in the Administrator client (and stored in the DSParams file in the project directory).

Then again, your jobs may set these as job parameters or even as hard-coded values in the Oracle stages involved.

Finally, get your DBA to confirm that all the settings in tnsnames.ora file on the DataStage server machine are set correctly.

shalini11 · Post by **shalini11** » Wed Feb 04, 2009 3:43 am

Thanks for the reply

Datastage and Oracle database are in different server.In the dsenv file, the oracle home path is mentioned which is the oracle client path in the datastage server.In administrator, I am just giving the database name,schema name and password for connection. Nowhere I could find the datafile path.

I am not able to find where is this datafile path specified so that I can change it to the new path.

anugrah86 · Post by **anugrah86** » Wed Feb 04, 2009 4:06 am

Shalini,

Pls contact with the DBAs coz (AFAIK) oracle doesn't allows you to access datafiles directly. You fetch them using tablespaces.

So, DBAs can set the datafile path in tablespace details.

check if below query gives you path for tablespace you are using.

select * from DBA_DATA_FILES

shalini11 · Post by **shalini11** » Wed Feb 04, 2009 4:36 am

I tried the query which u suggested.Actually datafile path is set but the job is not pointing to that path.

The entire filesystem is changed but datastage is pointing to the old filesystem path. Even DBA is confused what to do

ray.wurlod · Post by **ray.wurlod** » Wed Feb 04, 2009 2:46 pm

Then your DBA, may we suggest, needs to get some Oracle support.