I want to copy an Oracle schema from a production database to a validation database. Both databases are Oracle 11g R1 standard edition and need less interrupting time as I can (but I can do if it absolutely needed).

Both are installed on it's own proper server (Linux RedHat 5 Enterprise), instance names differ, tablespace and datafiles are already exist, both use archive logs.

What I want to do is select a whole schema on the production database and duplicate it to the validation database. The process must include all tables, indexes and all dependent schemas objects. There are some Lobs in some tables but no "exotical" objects (like types), no packages, no procedures, no functions, ... All objects reside in two well identified tablespaces (one for tables and datas, one for indexes).

I can't duplicate all instance. So RMan duplicate command is not an option.

Currently a process exist based on exp/imp utility but it's so slow, need database unavailability (in order to preserve data integrity) and error prone (mainly because full human's hand process). I've the feeling that use RMan Oracle utility would be fine but I need some some external advises or comments to define the right way to do it. Datapumps utilities could be a valuable second choice (I'm currently evaluating).

Thanks for your advises Thomas

1 Answer 1


Use expdp with the flashback_scn or flashback_time parameters, along with SCHEMAS= to export the schema at a consistent point in time.

This is the same as using CONSISTENT=Y in the legacy exp utility. Examples here.

If the data for the schema in question is in a tablespace of its own with no other objects from other schemas, you can use rman to do a tablespace point in time recovery.

  • expdp is a real solution, it do the job well, thanks. Could you develop the RMan tablespace point in time recovery ?
    – tdaget
    Mar 6, 2015 at 14:41

Your Answer

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

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