Oracle Enterprise Partition error on View

[snt_ds]

I am getting the following error, when I use Partition table property on Oracle Stage to partition a View.

APT_OracleRowid8PartTable: failed to get the block count.
Oracle_Enterprise_138: Errors were encountered partitioning table V_POS_SUM_DLY_R.

I have SELECT permission to all 8 Systems table.
Firstly, I am not sure if partioning will be working on Views as they are logical representations of tables. If I can partition on views, what else do i need to avoid this warning message.

[ray.wurlod]

ARE views partitioned? If not, then searching for them in the system tables is pretty much guaranteed to fail, hence the error message.

[snt_ds]

Thanks Ray for the information. Oracle DBA has mentioned that the view is partitioned and that this view is partitioned based on one of the fields in the view.

In the Partition table property ,I mentioned the same view name as that I used in the SELECT statement.

Can a view have a different view when it is partitioned?

I've permission to DBA_TAB_PARTITIONS and DBA_SUBTAB_PARTITIONS
including six other Sysytem tables.

However, I am not able to understand where the issue really is?

could you help me find what is needed?

[snt_ds]

Ray,
Now, I am getting an idea about what I am doing.

I ran the following queries to see if the Partition view information is available but did not retrieve any rows.

Code: Select all

SQL> select TABLE_NAME, PARTITION_NAME FROM DBA_TAB_PARTITIONS where TABLE_NAME like 'V_POS_SUM_DLY%
SQL> /
 
no rows selected
 
SQL> select TABLE_NAME, PARTITIONING_TYPE FROM DBA_PART_TABLES where TABLE_NAME like '%POS_SUM_DLY%'
SQL> /
 
TABLE_NAME                     PARTITIONING_TYPE
------------------------------ -------
POS_SUM_DLY                    RANGE
POS_SUM_DLY_HIST               RANGE
POS_SUM_DLY_TOT                RANGE

Based on these results, since no rows were returned for V_POS_SUM_DLY, I assume the view is not partitioned. Please clarify if I can find this information in another System table?[/code]

[ray.wurlod]

That's why I asked you to check whether the view was partitioned. It is not necessarily the case that a view on partitioned table(s) is itself partitioned. Here it is not. Present your findings to the DBA to confirm.

Meanwhile, remove the view name from the Partition Table field. Possibly replace it with the principal table name on which the view is defined.

[ray.wurlod]

You are stuck with sequential until you can be granted SELECT privilege to the table(s) on which the view is defined. Then you use the view-defining statement in your Oracle Enterprise stage instead of the view name.

Argue to the DBA that only the DataStage user needs to be granted this privilege, and it is impossible to do any harm with only SELECT privilege. Argue further that the quicker execution time for parallel query substantially decreases the possibility of "snapshot too old" and similar errors that the DBA would need to clean up.

[snt_ds]

thank you Ray for providing help in this regard and clarifying my query.
One thing that will not happen is the SELECT priviledges to the Tables since that has been the case all the time.

thanks once again..

[ray.wurlod]

Get the end users (management) to complain about the slowness of the ETL process, and let it be known that SELECT privilege to the table(s) would be a solution.