Can anyone tell me how I can generate the DDL for all tables, views, indexes, packages, procedures, functions, triggers, types, sequences, synonyms, grants, etc. inside an Oracle schema? Ideally, I would like to copy the rows too but that is less important.
I want to do this on a scheduled job of some kind and not manually each time, so that rules out using the wizard in SQL Developer.
Ideally, since I will be running this on several schemas that have grants and synonyms to one another, I would like to have a way to do a find/replace in the output so the schema names match whatever the names of my new schemas are going to be.
Thanks!
Best Answer
You can spool the schema out to a file via SQL*Plus and dbms_metadata package. Then replace the schema name with another one via sed. This works for Oracle 10 and higher.
Put everything in a script and run it via cron (scheduler). Exporting objects can be tricky when advanced features are used. Don't be surprised if you need to add some more exceptions to the above code.