Oracle stage-errors when using Truncate and Load
Moderators: chulett, rschirm, roy
Oracle stage-errors when using Truncate and Load
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.
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.
-
- Charter Member
- Posts: 193
- Joined: Tue Sep 05, 2006 8:01 pm
- Location: Australia
Re: Oracle stage-errors when using Truncate and Load
the error ORA-01031 seems to suggest that you do not have the appropriate privileges to perform the alter index in your Oracle database.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.
HAve you checked your permissions ?
Yes you can. Following is the procedure: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?
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)
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.....
Assume everything I say or do is positive
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.
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.