Solved

DBMS_STATS.gather_database_stats

Posted on 2003-10-22
8
2,061 Views
Last Modified: 2010-05-18
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
Comment
Question by:scoogie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 125 total points
ID: 9600797
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
 
LVL 5

Expert Comment

by:Pontis
ID: 9601142
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
 

Author Comment

by:scoogie
ID: 9601258
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:seazodiac
ID: 9601313
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
 

Author Comment

by:scoogie
ID: 9601487
lemme clarify  =)

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

Expert Comment

by:Pontis
ID: 9601581

 Database version? There was a bug in versions < 8.1.7.2 . Install patchset and try again :)
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9601583
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 9609414
Do you have LONG (RAW) field in the schema?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question