• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2833
  • Last Modified:

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

Hello,

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.
Datafile.JPG
0
miyahira
Asked:
miyahira
  • 2
2 Solutions
 
johnsoneSenior Oracle DBACommented:
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.
0
 
POracleCommented:
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.
0
 
miyahiraAuthor Commented:
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.
0
 
johnsoneSenior Oracle DBACommented:
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"

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/limits002.htm#i287915

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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now