Part 8/10: Migration Tools and Methods for Small Amount of Data

This is part 7 of our blog series about migrating Oracle databases to Oracle Cloud. In this part we will introduce the following migration tools and methods mostly suitable for moving a small amount of data, a limited number of objects, and data stored in flat files:

• SQL Developer Database Copy Utility
• SQL Developer Cart Utility
• SQL Developer Drag and Drop Option
• SQL Developer Export and Import Wizard
• SQL*Loader
• DBMS_CLOUD COPY_DATA & CREATE_EXTERNAL_TABLE Procedures
• DB Link & Create Table As Statement (CTAS)

After introducing Data Guard and Golden Gate in part 4, Data Pump in part 5, RMAN in part 6, and multitenant architecture in part 7, we are completing our long journey with the following migration methods:

19. SQL Developer Database Copy Utility

Oracle SQL Developer's Database Copy utility enables you to migrate your data in a very simplified way by using a graphical wizard taking you step by step through the migration options. You can use this utility to copy objects, schemas, tablespaces, and filter data using WHERE clauses. You have the options to replace existing objects on the target database or truncate the tables before copying.

The migration steps consist of:

1. In SQL Developer, create a connection to both your source and target databases.
2. Click on the "Tools" menu and select "Database Copy..." to open the wizard.
3. Select your source and destination connections created in step 1. Select your preferred copy, DDL, and data options. Click Next.
4. If needed, limit the object types to be migrated, e.g. exclude indexes. Click Next.
5. Select the schemas to be migrated and click Next. Create the schemas on the destination if not already exist.
6. Click on "Lookup" to explore the schema objects. Select the objects to be migrated, and click Next.
7. Specify a WHERE clause if needed and click Next.
8. Review the summary and click Finish to start the migration process.
9. Review the generated log file and the data on your target database.

For detailed steps and more screenshots have a look at:

• Using SQL Developer Database Copy Utility at Oracle Help Center.
• Database Copy using Oracle SQL Developer at Oracle doc.

20. SQL Developer Cart Utility

SQL Developer's Cart utility enables you to create a cart into which you add selected objects to be loaded into a target database. This method is useful when you need to create a subset of objects from one or more schemas on the source database and deploy them on the target.

The migration steps consist of:

1. In SQL Developer, create a connection to both your source and target databases.
2. Click on the "View" menu and select "Cart" to open the Cart window.
3. Drag and drop the database objects from your on-premise database into the Cart window.
4. For each table, enable the Data checkbox if you want to include the data. Specify a WHERE clause if needed.
5. Click on the Copy icon at the top right of the Cart window to deploy the Cart. The "Copy Objects" window opens.
6. In the "Copy Objects" window, select the destination connection, and choose if you want to replace the existing objects on the target database or truncate the tables before copying. Click Apply.
7. Review the generated log file and the data on your target database.

For detailed steps and more screenshots have a look at:

• Using SQL Developer Cart Utility at Oracle Help Center.

21. SQL Developer Drag and Drop Option

Did you know? You can just drag and drop your database objects to copy them from one database into another? It is as easy as that. This method is suitable to migrate a limited number of objects and to consolidate objects from different schemas or databases into one target database.

The migration steps consist of:

1. In SQL Developer, create a connection to both your source and target databases.
2. Select one or more database objects in the source connection.
3. Drag and drop the selected objects to the destination location in the connections tree of the target database. You can also drag an entire node, such as "tables", and it will drag all the tables and their related objects, such as triggers, indexes, and constraints, to your target database. The "Copy To Oracle" dialog box opens.
4. In the "Copy To Oracle" dialog box, select your preferred options for copy DDL and copy data. Click OK.
5. Review the generated log file and the data on your target database.

For detailed steps and more screenshots have a look at:

• Using SQL Developer Drag and Drop Utility at Oracle Help Center.

22. SQL Developer Export and Import Wizard

Using the Database Export Wizard you can export your data using one of the many available formats like CSV, XML, JSON, or a .sql file containing the appropriate SQL commands.

The migration steps consist of:

1. In SQL Developer, create a connection to both your source and target databases.
2. Click on the "Tools" menu and select "Database Export.." to open the Export Wizard.
3. Select your source database and data file format. Click Next.
4. Select your options in the following steps and click Finish to export your data.
5. On your destination connection, navigate to your target table, right mouse click, and choose "Import Data..." to open the Import Wizard.
6. Select your options in the following steps and click Finish to import your data.

For detailed steps and more screenshots have a look at:

• Importing and Exporting using the Oracle SQL Developer at Oracle doc.

23. SQL*Loader

SQL*Loader enables you to load data from an external file into a table in the Oracle database. It supports many file formats such as CSV, tab-delimited, and pipe-delimited files. Thanks to the powerful data parsing engine, there is very little limitation on the format of the data in the data file.

As an input, you have a control file that controls the behavior of SQL*Loader and one or more data files containing your data. The output of SQL*Loader is a log file, a bad file if there are rejected records, potentially a discard file, and for sure the data inserted into the target database.

For detailed steps and code examples have a look at:

• SQL*Loader at Oracle doc.

24. DBMS_CLOUD COPY_DATA & CREATE_EXTERNAL_TABLE Procedures

Oracle Autonomous Database includes the new DBMS_CLOUD PL/SQL package that allows you to create credentials to get access to Object Storage, copy files from and to Object Storage, list files in a specific directory, and much more.

The COPY_DATA procedure loads data into existing Autonomous Database tables from files on Object Storage.

For detailed steps and code examples have a look at:

• Create Credentials and Copy Data into an Existing Table at Oracle doc.

CREATE_EXTERNAL_TABLE procedure creates an external table in your Autonomous Database. You can run queries on the external data from your Autonomous Database while the data reside on the more cost-effective Object Storage.

For detailed steps and code examples have a look at:

• Query External Data at Oracle doc.

The package DBMS_CLOUD supports loading files from the following cloud services:

• Oracle Cloud Infrastructure Object Storage.
• Azure Blob Storage.
• Amazon S3 and Amazon S3-Compatible, including Google Cloud Storage and Wasabi Hot Cloud Storage.

25. DB Link & Create Table As Statement (CTAS)

Using the CREATE TABLE AS SELECT SQL statement you can create a new table populated with data returned by a specified SELECT statement. The table used in the SELECT statement can be on the same or a remote database.

CREATE TABLE local_table AS SELECT * FROM remote_table@database_link;

Keep in mind that indexes and constraints (except NOT NULL) will not be copied to the target table. You can use PARALLEL and NOLOGGING parameters for faster performance.

It is also possible to insert data into an existing table by query data from a remote table.

INSERT INTO local_table SELECT * FROM remote_table@database_link;

For examples have a look at:

• CTAS at Oracle FAQs.

Conclusion

SQL Developer provides the Database Copy, Cart, Export and Import graphical wizards to assist you migrating specific objects or even whole schemas in a very simplified way without dealing with command-line tools and SQL statements. SQL developer can be used to connect to both Oracle Database Cloud Service (DBCS) and Oracle Autonomous Database and use them as the source or target databases in the migration process.

You can copy your data from flat files into an Oracle Database by using SQL*Loader or DBMS_CLOUD package. It is also possible to keep the data on flat files on cheaper external storage and access them through the database SQL engine by using external tables.

By extracting the data and inserting it into a new database, we have the full flexibility to migrate tables and schemas between different database release versions, different architectures, and different endian formats.

Next Blog

Part 9/10: Migrating Oracle Databases from AWS to Oracle Cloud.

If you have any comments or thoughts, you are very welcome to leave a comment and start a discussion. Thank you!