Re: Why is sqlplus not responding sometimes?

Kalle W <kawi9365NOkaSPAM_at_student.uu.se.invalid> wrote:
>sometimes sqlplus stops responding to certain commands.
>
>Usually it comes back after a while. On certain commands it just
>blocks and I have to cancel them to regain control. Often simple
>select queries works but delete, drop and insert seems to give
>this halt phenomenon. Any suggestions?
>
>/Kalle W
>
>

Kalle, you need to be more specific about what commands on what kind of objects are causing a problem. Do they just take forever, or are you getting error messages back.

Common cause for problems involving deletes: 1) Deletes are expensive to being with and it will just take time. Perhaps a truncate can be used instead. 2) A FK exists on the rows being deleted and cascade deletes are taking place
3) Triggers exist on the target table causing a cascade effect

Common problem involving drops:
1) Someone analyzed the sys owned objects causing the optimizer to perform the wrong access plan for dictionary data 2) If it is a table or index the object is large and a lot of rollback data is being generated. Truncate the table then drop it.
3) A log of direct grants were made on the object and Oracle is having to clean up thousands of these entries. Create a role, do the grants to the role and grant the role to the users.

I hope this helps.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Tue Jul 25 2000 - 00:00:00 CDT