How to get the source code for all the views in a particular schema?

Experts -

I am trying a get source for all the views (1000+) from a particular schema to change the db link.

can I use dbms_metadata to do this?

select dbms_metadata.get_ddl('VIEW') from dual

Toad is my second option.

LVL 18
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yes, just query the dictionary instead of dual

select dbms_metadata.get_ddl('VIEW',view_name,owner) from all_views
where owner in ('SCHEMA1','SCHEMA2','SCHEMA3')

pick whichever schemas you want for the IN list

or for just one schema

select dbms_metadata.get_ddl('VIEW',view_name,owner) from all_views
where owner =  'SCHEMA1'
I see you tagged Oracle 8i

if your database version is really that old, then you won't be able to use dbms_metadata.

In that case you'll need to query the TEXT column from all_views

select text from all_views where owner = 'SCHEMA1';

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sventhanAuthor Commented:
Thanks ;)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.