Re: Dblink in Oracle10g

On Tue, Nov 18, 2008 at 7:54 AM, David Ballester
<ballester.david@xxxxxxxxx>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

Other related posts: