Question

ORA-01653: unable to extend table xxx by xxx

Asked by: vachooho

How to fix this?

ORA-01653: unable to extend table xxx by xxx

tablespace has 12 Gb free space
table next extent is 100K
alter tablespace xxx coalesce doesnt help

any other ideas?

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2003-08-14 at 16:12:59ID20710175
Tags

extend

,

unable

,

table

Topic

Oracle Product Info

Participating Experts
7
Points
100
Comments
16

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Tablespace fragmentation
    Hello, How will I come to know if the database/tablespace is fragmented. And what are other possible ways to defragment the tablespace other than 'alter tablespace coalesce' Please help Juno
  2. tablespace extents problem
    When I run a report in crystal i get a database dll error if i run the same report in sql it returns that it cannot allocate 29 extents in tablespace 10. I do not have a tablespace 10 all my tablespaces are named how do i resolve
  3. unable to create initial extent in target tablespace?
    hi all, from my earlier question (which is now closed) i have been trying to move a huge table to a new tablespace (so that i can drop the old one thereby freeing a datafile). however i get an "oracle 01658 - unable to create INITIAL extent for segment in tablespace RE...
  4. Adding extent vs datafile
    When there is an extent error (i.e. extents 505 reached) and tablespace is ~80% full, what would be the recommended solution? One says adding more data files, but the other says alter storage to i.e. 1010. How do you determine the way you go for?
  5. tablespace with extends
    Hi, I got an interview question regarding to whether it's good or bad when a tablespace with 32 extends? Now, I think it should be bad and should be well designed with a much bigger space, right? Thank you.
  6. alter tablespace tablespace_name coalesce
    Hi, Can i use safely "ALTER TABLESPACE tablespace_name COALESCE" to free up unused space in any tablespace ( like ununsed extents). Is there any way to find if a tablespace needs to be COALESCED to free up space before we go ahead adding space to tablespace...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: mightycountPosted on 2003-08-14 at 19:56:54ID: 9151858

Try doing

ALTER DATABASE XXX DATAFILE 'filename' AUTOEXTEND ON

helped me awhile back..

 

by: vachoohoPosted on 2003-08-15 at 11:05:05ID: 9161524

I can not  - file size is max allowed by OS size.
ok - lets rephrase question - how to compress file so it will have all empty blocks contiguous?

 

by: MathiasMagnussonPosted on 2003-08-16 at 09:57:14ID: 9165985

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

What does that show for your tablespace?

alter table abc move tablespace def;

where abc is the name of the table and def is a tablepsace that is large enlugh to hold the table. If you do that with all your tables/indexes in the ts and then move them back, you'll have reclaimed space. If you know some tables with large delete activity, try just that one and it may be enough.

 

by: bmoshierPosted on 2003-08-18 at 08:59:48ID: 9174440

The immediate issue is the ORA-01653 and the second issue is hte amount of free space (and still needing to extend).

Why don't you add another data file to get rid of the ORA-01653.

As for the free space, MathiasMagnusson's suggestion sounds good, if you can afford the downtime.  I suggest you startup the database in restricted mode OR insure the applicaiton using the tables/indexes in the tablepspace are not running.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

 

by: vachoohoPosted on 2003-08-18 at 10:42:07ID: 9175110

I NEED ONLINE SOLUTION - DATABASE CAN NOT BE OFFLINE FOR LONG TIME - AS FOR MOVE TABLESPACE AND EXPORT/IMPORT ACTIVITIES

 

by: vachoohoPosted on 2003-08-18 at 10:42:40ID: 9175112

ANOTHER QUESTION:
COULD THIS SITUATION PREVENTED FROM THE BEGINING?

 

by: mszacikPosted on 2003-08-18 at 14:52:53ID: 9176905

1. Verify that the next extent size for the table is indeed 100k.  If you allocated as

storage (initial 100k next 100k pctincrease 50)  or some other pctincrease other than 0

then your next extent grows to 150 then 225 etc.  If your table has many extents the next extent might be larger than you expect.

2. Yes this could have been prevented.  Use Locally managed tablespaces with uniform extent sizes.  If you are on a pre-8i version of Oracle you don't have LMT.  Let me know and I can summarize an Oracle white paper on how to simulate LMT and put a link to the paper.

 

by: vachoohoPosted on 2003-08-18 at 15:54:18ID: 9177213

extent size is ok - there is no pctincrease specified - it is 0

I found that tablespace is "managed in the dictionary"
What will I gain switching it to managed localy - could you give me a link (if I dont find it sooner)

 

by: mszacikPosted on 2003-08-18 at 16:12:55ID: 9177293

I cut this from Metalink.  I prefer the uniform extent size option.  That way, you can make your extent size 1m, then every allocation in the tablespace is 1m so every free chunk is a multiple of 1m.


A Locally Managed Tablespace is a tablespace that manages its own extents  maintaining a bitmap in each datafile to keep track of the free or used status  of blocks in that datafile. Each bit in the bitmap corresponds to a block or  a group of blocks. When the extents are allocated or freed for reuse, Oracle  changes the bitmap values to show the new status of the blocks. These changes  do not generate rollback information because they do not update tables in the  data dictionary (except for tablespace quota information), unlike the default  method of Dictionary - Managed Tablespaces.  

** Reduced recursive space management  
** Reduced contention on data dictionary tables  
** No rollback generated  
** No coalescing required

Advantages


 - Because locally managed tablespaces do not record free space in data    dictionary, it reduces contention on these tables.  
 - Local management of extents automatically tracks adjacent free space,    eliminating the need to coalesce free extents.
 - Avoids recursive space management operations, which can occur in    dictionary-managed tablespaces if consuming or releasing space in an    extent results in another operation that consumes or releases space in    a rollback segment or data dictionary table.  
 - Sizes of extents that are managed locally can be determined automatically    by the system. Alternatively, all extents can have the same size in a    locally managed tablespace.  
 - Changes to the extent bitmaps do not generate rollback information    because they do not update tables in the data dictionary (except for    special cases such as tablespace quota information).  
 - Reduced fragmentation
 

 

by: vachoohoPosted on 2003-08-18 at 16:16:09ID: 9177307

how this impact performance?

 

by: bmoshierPosted on 2003-08-18 at 18:56:36ID: 9178011

LMT (Locally Managed Tablespaces) improve performance (for one because to allocate/deallocate an extent the system no longer has to touch the data dictionary).

BTW, Oracle stated direction is to only support LMT in the future.

LMT are the only way to go in Oracle 9i!

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

 

by: gsmom94121Posted on 2003-08-21 at 11:07:07ID: 9197449

This is from Metalink, it addresses the immediate issue:

Error:  ORA-01653
Text: unable to extend table %s.%s by %s in tablespace %s  
-------------------------------------------------------------------------------
Cause:  Failed to allocate an extent for table segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
        files to the tablespace indicated.
 
 
Explanation:
------------
This error does not necessarily indicate whether or not you have enough space  
in the tablespace, it merely indicates that Oracle could not find a large enough area of free
contiguous space in which to fit the next extent.
 
 
Diagnostic Steps:
-----------------
1. In order to see the free space available for a particular tablespace, you must
   use the view DBA_FREE_SPACE.  Within this view, each record represents one
   fragment of space. How the view DBA_FREE_SPACE can be used to determine  
   the space available in the database is described in:
   [NOTE:121259.1] Using DBA_FREE_SPACE  
 
2. The DBA_TABLES view describes the size of next extent (NEXT_EXTENT) and the  
   percentage increase (PCT_INCREASE) for all tables in the database.  
   The "next_extent" size is the size of extent that is trying to be allocated (and for  
   which you have the error).  
     
   When the extent is allocated :  
            next_extent = next_extent * (1 + (pct_increase/100))
 
   Algorythm to allocate extent for segment is described in the Concept Guide
   Chapter : Data Blocks, Extents, and Segments - How Extents Are Allocated
 
3. Look to see if any users have the tablespace in question as their temporary tablespace.
   This can be checked by looking at DBA_USERS (TEMPORARY_TABLESPACE).
 
Possible solutions:
-------------------
- Manually Coalesce Adjacent Free Extents
       ALTER TABLESPACE <tablespace name> COALESCE;
   The extents must be adjacent to each other for this to work.
 
- Add a Datafile:  
        ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file name>'  
        SIZE <integer> <k|m>;  
 
- Resize the Datafile:  
        ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k|m>;  
 
- Enable autoextend:  
       ALTER DATABASE DATAFILE ?<full path and file name>? AUTOEXTEND ON  
       MAXSIZE UNLIMITED;
 
- Defragment the Tablespace:  
 
- Lower "next_extent" and/or "pct_increase" size:
        ALTER <segment_type> <segment_name> STORAGE ( next <integer> <k|m>  
        pctincrease <integer>);  
 
- If the tablespace is being used as a temporary tablespace, temporary segments may
  be still holding the space.

As far as preventing this error in the future, consider using Oracle SAFE (Simple Algorythm for Fragmentation Elimination)
go to http://otn.oracle.com/ultrasearch/wwws_otn/searchotn.jsp and search for this article for complete instructions:
"How to Stop Defragmenting and Start Living"


 

by: vachoohoPosted on 2003-08-21 at 11:18:55ID: 9197559

- Defragment the Tablespace:  

how?

 

by: gsmom94121Posted on 2003-09-02 at 10:47:48ID: 9268810

First:
Run this query (from Metalink) to be sure that the tablespace needs to be defragmented (Use SQL Plus)

select substr(ts.name, 1,10) TableSpace,
to_char(f.file#,990) "file #",
tf.blocks blocks,
sum(f.length) free,
to_char(count(*),9990) frags,
max(f.length) bigst,
to_char(min(f.length),999990) smllst,
round(avg(f.length)) avg,
to_char(sum(decode(sign(f.length-5), -1, f.length,0)),99990) dead
from sys.fet$ f, sys.file$ tf, sys.ts$ ts
where ts.ts# = f.ts#
and ts.ts# = tf.ts#
group by ts.name, f.file#, tf.blocks;

Before you defrag try coalescing free space in the Tablespace
(The extents must be adjacent to each other for this to work) Use sql plus

ALTER tablespace XYZ  COALESCE;

To defrag:
(1)export using owner mode, compress = Y.
(2)drop the tablespace.
(3)create the tablespace.
(4)import  



 

by: binghuPosted on 2004-01-23 at 14:35:36ID: 10188347

another easy one to aschieve this with downtime

1. Add a new data file
2. create a new_table with the same structure as the original one
3. put the table into read-only mode
4. export the data to the new table
5. replace the odl table new new one
6. get rid of unsued table space if necessary

 

by: HenkaPosted on 2004-02-29 at 22:31:17ID: 10484212

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept gsmom94121's comment as answer.

Please leave any comments here within the next four days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...