problem writing to oracle stage(partitoned-index organized)
Posted: Mon Aug 29, 2005 6:52 am
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?
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?