-
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.
-
there is no procedure called create_stats_table do you want gather_table_stats?
-
Hi,
It should be
dbms_Stats.Create_Stat_Table (
ownname => 'xx',
stattab => 'xxx',
tblspace => 'xxxx');
HTH
SS
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|