Question 1
-------------
Oracle 10g how to extract the data from DB?

Any link?

Any client software needed?

Is it as easy as Mysql database or MS SQL [ Just Click on Extract database to script... ] Then load back to the new database...

The script will create the schema and insert the data into the database....

Question 2
------------

Let say the database too huge 100G, can I extract a portion of the data?
Let say I extract 80% from the existing database...

How can I do this?

Thank you...

Last time I had to do that, I used TOAD, from http://www.toadsoft.com But I don't remember the procedure, it was a bit complicated. What toad did, in fact, was to create a sql script that you could have replayed elsewhere later. And yes, you could export specific tables too, so it would allow you to fragment the data. I only did this once, and it was almost 2 years ago, so I don't remember the procedure involved.

Originally Posted by fsloke

Let say the database too huge 100G, can I extract a portion of the data?
Let say I extract 80% from the existing database...

If there's such a thing as too much data for Oracle 10 G to manage, there's no way you can hope to fit it in a text file (aka script). Not even worth considering.

Scripts were great in the 1840s. Times have moved on. Anyone who's trying to do ETL by passing data around in scripts shouldn't be working with databases. Sorry.

Use something like Oracle Loader, or even Microsoft's SSIS or DTS. If you only have 100 rows it won't be such a big deal, but when you start talking about big data sets, it's the only way you'll succeed at this task you're trying to accomplish.

More detail for my case 2:
Let say the database server have 160 G hard disc, 120G are being used.
When I want to copy the data out from the database, I use external harddisc which is 60G.

It is no possible to extract 120G into 60G harddisc...

That why I ask this question...

Thank you

The database server uses an optimized binary format to persist the data, with no wasted space. Extra space in your data forces the server to work much harder to keep up with the disc IO, which is one of the slowest things a computer can do.

A text file with not only the values, but also the INSERT INTO {TABLE} ( {COLUMNS} ) VALUES ( {ACTUAL VALUES} ). This is going to require far more space than the database itself.

Your best chance is Oracle Loader with 2 direct connections, or making a full backup with compression.

Checkout the EXP and IMP commands. Not sure if you have a Unix, Linux or Windows platform. With EXP (export), you can export a table, list of tables, entire schema or the entire database to a single file, with or without the actual data in the tables. Conversley, you then use IMP (import) to load the export into another database. As long as you are moving lateral or forward (i.e. 9i to 10g or 10g to 10g), this is supported. These commands are super fast. You should try a single, large table and check it out, and note the file size and duration of the export. You should be able to extrapulate a rough estimate on a full export size and time needed. Also, you may wish to check out the UTL_FILE package. When implemented, this allows you to, with the help of PL/SQL, write directly to a local oracle filesystem (Oracle calls them "Directories"). You could then conceivably use PL/SQL to insert the data into the tables. IMO, this would be the 2nd choice and should be done only after IMPorting the entire database first. Note that EXP can be set to export only the object (no data) as well. This would establish a duplicate database. I've used this method to create TEST and STAGING environments.

If you want to extract a very small but referentially intact portion of the data from your DB, then http://jailer.sourceforge.net/ may help you.