Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DBMS_STATS.gather_database_stats

Posted on 2003-10-22
8
Medium Priority
?
2,074 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 375 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

715 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