Reverse engineer the DDL

Here is how to reverse engineer the DDL commands. I've found this pretty useful sometimes. For example, in case you want to recreate the db and you want all the tablespace creation scrips. Instead of doin' it from scratch you can use the package, GET_METADA.GET_DDL to get the generate the script for you and then you can use these scripts to create the tablespaces once the db is recreated.

For example, to reverse engineer the tablespace creation script,

sql > set long 900000

sql > select DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') from dual;

For example, if i want to get the tablespace creation script for the tablespace users, i use

sql > select DBMS_METADATA.GET_DDL('TABLESPACE','USERS') from dual;

and i set long as 900000 before doin' this, otherwise my output is truncated and i can't see the whole script.

To reverse engineer the table creation script,

select DBMS_METADATA.GET_DDL('TABLE','table_name') from dual;

You can do this to get the DDL of all the objects in the database.

Hope this helps.

-- Vish.