RDBMS version: Oracle 8i Enterprise 8.1.5.0.0 (Yes, I know it's old and
unsupported.)
Platform: Solaris 2.6
HW: Sun Enterprise 450, 1 processor, 1 gig memory.
Two external dive arrays connected via SCSI with software LVM (Veritas).

I don't have access to metalink and the web doesn't return any useful
results.

Has anyone seen an error of the kind:

ORA-00936: missing expression
ORA-06512: at "SYS.DBMS_STATS", line 3867
ORA-06512: at "SYS.DBMS_STATS", line 3998
ORA-06512: at "SYS.DBMS_STATS", line 4161
ORA-06512: at "SYS.DBMS_STATS", line 4143

when trying to execute a statement of the kind:
exec DBMS_STATS.GATHER_SCHEMA_STATS( ownname=>'SCHEMANAME' );

It's worked before. All I remember doing new is creating a table and a
function-based index. Other function-based indexes already exist in the
database and the procedure worked fine.

Any help would be appreciated.

--
Andreas
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer


"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding if you don't eat your meat?!?!"
---

WARNING:
DO NOT REPLY TO THIS EMAIL
Reply to me only on this newsgroup

Only thing I found of interest on metalink was bug 3491127 (document
id) which appears to be for 9.2 where a multi-columned index on a
partitioned table could produce this error

Have you verified that no one reran catproc under the wrong id? If
some of the sys owned objects were invalid or the public synonyms
pointed to the wrong owner then cleaning up the mess should resolve
this, but only if someone messed up.

That is all I can think of other than trying to get around the problem,
assuming it is reproducing, by generating gather_table_stats calls for
the schema.

HTH -- Mark D Powell --

"Mark D Powell" <Mark.Powell@eds.com> wrote in message
news:1124996740.359404.158710@g14g2000cwa.googlegr oups.com...
> Only thing I found of interest on metalink was bug 3491127 (document
> id) which appears to be for 9.2 where a multi-columned index on a
> partitioned table could produce this error
>
> Have you verified that no one reran catproc under the wrong id? If
> some of the sys owned objects were invalid or the public synonyms
> pointed to the wrong owner then cleaning up the mess should resolve
> this, but only if someone messed up.
>
> That is all I can think of other than trying to get around the problem,
> assuming it is reproducing, by generating gather_table_stats calls for
> the schema.
>
> HTH -- Mark D Powell --
>

No one re-ran catproc under any id.

We are not using partitions.

Doing a SELECT DISTINCT STATUS FROM DBA_OBJECTS;
returns only
STATUS
---------
VALID

The error is reproduced using:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS( ownname=>'SCHEMANAME' );

However, I do remember
creating SYSTEM.PLAN_TABLE (using the SYSTEM account),
creating a PUBLIC SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE (using the as
sysdba privileges) and
granting appropriate privileges for PLAN_TABLE to public.

Querying dba_tab_privs, I see that the privileges granted to PUBLIC for
PLAN_TABLE are ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, and REFERENCES.

That can't be what's $cr3wing up DBMS_STATS, is it?

--
Andreas
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer


"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding if you don't eat your meat?!?!"
---

WARNING:
DO NOT REPLY TO THIS EMAIL
Reply to me only on this newsgroup

Andreas Sheriff wrote:
> "Mark D Powell" <Mark.Powell@eds.com> wrote in message
> news:1124996740.359404.158710@g14g2000cwa.googlegr oups.com...
> > Only thing I found of interest on metalink was bug 3491127 (document
> > id) which appears to be for 9.2 where a multi-columned index on a
> > partitioned table could produce this error
> >
> > Have you verified that no one reran catproc under the wrong id? If
> > some of the sys owned objects were invalid or the public synonyms
> > pointed to the wrong owner then cleaning up the mess should resolve
> > this, but only if someone messed up.
> >
> > That is all I can think of other than trying to get around the problem,
> > assuming it is reproducing, by generating gather_table_stats calls for
> > the schema.
> >
> > HTH -- Mark D Powell --
> >
>
> No one re-ran catproc under any id.
>
> We are not using partitions.
>
> Doing a SELECT DISTINCT STATUS FROM DBA_OBJECTS;
> returns only
> STATUS
> ---------
> VALID
>
> The error is reproduced using:
> EXEC DBMS_STATS.GATHER_SCHEMA_STATS( ownname=>'SCHEMANAME' );
>
> However, I do remember
> creating SYSTEM.PLAN_TABLE (using the SYSTEM account),
> creating a PUBLIC SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE (using the as
> sysdba privileges) and
> granting appropriate privileges for PLAN_TABLE to public.
>
> Querying dba_tab_privs, I see that the privileges granted to PUBLIC for
> PLAN_TABLE are ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, and REFERENCES.
>
> That can't be what's $cr3wing up DBMS_STATS, is it?
>
> --
> Andreas
> Oracle 9i Certified Professional
> Oracle 10g Certified Professional
> Oracle 9i Certified PL/SQL Developer
>
>
> "If you don't eat your meat, you cannot have any pudding.
> "How can you have any pudding if you don't eat your meat?!?!"
> ---
>
> WARNING:
> DO NOT REPLY TO THIS EMAIL
> Reply to me only on this newsgroup

You could drop the table and find out. You could also trace the
session running dbms_stats.


David Fitzjarrell

<fitzjarrell@cox.net> wrote in message
news:1125007097.164339.168450@g14g2000cwa.googlegr oups.com...
>
> Andreas Sheriff wrote:
> > "Mark D Powell" <Mark.Powell@eds.com> wrote in message
> > news:1124996740.359404.158710@g14g2000cwa.googlegr oups.com...
> > > Only thing I found of interest on metalink was bug 3491127 (document
> > > id) which appears to be for 9.2 where a multi-columned index on a
> > > partitioned table could produce this error
> > >
> > > Have you verified that no one reran catproc under the wrong id? If
> > > some of the sys owned objects were invalid or the public synonyms
> > > pointed to the wrong owner then cleaning up the mess should resolve
> > > this, but only if someone messed up.
> > >
> > > That is all I can think of other than trying to get around the
problem,
> > > assuming it is reproducing, by generating gather_table_stats calls for
> > > the schema.
> > >
> > > HTH -- Mark D Powell --
> > >
> >
> > No one re-ran catproc under any id.
> >
> > We are not using partitions.
> >
> > Doing a SELECT DISTINCT STATUS FROM DBA_OBJECTS;
> > returns only
> > STATUS
> > ---------
> > VALID
> >
> > The error is reproduced using:
> > EXEC DBMS_STATS.GATHER_SCHEMA_STATS( ownname=>'SCHEMANAME' );
> >
> > However, I do remember
> > creating SYSTEM.PLAN_TABLE (using the SYSTEM account),
> > creating a PUBLIC SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE (using the as
> > sysdba privileges) and
> > granting appropriate privileges for PLAN_TABLE to public.
> >
> > Querying dba_tab_privs, I see that the privileges granted to PUBLIC for
> > PLAN_TABLE are ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, and
REFERENCES.
> >
> > That can't be what's $cr3wing up DBMS_STATS, is it?
> >
> > --
> > Andreas
> > Oracle 9i Certified Professional
> > Oracle 10g Certified Professional
> > Oracle 9i Certified PL/SQL Developer
> >
> >
> > "If you don't eat your meat, you cannot have any pudding.
> > "How can you have any pudding if you don't eat your meat?!?!"
> > ---
> >
> > WARNING:
> > DO NOT REPLY TO THIS EMAIL
> > Reply to me only on this newsgroup
>
> You could drop the table and find out. You could also trace the
> session running dbms_stats.
>
>
> David Fitzjarrell
>

How silly of me to overlook tracing.

Here's what I found in the trace file after running EXEC
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'OBJNAME') ; :

=====================
PARSE ERROR #7:len=433 dep=1 uid=0 oct=3 lid=0 tim=0 err=936
select /*+ */ count(*) CNT,count(PIN),count(distinct
PIN),avg(nvl(ceil(length(ltrim(rtrim(rtrim(to_char (PIN,'9.999999999999999999
999999999999999999999EEEE'),'+-0123456789'),'E0.')))/2+2),1)),min(PIN),max(P
IN),count(group),count(distinct
group),avg(nvl(ceil(length(ltrim(rtrim(rtrim(to_ch ar(group,'9.99999999999999
9999999999999999999999999EEEE'),'+-0123456789'),'E0.')))/2+2),1)),min(group)
,max(group) from "CLASSIFIEDXXXX"."ABR_PINS"
EXEC #1:c=0,e=0,p=0,cr=3537,cu=12,mis=0,r=0,dep=0,og=4, tim=0
ERROR #1:err=936 tim=0
*** 2005.08.25.23.58.13.000
=====================


Ooops... I also created another table called ABR_PINS. :-D

But, what the heck is up with that hint?

Here's what I did,
I tried running
EXEC DBMS_STATS.GATHER_TABLE_STATS()
for that table, but still got the missing expression error.

I ran
ANALYZE [TABLE,INDEX] COMPUTE STATISTICS;
That ran fine.

Tried again,
EXEC DBMS_STATS.GATHER_TABLE_STATS()
Still got the missing expression error.

The table is nothing special. It's defined as:

CREATE TABLE abr_pins (
pin number(6),
"group" number(1) CHECK "group" in(1,2,3),
CONSTRAINT pk_abr_pins PRIMARY KEY (pin,group));

Am I missing something here?
(Forgive the syntax, and no comments about naming col2 "group". I know, I
know

Or maybe that could be the problem...

I can't test a solution till the table is out of use, though.

In fact, I'm VERY sure that is the problem. DAMN!

"group" is referenced so many times in that select satement.

There is external code depending on the name group.
I know how to solve this. I'll rename the table to something like
base_abr_pins, rename the column "group" to "groupno" and create a view
called abr_pins for existing applications to use. DBMS_STATS won't touch
views when it's doing its analysis.

Ok, I guess I deserve a little-bit-o-chiding.
--

Andreas Sheriff
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer
----
"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding, if you don't eat your meat?"

DO NOT REPLY TO THIS EMAIL
Reply only to the group.

Being that GROUP is a reserved word I wonder if using it as a column
name is what is causing the problem. Any chance you can recreate the
table with a better column name and repeat the dbms_stats call. For
that matter if you create a second table using GROUP as a column name
can you duplicate the error?

HTH -- Mark D Powell --

"Mark D Powell" <Mark.Powell@eds.com> wrote in message
news:1125323686.578601.196870@g49g2000cwa.googlegr oups.com...
> Being that GROUP is a reserved word I wonder if using it as a column
> name is what is causing the problem. Any chance you can recreate the
> table with a better column name and repeat the dbms_stats call. For
> that matter if you create a second table using GROUP as a column name
> can you duplicate the error?
>
> HTH -- Mark D Powell --
>

Yes, that was the problem (in 8i) exactly. DBMS_STATS did not quote the
column names in its various queries.
I did create the same table in 9i, though, and ran DBMS_STATS again with
tracing turned on and noticed that all the column names were quoted this
time.

To fix the original problem, I executed the process I outlined in a previous
post. That is:

I renamed the old table.
ALTER TABLE abr_pins RENAME TO old_abr_pins;

I created a new table using CTAS and defined the new table as:
CREATE TABLE base_abr_pins(
pinno,
groupno check(groupno in(1,2,3),
CONSTRAINT pk_base_abr_pins PRIMARY KEY(pinno, groupno))
as
select pin, "group" from old_abr_pins;

I next created a view with the original table name.
CREATE VIEW abr_pins as
select pinno as pin, groupno as "group" from base_abr_pins;

And finally I reissued the appropriate grants.

GRANT SELECT ON abr_pins TO user1, user2, user3, etc;

I tested the application and it continues to function properly.

I then ran DBMS_STATS.GATHER_TABLE_STATS on base_abr_pins and it was
successful.
I also rant DBMS_STATS.GATHER_SCHEMA_STATS and
DBMS_STATS.GATHER_DATABASE_STATS just to make sure, and they were also
successful.

Thanks, David, for suggesting turning tracing on. It seems that the most
obvious tool was the most illusive to conceive, much akin to loosing your
glasses and finally finding it on your head. :-D

--
Andreas
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer


"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding if you don't eat your meat?!?!"
---

WARNING:
DO NOT REPLY TO THIS EMAIL
Reply to me only on this newsgroup

Andreas Sheriff wrote:
>
> <fitzjarrell@cox.net> wrote in message
> news:1125007097.164339.168450@g14g2000cwa.googlegr oups.com...
> >
> > Andreas Sheriff wrote:
> > > "Mark D Powell" <Mark.Powell@eds.com> wrote in message
> > > news:1124996740.359404.158710@g14g2000cwa.googlegr oups.com...
> > > > Only thing I found of interest on metalink was bug 3491127 (document
> > > > id) which appears to be for 9.2 where a multi-columned index on a
> > > > partitioned table could produce this error
> > > >
> > > > Have you verified that no one reran catproc under the wrong id? If
> > > > some of the sys owned objects were invalid or the public synonyms
> > > > pointed to the wrong owner then cleaning up the mess should resolve
> > > > this, but only if someone messed up.
> > > >
> > > > That is all I can think of other than trying to get around the
> problem,
> > > > assuming it is reproducing, by generating gather_table_stats calls for
> > > > the schema.
> > > >
> > > > HTH -- Mark D Powell --
> > > >
> > >
> > > No one re-ran catproc under any id.
> > >
> > > We are not using partitions.
> > >
> > > Doing a SELECT DISTINCT STATUS FROM DBA_OBJECTS;
> > > returns only
> > > STATUS
> > > ---------
> > > VALID
> > >
> > > The error is reproduced using:
> > > EXEC DBMS_STATS.GATHER_SCHEMA_STATS( ownname=>'SCHEMANAME' );
> > >
> > > However, I do remember
> > > creating SYSTEM.PLAN_TABLE (using the SYSTEM account),
> > > creating a PUBLIC SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE (using the as
> > > sysdba privileges) and
> > > granting appropriate privileges for PLAN_TABLE to public.
> > >
> > > Querying dba_tab_privs, I see that the privileges granted to PUBLIC for
> > > PLAN_TABLE are ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, and
> REFERENCES.
> > >
> > > That can't be what's $cr3wing up DBMS_STATS, is it?
> > >
> > > --
> > > Andreas
> > > Oracle 9i Certified Professional
> > > Oracle 10g Certified Professional
> > > Oracle 9i Certified PL/SQL Developer
> > >
> > >
> > > "If you don't eat your meat, you cannot have any pudding.
> > > "How can you have any pudding if you don't eat your meat?!?!"
> > > ---
> > >
> > > WARNING:
> > > DO NOT REPLY TO THIS EMAIL
> > > Reply to me only on this newsgroup
> >
> > You could drop the table and find out. You could also trace the
> > session running dbms_stats.
> >
> >
> > David Fitzjarrell
> >
>
> How silly of me to overlook tracing.
>
> Here's what I found in the trace file after running EXEC
> DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'OBJNAME') ; :
>
> =====================
> PARSE ERROR #7:len=433 dep=1 uid=0 oct=3 lid=0 tim=0 err=936
> select /*+ */ count(*) CNT,count(PIN),count(distinct
> PIN),avg(nvl(ceil(length(ltrim(rtrim(rtrim(to_char (PIN,'9.999999999999999999
> 999999999999999999999EEEE'),'+-0123456789'),'E0.')))/2+2),1)),min(PIN),max(P
> IN),count(group),count(distinct
> group),avg(nvl(ceil(length(ltrim(rtrim(rtrim(to_ch ar(group,'9.99999999999999
> 9999999999999999999999999EEEE'),'+-0123456789'),'E0.')))/2+2),1)),min(group)
> ,max(group) from "CLASSIFIEDXXXX"."ABR_PINS"
> EXEC #1:c=0,e=0,p=0,cr=3537,cu=12,mis=0,r=0,dep=0,og=4, tim=0
> ERROR #1:err=936 tim=0
> *** 2005.08.25.23.58.13.000
> =====================
>
> Ooops... I also created another table called ABR_PINS. :-D
>
> But, what the heck is up with that hint?
>
> Here's what I did,
> I tried running
> EXEC DBMS_STATS.GATHER_TABLE_STATS()
> for that table, but still got the missing expression error.
>
> I ran
> ANALYZE [TABLE,INDEX] COMPUTE STATISTICS;
> That ran fine.
>
> Tried again,
> EXEC DBMS_STATS.GATHER_TABLE_STATS()
> Still got the missing expression error.
>
> The table is nothing special. It's defined as:
>
> CREATE TABLE abr_pins (
> pin number(6),
> "group" number(1) CHECK "group" in(1,2,3),
> CONSTRAINT pk_abr_pins PRIMARY KEY (pin,group));
>
> Am I missing something here?
> (Forgive the syntax, and no comments about naming col2 "group". I know, I
> know
>
> Or maybe that could be the problem...
>
> I can't test a solution till the table is out of use, though.
>
> In fact, I'm VERY sure that is the problem. DAMN!
>
> "group" is referenced so many times in that select satement.
>
> There is external code depending on the name group.
> I know how to solve this. I'll rename the table to something like
> base_abr_pins, rename the column "group" to "groupno" and create a view
> called abr_pins for existing applications to use. DBMS_STATS won't touch
> views when it's doing its analysis.
>
> Ok, I guess I deserve a little-bit-o-chiding.
> --
>
> Andreas Sheriff
> Oracle 9i Certified Professional
> Oracle 10g Certified Professional
> Oracle 9i Certified PL/SQL Developer
> ----
> "If you don't eat your meat, you cannot have any pudding.
> "How can you have any pudding, if you don't eat your meat?"
>
> DO NOT REPLY TO THIS EMAIL
> Reply only to the group.

The "/*+ */" is a nice little generic thing - if you ask for parallel
processing, you'll see the hint "filled" appropriately

--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------