In a dataguard-scenario, we are using a read-only-standby for reporting purposes.
One query that uses a pipelined-table-function ptf
select * from table(ptf(...))
is called several dozen times every day with different parameters successfully since several months.
Yesterday, it suddenly reproducibly failed with ORA-16000 "database open for read only access".
sql-trace showed it tried to issue a
drop type "DP".SYS_PLSQL_6B4A54F6_DUMMY_1 force
So, as a workaround, we performed the same query on the primary (also with trace enabled) and could see that the type was dropped (and recreated again together with some other types).
Afterwards, the query also was successful again on the standby - it succeeded yesterday for several dozen times again.
Until - this morning.
Same ORA-16000 again, same workaround.
Why does it suddenly behave like that (after several months running without any problem) ?
And - more important:
How to fix that ?
Thanks in advance !
Are you perhaps using nested table / record types defined in this (or some other) package as opposed to explicitly created type objects ?
create package
type my_type is table of ....
will generally result in a dynamically created type to support it. Hence you don't really have any control over
- when its created
- what its named
- when its dropped/marked for recompilation/etc etc
You shouldn't be seeing that error (its most likely a bug) but I would explicitly create a type and use that - you shouldn't see the problem again after that.