Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced. Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On the Primary the same query executed well after gathering table (DBMS_STATS.GATHER_TABLE_STATS) stats.

How to refresh statistics on the standby-side? The DBMS_STATS.GATHER_TABLE_STATS gives a ORA-16224.

With 

SELECT table_name,last_analyzed FROM dba_tables WHERE table_name IN ('TAB1','TAB2');

I can clearly see that the standby stats are a bit too old ;)

SELECT guard_status FROM v$database;

GUARD_STATUS
------------
STANDBY
share|improve this question
What user did you connect with/as to run the stats? (There's DML that needs to run to store the statistics, not sure exactly what "class" of users are allowed to do that on a logical standby.) – Mat Mar 20 at 18:05
Hi, I connect as SYSTEM through a proxy user. – scratch Mar 21 at 14:49
Thanks Mat, you pointed me in the right direction, it's indeed a user and permission issue. On the primary the SYSTEM user is also able to gather stats. In the logical standby the only user who can gather stats in synced schemas is SYS. Please make a answer to earn your points ;) – scratch Mar 22 at 9:51

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.