Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a huge oracle test database whose size is more than 100 GB. I want to export only 1% of all the data and import it into my local database to have a local replica of that application. Is it possible in oracle 11g?

share|improve this question
    
Do you want a random sampling from each table? Or do you need a referentially correct 1% sample? So, if you choose a particular child row from among the 1% of rows from the child table, must the parent row be chosen from the parent table as well? If you choose the parent do you need to choose all the child rows (and grandchild rows)? Or are you fine missing some of the child rows (i.e. you pull order 123 and some number of order lines for 123 but not all of them)? Depending on the answers, this is probably not something export and import are designed for. –  Justin Cave Mar 11 at 23:57
    
As Justin says, I imagine you need referential integ. Do you know the data? –  Phil Mar 12 at 0:34
    
Yes, I know the data. It must be referential. The exported data should be valid with all the relations. –  user1614862 Mar 12 at 0:51

1 Answer 1

You can add a where clause to each table to filter rows exported:

http://www.dba-oracle.com/t_export_where_clause_selective.htm

However your referential integrity will not be maintained by this method, because the export is done per-object and filters are applied in the same way. You will need to either be very precise in how you define each where clause, which will make the export slow, or clean up the data afterwards by deleting any orphaned rows.

I would recommend creating a new schema in the same database and copying a chunk of the data into that, while maintaining the integrity during the copy. Then export the new schema.

share|improve this answer
    
Thanks for link to great feature of expdp. I suppose good usage would be: Query=”where rownum < SOME_VALUE order by 1" to get also ordering probably by PRIMARY Key. –  Franek Mar 12 at 13:42
    
I don't think you will get stable set of rows with this query. I would recommend using MOD function (assuming that primary key is integer): Query="where mod(key,100) = 0" would get you 1% of rows. –  sjk Mar 12 at 13:51
    
But I have more than 1500 tables in my database and the data is very complex and huge. –  user1614862 Mar 13 at 19:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.