How to Extract and Load Data from Oracle Using FastReader

This Oracle tutorial walks you step by step through the process of high-volume data transfer from an Oracle database.

1. First, export the schemas using the exp tool located on the ROWS=N parameter. On this level, all objects like PL/SQL, Views, Sequences, Table definitions, and Object privileges are on non-data. There will be one exp file for every schema exported. FastReader allows you to export the schema definitions such as tables, sequences, constraints, triggers, and scripts. You should use FastReader to export data from large tables.

2. Next, use FastReader or a native exp utility to export the data from the small code tables. The native exp utility should be used with the tables parameter. Only the small tables should be extracted. You should have one exp file for every exported schema.

3. Now, you will need to set up the destination database. You will need to include all the same global roles, users, user privileges, tablespaces, and system triggers.

4. The schemas will then be imported using imp. FastReader will allow you to export the schema definitions such as tables, constraints, sequences, and triggers. These definitions will be created on the destination database.

5. Now you need to create global object privileges and object synonyms. These specific definitions will not be exported during the schema export

6. After doing that, load the data from the tables that were extracted by FastReader into the database. FastReader will use the existing loaders for each database vendor. FastReader will then create the control files for every supported target database vendor. Before all the constraints and triggers are loaded, you should disable to option for direct load. You can also disable or enable constraints on target database with the FastReader Configurator.

7. If you used the materialized views with the refresh on commit option, you should disable and refresh this option later. If you want better control of the process, you should drop and recreated the indexes after the load. If the indexes are still there, Oracle will build them.

8. Now you need to recreate the indexes on the large loaded tables. After doing this, you will need to enable constraints and triggers on the large tables.

9. Use the Complete Refresh option to recreate and rebuild materialized views. After that, recreate indexes on the materialized views. You should have successfully extracted and loaded data from Oracle.