Solved

oracle script to create ddl for existing views

Posted on 2013-06-05
18
606 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 250 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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 13

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 13

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 250 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

623 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