• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1491
  • Last Modified:

Oracle Big table dump

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
D-pk
Asked:
D-pk
  • 4
  • 4
  • 3
  • +2
3 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
D-pkAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sdstuberCommented:
run the export from the remote box


exp scott/tiger@your_9i_db TABLES=emp.....
0
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
ajexpertCommented:
Is your table partitioned?

If it is, you can do exp/imp partition by partition
0
 
D-pkAuthor Commented:
After exporting,
Should the table and the partitions be created on the target database before importing?
0
 
ajexpertCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Aaron ShiloCommented:
hi

i would consider using transportable tablespaces option in export util
0
 
D-pkAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
ajexpertCommented:
Yes, you can partition the already partition table.

See this link

http://www.oracle-base.com/articles/misc/PartitioningAnExistingTableUsingExchangePartition.php
0
 
D-pkAuthor Commented:
Thanks everyone..
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now