I have been told I need to create an sql script that recreates all of the tables/procedures/triggers/views/sequences, etc of my current database. Does anybody have any idea how I go about doing this? It is an oracle 10g express database.


DBMS_METADATA.GET_DDL is your friend.

And then invest some time in learning a source control tool such as SVN or GIT.


Another option is to go old school and do a no rows export via the exp command.

Setting ROWS=N in the exp parameter file will mean metadata only is exported, no actual data. You would use the imp command to restore that metadata.

Be aware exp generates a binary dump file - if you want SQL scripts use DBMS_METADATA as mentioned by Gary.


If you don't fancy getting to grips with DBMS_METADATA then you can use Oracle's free developer tool called SQL Developer which can create this script for you in a few clicks, either for the whole database, an object type, or just one object.

I'm all for learning the DBMS packages but in this case you'll be done and enjoying a hot beverage of your choice in less then the time it'll take to read up about GET_DEPENDENT_DDL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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