Unusable Index Issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 84
- Joined: Sat Jul 19, 2008 8:50 am
Unusable Index Issue
Hi
I have a job that loads data into a table [Source(dataset)---->target(oracle stage)].
The data loads correctly into the table but the index becomes corrupt and has a status of 'Unusable'. I can correct this problem by manually rebuilding the indices through a sql client but shouldn't have to.
The target write method is Load and write mode is Append. I have tried it with the Index Mode = Rebuild as well as Maintainence but still have the same problem.
Is this an issue with DS or could it be an Oracle permission issue? DS is using the same credentials that is used to manually rebuild the indices through a sql client.
Thanks
k-v
I have a job that loads data into a table [Source(dataset)---->target(oracle stage)].
The data loads correctly into the table but the index becomes corrupt and has a status of 'Unusable'. I can correct this problem by manually rebuilding the indices through a sql client but shouldn't have to.
The target write method is Load and write mode is Append. I have tried it with the Index Mode = Rebuild as well as Maintainence but still have the same problem.
Is this an issue with DS or could it be an Oracle permission issue? DS is using the same credentials that is used to manually rebuild the indices through a sql client.
Thanks
k-v
-
- Participant
- Posts: 84
- Joined: Sat Jul 19, 2008 8:50 am
Hi
thanks for your replay, please can you tell me where i need to give the Environmental variable APT_ORACLE_LOAD_OPTIONS as (DIRECT=FALSE,PARALLEL=FALSE), do i need to go into adm--> properties-->environmental variable--->Operator Specific---> APT_ORACLE_LOAD_OPTIONS(right now the Value box is empty) and do i need to set the values as DIRECT=FALSE,PARALLEL=FALSE is that the wright thing to do. please can u help me in issue.
Thanks
k
thanks for your replay, please can you tell me where i need to give the Environmental variable APT_ORACLE_LOAD_OPTIONS as (DIRECT=FALSE,PARALLEL=FALSE), do i need to go into adm--> properties-->environmental variable--->Operator Specific---> APT_ORACLE_LOAD_OPTIONS(right now the Value box is empty) and do i need to set the values as DIRECT=FALSE,PARALLEL=FALSE is that the wright thing to do. please can u help me in issue.
Thanks
k
-
- Participant
- Posts: 84
- Joined: Sat Jul 19, 2008 8:50 am
Hi
Thanks for your replay i used the following steps to get the Indexs in my job
In Job properties-->environmental variable--->Operator Specific---> APT_ORACLE_LOAD_OPTIONS(right now the Value box is empty) and set the values as (DIRECT=FALSE,PARALLEL=FALSE)
But the job aborted and i got the following error in log:
Ora_Out,0: The call to sqlldr failed; the return code = 256;
please see the loader logfile: /opt/IBM/InformationServer/Server/Scratch/ora.14708.478299.0.log for details.
Ora_Table_Out,0: Log file contents:
Ora_Table_Out,0: The runLocally() of the operator failed.
Following is the log for details.
SQL*Loader: Release 10.2.0.4.0 - Production on Tue Nov 17 16:48:36 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-350: Syntax error at line 1.
Expecting keyword LOAD, found "DIRECT".
DIRECT=FALSE,PARALLEL=FALSE
Thanks
k
Thanks for your replay i used the following steps to get the Indexs in my job
In Job properties-->environmental variable--->Operator Specific---> APT_ORACLE_LOAD_OPTIONS(right now the Value box is empty) and set the values as (DIRECT=FALSE,PARALLEL=FALSE)
But the job aborted and i got the following error in log:
Ora_Out,0: The call to sqlldr failed; the return code = 256;
please see the loader logfile: /opt/IBM/InformationServer/Server/Scratch/ora.14708.478299.0.log for details.
Ora_Table_Out,0: Log file contents:
Ora_Table_Out,0: The runLocally() of the operator failed.
Following is the log for details.
SQL*Loader: Release 10.2.0.4.0 - Production on Tue Nov 17 16:48:36 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-350: Syntax error at line 1.
Expecting keyword LOAD, found "DIRECT".
DIRECT=FALSE,PARALLEL=FALSE
Thanks
k
-
- Participant
- Posts: 84
- Joined: Sat Jul 19, 2008 8:50 am
Thanks
Thanks for your help.
i solved this issue using Job param as APT_ORACLE_LOAD_OPTIONS = OPTIONS(DIRECT=FALSE,PARALLEL=FALSE)
i solved this issue using Job param as APT_ORACLE_LOAD_OPTIONS = OPTIONS(DIRECT=FALSE,PARALLEL=FALSE)
-
- Participant
- Posts: 86
- Joined: Mon Nov 27, 2006 3:42 am
- Location: Wilmington
-
- Participant
- Posts: 86
- Joined: Mon Nov 27, 2006 3:42 am
- Location: Wilmington
-
- Participant
- Posts: 86
- Joined: Mon Nov 27, 2006 3:42 am
- Location: Wilmington
I was trying to load the huge data from my source system into the Oracle table, It got aborted due to the Table index problem "Unusable" status. So they have enabled the below option
"OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE=TRUE,SKIP_UNUSABLE_INDEXES=TRUE)"
Please let me know what is the right option to choose, I need to reduce the PROD run timings?
"OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE=TRUE,SKIP_UNUSABLE_INDEXES=TRUE)"
Please let me know what is the right option to choose, I need to reduce the PROD run timings?