DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Please help.. imp results weird

  1. #1
    Join Date
    Nov 2000
    Posts
    205
    I ran an export of my production system. On of my tablespaces was 1.5gb. I just created an new QA db. The tablespace are named exactly the same. Most of the application tablespaces are defined as locally managed (the same in Prod and QA). Problem is when I import the data, the 1.5Gb tablespace grows weirdly. While monitoring it was running out, even after adding space to 4Gb, it filled up. I have no idea what to do.

    Please help urgently..

    This is my par file:
    userid=fndryr0200 file=fndry0421.dmp log=imp0424.log full=y commit=y rows=y buffer=60000

    Thanks, in advance.
    Nirasha


  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Try to use this parameter
    compress=y


  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Check pctincrease of tablespaces it must not be more then 1%

  4. #4
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    INDEXES

    Hi, 25th Arpil 2001 20:44 hrs chennai

    The order in which you import is very important.If a table with a FKey has a referential check on the table with the primary key and if the FK tables is imported first then all rows which ref the PK would have been imported will be rejected if the constraints have been enabled.For a full DB this would not have a problem.

    Break the export into multiple streams. For example, have five export scripts to export five different users (or large tables). Then import them in parallel. This works very well for me.

    Consider disabling indexes and rebuilding them later.

    Check up the log file size.?and let me know that.

    Cheers

    padmam



    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  5. #5
    Join Date
    Nov 2000
    Posts
    205
    Thanks for the replies.

    compress=y will only work for exports, and I was hoping to use my present export dump file, since I wanted to avoid another downtime.

    I checked all the tablespace.. They are local so no chance of % increase. Except for the temp, users, tools, system etc the rest are locally managed. The RBS is 50% (but that is the same on prod).

    I am just going to try one table at at time, since I have run out of options. If anyone has any ideas, I would appreciate it.

    Thanks,
    Nirasha

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What is your localy managed tablespace extents allocation defined in both databases? AUTOALOCATE or UNIFORM? If UNIFORM, which size? WHAT is the MINIMUM EXTENT definiton for this tablespace on both databases?What is the default storage definition for this tablespace on both database?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2000
    Posts
    205
    UNIFORM and size 10240 K on both

    Here are the DDL's extracted from both:

    PRODUCTION:

    Create tablespace TXNDATA02LM
    DataFile
    '/u04/oradata/fndry_p/txndata02lm_01.dbf' Size 2097152000 AutoExtend Off
    LOGGING
    ONLINE
    PERMANENT
    Extent Management Local Uniform Size 10485760

    QA SYSTEM:
    Create tablespace TXNDATA02LM
    DataFile
    '/u03/oradata/tiger/txndata02lm_01.dbf' Size 2097152000 AutoExtend Off
    LOGGING
    ONLINE
    PERMANENT
    Extent Management Local Uniform Size 10485760
    /

    Thanks for the help. Any more ideas?
    Nirasha

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    There could be the catch in the COMPRESS=Y option. When you say compress=Y and do an export, you import would have the space allocated using the default and the initial extent size would be the full ext size of your export table. There by you would loose all the table level space allocation set up.

    This could be the reason why you ended up seeing a huge amount of space requirement.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Nov 2000
    Posts
    205
    I did a default export. I think that uses a compress=y. Do you suggest that I use compress=y and export again?

    Thanks,
    Nirasha

  10. #10
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    By default COMPRESS=Y. I would like you to do the export using COMPRESS=N. And then using that exp.dmp file try the import. It would then be fine as the old one.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


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