Hi,
we 're trying to load an oracle table from DS. The oracle table is index-organized and hash-partitioned on the pk .
some ways we tried:
-we normally use an etl-user, that is not owner of the table, but has sufficient rigths. When we try this for this table, with option to truncate and load we get the error:APT_OraWriteSubOperator failed trying to get the tablespace name. This could be because you don't have read access to DBA_EXTENTS or because partition name supplied for the table 'tng.ACCOUNT_BALANCE_SUMM' does not exist.
followed by:Oracle_Enterprise_64: Could not place an operator for each
partition. We checked the dba_extents rights and they are ok.
-when using the owner of the table it gives an error on the index (we tried: without index option, with index option maintenance, and with index option rebuild error is always different)
-when it is changed to upsert it works for both users, but performance is very slow.
I've been thinking about a before job that truncates the table and then a user-defined update only in which we place an insert, since this is much more performant, but I find this a lot of cheap-tricks to do it.
Does anyone have any experience with these type of oracle-tables?
problem writing to oracle stage(partitoned-index organized)
Moderators: chulett, rschirm, roy
How did you declare your index mode in the Oracle write stage?
When loading to this type of partitioned table we have $APT_ORACLE_LOAD_OPTIONS set to "OPTIONS(DIRECT=TRUE,PARALLEL=FALSE)" as per the documentation and to not have problems loading. The documentation is pretty clear on the access rights you do require as a minimum in Oracle - you do need those rights.
When loading to this type of partitioned table we have $APT_ORACLE_LOAD_OPTIONS set to "OPTIONS(DIRECT=TRUE,PARALLEL=FALSE)" as per the documentation and to not have problems loading. The documentation is pretty clear on the access rights you do require as a minimum in Oracle - you do need those rights.
</a>access rigths are ok for all other 100 jobs we have running daily with this user on this DB, also the rigths on the table are ok.
I've tried with all combinations of the index option:
-rebuild :Oracle_Enterprise_64: index option rebuild may not be used on a partitioned index. Table 'tng.ACCOUNT_BALANCE_SUMM' contains one or more partitioned indexes. (no matter what is being put in COMPUTE STATISTICS / NOLOGGING clauses)
-rebuild:Oracle_Enterprise_64: index option maintenance may only be used on a range partitioned table. Table 'tng.ACCOUNT_BALANCE_SUMM' is not range partitioned.
statements are correct it is a hash-partitioned index, but that doesn't help me in which to use.
In my project the $APT_ORACLE_LOAD_OPTIONS is set to "OPTIONS(DIRECT=TRUE,PARALLEL=TRUE)" , but my stage is defined as sequential.
I've tried with all combinations of the index option:
-rebuild :Oracle_Enterprise_64: index option rebuild may not be used on a partitioned index. Table 'tng.ACCOUNT_BALANCE_SUMM' contains one or more partitioned indexes. (no matter what is being put in COMPUTE STATISTICS / NOLOGGING clauses)
-rebuild:Oracle_Enterprise_64: index option maintenance may only be used on a range partitioned table. Table 'tng.ACCOUNT_BALANCE_SUMM' is not range partitioned.
statements are correct it is a hash-partitioned index, but that doesn't help me in which to use.
In my project the $APT_ORACLE_LOAD_OPTIONS is set to "OPTIONS(DIRECT=TRUE,PARALLEL=TRUE)" , but my stage is defined as sequential.