basirana
asked on
objects in valid
Hi
We did a export and import of database and all public synonyms became invalid for some reason.
1. How to make all this synonyms valid?
2. Is there anything we are missing will export and import of database?
can you please help
Thanks
We did a export and import of database and all public synonyms became invalid for some reason.
1. How to make all this synonyms valid?
2. Is there anything we are missing will export and import of database?
can you please help
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
use following script
REM
REM DBAToolZ NOTE:
REM This script was obtained from DBAToolZ.com
REM It's configured to work with SQL Directory (SQLDIR).
REM SQLDIR is a utility that allows easy organization and
REM execution of SQL*Plus scripts using user-friendly menu.
REM Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM
REM File:
REM c_rebuild_synonymc_rebuild_synonyms.sql
REM
REM <SQLDIR_GRP>REVERSE</SQLDIR_GRP>
REM
REM Author:
REM Vitaliy Mogilevskiy
REM VMOGILEV
REM (vit100gain@earthlink.net)
REM
REM Purpose:
REM <SQLDIR_TXT>
REM Reverse engineers synonyms (DB LINKS are supported)
REM </SQLDIR_TXT>
REM
REM Usage:
REM c_rebuild_synonymc_rebuild_synonyms.sql
REM
REM Example:
REM c_rebuild_synonymc_rebuild_synonyms.sql
REM
REM
REM History:
REM 08-01-2001 VMOGILEV Created
REM
REM
set lines 300
set trims on
set feedback off
SELECT 'CREATE SYNONYM '||owner||'.'||synonym_name||
' FOR '||DECODE(db_link,NULL,table_owner||'.')||table_name||DECODE(db_link,NULL,';','@'||db_link||';')
FROM dba_synonyms
WHERE owner='&owner'
spool c_rebuild_synonyms.run
/
spool off
ed c_rebuild_synonyms.run
accept dummy prompt "Press Enter to run the script (CTL-C to cancel) ..."
set feedback on
--@c_rebuild_synonyms.run
yes,I'm sorry ,credits to DBA tools,bravo Vitalja,will work with following only
set lines 300
set trims on
set feedback off
SELECT 'CREATE SYNONYM '||owner||'.'||synonym_name||
' FOR '||DECODE(db_link,NULL,table_owner||'.')||table_name||DECODE(db_link,NULL,';','@'||db_link||';')
FROM dba_synonyms
WHERE owner='&owner'
spool c_rebuild_synonyms.run
/
spool off
Also, forgot to mention that Oracle 10+ will place synonyms in an invalid stage if their underlying objects (eg. a stored procedure gets recreated) get recreated or dropped.
So, if you're positive that the underlying object exists and are in a valid stage, then all you need to do is call the synonym and Oracle will do the rest (synonyms will be placed in a VALID stage again)
ASKER
If I try to select from public synonym then it giving error
ORA-01775: looping chain of synonyms
Can you please help.
Thanks
ORA-01775: looping chain of synonyms
Can you please help.
Thanks
this command returns very nice results:
SELECT 'CREATE SYNONYM '||owner||'.'||synonym_nam e||
' FOR '||DECODE(db_link,NULL,tab le_owner|| '.')||tabl e_name||DE CODE(db_li nk,NULL,'; ','@'||db_ link||';')
FROM dba_synonyms
just add
WHERE owner='&owner'
for Your user
SELECT 'CREATE SYNONYM '||owner||'.'||synonym_nam
' FOR '||DECODE(db_link,NULL,tab
FROM dba_synonyms
just add
WHERE owner='&owner'
for Your user
oh,of course You can run the script above in the export database,create the spool there and run the actual creation in new
run this query (replace <synonym name> with the actual name) :
Select Table_owner, Table_name, Db_link
From Dba_synonyms
Where Owner = 'PUBLIC'
And Synonym_name = Upper('<synonym name>')
Select Table_owner, Table_name, Db_link
From Dba_synonyms
Where Owner = 'PUBLIC'
And Synonym_name = Upper('<synonym name>')
ASKER
Thank you
Point them to a valid table
2. Is there anything we are missing will export and import of database?
The tables those synonyms were once pointing to.