Error in DBMS.CREATE_STATS_TABLE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Error in DBMS.CREATE_STATS_TABLE

  1. #1
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66

    Error in DBMS.CREATE_STATS_TABLE

    Hi,
    I am running Oracle 8.1.7.2
    I want to be able to preserve the current statistics prior to a major patch release for my application.
    I get the following error when I want do create the table to store the statistics.

    Command is:
    SQL> execute SYS.DBMS_STATS.CREATE_STATS_TABLE (OWNNAME=> 'MYNAME', STATTAB=> 'MYNAME_STATS_TAB');

    Error is:
    BEGIN SYS.DBMS_STATS.CREATE_STATS_TABLE (OWNNAME=> 'MYNAME', STATTAB=> 'MYNAME_STATS_TAB'); END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 22:
    PLS-00302: component 'CREATE_STATS_TABLE' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Is there something I can do to 'declare' this component, or is this functionality not available in 8.1.7.2?

    Thanks in advance for your help.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    there is no procedure called create_stats_table do you want gather_table_stats?

  3. #3
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Hi,

    It should be

    dbms_Stats.Create_Stat_Table (
    ownname => 'xx',
    stattab => 'xxx',
    tblspace => 'xxxx');


    HTH

    SS

  4. #4
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    Thanks for that!
    DBMS_STATS.CREATE_STAT_TABLE it is.
    I was using O'Reillys Oracle SQL Tuning pocket reference by Mark Gurry for my syntax. So definitely a typo on page 96!

    Davey, I am trying to keep a copy of my existing statistics, before a big upgrade. CREATE_STAT_TABLE is just to set up the table to store this information. I will then run DBMS_STATS.EXPORT_SCHEMA_STATS, to have a copy of current stats before upgrade.

    Thanks again.

  5. #5
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    Ok, another problem.
    When I try DBMS_STATS.EXPORT_SCHEMA_STATS I now get this


    SQL> BEGIN SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=>'MYNAME', STATTAB=>'MYNAME_STATS_TABLE', STATID=>'PRE_8416_PATCH'); END;

    *
    ERROR at line 1:
    ORA-20000: Schema MYNAME does not exist or insufficient privileges to analyze
    an object within it
    ORA-06512: at "SYS.DBMS_STATS", line 3028
    ORA-06512: at "SYS.DBMS_STATS", line 3050
    ORA-06512: at "SYS.DBMS_STATS", line 3326
    ORA-06512: at line 1

    I have tried running this as users internal, sys, system and myname but all get the same error. MYNAME schema does exist, but I'm not sure what privilege problem I might be having.

  6. #6
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66

    Unhappy

    Sorry, My mistake my own typo this time. I had edited the error message and changed the schema name to post here, and didnt change it back for running the export_schema_stats!

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