Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle script to create ddl for existing views

Posted on 2013-06-05
18
Medium Priority
?
608 Views
Last Modified: 2013-06-05
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?
0
Comment
Question by:bkreynolds48
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
18 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39222191
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 39222201
SELECT DBMS_METADATA.get_ddl('VIEW', object_name, owner)
  FROM dba_objects
 WHERE object_type = 'VIEW' AND owner = 'YOUR_OWNER'
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39222202
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39222244
The above select gives the syntax for dbms_metadata

expdp should go something like:
expdp user/password include=view dumpfile=myviews.dmp
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39222251
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
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39222391
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
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39222404
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 39222446
in sqlplus:
set lines 32000
set trimspool on
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39222477
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39222495
oops.. missed one (and it was 32000 not 3200):

set long 1000000
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39222594
did this
it was 32000 not 3200):

set long 1000000

still have broken lines
some of these views are huge
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39222617
>>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
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39222819
that file is huge and the data is proprietary
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39222824
Thanks for your help, - I will just edit the file - need to get this done
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39222845
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39222853
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39223043
I agree a split seems appropriate.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question