Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

How to Extract Large Data?

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
MirzaAmerBaig
Asked:
MirzaAmerBaig
  • 6
  • 5
  • 3
  • +5
1 Solution
 
myerpzCommented:
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
 
ser6398Commented:
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
 
WadhwaCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
WadhwaCommented:
sorry the above sql statment is as

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

Sameer

0
 
MirzaAmerBaigAuthor Commented:
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
 
WadhwaCommented:
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
 
ser6398Commented:
Oracle Import/Export IS an Oracle tool to move large amounts of data from one database to another.  
0
 
ramkbCommented:

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
 
WadhwaCommented:
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
 
sunrock_inCommented:
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
 
ramkbCommented:

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
 
MirzaAmerBaigAuthor Commented:
For all of you. I am on Oracle 8.1.5 on Solaris for Intel on NCR Server.

Regards
Amer Baig
0
 
RMZCommented:
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
 
MirzaAmerBaigAuthor Commented:
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
 
MirzaAmerBaigAuthor Commented:
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
 
WadhwaCommented:
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
 
MirzaAmerBaigAuthor Commented:
Rmkb's reply solved my problem. But how to give him marks.

rmkb -- Ramesh
Please tell me

Regds,
0
 
ramkbCommented:

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
 
ser6398Commented:
To give a comment points, first Reject the Answer by sunrock, then you can accept any comment as the answer.
0
 
MirzaAmerBaigAuthor Commented:
I used Transport_TableSpace. Its excellent

Thanks for solution.

Regards
Amer
0
 
upssCommented:
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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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