Re: Drop table and drop package body hang

From: Jared Still <jkstill_at_gmail.com>
Date: Mon, 12 Apr 2010 09:16:48 -0700
Message-ID: <l2sbf46381004120916sda80d315k81bdccfbb451279a_at_mail.gmail.com>

You may want to try checking v$session_wait to see what the session is waiting on.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

On Mon, Apr 12, 2010 at 8:38 AM, Guang Mei <gmei_at_prospectiv.com> wrote:

> I have never seen this before. We have an "Oracle Database 10g Enterprise
> Edition Release 10.2.0.1.0 - 64bit Production" db on a linux box.
>
> We have a cron job that runs every night and it worked fine for years.
>
> This past Saturday morning, whole schema export worked fine, no error.
> On Saturday night this cron job ran, it just calls an oracle package P1,
> what it does at first is to run
>
> EXECUTE IMMEDIATE 'drop table T1 purge';
>
> and this time it hang for two days. This morning I killed the cron job,
> logged into sqlplus, but when I did any of the following, the sqlplus
> session just hang:
>
> desc T1;
> alter package P1 compile body;
> drop package body P1;
> drop package P1;
>
>
> DBA_OBJECTS show there is no T1 in data dictionary. Trying to reload P1
> code into DB, it also just hang at "CREATE OR REPLACE PACKAGE BODY P1". When
> I tried all the above I also looked v$lock from another sqlplus session and
> nothing was there. BTW this table and package are not used by any other
> process. I also checked "df -k" on OS and asked our SysAdmin to verifiy
> there is no hardware issues.
>
> Any idea why I am getting this?
> TIA.
>
> Guang
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 12 2010 - 11:16:48 CDT