Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2082
  • Last Modified:

DBMS_STATS.gather_database_stats

i get the following error whenever i try to gather database stats:

ORA–01465: invalid hex number
ORA-06512: at "SYS.DBMS_STATS", line 4474
ORA-06512: at "SYS.DBMS_STATS", line 4605
ORA-06512: at "SYS.DBMS_STATS", line 4715
ORA-06512: at "SYS.DBMS_STATS", line 4697
ORA-06512: at line 1

i'm not working with BLOBs or CLOBs...just wanting to gather some stats on my database.  any advice?
0
scoogie
Asked:
scoogie
  • 3
  • 2
  • 2
  • +1
1 Solution
 
seazodiacCommented:
huh-uh!!!

You should NEVER NEVER NEVER collect statistics on the DATABASE. This is explicitly prohibited in Tom Kyte book. So never call DBMS_STATS.gather_database_stats.
the reason is that SYS, SYSTEM schem objects are designed in such a way that
they are more efficient in RULE-BASEd optimizer.
0
 
PontisCommented:
seazodiac is right, better use gather_schema_stats on the schemas you want, but don' touch whole database. Try to rerun the gather_schema_stats on the schema you need to collect stats on and see if you still get an error
0
 
scoogieAuthor Commented:
i get the same error whenever i run the gather_schema_stats as well---i failed to mention that i ran both gather_schema and gather_database...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
seazodiacCommented:
NO, scoogie:

just don't run anything like statistics collecting on SYS and SYSTEM schema. Period!!!

run get_schema_stats on Users' schema

get it?
0
 
scoogieAuthor Commented:
lemme clarify  =)

i ran DBMS_STATS.gather_schema_stats ('schema'); and it still failed with the same error
0
 
PontisCommented:

 Database version? There was a bug in versions < 8.1.7.2 . Install patchset and try again :)
0
 
DrJekyllCommented:
What is your Oracle version?

There is a  bug 1580612 which is fixed in the 8.1.7.2 patchset (and higher) releases.  

Please verify that you have installed the 8.1.7.2  or higher patchset.
If this is the case apply 8.1.7.4 patch

DJ
0
 
seazodiacCommented:
Do you have LONG (RAW) field in the schema?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now