?
Solved

Oracle Big table dump

Posted on 2011-03-01
15
Medium Priority
?
1,489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
15 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 668 total points
ID: 35011292
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 74

Expert Comment

by:sdstuber
ID: 35011422
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
ID: 35011433
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 668 total points
ID: 35011447
run the export from the remote box


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

Expert Comment

by:sdstuber
ID: 35011459
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35011462
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
ID: 35011474
Is your table partitioned?

If it is, you can do exp/imp partition by partition
0
 

Author Comment

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

Expert Comment

by:ajexpert
ID: 35013101
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35013230
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
ID: 35016263
hi

i would consider using transportable tablespaces option in export util
0
 

Author Comment

by:D-pk
ID: 35019671
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35019700
>>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 664 total points
ID: 35019722
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
ID: 35020064
Thanks everyone..
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

649 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