oracle, underlying table/view code, ddl, show table

Question

I am wondering what the analog of the below teradata code is in Oracle. I want to find the underlying table or view ETL code. Basically, I want to find the code that is used to create the table/view. Is there a query to do this? Please send this code. I only know how to do this in the schema browser. I am using ORacle SQL Developer, Schema Browser. Then clicked on SQL. I was looking for the underlying SQL on this table. This is the SQL code that creates the view. THis is the DDL. I just wanted to know how the view was created similarly, the analog to teradata code.

teradata
show VIEW Schema.Table
oracle
?

This Worked!!!! `select text from ALL_VIEWS where upper(view_name) like upper('TABLE');` — , Oct 30 '18 at 19:40
if you're in the admin (sys or system schema) you can use all_views instead of user_schema which works for an ordinary schema where your related view exists. So if you connect to the right schema, @Hady Lattouf 's solution works also. By the way, `upper` is superfluous provided the table name in all capital letters such like `select text from all_views where view_name = 'MY_VIEW'` — Barbaros Özhan, Oct 30 '18 at 19:49

Hady Lattouf · Answer 1 · 2018-10-30T19:12:48.590

1

For views please execute this query:

select text from user_views where view_name = 'EMP_VIEW';

For tables you can execute this query:

select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' ) from dual

edited Oct 30 '18 at 19:12

answered Oct 30 '18 at 19:08

Hady Lattouf

43
8

I tried that....didn't work :( . Any ideas? – Oct 30 '18 at 19:11
What did you get as a result? – Hady Lattouf Oct 30 '18 at 19:14
@coder1167 it should be working, did you try your view name all in capital ..? – Barbaros Özhan Oct 30 '18 at 19:15
I got NULL back in the results. I only have read only access. Could this be the issue? – Oct 30 '18 at 19:35
When you execute: select view_name from user_views can you find your view in the result? – Hady Lattouf Oct 30 '18 at 19:38

score 0 · Answer 2 · answered Oct 30 '18 at 19:11

Oracle has a package DBMS_METADATA which is can be used to exctract DDL command to create it. You can use GET_DDL function to get table definition.

SELECT DBMS_METADATA.GET_DDL( 
           object_type => 'TABLE', 
           name => 'Table' , 
           schema => 'Schema') 
FROM DUAL;

oracle, underlying table/view code, ddl, show table

2 Answers2