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'
