3 Replies Latest reply: Oct 5, 2012 5:56 PM by rp0428 RSS

    Remap schema

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