How to speed up loading production data into test database

How can I speed up the loading of production data into my test db? We want to load the test database once a week (in the weekend) with fresh data from our production database. We have about 40 Gb of data (roughly 20 Gb tables and 20 Gb indexes) in three SCHEMAS. We don't want to recreate the testdb. We do table exports and a full export without data on the proddb every night. Import into the testdb takes about 13-14 hours. Is there a way to create the indexes after the tables import completed? Is there a way to exp/imp so that indexes are created 'NOLOGGING'? Can I find a script that can generate 'create indexes'? Is there any other method of loading? Can I use SQLLoader? Both the prod and test environments are Solaris 2.6 and Oracle8i (8.1.7).

Well, that's more than one question! Best wishes. Thank you,
Urban Svanberg

Urban,

Mass loading of data is one thing that has always plagued DBAs who wish the whole process would go faster. Each version of Oracle has brought forth a few new features that make the data loading faster. Since you are on Oracle 8.1.7, you can try the following suggestions:

1. Import with INDEXES=N option. This will not create any indexes on import. Then import with the INDEXFILE information to get a script to create your indexes. This will not actually import any data; it just creates the script. Then run the script to create your indexes after all the data has been loaded.
2. Check out the Transportable Tablespace option! If you have your table and indexes separated into their own tablespace, then you can use the Transportable Tablespace option to plug these tablespaces into your new database. The operation that takes the longest is to FTP the files from one server to another!
3. You can use SQL*Loader and the DIRECT path method to load. Unfortunately, you have to use SQL to get the data out of your source database first.

Consider the other two options first.

For More Information

• What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our Oracle guru is waiting to answer your toughest questions.