Importing Prod data to Test instance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Importing Prod data to Test instance

Hybrid View

  1. #1
    Join Date
    May 2000
    Posts
    4

    Question

    Hi

    I have to import Prod data into Tes instance. The Prod .dmp file comes up to almost 700MB.
    I do my import using imp system/password file=x.dmp fromuser=userprod touser=usertest grants=y indexes=y

    All the data is imported into data TableSpace (TS). I would have to resize to data TS to accomodate both data, and index.
    I have to run a script to move index files from data TS to index TS.
    The data TS would then have a lot of space without data and I could not resize it to smaller size as to use diskspace more efficiently.

    Is there a way, where I can do imports where the tables goes into data TS and indexes would go to index TS.

    Best regards

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Rebuild your indexes to another tablespace,

    ALTER INDEX index_name REBUILD tablesapce_name;

    Hope this helps.

    Sanjay

  3. #3
    Join Date
    May 2000
    Posts
    4
    [QUOTE][i]Originally posted by SANJAY_G [/i]
    [B]Rebuild your indexes to another tablespace,

    ALTER INDEX index_name REBUILD tablesapce_name;

    Hope this helps.

    Sanjay [/B][/QUOTE]

    Hi Sanjay

    The script that I run to move the indexes actually does what you suggest above.
    After doing this when I try to resize the TS to a smaller size I hit ORA-03297:file contains XXX blocks of data beyond resize value.

    Best regards
    /Ashiq

  4. #4
    Join Date
    Mar 2001
    Posts
    1

    Smile

    You have to create the tables and indexes to your test instance before importing the .dmp file. In creating the the tables and indexes to your new instance, specify the tablespaces for the table and indexes.

    Note : create the same index name of the orignal table and put ignore=y during import.

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Do an alter tablespace with the RESIZE option. Other wise, take the table space offline and issue the alter tablespace resize and that should solve the problem.

    Good luck,

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Feb 2001
    Posts
    123
    You could create a script to create the indexes by using
    exp parameter indexfile=filename, then export the data with the exp indexes=n parameter.

    When you import the data, indexes to enforce primary keys etc. will still be built in the data tablespace though.

    Then you could either edit the index creation script and modify the tablespace in which the index is created, or you could set the quota on the data tablespace to 0, and the default tablespace to the index tablespace for the user into which you are importing.

    HTH

    David.

  7. #7
    Join Date
    May 2000
    Posts
    4
    Hi

    I tried the suggestion by sambavan, when TS is taken offline and resize is tried on that TS, you get ora-01135 file accessed for DML/query offline.

    I will try the other suugestion one by one.

    Thanks
    Ashiq

  8. #8
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    The way I did was to copy all the datafiles , control files , redo log files , inti ora files to a new location. Change the database name in the init file. Create a new control file and mount the database with resetlogs option.
    Regards


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