SQL Developer: Database Export Wizard to export DDL and Data as DML

Feb 27 2010

Sometimes, you would want to generate a script that you would like to run repeatedly in many environments for many objects. In such situations, use of Database Export wizard helps you generate DDL and DML (INSERT) script; – you can select object types, specific objects and filter out or restrict the data exported.

Step 2: Types to Export – You can select the type of objects you want to explore.

Step 3: Specify Objects – You can select the specific object that you need to export.

Step 4: Specify Data – You can apply conditions to data by using a select or filter criteria.

Step 5: Export Summary – At this stage you can review and verify the information list on the summary screen. Once you hit Finish, the SQL script is generated and loaded into SQL Worksheet, ready for you to run

on any schema.

Everything looks great, simple and most importantly the tool is free of cost – sample script generated by the wizard is below:

The DDL is not error free, to illustrate the issue, I selected only table T1 to export from my schema. If you run the sample script above as-is, you will receive an error “ORA-00955: name is already used by an existing object ”. It is because, the database wizard (SQL Developer Version 2.1.0.63, Build MAIN-63.73 ). simply produces one section of DDL statements for constraints and another set for indexes within the script file.

You will have to do a clean-up to make the script error free.

Update: October 24, 2011

I ran this test again on a newer version of SQL Developer (Version 3.0.04 Build MAIN-04.34 ). You will notice that the script generated by this version places the Indexes section prior to Constraints, which eliminates the above-mentioned error—ORA-00955: name is already used by an existing object —and you do not have perform a clean-up.