15

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.

Mithun Khatri
  • 636
  • 3
  • 9
  • 22
  • 4
    Simply replace `TABLE` with `VIEW` and `Table_Name` with a `View_Name` in your `dbms_metadata.get_ddl()` call. – Nick Krasnov May 07 '14 at 11:53
  • just be aware that text of view will/could be different from originally submitted. – vav May 07 '14 at 14:30
  • @NicholasKrasnov I tried the same. but its giving below error ORA-31603: object "prs_talent_assessment_vw" of type VIEW not found in schema "oradba" ORA-06512: at "SYS.DBMS_METADATA", line 5088 ORA-06512: at "SYS.DBMS_METADATA", line 7589 ORA-06512: at line 1 31603. 00000 - "object \"%s\" of type %s not found in schema \"%s\"" *Cause: The specified object was not found in the database. *Action: Correct the object specification and try the call again. But prs_talent_assessment_vw is present in db and oradba can access it. – Mithun Khatri May 08 '14 at 06:49

2 Answers2

34

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>);
Stephan
  • 41,764
  • 65
  • 238
  • 329
Sra1
  • 620
  • 1
  • 7
  • 10
1

For materialized views use:

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MView_name','Schema_Name') 
  from dual;

See all supported object types here: DBMS_METADATA: Object Types

Alexander Vladykin
  • 11
  • 2