Search code examples
sqloracleddldbms-metadata

Get VIEW ddl using query

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.

Solution

  • 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>);