Create a duplicate table in Oracle database

When working with an Oracle database, you may need to create a copy of the table to test or

develop your application properly. We have selected the best ways to accomplish different types of

table copying in Oracle and provided an accurate description of each method.

In this article, you will learn how to perform the following:

Create an exact copy of the table within the same schema

Copy only the table structure without copying data

Insert duplicate data into an already existing table

Copy a table between different database schemas

Use the GUI tool to implement any type of table copying

Copy a table with data within the same Oracle database

1. Oracle provides convenient syntax that helps implement the above-mentioned scenarios. By

means of the “CREATE TABLE … AS SELECT … ” command, you can create a duplicate table

within the same database schema. To create an exact copy of the table preserving the table structure

and all the data, execute the query as follows:

CREATE TABLE new_table_name

AS

SELECT *

FROM existing_table_name;

2. If you want to limit your data copying to specific columns, indicate the column names after

SELECT in the following way:

CREATE TABLE new_table_name

AS

SELECT column_name1,column_name2

FROM existing_table_name;

3. Provided that you have already created a table, and you want to insert the data from the existing

table into it, run the following:

INSERT INTO new_table_name

SELECT *

FROM existing_table_name;

Copy a table without data within the same Oracle database

4. In some cases, you may want to copy the table structure but leave out the data. Similarly, you can

use the “CREATE TABLE … AS SELECT … ” command, but this time you need to modify it by

adding the WHERE clause that is false and, consequently, will not select any data, for instance:

CREATE TABLE new_table_name

AS

SELECT *

FROM existing_table_name WHERE 1=5;

Copy a table from one Oracle database to another

5. To copy a table from one database schema to another one, you need to apply an SQL*Plus COPY

command. This powerful command allows you to actually copy data between different servers.

However, we will focus on copying a table between different database schemas in Oracle. The basic

syntax for the command looks as follows:

COPY FROM source_database TO target_database action -

destination_table (column_name, column_name, -

column_name ...) USING query

Let’s define the important prerequisites for using this command:

You need to have the access to the specified tables and know the valid credentials for the local and

remote databases in the FROM and/or TO clause.

You can indicate the new names for the columns in the destination table, otherwise, they will have

the same names by default.

You have to choose between four actions—REPLACE, CREATE, INSERT, or APPEND, the one

that suits your particular case.

In the USING clause, you need to write a query that will be used to specify the data for copying.

Here, you are free to use any type of SELECT.

To create a new table and then copy the data, use CREATE; to fill the created table with data, use

INSERT; to replace the created table together with its contents, use REPLACE. Besides, you can

use APPEND, which works both ways: if you have created a new table, it will fill it with data; if

you haven’t, it will first create a table and then insert the necessary data.

Let’s see an example of the COPY command that copies three columns from the source table and

copies only the rows in which the EMPLOYEE_ID value is greater than 50:

COPY FROM EMPLOYEES@DB -

REPLACE DBCOPY1 -

USING SELECT FIRST_NAME, LAST_NAME, SALARY -

FROM DETAILS_VIEW -

WHERE EMPLOYEE_ID > 50

More at https://www.devart.com/dbforge/oracle/studio/oracle-copy-table.html