How to Export DDL Script from Oracle Schema

Contents

• Introduction
• Task
• Prerequisites
• Compare Oracle schemas
• Export Oracle schema DDL
• Conclusion

Introduction

When working with Oracle, you frequently have to export Oracle schemas from one server to another. For this, you should have a reliable tool that answers all your needs. dbForge Schema Compare for Oracle is the tool that can compare database structures, export Oracle schemas, generate DDLs, etc. That's why it is an ideal tool for Oracle database comparison and synchronization.

This article will explain how to export Oracle schema with the help of dbForge Schema Compare for Oracle.

Task

Export Oracle schema without data to an empty Oracle schema on another Oracle server using dbForge Schema Compare for Oracle.

Prerequisites

Before you export Oracle schema, you should create an empty schema on the selected server. This can be also done using dbForge Schema Compare.

1. Open dbForge Schema Compare for Oracle.
2. Select File → New SQL from the application main menu.
   Schema Compare for Oracle: Create New SQL Document
   
   
3. Choose the needed connection in the Connect to Server dialog box that appeared or create a new connection using the New Connection button.

   Note that you should connect as SYSTEM user to perform the needed actions.

   Schema Compare for Oracle: Create New Connection
   
   
4. In the newly created SQL document type the following code:

             CREATE USER clone_hr_dev IDENTIFIED BY pass;
             GRANT CONNECT TO clone_hr_dev;
             GRANT RESOURCE TO clone_hr_dev;

5. Press the Execute button on the SQL toolbar to execute the script.
   Schema Compare for Oracle: Execute SQL Script
   
   

Compare Oracle schemas

To proceed, you should compare the schema you want to export and the empty schema. For this, you should:

1. Go to the Start page and click New Schema Comparison to open the comparison wizard.
2. Choose the needed Source and Target connections and schemas. Source should be the schema you are going to export, and Target - the empty schema you've created in the Prerequisites section. For the detailed description of this step, refer to the Diff Between Two Oracle Schemas: How to See It article.
3. Select File → New SQL from the application main menu.
   Schema Compare for Oracle: Open Synchronization Wizard
   
   

Export Oracle schema DDL

Before you export Oracle schema, you should have a DDL script for synchronization. Follow the steps below to generate one:

1. After the comparison, you will get a comparison document.
   Schema Compare for Oracle: Comparison Document
   
   
2. To generate a DDL script for synchronization, click Synchronize on the Comparison toolbar. Schema Synchronization Wizard will open.
   Schema Compare for Oracle: Invoke Schema Sync Wizard
   
   
3. If you want, you can learn more about adjusting Schema Synchronization Wizard settings from the How to Synchronize Oracle Schemas article.
   Schema Compare for Oracle: Set Sync Options
   
   
4. After synchronization is completed, the generated DDL script will be opened in a new SQL document.
5. Now you are ready to export Oracle schema DDL. For this, you should press the Execute button on the SQL toolbar. When script execution is completed, you can check the results.
   Schema Compare for Oracle: Execute SQL Script
   
   
6. To make the script universal, you can add the piece of code specified in the prerequisites section in the beginning of the generated script and save it by clicking File → Save in the main menu.
   Schema Compare for Oracle: Save Sync Script
   
   

Conclusion

After studying this article, you will be able to export Oracle schema without data to another database server using dbForge Schema Compare for Oracle. With the help of these simple steps, you can manage to complete such task easily and without performing extra actions.