Synonyms & and two databases

Opus · #1 (**permalink**) 01-06-09, 17:07

Here the senario:

We upgraded our production database and server from 32 to 64-bit. The testing server is still on 32-bit. The production server database was restored via a data dump.

My problem is that all the synonyms for a particular id are missing in the new production server. I don't want to rewrite hundreds of scripts to fully qualify all the tables and views.

My question: Is there a why to auto-generate the sql to recreate the synonyms in the test db and run it against the production db? I can't spend the time to create 629 synonyms individually.

Thanks.

buckeye234 · #2 (**permalink**) 01-06-09, 17:23

You don't mention Oracle Version or OS and version, but I guess it doesn't matter in this case.

Do you have any tools at your disposal. I would use TOAD to make quick work of this problem.

Otherwise, you could write a query against the DBA_SYNONYMS view in the test database to generate the create synonym commands you need, spool the ouput to a text file and then run this against the production database.

Something along the lines of:

Code:

SELECT 'CREATE SYNONYM '||synonym_name||' for '||table_owner||'.'||table_name||';'
FROM dba_synonyms
WHERE owner = 'user1';

Didn't test, but I think it should be OK.

Opus · #3 (**permalink**) 01-06-09, 17:31

D'OH ...

I know better than to leave out info.

Oracle version: 10.2 64-bit
Server: Windows Server 2003 64-bit

beilstwh · #4 (**permalink**) 01-07-09, 10:24

Is the missing schema on the new server. If it isn't the synonyms would have failed when they were generated.

Opus · #5 (**permalink**) 01-07-09, 10:41

The schema is all there. I ended using the suggestion above to recreate the synonyms. There must be something in the data pump process that is not exporting synonyms. Thanks for everyones help.

tungaw2001 · #6 (**permalink**) 01-14-09, 05:25

Create a hot backup and restore it.... All your configurations inside DB will be restored.

Internet Services

Web Development

Digital Marketing

Consumer Tech