Keeping the creation scripts is always a good idea. When you install Oracle, do not just let it install the starter database. Instead, invoke the Database Configuration Assistant (DBCA). The DBCA has an option to store the creation scripts in a number of files. This way, you will have the creation scripts that the DBCA actually runs for you.
If you already have the database created, then you can still get these scripts. Just search the Web for "reverse engineer Oracle" and you will get a lot of hits for scripts which will reverse engineer, or create the DDL statements to create many objects, including the database, tablespaces, tables, indexes, etc. There are tools on the market, like Toad, which also have this capability. In the end, they are just querying the data dictionary to recreate these DDL statements. You can do the same.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in April 2003