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?

  • 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. Mar 11, 2014 at 23:57
  • As Justin says, I imagine you need referential integ. Do you know the data?
    – Philᵀᴹ
    Mar 12, 2014 at 0:34
  • Yes, I know the data. It must be referential. The exported data should be valid with all the relations. Mar 12, 2014 at 0:51

1 Answer 1


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


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.

  • 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.
    – rtbf
    Mar 12, 2014 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, 2014 at 13:51
  • But I have more than 1500 tables in my database and the data is very complex and huge. Mar 13, 2014 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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