Thanks for the question, Anusha.
Asked: November 21, 2016 - 5:09 am UTC
Last updated: November 22, 2016 - 1:03 am UTC
Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Viewed 1000+ times
You Asked
Following is a query that results in an error -- "ORA-16000: database or pluggable database open for read-only access", when invoked for the 1st time.
But subsequent calls to the query are successful. The query runs successfully as long as its in the SGA, post which the 1st invokation again results in the above error.
Following the query, that gets called as a part of a stored proc.
with max_pub_dates as
(select business_date, max(publication_datetime) max_pubdate
from eqbproduct.version_details
where business_date = pi_business_date
AND upper(VERSION_NAME) LIKE UPPER('%' || LV_VERSION)
and comments = LV_COMMENT
group by BUSINESS_DATE)
select mcap.product_security_id,
mcap.business_date,
mcap.business_date as pro_forma_business_date,
CAST(COLLECT(obj_cur_mcap(mcap.iso_currency_symbol,
mcap.adj_market_cap,
mcap.unadj_market_cap_today,
mcap.initial_mkt_cap,
mcap.sec_idx_eod00d,
mcap.company_full_mkt_cap)) AS
nt_cur_mcap) MCAP
from eqbproduct.security_market_cap mcap, max_pub_dates mpd
where mcap.publication_datetime = mpd.max_pubdate
and mcap.business_date = mpd.business_date
and mcap.product_security_id =
nvl(pi_security_code, mcap.product_security_id)
group by mcap.product_security_id, mcap.business_date;
Type is created as follows:
create TYPE eqbproduct.obj_cur_mcap as object
(
ISO_CURRENCY_SYMBOL VARCHAR2(20),
ADJ_MARKET_CAP NUMBER,
UNADJ_MARKET_CAP_TODAY NUMBER,
INITIAL_MKT_CAP NUMBER,
SEC_IDX_EOD00D NUMBER,
COMPANY_FULL_MKT_CAP NUMBER
)
/
create type eqbproduct.nt_cur_mcap is table of obj_cur_mcap;
These types and procs are created in the primary (read-write database). Using ADG (Active Data Guard) the data is synced to a read only secondary database.
All application queries run out of the secondary database and all are just select statements.
Only the procs that use the "type" objects result in errors ( "ORA-16000: database or pluggable database open for read-only access")when the proc is run on the secondary database (which is read only database).
Please advise.
and Connor said...
Check to see if any object in your read-only database has a status of INVALID. Because if that is the case, then we will attempt to silently recompile/revalidate that object when first accessed. A compilation means attempting to *write* to the database.
So make sure all of your objects are valid.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment