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
Solved

Oracle Big table dump

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DBLINKS From 11g to 8i 3 47
make null the repeated levels 2 31
Checking for column width 8 28
Toad 12.10 Enterprise visual interface 4 20
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
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…

840 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