RE: ORA-1653: unable to extend table - Why?

From: <Govind.Arumugam_at_alltel.com>
Date: Mon, 02 Dec 2002 15:13:58 -0800
Message-ID: <F001.0051049D.20021202151358@fatcity.com>

My experience yesterday was that dropping an index and trying to rebuild the same index failed ( even after coalescing the tablespace) since we need to wait for SMON to clean up the extents to make them available. I don't know how we make SMON process to coalesce the free space faster enough( or immediately after we delete from the table or dropping an index ).

Any ideas?

-----Original Message-----
Sent: Monday, December 02, 2002 3:49 PM
To: Multiple recipients of list ORACLE-L

Okay, I just heard back from the developer. It was definitely not using either a Direct load or and Append hint. Just a regular insert.

Any more ideas?

-----Original Message-----
Sent: Friday, November 29, 2002 1:39 PM
To: Multiple recipients of list ORACLE-L

Did you insert using direct path ?
If so the insert inserts after the highwater mark. The highwater mark is not reinitialized after deletes. So maybe that's why the insert failed.  

• "Miller, Jay" <JayMiller_at_TDWaterhouse.com> a écrit : > Okay, I can't figure this one out. Earlier this
  > week I got an ORA-1653:
  > unable to extend table on a really big table.
  > However this was just after I
  > had deleted over 2 million rows in the table and we
  > were only inserting
  > 30,000.
  >
  > After reanalyzing the table I saw the following
  > stats in DBA_TABLES:
  >
  > num_freelist_blocks: 2266966
  > avg_space_freelist_blocks: 3895
  >
  > Unless I'm misreading this I should have had over
  > 8Gig available for
  > inserts.
  >
  > We tried the insert again and got the same error so
  > I added a datafile and
  > it went through (using about 40Meg of space in the
  > new datafile).
  >
  > Why isn't it making use of the existing blocks on
  > the freelist?
  >
  > Oracle 8.1.7.2
  > Solaris 2.6
  > PCTFREE = 10
  > PCTUSED = 75
  > Block Size = 4K
  >
  >
  > Jay Miller
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Miller, Jay
  > INET: JayMiller_at_TDWaterhouse.com
  >
  > Fat City Network Services -- 858-538-5051
  > http://www.fatcity.com
  > San Diego, California -- Mailing list and web
  > hosting services
  >
  

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (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).
  >
  >
  

  ---

Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant stephane_paquette_at_yahoo.com

---

Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Stephane=20Paquette?=
  INET: stephane_paquette_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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: <Govind.Arumugam_at_alltel.com
  INET: Govind.Arumugam_at_alltel.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).

Received on Mon Dec 02 2002 - 17:13:58 CST