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?

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 CaveMar 11 '14 at 23:57

As Justin says, I imagine you need referential integ. Do you know the data?
–
PhilMar 12 '14 at 0:34

Yes, I know the data. It must be referential. The exported data should be valid with all the relations.
–
user1614862Mar 12 '14 at 0:51

1 Answer
1

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.
–
FranekMar 12 '14 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.
–
sjkMar 12 '14 at 13:51

But I have more than 1500 tables in my database and the data is very complex and huge.
–
user1614862Mar 13 '14 at 19:17