Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143

    Unanswered: Why does my datafile not autoextend?

    Hi all,

    I have a tablespace which has 3 associated datafiles. One of the datafiles is set to autoextend, the others are not. Can anyone tell me why I'm still getting space errors? I thought that the datafile that is set to autoextend should do just that. The server has a stack of space, we're not even 2% full!

    Many thanks!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Operating System (OS) name & version?
    Oracle version to 4 decimal places?

    >One of the datafiles is set to autoextend, the others are not.
    Provide proof via CUT & PASTE

    >Can anyone tell me why I'm still getting space errors?
    ERROR? What Error? I do not see any error.

    >I thought that the datafile that is set to autoextend should do just that.
    It depends.

    >The server has a stack of space, we're not even 2% full!
    Provide proof via CUT & PASTE

    Post complete session showing exactly what you are doing & how Oracle responds.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Code:
    Operating System and Versions……
    
    
    Sun Microsystems Inc.   SunOS 5.10      Generic January 2005
    
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
    
    
    
    Datafile……
    
    
    ALTER TABLESPACE "DESKADM" ADD DATAFILE '/u03/app/oradata/DESKLIVE/DESKADM03.DBF' SIZE 2764800K REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M
    
    
    Error……
    
    ‘unknown error: check whether database size has reached limit’.
    
    
    Server Space……
    
    
    Filesystem             size   used  avail capacity  Mounted on
    /dev/dsk/c0t0d0s0       55G   3.1G    51G     6%    /
    /devices                 0K     0K     0K     0%    /devices
    ctfs                     0K     0K     0K     0%    /system/contract
    proc                     0K     0K     0K     0%    /proc
    mnttab                   0K     0K     0K     0%    /etc/mnttab
    swap                    19G   924K    19G     1%    /etc/svc/volatile
    objfs                    0K     0K     0K     0%    /system/object
    /usr/lib/libc/libc_hwcap1.so.1
                            55G   3.1G    51G     6%    /lib/libc.so.1
    fd                       0K     0K     0K     0%    /dev/fd
    /dev/dsk/c0t0d0s1       20G   481M    19G     3%    /var
    swap                    19G    88K    19G     1%    /tmp
    swap                    19G    28K    19G     1%    /var/run
    /dev/md/dsk/d3         224G    64M   222G     1%    /u03
    /dev/dsk/c0t0d0s5       20G   553M    19G     3%    /opt
    /dev/md/dsk/d2         224G    20G   202G    10%    /u02
    /dev/md/dsk/d1         224G    23G   199G    11%    /u01
    /dev/dsk/c0t0d0s7       20G   2.2G    17G    12%    /export/home

    Thanks.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Oracle generate errors begin with "ORA-"
    GOOGLE does not find your posted "error".
    I have no idea what you are doing or seeing.

    Good Luck solving your mystery.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    I am capable of googling for myself and have done. I work in the real world where people use applications in front of databases. My user provided me with the error they saw in the front end of their third party application, and I have provided that here. I am not in a position to add data to the database as it is a live system owned by a department, therefore I cannot generate an ORA error myself, but the fact remains, I can see in Enterprise Manager that the datafile is 99.7% full. I just want to know.....is there ever a situation where autoextend does not do what it should.

    I give up now and will go to another forum where hopefully I will find friendly and helpful people. I was looking for people with more experience than me in this particular area to use their knowledge to help. I didn't need someone to google for me or to be rude and obstructive.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    The point is that ‘unknown error: check whether database size has reached limit’. is not an Oracle error message.

    So there is most probably something else involved. The message that you showed can be caused by anything.

    Things that I could think of are:
    * maybe the script you are running
    * maybe an application error
    * maybe some third party library accessing the database
    * maybe it's an error message generated by the application caused by a different Oracle error (thus hiding the real problem)
    * maybe it's an OS error

    How do you know by that error message that it's really caused by the inability to extent the datafile?

    Does your application have a log file that could show more information?
    Is there anything in Oracle's alert log?

    am not in a position to add data to the database as it is a live system owned by a departmen
    Sure you are. Just insert some records without committing them.

    Without more information it is basically impossible to know what's going on.

  7. #7
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Polly,
    If this were a space issue with the database, an error message would have been written to your alert.log. Have you cheked that for error messages and did you find any if you have?


    Edit:{Sorry shammat. Must have been typing at the same time. I did not mean to step on your post.}
    Last edited by buckeye234; 03-16-09 at 13:50.

  8. #8
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Quote Originally Posted by Polly013
    [CODE]ALTER TABLESPACE "DESKADM" ADD DATAFILE '/u03/app/oradata/DESKLIVE/DESKADM03.DBF' SIZE 2764800K REUSE AUTOEXTEND ON NEXT 51200K MAXSIZE 32767M
    Filesystem size used avail capacity Mounted on
    /dev/md/dsk/d3 224G 64M 222G 1% /u03

    Thanks.
    Unless I am reading this wrong, something does not make sense to me. In your add datafile statement, the datafile is sized at 2.7Gb on /u03, but your server data shows that only 64m is used. Is this correct, or am I looking at this incorectly? If this is correct, can you explain the issue?

    Thanks

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What verison of the database are you using? 32 bit or 64 bit?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    Quote Originally Posted by Polly013
    I am capable of googling for myself and have done. I work in the real world where people use applications in front of databases. My user provided me with the error they saw in the front end of their third party application, and I have provided that here. I am not in a position to add data to the database as it is a live system owned by a department, therefore I cannot generate an ORA error myself, but the fact remains, I can see in Enterprise Manager that the datafile is 99.7% full. I just want to know.....is there ever a situation where autoextend does not do what it should.

    I give up now and will go to another forum where hopefully I will find friendly and helpful people. I was looking for people with more experience than me in this particular area to use their knowledge to help. I didn't need someone to google for me or to be rude and obstructive.
    checklist :

    1. What is the block size of that tablespace ?

    -- If it is 8k the max size of the datafile is 32GB.

    2. Is max size of the datafile Set ?

    -- If your datafile auto extend on and it max size is set to 5GB, the datafile can not automatically extend after heating /reaching 5GB mark. set max size to unlimited.

    3. do you have enough space in the drive ?
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •