I have a production enviroment in a Oracle 10g database which I would like to authomatically duplicate in a test enviroment. My production enviroment is just a number of tables in which I have usually indices, constrains and triggers. Is there a way to authomatically make a copy of the prod-enviroment including all tables, indices, triggers and constrains without the data in the tables? Thanks in advance for your help.

  • Look into the DataPump utility
    – user1822
    Commented Oct 13, 2014 at 12:32
  • you probably can replicate it, but notice that the idea of keeping prod and dev is to have some isolation from one another for security reasons (so your dev code won't affect your prod system).
    – Leo
    Commented Oct 13, 2014 at 12:33

2 Answers 2


This can be achieved with the expdp CONTENT flag:

expdp username/password DIRECTORY=directory_object_name \
                        DUMPFILE=dumpfile_name \
                        TABLES=table_names|TABLESPACES=tablespace_names|FULL=y \

mmmmmpie offers you a very viable way to do it. However, especially if you want to get your test environment as close to possible as prod for testing purposes, I highly recommend you look into RMAN cloning. You will end up with a block-for-block recreation of your production environment, which IMO is worth its weight in gold in regard to performance tuning (assuming the underlying tech stack is similar).


There's a 10g version of the methodology. HTH.

Your Answer

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