Re: While I was dropping UNIQUE constraints on the table, I killed my process

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1066676214.690592_at_yasure>...
> Jay wrote:
>
> >I was using SQL plus but it was hung for a about 20 min.
> >So I killed kill the SQL plus using window task manager.
> >
> >Now.
> >If I "desc table", it hangs.
> >No, I'm not dropping table.
> >
> >
> >Thanks.
> >-Jay
> >
> >
> >"Anurag Varma" <avarmadba.skipthis_at_yahoo.com> wrote in message
> >news:qWVkb.7914$Fc5.1839_at_nwrdny01.gnilink.net...
> >
> >
> >>"Jay" <no_at_spam.com> wrote in message
> >>
> >>
> >news:bn19ds$5be$1_at_msunews.cl.msu.edu...
> >
> >
> >>>While I was dropping UNIQUE constraints on the table, I killed my
> >>>
> >>>
> >process.
> >
> >
> >>>Now, I can not even "desc table". all other table is fine though.
> >>>
> >>>Is Oracle still dropping constraints?
> >>>Or rolling back dropping ?
> >>>Or crashed?
> >>>
> >>>Is there anyway I can check?
> >>>
> >>>
> >>>Please help.
> >>>-Jay
> >>>
> >>>
> >>>
> >>>
> >>Are you getting any error while doing desc table?
> >>How were you dropping the constraints (which tool?)?
> >>If you were running a script .. did the script include dropping table
> >>
> >>
> >also?
> >
> >
> >>How did you kill your session?
> >>
> >>Drop comands are usually quick ... I don't understand why you had to kill
> >>the session ...
> >>
> >>Post more info!
> >>
> >>Anurag
> >>
> >>
> >>
> >>
> Is this a production database? What version and edition on what
> hardware? Local or remote to the
> SQL*Plus client?
>
> My initial instinct is to bounce the database and query user_constraints.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
> --

How did you kill your process? It is possible depending on how and from where you connected to the instance and how you killed your process that you killed only the front-end process and your user session background process is still alive and kicking. You may want to open two new sessions. From the first issue an lock table in exclusive mode and from the second follow the locking chain back to see what session the lock command is waiting on. If it is the user session background process terminate it via the OS. If it is an Oracle server background process you will need to bounce the instance.

HTH -- Mark D Powell -- Received on Mon Oct 20 2003 - 19:06:03 CDT