You might be able to get the data into Oracle in a number of ways.
- The Oracle command sqlldr to load the data from an input file
- The Oracle command imp if the file format is compatible with Oracle exports (usually written with Oracle command exp)
- Use the file as a table directly from the database. You would use CREATE DIRECTORY, however this requires elevated privileges and is a risk because the file is left in plain sight. You could mitigate against this by subsequently running a CREATE TABLE new_table AS SELECT * FROM my_temporary_table ; and then dropping the plain file on disk.
- If the file is not too large, read the file use a shell script and run a set of commands:-
- CREATE TABLE with the correct structure
- INSERT statements for each record of the input file
- CREATE INDEX if appropriate
You might get away with writing to/reading from a pipe rather than a regular file. The command
mknod p /path/to/file will create the pipe. It's a special type of file that is a sort of buffer for data. If you then start your importing process (one of the first two options) reading it and then in another session run the export to it, you might not only save disk space but you will be doing both in parallel so you will save on the elapse time, i.e. 30 minutes to export plus 40 minutes to import (harder work for the database) might become 42 minutes for both together.
Of course this is dependant on the volume of data, disk structure (contention) and CPU load to a small extent however if these databases are on two separate servers, you can extend this trick even more. Consider the process actually requires three steps totalling 90 minutes:-
- Export - 30 minutes
- File transfer - 20 minutes
- Import - 40 minutes
You can:-
- Create pipe for export
- Create pipe for import
- Start import
- Start propagation process (I've used a dd & ssh pipeline but there are other options)
- Start export
You might find that this all runs in 40 minutes because there is less disk contention on the import side. Of course, it's just theory and is entirely dependant on what hardware is in play and what contention there actually is at the time.
Robin