I run Oracle 184.108.40.206 on HP-UX 11. Every week we export a full DB from one system and import it in full to a second system (26 GB bytes of data). It takes 26 hours to load. But to import one table of 8 GB bytes to the same sytem is taking 50 hours. My question is why would it take 26 hours for a full import and 50 hours for only one table?
My first thought is that the table and index are sized to small, and they are having to extend many, many times. Here is some SQL that will help you determine how many extents that the table and indexes have:
select OWNER, SEGMENT_NAME, SEGMENT_TYPE,
segment_name in ('YOUR_TABLE', 'INDEX_1', 'INDEX_2')
group by OWNER, SEGMENT_NAME, SEGMENT_TYPE
order by max(extent_id);
Try to size the table and indexes in one extent. The default when using exp is to compress the tables and indexes in one extent.
Another option is to create only the table, then run and import without indexes (imp system/manager table=your_table indexes=n), then create the indexes later in sqlplus after changing your sort_area_size to a very large number (eg. 50m if you have the memory) using "alter session set sort_area_size=50000000;"
Dig Deeper on Oracle database export, import and migration
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.