Solved

DBMS_STATS.gather_database_stats

Posted on 2003-10-22
8
2,024 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
 
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
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.

 

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.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now