Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

How To Copy Table Including Data, Indexes, Constraints?

Leave a Comment / SQL, TABLES / By

How To Copy Table Including Data, Indexes, Constraints?

We can create a table in oracle based on structure or data or both of another table.

First we will create a test table to check all the scenarios.

Test table setup: 
--How To Copy Table Including Data, Indexes, Constraints? : Setup

CREATE TABLE EMPLOYEES_COPY
(EMPLOYEE_ID         NUMBER(6),
 FIRST_NAME          VARCHAR2(20),
 LAST_NAME           VARCHAR2(25) CONSTRAINT LAST_NAME_NN NOT NULL,
 HIRE_DATE           DATE CONSTRAINT HIRE_DATE_NN NOT NULL,
 JOB_ID              VARCHAR2(10),
 SALARY              NUMBER(8,2),
 DEPARTMENT_ID       NUMBER(4),
 CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY(EMPLOYEE_ID),
 CONSTRAINT SALARY_CHECK CHECK(SALARY > 0)
);
--How To Copy Table Including Data, Indexes, Constraints? : Setup

INSERT INTO EMPLOYEES_COPY(EMPLOYEE_ID,
                           FIRST_NAME,
                           LAST_NAME,
                           HIRE_DATE,
                           JOB_ID,
                           SALARY,
                           DEPARTMENT_ID
                          )
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       HIRE_DATE,
       JOB_ID,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID;
Table EMPLOYEES_COPY created.

109 rows inserted.
COMMIT;

Now we will check the data dictionary of the newly created table EMPLOYEES_COPY 

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       VALIDATED, 
       INDEX_OWNER, 
       INDEX_NAME 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES_COPY';
How To Copy Table Including Data, Indexes, Constraints? : Output
SELECT * FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES_COPY';
SELECT INDEX_NAME, 
       INDEX_TYPE, 
       TABLE_OWNER, 
       TABLE_NAME, 
       TABLE_TYPE, 
       UNIQUENESS, 
       STATUS, 
       VISIBILITY, 
       INDEXING
FROM USER_INDEXES
WHERE TABLE_NAME = 'EMPLOYEES_COPY';

Creating table with the structure and data of another table:

Now we will create a copy of table EMPLOYEES_COPY

CREATE TABLE EMPLOYEES_COPY_DUP
AS
SELECT * FROM EMPLOYEES_COPY;

Checking the data dictionary for table EMPLOYEES_COPY_DUP 

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       VALIDATED, 
       INDEX_OWNER, 
       INDEX_NAME 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES_COPY_DUP';
SELECT * FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES_COPY_DUP';
SELECT INDEX_NAME, 
       INDEX_TYPE, 
       TABLE_OWNER, 
       TABLE_NAME, 
       TABLE_TYPE, 
       UNIQUENESS, 
       STATUS, 
       VISIBILITY, 
       INDEXING
FROM USER_INDEXES
WHERE TABLE_NAME = 'EMPLOYEES_COPY_DUP';

This time oracle has given system defined name to the created constraints. Also Primary key is not created.

Index is not created as the Primary key is not created.

Creating table with the structure of another table:

DROP TABLE EMPLOYEES_COPY_DUP;
CREATE TABLE EMPLOYEES_COPY_DUP
AS
SELECT * FROM EMPLOYEES_COPY
WHERE 1 = 2;

Lets check the data dictionary of newly created table EMPLOYEES_COPY_DUP 

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       VALIDATED, 
       INDEX_OWNER, 
       INDEX_NAME 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES_COPY_DUP';
SELECT * FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES_COPY_DUP';
SELECT INDEX_NAME, 
       INDEX_TYPE, 
       TABLE_OWNER, 
       TABLE_NAME, 
       TABLE_TYPE, 
       UNIQUENESS, 
       STATUS, 
       VISIBILITY, 
       INDEXING
FROM USER_INDEXES
WHERE TABLE_NAME = 'EMPLOYEES_COPY_DUP';

This time we are viewing the same result as we have seen in the previous step.

Copy table with exactly similar structure of another table including data, indexes and constraints:

There is a system defined package in Oracle DBMS_DDL. It helps us to to get the DDL script of a DB object.

Now we will extract the DDL script of test table EMPLOYEES_COPY

SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES_COPY', 'HR') DDL FROM DUAL;
--How To Copy Table Including Data, Indexes, Constraints? : Setup

CREATE TABLE HR.EMPLOYEES_COPY 
   (	EMPLOYEE_ID NUMBER(6,0), 
	FIRST_NAME VARCHAR2(20), 
	LAST_NAME VARCHAR2(25) CONSTRAINT LAST_NAME_NN NOT NULL ENABLE, 
	HIRE_DATE DATE CONSTRAINT HIRE_DATE_NN NOT NULL ENABLE, 
	JOB_ID VARCHAR2(10), 
	SALARY NUMBER(8,2), 
	DEPARTMENT_ID NUMBER(4,0), 
	CONSTRAINT SALARY_CHECK CHECK (SALARY > 0) ENABLE, 
	CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY (EMPLOYEE_ID)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE SYSAUX  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE SYSAUX

Renaming the table name, constraint name as oracle will not allow to create object with same name.

--How To Copy Table Including Data, Indexes, Constraints? : Setup

DROP TABLE EMPLOYEES_COPY_DUP;

CREATE TABLE HR.EMPLOYEES_COPY_DUP 
   (	EMPLOYEE_ID NUMBER(6,0), 
	FIRST_NAME VARCHAR2(20), 
	LAST_NAME VARCHAR2(25) CONSTRAINT LAST_NAME_NN_DUP NOT NULL ENABLE, 
	HIRE_DATE DATE CONSTRAINT HIRE_DATE_NN_DUP NOT NULL ENABLE, 
	JOB_ID VARCHAR2(10), 
	SALARY NUMBER(8,2), 
	DEPARTMENT_ID NUMBER(4,0), 
	 CONSTRAINT SALARY_CHECK_DUP CHECK (SALARY > 0) ENABLE, 
	 CONSTRAINT EMPLOYEE_ID_PK_DUP PRIMARY KEY (EMPLOYEE_ID)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE SYSAUX  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE SYSAUX

Again checking the data dictionary for table EMPLOYEES_COPY_DUP

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       VALIDATED, 
       INDEX_OWNER, 
       INDEX_NAME 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES_COPY_DUP';
SELECT * FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES_COPY_DUP';
SELECT INDEX_NAME, 
       INDEX_TYPE, 
       TABLE_OWNER, 
       TABLE_NAME, 
       TABLE_TYPE, 
       UNIQUENESS, 
       STATUS, 
       VISIBILITY, 
       INDEXING
FROM USER_INDEXES
WHERE TABLE_NAME = 'EMPLOYEES_COPY_DUP';

The structure of EMPLOYEES_COPY_DUP is now exactly same as table EMPLOYEES_COPY.

Creating the table EMPLOYEE_COPY_DUP with the exactly same structure and data.

Rename the table and constraint name while extracting the DDL script.

SELECT REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES_COPY', 'HR'), 
               '"', ''
               ), 
               'EMPLOYEES_COPY', 'EMPLOYEES_COPY_DUP'
               ),
               'EMPLOYEE_ID_PK', 'EMPLOYEE_ID_PK_DUP'
               ), 
               'AST_NAME_NN', 'AST_NAME_NN_DUP'
               ),
               'HIRE_DATE_NN', 'HIRE_DATE_NN_DUP'
               ),
               'SALARY_CHECK', 'SALARY_CHECK_DUP'
               ) DDL_STRING 
FROM DUAL;
  CREATE TABLE HR.EMPLOYEES_COPY_DUP 
   (	EMPLOYEE_ID NUMBER(6,0), 
	FIRST_NAME VARCHAR2(20), 
	LAST_NAME VARCHAR2(25) CONSTRAINT LAST_NAME_NN_DUP NOT NULL ENABLE, 
	HIRE_DATE DATE CONSTRAINT HIRE_DATE_NN_DUP NOT NULL ENABLE, 
	JOB_ID VARCHAR2(10), 
	SALARY NUMBER(8,2), 
	DEPARTMENT_ID NUMBER(4,0), 
	 CONSTRAINT SALARY_CHECK_DUP CHECK (SALARY > 0) ENABLE, 
	 CONSTRAINT EMPLOYEE_ID_PK_DUP PRIMARY KEY (EMPLOYEE_ID)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE SYSAUX  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE SYSAUX;

Now we will create a function within WITH clause of SQL. With that function we will copy the structure of EMPLOYEES_COPY table and data.

WITH
FUNCTION FUN_COPY_TABLE RETURN VARCHAR2
AS
  V_STRING VARCHAR2(32767) := NULL;
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  SELECT REPLACE(
         REPLACE(
         REPLACE(
         REPLACE(
         REPLACE(
         REPLACE(DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES_COPY', 'HR'), 
                 '"', ''
                 ), 
                 'EMPLOYEES_COPY', 'EMPLOYEES_COPY_DUP'
                 ),
                 'EMPLOYEE_ID_PK', 'EMPLOYEE_ID_PK_DUP'
                 ), 
                 'AST_NAME_NN', 'AST_NAME_NN_DUP'
                 ),
                 'HIRE_DATE_NN', 'HIRE_DATE_NN_DUP'
                 ),
                 'SALARY_CHECK', 'SALARY_CHECK_DUP'
                 ) DDL_STRING 
  INTO V_STRING
  FROM DUAL;
  --
  EXECUTE IMMEDIATE V_STRING;
  --
  EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEES_COPY_DUP(EMPLOYEE_ID,
                                                    FIRST_NAME,
                                                    LAST_NAME,
                                                    HIRE_DATE,
                                                    JOB_ID,
                                                    SALARY,
                                                    DEPARTMENT_ID
                                                   )
                      SELECT EMPLOYEE_ID,
                             FIRST_NAME,
                             LAST_NAME,
                             HIRE_DATE,
                             JOB_ID,
                             SALARY,
                             DEPARTMENT_ID
                      FROM EMPLOYEES_COPY
                      ORDER BY EMPLOYEE_ID';
  --
  COMMIT;
  --
  RETURN '0';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'ERROR -> '||SQLERRM;
END;
--
SELECT FUN_COPY_TABLE FROM DUAL;
SELECT * FROM EMPLOYEES_COPY_DUP;

RELATED TOPICS:

Author

Leave a Comment

Your email address will not be published.