Error in DBMS.CREATE_STATS_TABLE
I am running Oracle 18.104.22.168
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.
SQL> execute SYS.DBMS_STATS.CREATE_STATS_TABLE (OWNNAME=> 'MYNAME', STATTAB=> 'MYNAME_STATS_TAB');
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 22.214.171.124?
Thanks in advance for your help.
there is no procedure called create_stats_table do you want gather_table_stats?
It should be
ownname => 'xx',
stattab => 'xxx',
tblspace => 'xxxx');
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.
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!
Click Here to Expand Forum to Full Width