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

Remap schema

user7202581 Newbie
Currently Being Moderated

There are two users in my database

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?

  • 1. Re: Remap schema
    mseberg Guru
    Currently Being Moderated

    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;
    You don't have to do anything to synonyms. When you touch them they become valid.

    Best Regards

  • 2. Re: Remap schema
    phaeus Pro
    Currently Being Moderated
    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.

  • 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.


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