Re: Dblink in Oracle10g

On Tue, Nov 18, 2008 at 7:54 AM, David Ballester <ballester.david_at_gmail.com>wrote:

>
> from 9i you can use dbms_metadata.get_ddl to retrive the 'command' to
> create the object
>
> *DBMS_METADATA*.*GET_DDL*('OBJECT_TYPE','OBJECT_NAME',OWNER) from dual;
>
> For example
>
> set long 200000
> select dbms_metadata.get_ddl('DB_LINK','PRODLINK',SCHEMA) from dual;
>
>

Nice, I didn't know this worked for db_links.

You can get them all from the DD this way:

• standard dbms_metadata setup begin

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',TRUE);

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', TRUE); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',TRUE);

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',TRUE);

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRUE);

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',TRUE); end;
/

set linesize 200 long 1000
col db_link_ddl format a200
set pagesize 0 head off feed off

spool _db_link_ddl.sql

select DBMS_METADATA.GET_DDL('DB_LINK',db_link,OWNER) db_link_ddl from dba_db_links
/

spool off

set head on feed on

--
http://www.freelists.org/webpage/oracle-l