We help IT Professionals succeed at work.

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

miyahira
miyahira asked
on
Medium Priority
3,135 Views
Last Modified: 2013-12-18
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
Comment
Watch Question

Senior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.