Re: DBMS_STATS "Missing expression"

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:1125323686.578601.196870_at_g49g2000cwa.googlegroups.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