Oracle stage-errors when using Truncate and Load

[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.

[John Smith]

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 ?

[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?

[csrazdan]

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.....

[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.

[ssunda6]

...

[ssunda6]

...

[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.

[chulett]

Talk to your DBA, they love to explain stuff like that.