Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlrvw.sql

Rem
Rem $Header: rdbms/admin/utlrvw.sql /main/1 2009/04/23 07:48:05 nlee Exp $
Rem
Rem utlrvw.sql
Rem
Rem Copyright (c) 2009, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      utlrvw.sql - Recompile all views while still in UPGRADE mode
Rem
Rem    DESCRIPTION
Rem      This script recompiles all views in UPGRADE mode.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    nlee        04/02/09 - Created
Rem

DEFINE UPGRADE_NUMBER =  8289601 
DEFINE UPGRADE_DESC   =  'view recompilation'
SET VERIFY OFF

Rem =======================================================================
Rem Create table registry$history.
Rem Supress error message if table "already exists"
Rem =======================================================================

BEGIN
 execute immediate
  '
   CREATE TABLE registry$history (
        action_time     DATE, /*time stamp */
        action          VARCHAR2(30), /*name of action */
        namespace       VARCHAR2(30),/* upgrade namespace */
        version         VARCHAR(30),/*server version */
        id              NUMBER,/* Upgrade ID */
        comments        VARCHAR2(255)/* comments */
)
 ';
    EXCEPTION
         WHEN OTHERS THEN
             IF sqlcode = -955 THEN NULL;
             ELSE raise;
             END IF;
END;
/

Rem =======================================================================
Rem Check if recompilation script was previously run.
Rem =======================================================================

DECLARE
UPGRADE_ENTRY NUMBER;
BEGIN
  SELECT DISTINCT COUNT(ID) INTO UPGRADE_ENTRY FROM registry$history 
    where ID = '&&UPGRADE_NUMBER';
  if UPGRADE_ENTRY > 0 then
    dbms_output.put_line ('utlrvw.sql script is already applied');
    RETURN;
  end if;

--Rem =======================================================================
--Rem Recompile views
--Rem =======================================================================

DECLARE

CURSOR alter1(objectno number) IS
 SELECT o.obj#,
      'ALTER VIEW' || ' "' || u.name || '"."' || o.name || '" '
       || 'COMPILE '
  FROM obj$ o, user$ u WHERE o.type#=4 AND
  u.user# = o.owner# AND o.obj# in (select unique d_obj# from access$ where types=9)
  AND o.obj# > objectno order by obj#;

ddl_statement varchar2(1000);
 my_err    number;
 objnum    number;

BEGIN

objnum := 0;

OPEN alter1(objnum);

LOOP
   BEGIN
     FETCH alter1 INTO objnum, ddl_statement;
      EXIT WHEN alter1%NOTFOUND;
   EXCEPTION
     WHEN OTHERS THEN
       my_err := SQLCODE;
       IF my_err = -1555 THEN -- snapshot too old, re-execute fetch query
         CLOSE alter1;
         OPEN  alter1(objnum);
         GOTO continue;
       ELSE
         RAISE;
       END IF;
   END;

BEGIN
     -- Issue the Alter view compile statement
     EXECUTE IMMEDIATE ddl_statement;
   EXCEPTION
     WHEN OTHERS THEN
     null; -- ignore, and proceed.
   END;

<<continue>>
   null;

END LOOP;

CLOSE alter1;

END;

END;
/

Rem =======================================================================
Rem Insert values for upgrade into registry.
Rem =======================================================================

INSERT INTO registry$history (action_time, action, id, comments)
        values ( SYSTIMESTAMP, 'VIEW RECOMPILE', &&UPGRADE_NUMBER, '&&UPGRADE_DESC' );

COMMIT;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de