I have been able to successfully recompile most views. However I did this by disabling dependant views. Now I need to list all disabled views and enable them. How can I do this?

asked 27 Mar '15, 01:04

LoganTennis's gravatar image

LoganTennis
1217712
accept rate: 100%


The view status is listed in the system catalog in SYSOBJECT (and not in SYSVIEW, as I had expected...).

A for loop is very handy for listing database objects and manipulate them, as it does hold the cursor open although the DDL statements within the loop do an automatic commit.

The following should do:

begin
   for forLoop as crs cursor for
      select ST.table_name as strViewName
      from systab ST inner join sysobject SO on ST.object_id = SO.object_id
      where ST.table_type = 21 -- i.e. a regular view
          and SO.status = 4 -- a disabled view
   do
      begin
         declare strStmt varchar(1000);
         set strStmt = 'ALTER VIEW ' || strViewName || ' ENABLE;';
         message  strStmt to client; -- test to check the ALTER VIEW statement
         execute immediate strStmt;
      end;
   end for;
end;

Note: If your view have different owners, you will have to need to add the owner name to the ALTER VIEW statement (and therefore extract it from the system catalog like-wise).


A further note: If these views are dependent on another, you might need to apply the ALTER VIEW statements in such an order that those views that are dependent on other disabled views are enabled after the latter. (Some ordering by SYSDEPENDENCY will be of help.)

permanent link

answered 27 Mar '15, 05:01

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 27 Mar '15, 05:11

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25

question asked: 27 Mar '15, 01:04

question was seen: 445 times

last updated: 27 Mar '15, 05:11