Page 1 of 1
Oracle stage-errors when using Truncate and Load
Posted: Thu May 31, 2007 12:23 am
by ssunda6
Hi,
My requirement is to truncate a table and then load data.
I tried using Write Mode=Truncate and Write Method=Load. It gave the following error and the job got aborted.
Indexes on table 'xxx' preclude direct parallel loading unless an index option is included. Add an index option or drop indexes and rerun step.
So, I added an index option with Index Mode=Rebuild , and it gave the following error:
Oracle call failed; sqlcode = -1031; message: ORA-01031: insufficient privileges
ExecuteImmediate failed for: alter index <table name> rebuild parallel nologging compute statistics.
Index 'PK_xxx' on table 'xxx' has NOT been rebuilt
Index: 'PK_xxx' ; status: UNUSABLE.
This time table got truncated and rows also got inserted but the job aborted with the above error.
I am able to successfully truncate and load on another table ( which has a primary key) without using "index" option. For both the tables, im using the same UID/pwd.
Any help would be very useful.
Thanx,
ssunda.
Re: Oracle stage-errors when using Truncate and Load
Posted: Thu May 31, 2007 6:07 am
by John Smith
ssunda6 wrote:Hi,
My requirement is to truncate a table and then load data.
I tried using Write Mode=Truncate and Write Method=Load. It gave the following error and the job got aborted.
Indexes on table 'xxx' preclude direct parallel loading unless an index option is included. Add an index option or drop indexes and rerun step.
So, I added an index option with Index Mode=Rebuild , and it gave the following error:
Oracle call failed; sqlcode = -1031; message: ORA-01031: insufficient privileges
ExecuteImmediate failed for: alter index <table name> rebuild parallel nologging compute statistics.
Index 'PK_xxx' on table 'xxx' has NOT been rebuilt
Index: 'PK_xxx' ; status: UNUSABLE.
This time table got truncated and rows also got inserted but the job aborted with the above error.
I am able to successfully truncate and load on another table ( which has a primary key) without using "index" option. For both the tables, im using the same UID/pwd.
Any help would be very useful.
Thanx,
ssunda.
the error ORA-01031 seems to suggest that you do not have the appropriate privileges to perform the alter index in your Oracle database.
HAve you checked your permissions ?
Posted: Tue Jun 05, 2007 5:22 am
by ssunda6
Hi,
Yes John, You are right.I have checked for privileges and the user i am using is not having privilege to alter the index.
Is there any way to skip index maintanance so that I can use the same user ID?
Posted: Tue Jun 05, 2007 10:57 am
by csrazdan
ssunda6 wrote:Hi,
Yes John, You are right.I have checked for privileges and the user i am using is not having privilege to alter the index.
Is there any way to skip index maintanance so that I can use the same user ID?
Yes you can. Following is the procedure:
1. Add a parameter APT_ORACLE_LOAD_OPTIONS to your job.
2. Enter value for this parameter as:
Code: Select all
OPTION(SKIP_UNUSABLE_INDEXES=TRUE,SKIP_INDEX_MAINTENANCE=TRUE)
3. Execute your job.
This will make you job to load data and finish successfully, however the table will be in unusable state. You will have to re-build indexes before doing any further oprations on this table.
Hope it helps.....
Posted: Thu Jun 07, 2007 2:35 am
by ssunda6
Hi csrazdan,
Thank you for the reply.
When I did not add the parameter APT_ORACLE_LAOD_OPTIONS , by default it was taking SKIP_INDEX_MAINTENANCE=TRUE.
But the problem of rebuilding indexes again occurs and I have to do it using another userID, which I am not supposed to.
Thanx,
ssunda.
Posted: Mon Jul 02, 2007 6:15 am
by ssunda6
...
Posted: Mon Jul 02, 2007 6:19 am
by ssunda6
...
Posted: Mon Jul 02, 2007 6:28 am
by ssunda6
Hi,
Providing OPTIONS(DIRECT=FALSE, PARALLEL=TRUE) value to $APT_ORACLE_LOAD_OPTIONS,
the job is working fine without throwing any errors about index.
(I have removed index option in the oracle stage)
Does the options DIRECT and PARALLEL does anything about index also. Is index rebuilt after loading or is index maintanence dropped?
Explanation on the above would be helpful.
Regards,
ssunda.
Posted: Mon Jul 02, 2007 6:30 am
by chulett
Talk to your DBA, they love to explain stuff like that.
