This discussion is archived
3 Replies Latest reply: Oct 5, 2012 3:56 PM by rp0428 RSS

Remap schema

user7202581 Newbie
Currently Being Moderated
Hi,

There are two users in my database
INFAI2X_RO
INFAI2


There are objects and views which are owned by INFAI2X_RO and the base tables are owned by INFAI2.

How can I compile the views and synonyms such that they become valid?

Regards,
VN
  • 1. Re: Remap schema
    mseberg Guru
    Currently Being Moderated
    Hello;

    I believe this will work :
    SET heading off;
    SET feedback off;
    SET echo off;
    SET lines 999;
     
    SPOOL invalid.sql
    
    
    SELECT 'ALTER ' || object_type || ' ' || owner || '.' || object_name
           || ' COMPILE;'
      FROM  dba_objects
     WHERE status = 'INVALID'
       AND owner in ('INFAI2X_RO')
       AND object_type IN ('VIEW');
    
    
    SPOOL off;
     
    SET heading on;
    SET feedback on;
    SET echo on;
     
    @invalid.sql
    You don't have to do anything to synonyms. When you touch them they become valid.

    Best Regards

    mseberg
  • 2. Re: Remap schema
    phaeus Pro
    Currently Being Moderated
    Hello,
    the question is why they are invalid. But you can also start sqlplus on the database server as sys and type @?/rdbms/admin/utlrp

    This will try to compile all invalid objects in the correct order.

    regards
    Peter
  • 3. Re: Remap schema
    rp0428 Guru
    Currently Being Moderated
    Why are you responding to mseberg? I'm pretty certain he knows what the question is.

    If you have feedback for the OP you should use the 'reply' button of OP's post.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points