Showing results for 
Search instead for 
Did you mean: 

Migrating a Subset of Data

Mary Claire Pollard
Occasional Advisor

Migrating a Subset of Data

I am setting up a development database (Oracle 8.1.7). My schemas are identical - with the exception of storage clauses. I want to move 20% of the production data to the development server. By what means would I select 20% of the data from each table in a schema?

Any ideas are appreciated!
-Mary P.

Valued Contributor

Re: Migrating a Subset of Data


I wouldn't suggest this route unless you really know the application and data flow on all tables. presuming you know them, then you can create a dblink from dev to production and starting inserting rows. make sure to grant appropriate privs, reset sequence values if any..well, getting painful.

however there are other options which are safe and simple

export / import data


build the dev database from a hotbackup of production db.


export/import transportable tablespaces tts.

Frequent Advisor

Re: Migrating a Subset of Data

I agree with Stan.

It is extremely dificult to create sub-set database by hand.

I want 20% of the data.
So I take 20% of transaction data and 20% of vendor data and 20% of the terms table.

Typically the three chunck won't line up.

e.g. The 20% of the vendors didn't include IBM ...and IBM transactions are part of the 20% of the transaction data I took. You could 'Add' IBM to the vendor list ... but you will have to that kind of analysis for every element/table in your database.

There are tools from comapniesl like that might help. I have not used them though so I can't say .. I have only had their sales people call.

Graham Cameron_1
Honored Contributor

Re: Migrating a Subset of Data

We spend an awful lot of time and effort on subsetting our production databases to give smaller development databases and I concur with previous posters that this is a seriously non-trivial task, and needs in-depth applications knowledge.
Having said that, I am aware of a product designed to help this process, although I have no experience of it. Try

- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Honored Contributor

Re: Migrating a Subset of Data

Hi Mary,

You should create all the objects first (exp with rows=N then import) then get the data.
set heading off
set feedback off
select 'exp userid=/ owner=' || username || 'file=' || username || '.dmp rows=N &'
from all_users
where username not in ( 'SYS', 'SYSTEM', ...., 'DEVLOPMENT' );
spool off
host chmod a+x

One way to get the data would be, as mentioned by one of the posters above, create a database link and use the sqlplus copy command or direct load inserts to populate your tables in the development database - You must know your tables/data well to be able to write the SQL statement. This might be a tidious job since there may be foreign key constraints preventing you in some cases. (although you may disable these constraints before doing it)

E.g. insert /*+ append */ into t select * from t@another_place
COPY FROM user/pass@instance_1 TO user/pass@instance_2 APPEND table_1 USING select_stmt

Another method is to use Export/Import - again you must know your tables well.

$ exp userid=/ tables=t query=\"where object_id \< 5000\"

or, if I simply use a parameter file with this in it:

query="where object_id < 5000"

I can now use the single command:

exp userid=/ tables=t parfile=exp.par

Hope this helps!

Best Regards
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)