Solved

oracle script to create ddl for existing views

Posted on 2013-06-05
18
604 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

732 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