Exporting DDL of all views Oracle

ddloracleoracle-9i

How can I go about exporting DDL of all my schema views in single .sql file ?
I've read there is a function DBMS_METADATA.GET_DDL But I can't figure out how to use it for my case.

I know how to do this for one view (Oracle Entreprise Manager->MySchema->Views->Selecting my view->show object DDL->Save..)

I'm using SQL*Plus

Best Answer

Try this for DBMS_METADATA.GET_DLL:

set long 200000 pages 0 lines 150
spool vw.sql
select 
   dbms_metadata.GET_DDL('VIEW',u.view_name,DECODE(u.owner,'SYS','',owner)) 
from 
   all_views u
where 
   owner IN ('HR','SCOTT') 
order by owner,view_name ;
spool off

You can either include the schemas you are interested in or exclude the schemas you do not want in the "owner IN" clause.

Related Solutions

Sql-server – Required to use Oracle Performance Manager to monitor SQL Server

I think you are probably looking for Oracle Enterprise Manager or OEM as it is normally referred as, a good starting point for more information is here.
- a. Oracle database starting with 10G+ do their own data collection (Automatic workload Repository), however, you need to be licensed to access much of that data. You can still use statspack (which is free). OEM actually uses this collected performance metrics and uses them for display via its pages.
- b. Live views are available to use (the V$ and GV$) in Oracle, no experience with SQLServer, but I suspect you have similar views available.
- c. OEM can send you alerts on events and conditions that exceed user defined thresholds. This is configurable via templates or custom metrics (aka User Defined Metrics).
- d. OEM has some performance reporting built-in, but you can use OEM repository to roll your own reports, it is not very difficult (have done it in the past). OEM repository is simply a different database, the SYSMAN schema contains tables and views which expose all collected data. You can adjust retention, only constrained by available storage. It can provide your historic information as well as realtime performance for Oracle. You will have to check the plug-in for similar capability for SQLServer.
OEM can (with using optional plugins) monitor SQL Server, check this link for a brief overview. In my shop there is no SS, so I don't have first hand experience in using these plugins.
Licensing information for OEM is here, I'd definitely recommend reading this manual to you and your customers.
There could be, but once you read through the information linked above, you will get a better idea. Then a quick chat with your Oracle Sales team will yield more information as well.
Some of the extension packs for OEM are listed here.

Hope this gives you enough information to get started on it.

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

I'm not sure if I'm understanding your question properly. I created a user with the ability to create views for his own schema.

SQL> create user view_user identified by password default tablespace users quota unlimited on users;

User created.

SQL> grant create session, create table, create view to view_user;

Grant succeeded.

SQL> connect view_user
Enter password: 
Connected.
SQL> CREATE TABLE Members (ID number(10), Name varchar2(100), Street_No varchar2(100), ZIP number(5), City varchar2(100));

Table created.

SQL> CREATE VIEW MembersNamesOnly AS SELECT ID, Name FROM Members;

View created.

SQL>

Best Answer

Related Solutions

Sql-server – Required to use Oracle Performance Manager to monitor SQL Server

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

Related Question