Drop a view after a certain date?

[former_SQLA_member1694869]

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

[Breck_Carter]

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.

[VolkerBarth · Accepted solution]

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';

[former_SQLA_member1694869 · Accepted solution]

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?

[VolkerBarth · Accepted solution]

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