Q
Problem solve Get help with specific problems with your technologies, process and projects.

Time it takes to export databases

I run Oracle 7.3.4.5 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,
max(EXTENT_ID) extents
from dba_extents
where
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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close