Creating a table from the existing table along with the constraints in oracle

oracle

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.

Best Answer

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.

Related Question