Sunday, November 16, 2008

How to take backup of dblinks & synonym etc

For Dropping objects :
select 'drop 'object_type,owner'.' object_name';' from dba_objects where owner='BPA2' and object_type!='INDEX';
select 'drop 'object_type,owner'.' object_name' cascade constraint;' from dba_objects where owner='BPA2' and object_type!='INDEX';
Taking the backup of Synonyms :
select 'create synonym 'synonym_name' for 'table_owner'.'table_name'@'db_link'; ' Create_Synonym_Script from dba_synonyms where owner=upper('&owner');
Taking the backup of Database links :
select 'conn 'owner'/&&'owner chr(10)'create database link 'rtrim(ltrim(db_link))' ''connect to 'rtrim(ltrim(username))' identified by 'rtrim(ltrim(password))' using '''''rtrim(ltrim(b.host))''''';' from dba_db_links A, sys.link$ B where A.db_link = B.name and B.ctime = A.created and owner like '&Owner' order by owner;


DB link :
select 'conn 'owner'/&&'owner chr(10)'create database link 'rtrim(ltrim(db_link))' ''connect to 'rtrim(ltrim(username))' identified by 'rtrim(ltrim(password))' using '''''rtrim(ltrim(b.host))''''';' from dba_db_links A, sys.link$ B where A.db_link = B.name and B.ctime = A.created and owner like '&Owner' order by owner;

Synonym:
select 'create synonym 'synonym_name' for 'table_owner'.'table_name'@'db_link'; '
Create_Synonym_Script from dba_synonyms where owner=upper('&owner');
select count(*),object_type,status from user_objects group by object_type,status;

No comments: