Posts Tagged ‘database links’
Moving tables between database
Today I needed to move a table from a 9i database to a 10g database. What do you suppose is the quickest way to get the move completed? The table is 59+ million rows with 2 indexes. The indexes were easy. I knew I would not move the indexes and simply rebuild them on the new database.
To move the actual table my first thought was to use the export and import utilities. Unfortunately I did not have enough local storage to export the data locally. So I mounted via NFS a storage array I already had online. I started the export with the following command:
exp tables=localizer DIRECT=Y INDEXES=N STATISTICS=NONE BUFFER=2000000
Over an hour later and I am still waiting, so I started to look at my other methods. Exporting to an NFS mount point can be painfully slow. Here are a few options I considered.
- Spool to Flat File, Load with SQL*Loader or External Tables: SQL*Plus does a reasonable job of unloading. Tom Kyte has a C utility for unloading data also.
- SQL*Plus COPY command: Sadly deprecated, and without support for all data types, but a handy tool for basic data transfers. Allows arbitrary SQL on the data selection.
- Database Links: Direct movement from one database to another. Aside from the network latency this is basically the same as reading data from your local database. If you combine database links and materialized views, then you can replicate remote data to your local database through the databae link.
- Export/Import: Sometimes mistaken for part of a backup and recovery strategy!
- Transportable Tablespaces: Closely related to Export/Import, allowing movement of entire tablespaces without the need for unloading and reloading data.
- Data Pump: Export/Import for the new millenium - and with many new features.
- Oracle Streams: Powerful functionality here. Non-trivial, but probably the modern tool of choice for the regular propagation of data around your corporate network.
In the end since this was simply a single table I went with just insert into as a select statement across database links. I still had to deal with some network latency but it was much faster than dealing with my NFS mounted storage.
