Oracle how to copy a table

In this Oracle tutorial, we will learn how to copy a table in oracle databases 19c and 21c. Also, we will cover these topics.

• Oracle how to copy a table
• Oracle how to copy a table without data
• Oracle how to copy a table with data
• Oracle fastest way to copy a table
• Oracle how to copy a table from one schema to another

Oracle how to copy a table

Copy a table in oracle means duplicating the columns and values in a new table. This new table can be inside the same schema or a different schema.

A table can be copied due to following reasons in the oracle database:

• for creating a backup of the table
• Replicating the table structure
• Copying table from Test database to Production database or vice a versa.

There are three ways to copy a table in oracle databases 21c and 19c:

1. Copy table using As Select statement in oracle database.
2. Copy table using Oracle SQL Developer Tool (GUI)
3. Copy table using SQL Plus (CLI)

With this, we have covered the overview of how to copy tables in oracle databases 21c and 19c. In the upcoming sections, we will discuss all of these methods in detail with an example.

Also, check:  How to create table in Oracle

Oracle how to copy a table without data

In the previous section, we have learned three ways of copying a table in the oracle database. In this section, we will learn to copy a table without data using those 3 methods.

We have created two databases ProductionDB and TestDB

Copy table using As Select statement in oracle database

Using As Select statement in the oracle database new table can be created without data. It is the most common and quickest way of copying tables in oracle database 21c.

Oracle As Select statement allows the selection of a few or all columns from the source table. You can copy tables from different schemas with or without data values.

A few limitations of As Select statement are:

• Source and destiny tables must be inside the same user or database.
• It does not replicate the triggers, sequences, indexes, few constraints, materialized view, etc from the source table to destination table.

By adding a where 1=0 condition you can copy the table without data in oracle database 21c. In the

Example:

In the below example, we have created a Books table inside the bookstore schema. Using this table we are going to create a new table inside the same username.

SELECT * FROM BOOKSTORE.BOOKS;

In the below script, a new table is created with the name OnlyColumns using another table in the Bookstore schema. Where 1=0 is the statement that does not copy the data.

CREATE TABLE OnlyColumns AS 
    (SELECT * FROM BOOKSTORE.BOOKS where 1=0);

In the below output, you can see that BOOK_ID, BOOK_NAME, BOOK_AUTHOR, etc, columns have been created from another table.

Read: Oracle Create Index

Oracle Copy table using SQL Developer tool

Oracle SQL developer tool allows performing oracle queries using a graphical user interface and command line.

In this section, we will learn how to copy the table using the graphical user interface in the SQL developer tool. We will discuss the command line statement in the next section.

This method allows copying views, constraints, indexes, and other objects added to the source table. Also, you can copy data from tables for different users.

Follow these steps to copy the table using the SQL developer tool:

Step 1) Click on the tools and select Database Copy on the SQL developer tool.

Step 2) Select source and destination connections from the dropdown. Log in to the database in case you are not seeing the preferred database in the dropdown.
Also, uncheck or deselect the Copy Data check box and then click on the Next button.

Step 3) Oracle Object Types wizard allows to copy the views, triggers, constraints, types, sequences, etc. into a new table. You deselect the objects that you don’t want to copy.

This method is the perfect substitute for As Select statement in the oracle database.

Step 4) If you don’t want to make any changes then click on the Finish button.

Copying data will start, It may take some time depending upon the size of the data. Once completed, the table will be created in the destination schema.

Oracle Copy table using SQL Plus

SQL Plus provides a Copy function or statement using which table in the source connection can be copied in the destination table.

We are going to do the same thing we did in the previous section – Oracle Copy table using SQL Developer tool but this time using the command line.

Below is the syntax to copy the Oracle table using SQL Plus:

COPY FROM <db> TO <db> 
<opt> <table> { (<cols>) } 
USING <sel>

Let’s understand the abbreviation and parameters used in the syntax:

• <db> : database string, e.g., hr/your_password@d:chicago-mktg
• <opt> : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
• <table>: name of the destination table
• <cols> : a comma-separated list of destination column aliases
• <sel> : any valid SQL SELECT statement

Also, check: Alter Table Add Column Oracle

Oracle how to copy a table with data

In the previous section, we have learned how to copy a table without data in oracle databases 21c and 19c. We showed three ways of copying a table there.

Similar three ways we will demonstrate here as well but this time data will also be copied with the table in oracle. Those three ways to copy a table with data are:

• Oracle copy table using As Select
• Oracle Copy table using the SQL Developer tool

Oracle copy table using As Select

Oracle AS SELECT statement allows copying the data from any table or schema within the same user. You can choose all or a few table columns using As select in the oracle database.

In the below example, we have copied the table with data from the Books table in the BookStore schema and created a new table with the name ‘newtable‘ in the current schema.

CREATE TABLE OnlyColumns AS 
    (SELECT * FROM BOOKSTORE.BOOKS where 1=0);

The below output shows, the name table is created with the data. Compare this output with the output in the previous section for the AS SELECT statement to strike the difference.

Read: Oracle Install sample schemas 21c

Oracle Copy table using the SQL Developer tool

SQL developer tool is a free tool that allows running huge oracle queries. Using the SQL developer tool we can perform oracle operations using a Graphical user interface (GUI).

In the above section on – Oracle how to copy a table without data, we have explained step by step to copy a table using the SQL developer tool but without data.

In the second step, we have deselected the check box which says copy data. By checking that checkbox you can copy the table with data in oracle databases 21c and 19c on the SQL developer tool.

Read: Oracle add primary key

Oracle fastest way to copy a table

Oracle provides AS SELECT statement using which table can be copied quickly in oracle database 21c and 19c.

Features of As Select statement in oracle:

• Copy table with and without data.
• Allows to select columns to be copied.
• table can be created from different schemas.

Limitations of As Select statement in oracle:

• Table can be copied from different schemas but inside the same user.
• Do not replicate the objects of the source table like views, constraints, indexes, etc.
• Do not provide remote support.

View section – Oracle how to copy a table without data of this blog to see the example.

Read: How to convert rows into columns in Oracle

Oracle how to copy a table from one database to another

In oracle, each database is an object created inside a user. There are 3 ways of copying a table from one database to another database.

• As Select statement
• SQL Developer tool
• SQL Plus

We have explained to each one of them in the section – Oracle how to copy a table without data from this blog.

Read: Oracle Stored Procedure

Oracle how to copy a table from one schema to another

Schema is the additional partition created inside the oracle database. This partition holds the information of tables and views.

In this section, we will learn how to copy a table from one schema to another schema in oracle databases 21c and 19c.

There are mainly 3 ways of copying a table from one schema to another. we have explained all of these methods in the above sections – Oracle how to copy a table without data.

If you want to copy the table quickly then use AS SELECT statement but the condition is the table and schemas must be inside the same user.

You may also like to read the following Oracle tutorials.

• Oracle check database status
• How to backup table in Oracle
• Oracle get database name
• Oracle Drop Database
• Connect to oracle database
• Oracle Add Row Number

In this tutorial, we have learned how to copy a table in oracle databases 19c and 21c. Also, we have covered these topics.

• Oracle how to copy a table
• Oracle how to copy a table without data
• Oracle how to copy a table with data
• Oracle fastest way to copy a table
• Oracle how to copy a table from one schema to another