Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    106

    Unanswered: Unable to extend error occurs on HP-UNIX machine though there is ample free space

    Environment:
    Oracle 9.2.0.1 on HP-UNIX machine.

    We have created table space
    CREATE TABLESPACE TS_DATA
    DATAFILE '/u03/oracle/oradata/TS_DATA/TS_DATA.dbf' SIZE 25600K AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    LOGGING
    ONLINE
    SEGMENT SPACE MANAGEMENT AUTO
    /

    Now when we are importing data the dbf file grows but it stops at around 1670MB and gives the following error.

    IMP-00058: ORACLE error 1653 encountered
    ORA-01653: unable to extend table TEST_USER.EMP by 1024 in tablespace TS_DATA
    ORA-27059: skgfrsz: could not reduce file size
    HP-UX Error: 27: File too large
    Additional information: 2
    IMP-00028: partial import of previous table rolled back: 830791 rows rolled back
    About to enable constraints...
    Import terminated successfully with warnings.

    We have nearly 100GB OS disk space free allocated to this mount point.

    This problem gets solved by adding one more data file to the same tablespace but we need to know why the first data file is not extending beyond a certain size.

    This problem occured for 3 different databases.Database 1 it gave the error at 1670 MB,Database 2 gave error at 1900MB and Database 3 gave error at 2090MB.

    We are not able to understand why it stops at a particular size and gives this error.

    Pl. respond if you have come across the same situation and solved it.
    We would like to have only one data file with autoextend.

    The same setup works fine on Solaris and Windows machines but not on HP-UNIX machine.
    Sachi

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    my guess is your datafile autoallocated as much extents of 1024 until the default setting of the max estends on the datafile level which may differ from the tablespace settings

  3. #3
    Join Date
    Nov 2002
    Posts
    833
    my guess is your datafile autoallocated as much extents of 1024 until the default setting of the max estends on the datafile level which may differ from the tablespace settings

  4. #4
    Join Date
    Nov 2002
    Posts
    833
    my guess your datafile autoextended numerious times but then reached the default of max extents on the datafile level which may differ from the tablespace settings

    by default the max extents is 4096

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You might want to use the edit button in the future instead of resumitting three different versions of the same post. I am not yelling, just a suggestion.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Oct 2004
    Posts
    9
    Does this paricular file system have large file (> 2GB) support?
    In general, errors like "skgfrsz" show that something wrong is going on between Oracle processes and the operating system.

  7. #7
    Join Date
    Jan 2003
    Posts
    106

    I dont think that is the reason

    We have used the same tablespace creation scripts for 2 other environments windows and solaris and it worked fine there.We were able to import large data.It was autoallocating properly.

    But it is this OS which is giving this problem.
    Can you give more details as to how set the maximum extents.
    We had given to increase 1024k for each extend which ORACLE does.

    Then if default number of extents was 4096 then
    ( 1024k * 4096 ) / 1024k=4096MB should have been allocated right?

    But it has just allocated 2090M and stopped and this also differs for other 2 databases on the machine.

    Pl. correct me if I am wrong.
    I suspect we need some patch to be applied as I guess this is a bug with ORACLE.
    We have oracle 9.2.0.1 ,can anyone tell me whether after upgrading to oracle 9.2.0.4 this problem will be rectified.
    Sachi

  8. #8
    Join Date
    Jan 2003
    Posts
    106

    how to check the file system large file size

    parisss,can u pl. tell me how to find out whether the file system handles files of size > 2gb on HP-UNIX system.
    Sachi

  9. #9
    Join Date
    Oct 2004
    Posts
    9
    Hi sachin_mt,
    Excuse me for my late response, but I've been out of town on a business trip. I am not fluent in HP-UNIX, but the "lsfs -q <file system>" command shows on AIX the information requested under the "bf" property.

  10. #10
    Join Date
    Dec 2003
    Posts
    47
    Maybe your datafile was exceeded ulimit in Unix
    You can check parameter ulimit in the file : /etc/default/login

Posting Permissions

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