Equivalent to sp_refreshview in oracle

Does Oracle has something like sp_refreshview on SQL Server?

Thanks in adva开发者_如何学Cnce, Goran

In Oracle there is an equivalent, but it's not necessary to use it. Here is an example:

A table:

SQL> create table t (id,name)
  2  as
  3  select 1, 'StackOverflow' from dual
  4  /

Table created.

A view:

SQL> create view v
  2  as
  3  select id
  4       , name
  5    from t
  6  /

View created.

Which is valid:

SQL> select *
  2    from v
  3  /

        ID NAME
---------- -------------
         1 StackOverflow

1 row selected.

SQL> select status
  2    from user_objects
  3   where object_name = 'V'
  4  /

STATUS
-------
VALID

1 row selected.

Now do something to the underlying table:

SQL> alter table t add (description varchar2(100))
  2  /

Table altered.

Since Oracle stores dependencies (see the DBA/ALL/USER_DEPENDENCIES views), the view is marked as invalid:

SQL> select status
  2    from user_objects
  3   where object_name = 'V'
  4  /

STATUS
-------
INVALID

1 row selected.

But you can still select from it. Oracle tries to execute it anyway, even if it knows the view is marked INVALID:

SQL> select *
  2    from v
  3  /

        ID NAME
---------- -------------
         1 StackOverflow

1 row selected.

And by selecting from the view, it has marked the view VALID again:

SQL> select status
  2    from user_objects
  3   where object_name = 'V'
  4  /

STATUS
-------
VALID

1 row selected.

You can also compile the view, to be sure the view is valid in advance:

SQL> alter table t add (description2 varchar2(100))
  2  /

Table altered.

SQL> select status
  2    from user_objects
  3   where object_name = 'V'
  4  /

STATUS
-------
INVALID

1 row selected.

SQL> alter view v compile
  2  /

View altered.

"Compiling" the view marks it as valid:

SQL> select status
  2    from user_objects
  3   where object_name = 'V'
  4  /

STATUS
-------
VALID

1 row selected.

If the change affects the view in such a way that the underlying query cannot execute anymore:

SQL> alter table t drop column name
  2  /

Table altered.

SQL> select status
  2    from user_objects
  3   where object_name = 'V'
  4  /

STATUS
-------
INVALID

1 row selected.

Then you'll get a runtime error:

SQL> select *
  2    from v
  3  /
  from v
       *
ERROR at line 2:
ORA-04063: view "OWNER.V" has errors


SQL> select status
  2    from user_objects
  3   where object_name = 'V'
  4  /

STATUS
-------
INVALID

1 row selected.

Hope this helps.

Regards, Rob.

As far as I know not. And I usually see for views like select * from the only the expanded form saved in the database. I think there is no chance that you can add a column to an underlying table and get this column into the view than altering the view.

For the ORACLE users here the example what you can do with SQL-Server (since SQL2008), but the unmodified definition was stored in syscomments in sysbase times as well:

Warning this is T-SQL

create table t1 (i int, c1 int);
create view v1 as select * from t1;
alter table t1 add c2 int;
select * from v1;   -- c2 is not present here
sp_refreshview v1;
select * from v1;   -- c2 is present here 

I guess as Oracle doesn't store the definition, it can't refresh the view.