Got ORA-1691 error even when my tablespace was set up with Autoextend

Posted on 2011-04-25
Last Modified: 2013-12-18

Yesterday at night I was awaked by a telephone call, informing me that our database reported these errors:

ORA-1691: unable to extend lobsegment VAE2011EG.SYS_LOB0000149472C00004$$ by 128 in tablespace              VAE2011EG_BLOB
ORA-1691: unable to extend lobsegment VAE2011EG.SYS_LOB0000149472C00004$$ by 8192 in tablespace              VAE2011EG_BLOB

VAE2011EG_BLOB is a tablespace for storing blob data. Disk has plenty of space and datafile was set up with Autoextend, as it is shown in attached picture.  Additional space to allocate to this datafile as needed is: 10MB.

Because it was an emergency I dind't have time to investigate, and just add to the tablaspace a second datafile of 20GB with Autoextend of 20MB. Very similar as first datafile.

I suppose that yesterday at night, users wanted to load Blob files larger than 128Kb or 8192Kb, but don't understand why Oracle don't allow them to do that since datafile is setup to grow 10MB if required.Why Oracle was unable to extend lobsegment?

This morning I checked alert.log, and as long as I read it, everything was going as I explained. Attached picture shows first and second datafile, but yesterday at night was only first datafile. I didn't change it, only added second datafile.
Question by:miyahira
    LVL 34

    Accepted Solution

    Looks like you have hit a database limit.

    If I did the math correctly, your file is 32G.  The reason it will not autoextend further is that 32G is the maximum size of a database file if you are using an 8K block size.
    LVL 8

    Assisted Solution

    Yes, this is operating system limit...If OS is 32 bit, this is maximum size of any file it can support.

    If your OS is of 64 bit then this might not be case. in this case there might be possibility of maxsize of datafile is set to 32G during tablespace creation.

    But I think OS has imposed limit in your case.
    LVL 1

    Author Comment

    Thanks for your comments.

    I didn't know about max size limits of datafiles depending on block size. I'll read more about that.

    My O/S is Windows 2003 64 bits. So, I think it doesn't matter if O/S is 32 bits or 64 bits.
    LVL 34

    Expert Comment

    32 or 64 bit does not matter.  The link below should get you to the physical database limits.  The one you are interested here is titled "Database file size"

    The limit on file size is 4M blocks.  So the actual file size limit is dependent on block size.  The doc link is for 11g, but that limit hasn't changed in a long time.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now