oracle script to create ddl for existing views

I have hundreds of views that need to be created in another database.
Does anyone have a script to create the ddl for views using just the owner?
LVL 1
bkreynolds48Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
SELECT DBMS_METADATA.get_ddl('VIEW', object_name, owner)
  FROM dba_objects
 WHERE object_type = 'VIEW' AND owner = 'YOUR_OWNER'
0
 
slightwv (䄆 Netminder) Commented:
You can write your own script using dbms_metadata.get_ddl or just export (expdp) views for a specific schema.

You might just be able to set up a sqlplus script to select them and spool them out.
0
 
bkreynolds48Author Commented:
I have never used dbms_metatdata -what would that query look like
can you user expdp for views only? - again - do you have an example?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
The above select gives the syntax for dbms_metadata

expdp should go something like:
expdp user/password include=view dumpfile=myviews.dmp
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
If you're using DBMS_METADATA, you could create a db link upon your target + a "remote" synonym for DBMS_METADATA@source_db and execute your script inside your target db.

BUT, I'd take the "secure" way and use expdp/impdp or exp/imp, whatever versions you're using...
0
 
bkreynolds48Author Commented:
when running this
SELECT DBMS_METADATA.get_ddl('VIEW', object_name, owner)
  FROM dba_objects
 WHERE object_type = 'VIEW' AND owner = 'YOUR_OWNER'

the lines/columns are broken - I would have to edit all of these to get this to work
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
what do you mean by "broken"?! if there are whitespaces or blank lines, you may still exec the ddl (e.g. set sqlblanklines on)...
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
in sqlplus:
set lines 32000
set trimspool on
0
 
bkreynolds48Author Commented:
example
CREATE OR REPLACE VIEW "PROD"."V_FIRST_FULL_LIS" ("PARTDID","APP
NUM",

I did the set lines 3200 and trimspool on - that didn't help
0
 
slightwv (䄆 Netminder) Commented:
oops.. missed one (and it was 32000 not 3200):

set long 1000000
0
 
bkreynolds48Author Commented:
did this
it was 32000 not 3200):

set long 1000000

still have broken lines
some of these views are huge
0
 
slightwv (䄆 Netminder) Commented:
>>some of these views are huge

export/import.

You could write some pl/sql to use dbms_metadata and utl_file to write the results to a file.

Just for grins, provide the output from:


SELECT object_name, length(DBMS_METADATA.get_ddl('VIEW', object_name, owner))
  FROM dba_objects
 WHERE object_type = 'VIEW' AND owner = 'YOUR_OWNER'
0
 
bkreynolds48Author Commented:
that file is huge and the data is proprietary
0
 
bkreynolds48Author Commented:
Thanks for your help, - I will just edit the file - need to get this done
0
 
slightwv (䄆 Netminder) Commented:
I appreciate the points but it appears you are using the select provided by sdstuber.

I feel a split should be warranted.

Please clarify.
0
 
slightwv (䄆 Netminder) Commented:
If the file is huge, editing will take a pretty long time.  Why not spend a little more time getting the script to generate exactly what you need?

One last attempt at getting good output w/o editing:
set longchunksize 10000000
0
 
sdstuberCommented:
I agree a split seems appropriate.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.