I have table emp and I want to create a duplicate table from the emp table. I tried the below query, which is copying all the table columns, data and the not null constraints.

create table emp_backup as select * from emp.

Is there any way to duplicate a table along with all constraints?

Here is the base table:

create table emp
empno number Primary key,
ename varchar2(50),
deptno number,
constraint emp_deptno_fk foreign key(deptno) references dept(deptno)

I need to create a table emp_backup from the emp along with emp_deptno_fk constraint.


1 Answer 1


You may try to obtain the source table's DDL using:

select dbms_metadata.get_ddl( 'TABLE', 'SCHEMA_NAME', 'DATABASE_NAME' ) from dual;

Select it into a variable, replace table name to one you need (maybe edit or remove something else) and execute.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.