Re: after running a query (count, select...like) i don't get the prompt back

Hi,

I would love to drop and recreate the table, but the problem is, its a production database, so i can't afford to lose the data in that table. I've already tried to export the table but then the problem occurs also. It exports about 86500 records and then hangs. If I can't find another solution i will try to do an offline backup (copy of the datafiles) and bring them to an test environment so i can try more drastic things.

The table has 2 indexes, i'll check them to see if there is something messed up.

thanks,
Cihat

joel-garry_at_nospam.cox.net (Joel Garry) wrote in message news:<slrnal3knk.hm.joel-garry_at_zr1.vista1.sdca.cox.net>...
> On 7 Aug 2002 13:35:02 -0700, Cihat <c.genc_at_interpay.nl> wrote:
> >Hi Richard,
> >
> >The size isn't that big, it holds 200.000 records and the table size
> >is 8,5 MB.
> >In the same database I have a larger table and that's working fine.
> >It's weird,
> >if i do 'select count(*) from cardholder' the problem occurs, but if I
> >do
> >'select count(*) from cardholder where cardholder_id < 250000' it
> >shows the correct total amount of records and doesn't give any
> >problems.
>
> I've seen variations on this problem due to constraint problems (missing or
> invalid, or messed up index) and optimizer problems (in particular,
> rule-based "choose" system was messed up due to an import of a table that
> someone had run analyse on).
>
> Also, if you have a lot of deleted records you could be scanning a whole
> lot of empty blocks or have a high-water problem. Try deleting and
> recreating the table (or truncating/reloading).
>
> And just for the heck of it, dbverify.
>
> >
> >regards,
> >Cihat
> >
> >
> >
> >Richard Foote <Richard.Foote_at_oracle.com> wrote in message news:<3D50E900.F910A331_at_oracle.com>...
> >> Hi Cihat,
> >>
> >> Do you have any idea on the size of the cardholder table ? If it's
> >> large, then it could very well take a considerable amount of time (try
> >> as I might, it takes me a while to walk from Canberra to Dubbo).
> >>
> >> Take a look if you can (or get your DBA to take a look) at
> >> v$session_longops, it displays details of long running queries and might
> >> give you an indication on how long to go.
> >>
> >> Cheers
> >>
> >> Richard
> >>
> >> Cihat wrote:
> >> >
> >> > Hi,
> >> >
> >> > When i'm running a query it doesn't show the result and it also doesn't
> >> > go back to the prompt. This only happens in one table in the database.
> >> >
> >> > The query what i'm running is simple:
> >> >
> >> > select count(*)
> >> > from cardholder
> >> >
> >> > It also happens when i do a query like this:
> >> >
> >> > select *
> >> > from cardholder
> >> > where cardholder_name like 'Cihat%'
> >> >
> >> > Can anyone advise me about this?
> >> >
> >> > regards,
> >> > Cihat
> >> --
>
> jg
Received on Thu Aug 08 2002 - 01:39:13 CDT