Solved

DBMS_STATS.gather_database_stats

Posted on 2003-10-22
8
2,050 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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