Please don't hijack other users threads - Start your own using your own
subject line and then the OP might be able to get a response to his or
her question.
-----Original Message-----
From: martin
[mailto:oracledba-ezmlmshield-x56489495.[Email address protected]
Sent: 27 June 2007 20:23
To: LazyDBA Discussion
Subject: RE: Problems with dbms_stats
Hi All,
Two things.
1) I would like some assistance in writing a function that
Should return the first instance of a number found in that string i.e.
ABC F1 78HGTFSB NMKO would return 6.
2) I would also like to find out how many time a space occurs in a
string. I.e.. ABC F1 78HGTFSB NMKO 34 would return 4.
Any help would be really appreciated.
Thanks
Martin Thomas
-----Original Message-----
From: karen
[mailto:oracledba-ezmlmshield-x28005400.[Email address protected]
Sent: Wednesday, June 27, 2007 3:10 PM
To: LazyDBA Discussion
Subject: Problems with dbms_stats
I hope someone can help with a problem I'm having generating statistics
in Oracle 9i. This problem started suddenly with no known changes to
the database. The Oracle instance that this database is on also
contains another database which is not having this problem.
When executing the following:
exec dbms_stats.gather_schema_stats(ownname=>'ORACLE',
estimate_percent=>30, block_sample=>TRUE, cascade=>TRUE, method_opt=>
'for all indexes for all indexed columns size auto'); I get this error:
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9616
ORA-06512: at "SYS.DBMS_STATS", line 9800
ORA-06512: at "SYS.DBMS_STATS", line 9854
ORA-06512: at "SYS.DBMS_STATS", line 9831
ORA-06512: at line 1
If I execute an analyze on an individual table, I get the following
error:
dbms_stats.gather_table_stats(ownname=> 'ORACLE', tabname=> 'table_x',
partname=> NULL);
ORA-20000: Cannot parse for clause: FOR ALL COLUMNS SIZE 1
ORA-06512: at sys.dbms_stats, line .....
If I execute an analyze on an individual index, it works correctly.
dbms_stats.gather_index_stats(ownname=> 'ORACLE', indname=> 'index_y',
partname=> NULL);
And if I issue an analyze without using the dbms_stats package, it works
correctly.
ANALYZE TABLE "ORACLE"."table_x" COMPUTE STATISTICS;
Thanks for your help.
Karen Coulter
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
**********************************************************************
APAK Group Limited, Registered in England No. 01451033.
Registered office APAK House, Badminton Court, Station Road, Yate, Bristol, BS37 5HZ.
The information contained in this message or any of its attachments may
be privileged and confidential and intended for the exclusive use of the
addressee. The views expressed may not be APAK policy but the personal
views of the originator. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message and
any attachments without retaining a copy. This footnote also confirms that
this email message has been swept for the presence of known computer
viruses before being sent.
**********************************************************************
Oracle LazyDBA home page