Solved

Oracle Big table dump

Posted on 2011-03-01
15
1,472 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
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 73

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
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.

 
LVL 73

Assisted Solution

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


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

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 76

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 76

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 76

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 166 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dataware house query tuning 9 78
case statement in where clause with not exist 15 52
How to free up undo space? 3 39
Shredding xml into an oracle 11g Database 2 43
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

776 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