Solved

oracle script to create ddl for existing views

Posted on 2013-06-05
18
599 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
18 Comments
 
LVL 76

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 73

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
 
LVL 76

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39222446
in sqlplus:
set lines 32000
set trimspool on
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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 76

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 76

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 76

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 73

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now