sinonimos

 DROP  PUBLIC  SYNONYM tabla_sinonimo1;
 DROP  PUBLIC  SYNONYM tabla_sinonimo2;
 DROP  PUBLIC  SYNONYM tabla_sinonimo3;
 DROP  PUBLIC  SYNONYM tabla_sinonimo4;
COMMIT;

 CREATE   PUBLIC  SYNONYM tabla_sinonimo1   FOR esquema1.tabla1;
 CREATE   PUBLIC  SYNONYM tabla_sinonimo2   FOR esquema1.tabla2;
COMMIT:
-------------------------

Pueden estar mal generados sin embargo:


SELECT ' *

 FROM ALL_TABLES WHERE OWNER in ('esquema1','esquema2', 'esquema3','esquema4', 'esquema5','esquema6')
 AND  TABLE_NAME='tabla1'



 select *
from DBA_synonyms where
--TABLE_OWNER in ('esquema1','esquema6', 'esquema2','esquema3', 'esquema4','esquema5')
-- AND
TABLE_NAME='tab'le1


--- LOS BUSCAMOS COMO TABLAS

-- PARA VER SU ESQUEMA



SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'table1' UNION
SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'table2' UNION
SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'table3' UNION
SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'table4' UNION
SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'table5' UNION
SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'table6' UNION
SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'table7' UNION
SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'table8' UNION
ORDER BY 2,1
-------------------------------------------------
ALTER TABLE esquema1.tabla1MOVE TABLESPACE tbspace1;


--------------------------------


SELECT * FROM dba_objects  WHERE status = 'INVALID'


SELECT DISTINCT owner  FROM all_synonyms;

SELECT *  FROM all_synonyms;


-------------------------------


select s.TABLE_OWNER, count(*)Cantidad from all_synonyms s
where not exists(select 1 from all_objects o where s.TABLE_OWNER=o.OWNER and s.TABLE_NAME=o.OBJECT_NAME )
and owner not in ('SYS','SYSTEM')
group by s.TABLE_OWNER;


SELECT *  FROM USER_synonyms;




SELECT sy.OWNER, sy.SYNONYM_NAME, sy.TABLE_OWNER, sy.TABLE_NAME, ta.TABLESPACE_NAME
FROM user_tables ta, all_synonyms sy
WHERE ta.TABLE_NAME = sy.TABLE_NAME


SELECT sy.SYNONYM_NAME, sy.TABLE_OWNER, sy.TABLE_NAME, ta.TABLESPACE_NAME
FROM user_tables ta, user_synonyms sy
WHERE ta.TABLE_NAME = sy.TABLE_NAME


SELECT * FROM dba_synonyms



SELECT * FROM ALL_TABLES;



--- ==============================
--- &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&



SELECT    'CREATE PUBLIC SYNONYM '
       || synonym_name
       || ' FOR '
       || table_owner
       || '.'
       || table_name
       || ';'
  FROM dba_synonyms
WHERE   TABLE_OWNER = 'esquema1' OR TABLE_OWNER = 'esquema2' OR  TABLE_OWNER = 'esquema3'
OR TABLE_OWNER = 'esquema4'  OR TABLE_OWNER = 'esquema5' OR   TABLE_OWNER = 'esquema6'



select 'CREATE SYNONYM '||SYNONYM_NAME||' FOR '||TABLE_OWNER||'.'||TABLE_NAME||';'
from DBA_synonyms where TABLE_OWNER in ('esquema1','esquema2');



En cada esquema de origen en la BD de origen ejecutar las siguientes querys:

Sinónimos:

select 'CREATE SYNONYM '||SYNONYM_NAME||' FOR '||TABLE_OWNER||'.'||TABLE_NAME||';' from user_synonyms where TABLE_OWNER in ('UN_ESQUEMA','OTRO_ESQUEMA');

Privilegios:
select 'GRANT '||PRIVILEGE||' ON '||TABLE_NAME||' TO '||GRANTEE||';' from all_tab_privs_made where GRANTOR='MI_ESQUEMA' and GRANTEE='OTROS_ESQUEMAS';

Secuencias:
select 'CREATE SEQUENCE '||sequence_name||' START WITH '||to_char(last_number+1)||';' from user_sequences order by sequence_name;


replace public synonym suppliers
for app.suppliers;

drop [public] synonym [schema .] synonym_name [force];


CREATE PUBLIC SYNONYM WCTG_REG FOR CB_CVP_001.WCTG_REG;

Drop PUBLIC  SYNONYM  WCTG_REG;
           ROLLBACK;

select ' DROP  PUBLIC  SYNONYM '||SYNONYM_NAME||';'
from DBA_synonyms where TABLE_OWNER in ('esquema1','esquema2', 'esquema3','esquema4', 'esquema5'   );


select ' CREATE   PUBLIC  SYNONYM '||SYNONYM_NAME||' FOR '||TABLE_OWNER||'.'||TABLE_NAME||';'

from DBA_synonyms where TABLE_OWNER in ('esquema1');


select ' CREATE   PUBLIC  SYNONYM '||SYNONYM_NAME||' FOR '||TABLE_OWNER||'.'||TABLE_NAME||';'

from DBA_synonyms where TABLE_OWNER in ('esquema1','esquema2', 'esquema4','esquema5', 'esquema6'   );


--  WHERE   TABLE_OWNER = 'esquema1' OR TABLE_OWNER = 'esquema2' OR  TABLE_OWNER = 'esquema3'
-- OR TABLE_OWNER = 'esquema4'  OR TABLE_OWNER = 'esquema5' OR   TABLE_OWNER = 'esquema6'



SELECT * from DBA_synonyms;

-- REPLACE  PUBLIC SYNONYM tabla1 FOR esquema1.tabla1;


SELECT * FROM dba_objects   --WHERE OBJECT_NAME = 'tabla1' AND  OBJECT_TYPE ='SYNONYM'

 where status = 'INVALID'