ORA-30476 When Executing DBMS_MVIEW.ESTIMATE_MVIEW_SIZE Procedure

(Doc ID 780893.1)

Last updated on AUGUST 01, 2012

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

In 10gR2, the following errors returned when executing DBMS_MVIEW.ESTIMATE_MVIEW_SIZE procedure:

SH@lv102> set serveroutput on;
SH@lv102> DECLARE
2 no_of_rows NUMBER;
3 mv_size NUMBER;
4 BEGIN
5 no_of_rows :=555;
6 mv_size :=5555;
7 DBMS_MVIEW.ESTIMATE_MVIEW_SIZE ('MV 1', <==
8 'SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
9 sum(s.quantity_sold) as quantity
10 FROM sales s , customers c, products p
11 WHERE c.cust_id = s.cust_id
12 AND s.prod_id = p.prod_id
13 GROUP BY c.cust_id, p.prod_id' , no_of_rows, mv_size );
14 DBMS_OUTPUT.put_line ( '');
15 DBMS_OUTPUT.put_line ( 'No of Rows: ' || no_of_rows );
16 DBMS_OUTPUT.put_line ( 'Size of Materialized view (MB): ' ||
17 round(mv_size/(1024*1024),2) );
18 DBMS_OUTPUT.put_line ( '');
19 END;
20 /
DECLARE
*
ERROR at line 1:
ORA-30476: PLAN_TABLE does not exist in the user's schema
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SUMADVISOR", line 2139
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3284
ORA-06512: at line 7


In 11.1.0.7, no error reported, but the output is wrong:

No of Rows: -1
Size of Materialized view (MB): 0

PL/SQL procedure successfully completed.



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms