Solved

Oracle Big table dump

Posted on 2011-03-01
15
1,468 Views
Last Modified: 2012-06-27
I have a big table (125 GB) in a remote database (Oracle 9i), I want to export that table to my current database (Oracle 11g) in my dump folder, But I want to do the export part by part so that it doesnt slows the database. The table has a date column. so I would like to create a PL/SQL script which exports the data one day at a time, something like a loop. Any suggestions?

Thanks@
0
Comment
Question by:D-pk
  • 4
  • 4
  • 3
  • +2
15 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
Comment Utility
Use the QUERY option of export:

from: http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm

QUERY
Default: none

This parameter allows you to select a subset of rows from a set of tables when doing a table mode export. The value of the query parameter is a string that contains a WHERE clause for a SQL SELECT statement that will be applied to all tables (or table partitions) listed in the TABLE parameter.

For example, if user scott wants to export only those employees whose job title is SALESMAN and whose salary is less than 1600, he could do the following (this example is UNIX-based):

exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"


0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
applying a where clause means your 9i db will have to "think" more about the data.

You may find your system performance is better just letting the table dump blindly
0
 

Author Comment

by:D-pk
Comment Utility
while doing the export, is there a way to directly copy it to a remote unix box instead of the server I am working on?

Thanks@
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 167 total points
Comment Utility
run the export from the remote box


exp scott/tiger@your_9i_db TABLES=emp.....
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
BUT... the export should be 9i,  not the 11g export.

Which means you'll have to have a 9i client on the remote box where you intend to run 11g.

You'll only need it until you are done with the export, but still an extra step
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
if both boxes are unix then you can probably set up some names pipes with some rcp/ftp/??? in the middle.

I talked about them in your other question:
http://www.experts-exchange.com/Database/Oracle/Q_26848079.html?#a34981417
0
 
LVL 14

Expert Comment

by:ajexpert
Comment Utility
Is your table partitioned?

If it is, you can do exp/imp partition by partition
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:D-pk
Comment Utility
After exporting,
Should the table and the partitions be created on the target database before importing?
0
 
LVL 14

Expert Comment

by:ajexpert
Comment Utility
There are various ways you can import (even though its partition level import) into target table without creating partitions.  It might get complicated.

Lets address your scenario:

>>But I want to do the export part by part so that it doesnt slows the database

The best way is to create the partitions in target table and import it partition by parititon.

Here is oracle documentation to refer

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#sthref2235
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
The more important question here is: Partitioning isn't free.  Are licensed for it?

If so, it 'might' be the way to go.  It depends on the data and how you use it.

You should pre-create the table in the 11g database as you want it before the import.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
hi

i would consider using transportable tablespaces option in export util
0
 

Author Comment

by:D-pk
Comment Utility
I need some more time to think about the options.. Thanks everyone.. One final question, can we partition a  partially partitioned table? For ex:
The table is partitioned by date and its partitioned only from 2008, but it has data from 2005. So from 2005 to 2007 there are no partitions. Is that possible?

Thanks@
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Is that possible?

Depends on the version.  Newer versions of Oracle have a 'default' partition where rows that do not match any existing partition will reside.

I still suggest you only partition if it makes sense to do so.  Doing something 'because you can' typically ends up hurting you in the long run.
0
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 166 total points
Comment Utility
Yes, you can partition the already partition table.

See this link

http://www.oracle-base.com/articles/misc/PartitioningAnExistingTableUsingExchangePartition.php
0
 

Author Closing Comment

by:D-pk
Comment Utility
Thanks everyone..
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now