Is it possible to drop a view after a certain date or if it older than 4 weeks etc.?

asked 20 Mar '16, 16:15

Rolle's gravatar image

Rolle
379233342
accept rate: 0%

edited 20 Mar '16, 16:21

2

There is no built-in "expiry date" feature, but you could build your own using a CREATE EVENT ... SCHEDULE START TIME '12:01 AM' EVERY 24 HOURS statement.

(21 Mar '16, 07:59) Breck Carter

Just out of curiosity: What is the particular reason your view will need to be dropped (and re-created?) after some time?

Note, the "age" of an object can be found in system table SYS.SYSOBJECT in field "creation_time", such as

select table_name, creation_time
from SYS.SYSTAB ST key join SYS.SYSOBJECT SO
where table_name = 'MyYoundView';
permanent link

answered 22 Mar '16, 04:54

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 22 Mar '16, 04:54

We want a plan to operate a certain time. The best is perhaps not remove it, maybe we should put conditions on it so it does not return any values?

(22 Mar '16, 09:35) Rolle
Replies hidden

Well, we can't tell whether you would like to

  • make the view unavailable (i.e. trying to select from it would return an error) or to
  • make it return an empty result set

outside that certain time. Both are possible, the former for example with the ALTER VIEW ... DISABLE clause, the latter with the help of a condition that returns false/unknown outside the desired interval, say, by filtering based on the current time or by checking the existence of a particular connection variable or by a flag in another table...

(24 Mar '16, 05:27) Volker Barth
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: 20 Mar '16, 16:15

question was seen: 1,447 times

last updated: 24 Mar '16, 05:27