Path: news.easynews.com!easynews!cyclone.swbell.net!cyclone-sf.pbi.net!64.245.249.51!sfo2-feed1.news.algx.net!allegiance!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: c.genc@interpay.nl (Cihat) Newsgroups: comp.databases.oracle.server Subject: Re: after running a query (count, select...like) i don't get the prompt back Date: 7 Aug 2002 23:39:13 -0700 Organization: http://groups.google.com/ Lines: 86 Message-ID: <75fbbd1e.0208072239.604a4fb@posting.google.com> References: <75fbbd1e.0208070058.2a11f64c@posting.google.com> <3D50E900.F910A331@oracle.com> <75fbbd1e.0208071235.7795d4f2@posting.google.com> NNTP-Posting-Host: 193.78.119.38 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1028788753 522 127.0.0.1 (8 Aug 2002 06:39:13 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 8 Aug 2002 06:39:13 GMT Xref: easynews comp.databases.oracle.server:157230 X-Received-Date: Wed, 07 Aug 2002 23:36:18 MST (news.easynews.com) 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@nospam.cox.net (Joel Garry) wrote in message news:... > On 7 Aug 2002 13:35:02 -0700, Cihat 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 wrote in message news:<3D50E900.F910A331@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