Take the 2-minute tour ×
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.

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
1  
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 '13 at 18:05
    
Hi, I connect as SYSTEM through a proxy user. –  scratch Mar 21 '13 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 '13 at 9:51

1 Answer 1

You cannot run any DML in a logical standby database unless you deactivate, temporary, the guard feature. To overcome you isse you can do:

SQL> ALTER DATABASE GUARD NONE;
SQL> SELECT GUARD_STATUS FROM V$DATABASE; 
GUARD_STATUS 
-------------------- 
NONE

And then run your dbms_stats.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.