Oracle : Copying tables from a schema to another schema with data

Suppose, you want to work with some previously generated tables of a schema in Oracle database, but you don’t want to modify or lose the data of that tables. Then you have to copy the tables and work with the copied version. There are options like duplicate or copy paste etc. But I know what you want to do. Actually you want to make a copy of that tables to another schema and work with them. Here you go.

Here I’m copying the built-in HR Schema tables to another schema, and I will use this copied version for making experiments and rough uses fearlessly. I don’t want to lose or modify the data of HR Schema tables, as it will be difficult to recover.

This process is applicable for any schema. Please follow the steps given below to do this:

Step1:

I hope you have created an user already. If you don’t know what is a user, then to be simple, HR is an user. Like this HR, you can create more users. New users will have no tables. An user also holds a schema. So we can also call a user ‘a schema’.

If you already have user then skip this step and go to step 2. Otherwise to create an user:

Go to Start Menu > SQL Plus

Put your username and password to login into your database. Your username is maybe ‘SYSTEM’.

After login to Oracle Database using SQL Plus

Now write:

CREATE USER give_a_name IDENTIFIED BY give_a_password;

After you run this, user is created. Now we have to grant privileges to this user. Now write:

GRANT ALL PRVILEGES TO give_a_name;

After you run this, and if this execution is successful, then you are done.

After granting privileges to this user

Now go to the SQL editor software and create/open a Oracle connection and open this new schema of this newly created user. And follow from step2.

Step2:

In the new schema, make a new query. This query is to make new tables same as HR tables.

Here is the query:

CREATE TABLE COUNTRIES AS SELECT * FROM HR.COUNTRIES;
CREATE TABLE DEPARTMENTS AS SELECT * FROM HR.DEPARTMENTS;
CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES;
CREATE TABLE JOB_HISTORY AS SELECT * FROM HR.JOB_HISTORY;
CREATE TABLE JOBS AS SELECT * FROM HR.JOBS;
CREATE TABLE LOCATIONS AS SELECT * FROM HR.LOCATIONS;
CREATE TABLE REGIONS AS SELECT * FROM HR.REGIONS;

Step3:

Copy the queries written in step 2. Run these queries in your sql software whatever you use (i.e Navicat, PopSQL etc).

You should run all of these lines one by one (in maximum database execution software) , or at a time (some software like Navicat, it can be run at a time).

Step4:

Your are done. You have generated copies of HR tables here. Now you can make experiments with this without making harm of main HR schema tables.

Some issues:

Here in new schema, the tables are not interconnected, as you have created separately. But it’s ok, you can do almost everything with these tables.

If you don’t understand the word interconnected, I am saying about this:

Suppose you have a table named LOCATION which have a column named LOCATION_ID. Another table named EMPLOYEE also has a column named LOCATION_ID and this is inherited from LOCATION table. So this LOCATION and EMPLOYEE table are interconnected.

In this copied version, the tables won’t be interconnected, because the new copies are created separately one by one, constraints won’t be here like the main schema. But don’t worry, maximum work can be done without any kind of problem. Actually you will face less error in this copied version, because these new tables are independent to each others. If you want to understand this dependency, make some interconnected tables and try to delete the tables. :)

Hope this article was helpful. Thank you for reading…