tbs space problem with import - help needed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: tbs space problem with import - help needed

  1. #1
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Hi All,

    The import file I got from an external is 62 MB in size.
    I have a tablespace of 1 gig in size.

    The initial extents for lot of tables is specified as 10MB.
    ( from error message)

    My import is failing because I am running out of space before the import completes. Since the fileitself is so small, it there a way to come out of this situation.

    Please help

    ( I am not able to get a new export with compress=y, and I dont have enuf space on my test DB to create a large TBS )

    Thanks
    Sridhar

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    You may want to check the NEXT setting and the PCTINCREASE on the tables, if it is the default 50% you can get into trouble. How much free space is available on the tablespace?

    Can you post the error as well.

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    You have a couple options that I can think of... some easy, some not so easy.

    First of all, You can increase the size of the tablespace. That's the easy one. But I guess you said you don't have room to do that.

    More complicated:
    Import the file with ROWS=NO. This will create the schema.
    Then extract the DDL for the tables. Modify the DDL to use a smaller initial and next extent size. Then drop and recreate the tables with the new DDL. Then reimport with ROWS=YES and IGNORE=YES. That should help!

    Good Luck!
    Jodie

  4. #4
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Then next is 10 MB as well, pct increase show 50%.
    I am dealing with an export file that I need to import. None of the tables seem to have data that can fill even 1 mb of this 10 mb. I dont think db will try to take a next extent.

    The error is something like " not able to allocate initial extent of 10MB for table accounts "




  5. #5
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Hanky,

    No of rows is a not an issue because the size of export file is just 62 MB (there is not much data here)

    I tried it, but DB is trying to take the initial extent of 10MB and then running out of space.

    I was thinking of generating a text file using show=y option, modifying this file and then run it from sqlplus. But I got the text file size of 4.5 mb, almost impossible to edit.

    Thanks
    Sridhar



  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    Are you using a parfile for this, I always do. That way you can spool a logfile for the import and view the exact error.

    You could also try to import one table and see if that works, get the IMP error number if you can. Are you sure that this user is assigned the tablespace as default, check dba_users.

    MH
    I remember when this place was cool.

  7. #7
    Join Date
    Jul 2000
    Posts
    521
    What is the error message that you get ?

    Make sure that the 1G tablespace is not fragmented.
    svk

  8. #8
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    check if you have have any block corruption in the datafiles. using dbv command .
    siva prakash
    DBA

  9. #9
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    I think Jodie has given out a good solution.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

Posting Permissions

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



Click Here to Expand Forum to Full Width