Analyze the specific partition

Dear all,

I am trying to pass the partition name as a parameter. But it is saying some privileges error. This schema has ANALYZE ANY privileges. Am i missing any other privileges?

SQL>
SQL>
SQL> declare
2 v_partname VARCHAR2(20) := 'June 2006';
3 BEGIN
4 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'REPORT',
5 TABNAME => 'T_PART',
6 ESTIMATE_PERCENT => 10,
7 METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
8 CASCADE => TRUE,
9 PARTNAME=>'"July 2006"');
10 END;
11 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> declare
2 v_partname VARCHAR2(20) := 'June 2006';
3 BEGIN
4 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'REPORT',
5 TABNAME => 'T_PART',
6 ESTIMATE_PERCENT => 10,
7 METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
8 CASCADE => TRUE,
9 PARTNAME=>v_partname);
10 END;
11 /
declare
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "REPORT"."T_PART"
JUNE 2006, insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10061
ORA-06512: at "SYS.DBMS_STATS", line 10084
ORA-06512: at line 4


SQL>

You're kidding with a space in the partition name, right? That's your problem. Start trying different combinations of quoting or dynamic sql my friend.

Thank you. You are right. The space is a problem. It works now.

SQL>
SQL> declare
2 v_partname VARCHAR2(20) ;
3 BEGIN
4 select STF_CURRENT_PERIOD INTO v_partname FROM DUAL;
5 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SQL',
6 TABNAME => 'T_PART',
7 ESTIMATE_PERCENT => 10,
8 METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
9 CASCADE => TRUE,
10 PARTNAME=>'"'||v_partname||'"');
11 END;
12 /

PL/SQL procedure successfully completed.

SQL>