Re: Export Oracle View

On Feb 6, 10:18 am, gazzag <gar..._at_jamms.org> wrote:
> On 6 Feb, 14:51, l..._at_tech-trans.com wrote:
>
> > Hi!
>
> >   Anyone have experience to export Oracle view individually?
>
> > Ming
>
> What do you mean "export" a view?  If you want to see the view
> definition, DBA_VIEWS can help:
>
> E.g:
>
> SQL> set long 10000
> SQL> SELECT TEXT
> 2  FROM DBA_VIEWS
> 3  WHERE OWNER='<schema_name>' AND VIEW_NAME='view_name';
>
> Dependent on Oracle version, you could use the DBMS_METADATA package:
>
> select dbms_metadata.get_ddl('VIEW','<view_name>', '<schema>') from
> dual;
>
> HTH
>
> -g

What version of Oracle?

The traditional Oracle export utility, exp, does not have an export veiw only feature. The newer expdp utility does have DDL and object specific export options.

Depending on your version the dbms_metadata package can be used to extract the view DDL.

You can write SQL to build view source via selects against Oracle rdbms dictionary views using dba_views as mentioned but be advised that the text in dba_views does not correctly produce the view column list names for all views. To do that you have to read the view column names from dba_tab_columns.

HTH -- Mark D Powell -- Received on Wed Feb 06 2008 - 09:54:04 CST