Solved

How to Extract Large Data?

Posted on 2001-08-20
21
683 Views
Last Modified: 2012-06-21
I have database running on Solaris. One table contains 63000000 records. I want to take these records out into a text file then I have to load data in another Oracle Table. Please suggest me a optimum way. I don't want to make my source database heavy.

Please help me. Thanks in advance

Regards
Amer Baig
0
Comment
Question by:MirzaAmerBaig
  • 6
  • 5
  • 3
  • +5
21 Comments
 
LVL 3

Expert Comment

by:myerpz
ID: 6406854
i would export the table using "exp"
then import the data into your target table using "imp"

go here for details on these two tools :-

http://technet.oracle.com/doc/server.804/a58244/toc.htm

hope this helps, good luck

0
 
LVL 5

Expert Comment

by:ser6398
ID: 6407138
Is the table structure exactly the same in both databases?  If so, then export/import is probably the best way to go.  If not, then you may have to import into a temporary table and then write PL/SQL code to move the data from the temporary table into the perm table.
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6407151
Hi,

You can take the help of sql*loader to load data. The advatage of sql*loader is,  it is 100 times faster as compared to exp imp. Use can use direct option to load the data. .

For loading the data through sql*loader , you have to create a text file. You can create a textfile by PL/SQL or By SQL.

Example of creating a text file from sql*plus

Let us consider you want to create a text file of scott.emp table

SQL :> define x=chr(39)||','||chr(39)
SQL :> set heading off;
SQL :> select empno||&x||ename||&x||job||chr(39) from emp;

7369','SMITH','MANAGER'
7499','ALLEN','MANAGER'
7521','WARD','MANAGER'
7566','JONES','MANAGER'
7654','MARTIN','MANAGER'
7698','BLAKE','MANAGER'
7788','SCOTT','MANAGER'
7839','KING','MANAGER'
7844','TURNER','MANAGER'
7876','ADAMS','MANAGER'
7900','JAMES','MANAGER'

Once text file is ready , you can easily load by sql*loader.

If it is difficult for you to create a text file , you can use Microsoft access to create a text file.

Make a ODBC connect string from a machine where the MS Access is. For making a connection to the server or database , u also need oracle client.

Once you configure the ODBC. You can open the blank database ,FILE->GET extenal data->link table
link the table you are interested in.
After link again click FILE->Export->save as type (*.txt)->filename -> linktablename -> save

Your text file is ready to use with sql*loader.

ftp this text file on solaris box and take the help of sql*loader to load it.
You will able to load the with in minutes.

I mostly follow this approach to load millions of records into the database.

If you do not want to take headache of this , export ,import is the only option left for you.

Hope this helps
Sameer
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6407160
sorry the above sql statment is as

SQL :> select chr(39)||empno||&x||ename||&x||job||chr(39) from emp;

Sameer

0
 

Author Comment

by:MirzaAmerBaig
ID: 6407252
But the Huge Data is a problem. Simple SQL or Export will take very long time to complete. Is there any tool by Oracle to come over this problem ???


Regards
Amer
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6407348
If you do not want to use sql*loader and export import the
other best approach for you is the use of COPY command.

sql>set copycommit 1
sql>set arraysize 1000
SQL :> copy from scott/tiger@connectstring -
                 to ?
            scott/tiger@connectstring
         create bonusnew(ename,job,empno)  -
         using  -
         select ename,job,empno from bonus;

Here bonusnew is created from bonus table by the use of copy command. Instead of create you can also use append,insert or replace

Note 1. Although you are on the server u have to make a connectstring.

Note 2. - at the end of each command is important as it is continuous command in sql*plus

I do not think any other utility will help you.

Good luck
Sameer

0
 
LVL 5

Expert Comment

by:ser6398
ID: 6407472
Oracle Import/Export IS an Oracle tool to move large amounts of data from one database to another.  
0
 
LVL 3

Accepted Solution

by:
ramkb earned 65 total points
ID: 6407813

Hi there,

Here are options for you to consider:

1) Select * from table and spool to text file.  Load data thro' SQL*Loader.  Loading is faster, but querying involves FTS (full table scan) of your 63 million rows.

2) Export/Import is reliable.  You can tune your buffers to certain extent to expedite this.  It may not be as bad as you think.

3) COPY command as suggested earlier by Sameer.  SQL*Plus performs a commit at the end of each successful COPY.  Make sure you set the SQL*Plus COPYCOMMIT n, and ARRAYSIZE variables to commit at regular intervals.

4) Direct Path Export -  Direct path Export extracts data much faster than a conventional path export. Direct path Export achieves this performance gain by reading data directly, bypassing the SQL command processing layer and saves on data copies whenever possible.  Data is read from disk into the buffer cache and rows are transferred directly to the Export client. The evaluating buffer is bypassed. The data is already in the format that Export expects, thus avoiding unnecessary data conversion.

Note that there is no corresponding Direct Import though.  You need to do a regular import.  On that note, the best way to tune import is by setting your SORT_AREA_SIZE parameter optimally.  This will yield very good performance benefits for import.

5) Transportable Tablespaces - If you have your table individually in a seperate tablespace and you are running Oracle 8i you can use this to simply plug-out and plug-in to the other database.  You will be exporting only the metadata of the tablespace you transport which takes notime.

>  Given that you want to reduce your load in the source database, i would say either of the last 2 options are better suited (if you can use TTS feature that will be the best).  The rest of the options essentially runs thro' your 63 Million rows by SQL querying which is quite a overhead.

Cheers,
Ramesh
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6407873
I think first and third and fifth option is the best

The first option is best if you create a text file by using MS-ACCESS in that case there is no sql causing full table scan. Once the text file is ready , loading from sql*load is a timeless task.

The third option is better because it us very easy to implement . You have to just type a copy command.
set copycommit and set arraysize will avoid the rollback segment overhead.

Fifth option has a restriction of differant tablespace .It is good in a way when you are transporting a data from one database to another. You cannot transport a tablespace with a different tablespace name , the tablespace name should be same . for doing this you have to drop a tablespace after exporting a tablespace. This is quite dangerous though on the same database.

Second and fouth (Export import) will not work if the table name of export import is different.

Hope this helps
Sameer





0
 
LVL 1

Expert Comment

by:sunrock_in
ID: 6408303
You can use oracle Tracnsportable tablespace option for these kind of bulk transfers. This option is availbale in
8.1.x.x.x .

The prereqs for this

Both databases should be on the same operating system.
Data block size should be the same.

Steps.

Exp from first database
transportable tablespace=y

copy the datafile in the same directory structure of 2nd database.

imp the specified tablespace.

it will take no more than 5 minutes.

for complete list check the oracle documentation on transportable tablespaces in oracle 8i.

Thanks

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

Expert Comment

by:ramkb
ID: 6408590

Hi Sunrock_in,

You should have posted this as a comment and not as an answer to this question.  

MirzaAmirBaig has never told that he is using Oracle 8i and neither of us know if Transportable Tablespaces will be a definite solution to this problem.

Thanks,
Ramesh
0
 

Author Comment

by:MirzaAmerBaig
ID: 6408634
For all of you. I am on Oracle 8.1.5 on Solaris for Intel on NCR Server.

Regards
Amer Baig
0
 
LVL 2

Expert Comment

by:RMZ
ID: 6409152
HI
if u can connect to the other oracle server u can use
insert into table1 as select * from database2.table2
-----
if u can't connect to database there 2 solution
1-export this table using
  exp user/pass tables=(table1)
then import it in new database
2-using text file
 spool c:\r.txt
SQL> select DEPT_ID ||''''||DEPT_NAME  from dept;

DEPT_ID||''''||DEPT_NAME
-----------------------------------------------------------------------
10'software
20'hardware
30'network
40'sales

SQL> select DEPT_ID ||';'||DEPT_NAME  from dept;

DEPT_ID||';'||DEPT_NAME
-----------------------------------------------------------------------
10;software
20;hardware
30;network
40;sales
then use sqlloader to import data into database
---rmz----
0
 

Author Comment

by:MirzaAmerBaig
ID: 6409246
Can u guide me How to use Pro/C code to run. I got some excellent code in Pro/C to extract huge Data. I have never run this type of Code before.

Regds
0
 

Author Comment

by:MirzaAmerBaig
ID: 6409316
Can u guide me How to use Pro/C code to run. I got some excellent code in Pro/C to extract huge Data. I have never run this type of Code before.

Regds
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6409840
Here is a third party free trial tool
http://www.benthicsoftware.com
which will help you to load a large amount of data. I personally have tried this. For me it worked well. It mey work well for you too . Give it a shot.

Sameer
0
 

Author Comment

by:MirzaAmerBaig
ID: 6417216
Rmkb's reply solved my problem. But how to give him marks.

rmkb -- Ramesh
Please tell me

Regds,
0
 
LVL 3

Expert Comment

by:ramkb
ID: 6418027

Hi,

I'll be interested in knowing which one of the solution helped?

For points, i think you can accept even a comment as an answer.  I'm not sure how.

Thanks,
Ramesh
0
 
LVL 5

Expert Comment

by:ser6398
ID: 6418094
To give a comment points, first Reject the Answer by sunrock, then you can accept any comment as the answer.
0
 

Author Comment

by:MirzaAmerBaig
ID: 6420849
I used Transport_TableSpace. Its excellent

Thanks for solution.

Regards
Amer
0
 

Expert Comment

by:upss
ID: 25696999
If it is Oracle 8 and above it could be also efficient to  extract into ASCII pipe and then load it using direct path parallel sql loader. Or take a look this example http://it.toolbox.com/wiki/index.php/Extract_and_load_data_from_Oracle
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

Title # Comments Views Activity
Number Format 1 45
Oracle SQL Syntax 8 58
Add 0 to end of Number 21 72
Deny Oracle DBAs from Connecting  "/ as sysdba" 5 41
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…
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

760 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

18 Experts available now in Live!

Get 1:1 Help Now