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?

You got the exact table definition, i.e., the name of the fields, their type and length. How it was created is irrelevant to Oracle. It doesn't matter if it was created from a query, if the command was written all caps or if it was written using lower case.. — pablomatico, Mar 08 '18 at 06:44
Wrong question. The right question is "**can** I get this?" The answer is NO. The CREATE TABLE statement you get from GET_DDL does not exist in the database; it is created by that function, based on the information in the data dictionary (which includes table and column names, the order and the data type of columns, etc.) - **not** CREATE TABLE statements. — , Mar 08 '18 at 06:47
Everything that could be created with `CREATE OR REPLACE ... ` statement can have its definition extracted as a whole. For the objects, such as tables - this is not possible. — g00dy, Mar 08 '18 at 08:04

smnbbrv · Answer 1 · 2018-03-08T07:17:37.887

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.

How to get exact table definition in PlSql

1 Answers1