Re: table space exdtent failure
Date: 1995/05/16
Message-ID: <3p8r3r$g6m_at_grivel.une.edu.au>#1/1
Tom Campbell (campbell_at_mindspring.com) wrote:
: Given a separate tablespace named CUSTOMER of around 120m.
: The Only table in the tablespace has its storage declared as:
: STORAGE(INITIAL 2m NEXT 2m MINEXTENTS 1 MAXEXTENTS 121
: PCTINCREASE 5 FREELISTS 1 FREELIST GROUPS 1)
: Why would I get the following message after loading 305000+ records ??
: Record 305605: Rejected - Error on table CUSTOMER.
: ORA-01653: unable to extend table SYSTEM.CUSTOMER by 3162 in
: tablespace CUSTOMER
: It this point the tablespace shows
: 19122 free
: 101850 Used
: 120972 Total
: SYS.SBA_SEGMENTS shows extents as 24,max_extents as 121 and
: next_extent as 6475776..
: Why would this fail ?
: The only thing I can think of is fragmentation of some sort...
: Any ideas are most welcome...
: //
: // campbell_at_mindspring.com DON'T PANIC
: // campbell_at_peach.com
: //
: // My opinions may not represent those of my employer's
: // (They don't know I'm Logged in )
Tom,
The oracle error shows the required extent size in Oracle blocks, whereas I'm not sure what unit you used above to show available free space, since DBA_FREE_SPACE shows free extents in both bytes and Oracle blocks. If free_space does show no free extents greater than 3,000 blocks, then the tablespace is fragmented. If the table you are loading is the only one in this tablespace, the easiest method is to drop both table and tablespace, recreate the tablespace and table (and make a larger initial extent for the table), and re-load. Or, export the table as is, drop and recreate the tablespace, import, and continue loading from the last record.
Karen
Karen Payten
Database Administrator
Computing and Communication Services
University of New England, Armidale NSW AUSTRALIA Email: karen_at_metz.une.edu.au Phone: +61 67 733549
Received on Tue May 16 1995 - 00:00:00 CEST