2

How can I go about exporting DDL of all my schema views in single .sql file ?
I've read there is a function DBMS_METADATA.GET_DDL But I can't figure out how to use it for my case.

I know how to do this for one view (Oracle Entreprise Manager->MySchema->Views->Selecting my view->show object DDL->Save..)

I'm using SQL*Plus

1

Try this for DBMS_METADATA.GET_DLL:

set long 200000 pages 0 lines 150
spool vw.sql
select 
   dbms_metadata.GET_DDL('VIEW',u.view_name,DECODE(u.owner,'SYS','',owner)) 
from 
   all_views u
where 
   owner IN ('HR','SCOTT') 
order by owner,view_name ;
spool off

You can either include the schemas you are interested in or exclude the schemas you do not want in the "owner IN" clause.

0

You can SPOOL the output into a file:

SPOOL 'C:\Temp\all_views.sql';
SELECT dbms_metadata.get_ddl('VIEW', view_name) FROM user_views;
SPOOL OFF;
0

I've found all_mviews view helpful. With name and text fields i could easily generate create statement for all necessary views without unnecessary information. I've used Java and JDBC to write to file, but i think you also can do it with spool in sqlplus.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.