Cloning-precaution-Script

set head off
set lines 200
set pages 9999
col owner for a20
col db_link for a30
col username for a15
col host for a40
col created for a12

spool db_details.lst

select * from global_name;
select * from dba_db_links;
select name from v$controlfile;
select member from v$logfile;
select file_name from dba_data_files;
select file_name from dba_temp_files;

spool off

spool create_db_links.lst

select 'create DATABASE LINK '||owner#||'.'||NAME|| ' connect to '|| userid || ' identified by '|| password || ' using '||''''|| host ||''''||'; ' FROM sys.link$ order by owner#;

select username,user_id from dba_users where user_id in (select distinct owner# from link$);

spool off

spool alter_user.lst

select ' alter user '||username||' identified by values ' || chr(39)||password||chr(39) || ';' from dba_users;

spool off


Set verify off
Set space 0
set feedback off;
set echo off;
set pages 1000;
set lines 150;

spool create_synonym.sql

Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR '||table_owner||'.'||table_name||'@'||db_link||';' from dba_synonyms where db_link is not null and table_owner is not null;

Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR '||table_name||'@'||db_link||';' from dba_synonyms where db_link is not null and table_owner is null;

spool off;

Spool profile.sql

select ' alter user '||username||' profile '||PROFILE||';' from dba_users;

spool off