Solved

objects in valid

Posted on 2008-10-29
11
762 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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 23

Expert Comment

by:paquicuba
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
 
LVL 21

Expert Comment

by:oleggold
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:paquicuba
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now