RE: Dropping table is not completing
Just check whether you have space problem on a filesystem/mountpoint where your alertSID.log is located... Regards MOHAMMAD RAFIQ Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 04 Oct 2001 11:10:22 -0800 It just hangs and I am not sure what tables/views to query to find out what the problem is. Thanks Rick -Original Message- Sent: Thursday, October 04, 2001 2:30 PM To: Multiple recipients of list ORACLE-L What is the error? Or does it just hang? Have you looked at session wait events to see what is holding it up? If it was locked, it would echo right back saying acquire with nowait failed. Perhaps trying to drop it with a trace behind it. Or trace the session as you wait from sys. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, October 04, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Hi All, I am trying to drop a table. It is not locked and has very few records in it. I am trying to drop it but it will not. What can I check to find out why it will not drop? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Dropping table is not completing
The first script will show you what transactions are taking place.
Look for lot's of work on sys.uet$ and sys.fet$
The second query will show session waits, and you may
in fact want to run it first.
Jared
=
set line 120
col osuser format a8 heading 'O/S|User'
col username format a10 heading 'Oracle|Userid'
col sid format head 'SID'
col segment_name format a6 heading 'R-S|Name'
col space format a5 head 'Space|Trans'
col recursive format a5 head 'Recur|sive|Trans'
col noundo format a5 head 'No|Undo'
col used_ublk format 99 head 'Used|Rbs|Blks'
col used_urec format 999 head 'Used|RBS|Recs'
col log_io format head 'Logical|IO Blks'
col phy_io format head 'Physical|IO Blks'
col txt format a30 heading 'Current|Statement' word
--spool showtrans.txt
select s.osuser
,s.username
,s.sid
,r.segment_name
,t.space
,t.recursive
,t.noundo
,t.used_ublk
,t.used_urec
,t.log_io
,t.phy_io
,substr(sa.sql_text,1,200) txt
from v$session s,
v$transaction t,
dba_rollback_segs r,
v$sqlarea sa
where s.taddr=t.addr
and t.xidusn=r.segment_id(+)
and s.sql_address=sa.address(+)
/
--spool off
=
select
s.username username,
e.event event,
s.sid,
e.p1text,
e.p1,
e.p2text,
e.p2,
e.wait_time,
e.seconds_in_wait,
e.state
from v$session s, v$session_wait e
where s.username is not null
and s.sid = e.sid
and s.username like upper('&uusername')
-- skip sqlnet idle session messages
and e.event not like '$message from client'
order by s.username, upper(e.event)
/
"Cale, Rick T
(Richard)" To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
Subject: RE: Dropping table is not
completing
Sent by:
[EMAIL PROTECTED]
m
10/04/01 12:10
PM
Please respond
to ORACLE-L
It just hangs and I am not sure what tables/views to query to find out what
the problem is.
Thanks
Rick
-Original Message-
Sent: Thursday, October 04, 2001 2:30 PM
To: Multiple recipients of list ORACLE-L
What is the error? Or does it just hang?
Have you looked at session wait events to see what is holding it up?
If it was locked, it would echo right back saying acquire with nowait
failed. Perhaps trying to drop it with a trace behind it. Or trace the
session as you wait from sys.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Thursday, October 04, 2001 1:55 PM
To: Multiple recipients of list ORACLE-L
Hi All,
I am trying to drop a table. It is not locked and has very few records in
it. I am trying to drop it but it will not.
What can I check to find out why it will not drop?
Thanks
Rick
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public In
RE: Dropping table is not completing
As someone else asked, how many extents does the table have? -Original Message- Sent: Thursday, October 04, 2001 3:10 PM To: Multiple recipients of list ORACLE-L It just hangs and I am not sure what tables/views to query to find out what the problem is. Thanks Rick -Original Message- Sent: Thursday, October 04, 2001 2:30 PM To: Multiple recipients of list ORACLE-L What is the error? Or does it just hang? Have you looked at session wait events to see what is holding it up? If it was locked, it would echo right back saying acquire with nowait failed. Perhaps trying to drop it with a trace behind it. Or trace the session as you wait from sys. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, October 04, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Hi All, I am trying to drop a table. It is not locked and has very few records in it. I am trying to drop it but it will not. What can I check to find out why it will not drop? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Dropping table is not completing
Rick, Open up another sqlplus session and check the activivity of sys.uet$ and sys.fet$ as Joe suggested. If you had a large number of extents in this table you could be doing a lot of deletes and inserts into these tables. (UET$ = Used Extent Table, FET$ = Free Extent Table). To check this do the following: select count(u.ts#) UET_COUNT, count(f.ts#) FET_COUNT from sys.uet$ u, sys.fet$ f, v$tablespace t where u.ts# = t.ts# andf.ts# = t.ts# andt.name = 'USERS'; /*or whatever tablespace name your table is in*/ UET_COUNT FET_COUNT -- -- 21588242158824 1 row selected. Run this a few times and see how much the numbers are changing. This will let you know where you are with your truncate. Rodd Holman "Cale, Rick T (Richard)" wrote: > Joe,It is just sitting there. I am trying to truncate then drop but > truncating is just sitting there. There are no constraints on this > table and I am the only one accessingdatabase currently. ThanksRick > > -Original Message- > From: JOE TESTA [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 04, 2001 2:16 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Dropping table is not completing > > Richard, is it just sitting there? could it have a large > numebr of extents that oracle is deleting entries from uet$ > and inserting back to fet$? joe > > >>> [EMAIL PROTECTED] 10/04/01 01:55PM >>> > Hi All, > > I am trying to drop a table. It is not locked and has very > few records in > it. I am trying to drop it but it will not. > What can I check to find out why it will not drop? > > Thanks > Rick > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Cale, Rick T (Richard) >INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) > 538-5051 > San Diego, California-- Public Internet access / > Mailing Lists > - > -- > To REMOVE yourself from this mailing list, send an E-Mail > message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). > You may > also send the HELP command for other information (like > subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Dropping table is not completing
It just hangs and I am not sure what tables/views to query to find out what the problem is. Thanks Rick -Original Message- Sent: Thursday, October 04, 2001 2:30 PM To: Multiple recipients of list ORACLE-L What is the error? Or does it just hang? Have you looked at session wait events to see what is holding it up? If it was locked, it would echo right back saying acquire with nowait failed. Perhaps trying to drop it with a trace behind it. Or trace the session as you wait from sys. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, October 04, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Hi All, I am trying to drop a table. It is not locked and has very few records in it. I am trying to drop it but it will not. What can I check to find out why it will not drop? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Dropping table is not completing
Joe, It is just sitting there. I am trying to truncate then drop but truncating is just sitting there. There are no constraints on this table and I am the only one accessing database currently. Thanks Rick -Original Message-From: JOE TESTA [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 04, 2001 2:16 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Dropping table is not completing Richard, is it just sitting there? could it have a large numebr of extents that oracle is deleting entries from uet$ and inserting back to fet$? joe >>> [EMAIL PROTECTED] 10/04/01 01:55PM >>>Hi All,I am trying to drop a table. It is not locked and has very few records init. I am trying to drop it but it will not.What can I check to find out why it will not drop?ThanksRick-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Dropping table is not completing
What is the error? Or does it just hang? Have you looked at session wait events to see what is holding it up? If it was locked, it would echo right back saying acquire with nowait failed. Perhaps trying to drop it with a trace behind it. Or trace the session as you wait from sys. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, October 04, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Hi All, I am trying to drop a table. It is not locked and has very few records in it. I am trying to drop it but it will not. What can I check to find out why it will not drop? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Dropping table is not completing
What kind of errors are you getting ?? Any messages or is it just not returning and you are killing the job ?? -Original Message- Sent: Thursday, October 04, 2001 12:55 PM To: Multiple recipients of list ORACLE-L Hi All, I am trying to drop a table. It is not locked and has very few records in it. I am trying to drop it but it will not. What can I check to find out why it will not drop? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Dropping table is not completing
Title: RE: Dropping table is not completing Rick, what errors are you seeing that are telling you it won't drop the table? or is it just sitting there? Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Cale, Rick T (Richard) [SMTP:[EMAIL PROTECTED]] Sent: Thursday, October 04, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Subject: Dropping table is not completing Hi All, I am trying to drop a table. It is not locked and has very few records in it. I am trying to drop it but it will not. What can I check to find out why it will not drop? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Dropping table is not completing
Richard, is it just sitting there? could it have a large numebr of extents that oracle is deleting entries from uet$ and inserting back to fet$? joe >>> [EMAIL PROTECTED] 10/04/01 01:55PM >>>Hi All,I am trying to drop a table. It is not locked and has very few records init. I am trying to drop it but it will not.What can I check to find out why it will not drop?ThanksRick-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Dropping table is not completing
Hi All, I am trying to drop a table. It is not locked and has very few records in it. I am trying to drop it but it will not. What can I check to find out why it will not drop? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
