[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

oracle script to create ddl for existing views

Posted on 2013-06-05
18
Medium Priority
?
619 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
  • 7
  • 6
  • 2
  • +1
17 Comments
 
LVL 78

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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 78

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 78

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 78

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 78

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 78

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 78

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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

590 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