Get VIEW ddl using query

sql oracle ddl dbms-metadata
Mithun Khatri picture Mithun Khatri · May 7, 2014 · Viewed 80.8k times · Source

For database re-architecture I need to get DDL of each table and view in the database(Oracle). I don't want to go to property of each table/view and get SQL out of it in SQL Developer.

I successfully got DDL for table using-

select dbms_metadata.get_ddl('TABLE','Table_name','Schema_Name') 
  from dual;

But facing problem with VIEW and MVIEW. Could anyone provide commands/keywords for elements other than table.

Also, I want to export the result in an excel file with first column as TableName and second column as DDL.

Answer

Sra1 picture Sra1 · May 7, 2014

Try the below query for view:

select text from ALL_VIEWS where upper(view_name) like upper(<view_name>);

For mviews:

select query from ALL_MVIEWS where upper(mview_name) like upper(<mview_name>);

Related questions

How can I generate (or get) a ddl script on an existing table in oracle? I have to re-create them in Hive

How can I generate a DDL script on an existing table in oracle? I am working on a project where I have to re-create some tables that are present in Oracle table into Hive.

Read More
Grant privileges for an Oracle package?

I have a package A, which uses some variables and procedures in another package B in the same schema. Now I want to move package A to a new schema. What privileges should I grant to the new schema for …

Read More
Insufficient Privileges when creating tables in Oracle SQL Developer

I have granted the user in my connection to create tables, triggers, procedures, and sequence using sql+ (grant create table to <my_user>); however, that still does not allow me to create a table in that schema showing …

Read More