Analyze the specific partition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Analyze the specific partition

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    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>

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width