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.
[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.
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.
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.
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.
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.