?
Solved

objects in valid

Posted on 2008-10-29
11
Medium Priority
?
769 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:basirana
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 23

Expert Comment

by:paquicuba
ID: 22832960
1. How to make all this synonyms valid?

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.
0
 
LVL 23

Accepted Solution

by:
David earned 2000 total points
ID: 22832982
As SYS / SYSDBA from the db server (not a client) do this routine cleanup of invalid objects:  sqlplus >  @?\rdbms\admin\utlrp.sql .   Read the code header for its description.  FWIW I use this in a database startup trigger to help minimize any surprises to the users.
 
0
 
LVL 21

Expert Comment

by:oleggold
ID: 22832989
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

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 21

Expert Comment

by:oleggold
ID: 22833002
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

Open in new window

0
 
LVL 23

Expert Comment

by:paquicuba
ID: 22833012
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.
 
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 22833066
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)
0
 

Author Comment

by:basirana
ID: 22833412
If I try to select from public synonym then it giving error

ORA-01775: looping chain of synonyms

Can you please help.

Thanks
0
 
LVL 21

Expert Comment

by:oleggold
ID: 22833553
this command returns very nice results:
SELECT 'CREATE SYNONYM '||owner||'.'||synonym_name||
        ' FOR '||DECODE(db_link,NULL,table_owner||'.')||table_name||DECODE(db_link,NULL,';','@'||db_link||';')
FROM   dba_synonyms
just add
WHERE  owner='&owner'
for Your user
0
 
LVL 21

Expert Comment

by:oleggold
ID: 22833569
oh,of course You can run the script above in the export database,create the spool there and run the actual creation in new
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 22843452
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>')
0
 

Author Closing Comment

by:basirana
ID: 31511258
Thank you
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question