How to get exact table definition in PlSql

Question

ESU_1 is the Source table

create table ESU_1
(
  emp_id   NUMBER(10),
  emp_name VARCHAR2(100)
);

I created a table ESU_2 by using ESU_1

create table ESU_2
as
select * from ESU_1 t
where t.emp_id>20;

When I used below query to get table definition

select dbms_metadata.get_ddl('TABLE', 'ESU_2','SNAPREP') from dual;

I got this o/p

CREATE TABLE ESU_2 
(  EMP_ID NUMBER(10), 
EMP_NAME VARCHAR2(100)
); 

But I want the exact table definition that is

create table ESU_2
as
select * from ESU_1 t
where t.emp_id>20;

How can I get this?

Answer 1

When you run

create table ESU_2
as
select * from ESU_1 t
where t.emp_id>20;

internally it will check the definiton of ESU_1 and create a similar table.

create table ESU_2
(
  emp_id   NUMBER(10),
  emp_name VARCHAR2(100)
);

Then it will insert all the matching rows into the table:

insert into ESU_2 select * from ESU_1 where t.emp_id>20;

and perform a commit to pretend that the whole operation is DDL:

commit;

That is why the table definition you get is exactly what the table definition is.

Why is the original DDL not saved? Because it does not make any sense. If later on you change the content of ESU_1 or even the structure of ESU_1 the ESU_2 will not be automatically updated, so the initial query cannot create the same table as it was before.

However it makes total sense to store the DDL for views and materialized views (because materialized view in fact is a combination of a view and a table); it is stored and you can always retrieve it.