Friday 8 March 2013

Alter Materialized View ( for start time)

Note: Start Time was 9:20AM, but to 11AM.

declare
  no_primary_key exception;
  PRAGMA EXCEPTION_INIT (no_primary_key, -12014);
begin
  -- Oracle does not make it easy to reliably determine if a MV is
  -- created with the PRIMARY KEY clause or not.  So we're going to
  -- assume it does, but if that fails, we'll try again without it.
  execute immediate 'ALTER MATERIALIZED VIEW Schema_name.mv_name
 REFRESH FORCE
 ON DEMAND
 START WITH TO_DATE(''08-Mar-2013 11:00:00'',''dd-mon-yyyy hh24:mi:ss'')
 NEXT SYSDATE + 1/24
 WITH PRIMARY KEY';
exception
  when no_primary_key then
    execute immediate 'ALTER MATERIALIZED VIEW schema_name.mv_name
REFRESH FORCE
 ON DEMAND
 START WITH TO_DATE(''08-Mar-2013 11:00:00'',''dd-mon-yyyy hh24:mi:ss'')
 NEXT SYSDATE + 1/24';
  when others then
    raise;
end;

No comments:

Post a Comment