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 CaveCommented Mar 11, 2014 at 23:57
-
As Justin says, I imagine you need referential integ. Do you know the data?– PhilᵀᴹCommented 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.– user1614862Commented Mar 12, 2014 at 0:51
Add a comment
|
1 Answer
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.
-
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.– rtbfCommented 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.– sjkCommented Mar 12, 2014 at 13:51
-
But I have more than 1500 tables in my database and the data is very complex and huge. Commented Mar 13, 2014 at 19:17