Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    10

    Unanswered: Synonyms & and two databases

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    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.

  3. #3
    Join Date
    Aug 2008
    Posts
    10
    D'OH ...

    I know better than to leave out info.

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

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Is the missing schema on the new server. If it isn't the synonyms would have failed when they were generated.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2008
    Posts
    10
    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.

  6. #6
    Join Date
    Nov 2002
    Posts
    25
    Create a hot backup and restore it.... All your configurations inside DB will be restored.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •