Oracle 11g online redefinition table to view

Question

This may be impossible, but I'd hoped to see if there is an approachable way to run an online replacement of a table with a view.

For online table restructuring like partitioning, etc. DBMS_REDEFINITION works great. But I'd like to replace a table with a (materialized) view, so DBMS_REDEFINITION appears to be unsuitable.

I have no constraints, dependencies or mutating dml, etc. to worry about during the rename; I would only like to keep the target SELECTable when replacing the table with a view. A trumped-up example is below.

  CREATE TABLE SCI_FI_MOVIE (
  SCI_FI_MOVIE_ID NUMBER(10, 0)        NOT NULL PRIMARY KEY,
  NAME            VARCHAR2(100) UNIQUE NOT NULL,
  DIRECTOR        VARCHAR2(100)        NOT NULL,
  REVIEW_SCORE    NUMBER(1, 0) CHECK ( REVIEW_SCORE IN (1, 2, 3, 4, 5))
);

CREATE TABLE NO_SCORES_SCI_FI_MOVIE (
  SCI_FI_MOVIE_ID NUMBER(10, 0)        NOT NULL PRIMARY KEY,
  NAME            VARCHAR2(100) UNIQUE NOT NULL,
  DIRECTOR        VARCHAR2(100)        NOT NULL
);

CREATE MATERIALIZED VIEW KUBRICK_SABOTAGE
(SCI_FI_MOVIE_ID, NAME, DIRECTOR, REVIEW_SCORE)
REFRESH COMPLETE ON COMMIT
AS
  SELECT
    SCI_FI_MOVIE_ID,
    NAME,
    DIRECTOR,
    CASE WHEN DIRECTOR = 'KUBRICK'
      THEN 5
    ELSE 2 END AS REVIEW_SCORE
  FROM NO_SCORES_SCI_FI_MOVIE;

INSERT INTO SCI_FI_MOVIE VALUES (1, 'Apollo 13', 'HOWARD', 5);
INSERT INTO SCI_FI_MOVIE VALUES (2, '2001: A Space Odyssey', 'KUBRICK', 4);

INSERT INTO NO_SCORES_SCI_FI_MOVIE VALUES (1, 'Apollo 13', 'HOWARD');
INSERT INTO NO_SCORES_SCI_FI_MOVIE VALUES (2, '2001: A Space Odyssey', 'KUBRICK');

COMMIT;

-- THEN WHAT STEPS TO REPLACE TABLE WITH VIEW?

In this example I'd like to end up with the MV named SCI_FI_MOVIE and the TABLE SCI_FI_MOVIE renamed to SCI_FI_MOVIE_TEMP or whatever pending its removal. There isn't any requirement for the MV to exist prior to replacing the original table, if the replacement can be done atomically

I'd like to avoid any interruption or compromise to object name resolution (CREATE PUBLIC SYNONYM then renaming original won't work here)

Is there a clean no-downtime way to do this?
I am free to disable logging, read-only etc. anything as needed; the only goal is to prevent "ORA-00942: table or view does not exist" during the dictionary switch. I'm on 11gR2 but would welcome in 12c solutions as well.

Thanks so much for your thoughts

Answer 1

You don't need to do on-line redefinition or any clever; what you're trying to do is built in via the ON PREBUILT TABLE clause:

The ON PREBUILT TABLE clause lets you register an existing table as a preinitialized materialized view. This clause is particularly useful for registering large materialized views in a data warehousing environment. The table must have the same name and be in the same schema as the resulting materialized view.

If the materialized view is dropped, then the preexisting table reverts to its identity as a table.

So you can just do:

CREATE MATERIALIZED VIEW SCI_FI_MOVIE
(SCI_FI_MOVIE_ID, NAME, DIRECTOR, REVIEW_SCORE)
ON PREBUILT TABLE
REFRESH COMPLETE ON COMMIT
AS
  SELECT
    SCI_FI_MOVIE_ID,
    NAME,
    DIRECTOR,
    CAST(CASE WHEN DIRECTOR = 'KUBRICK'
      THEN 5
    ELSE 2 END AS NUMBER(1,0)) AS REVIEW_SCORE
  FROM NO_SCORES_SCI_FI_MOVIE;

Materialized view SCI_FI_MOVIE created.

The CAST(... AS NUMBER(1,0)) is needed to make the generated data type match the underlying table.

The table is locked while the MV is built (which is pretty much instant anyway as there is no data collection or creation) so queries against it while that is happening will just block briefly.

The view will have the original table values:

select * from SCI_FI_MOVIE;

SCI_FI_MOVIE_ID NAME                           DIRECTOR   REVIEW_SCORE
--------------- ------------------------------ ---------- ------------
              1 Apollo 13                      HOWARD                5
              2 2001: A Space Odyssey          KUBRICK               4

... until it's refreshed, which (in this example) will be on next commit:

INSERT INTO NO_SCORES_SCI_FI_MOVIE VALUES (3, 'Star Wars', 'LUCAS');

1 row inserted.

COMMIT;

select * from SCI_FI_MOVIE;

SCI_FI_MOVIE_ID NAME                           DIRECTOR   REVIEW_SCORE
--------------- ------------------------------ ---------- ------------
              1 Apollo 13                      HOWARD                2
              2 2001: A Space Odyssey          KUBRICK               5
              3 Star Wars                      LUCAS                 2

---

For a normal (non-materialized view) you could do a bit of shuffling to achieve the same thing, as long as you can create a public synonym:

CREATE TABLE SCI_FI_MOVIE_TMP_TAB AS SELECT * FROM SCI_FI_MOVIE;

CREATE VIEW SCI_FI_MOVIE_TMP_VIEW AS SELECT * FROM SCI_FI_MOVIE_TMP_TAB;

CREATE PUBLIC SYNONYM SCI_FI_MOVIE FOR SCI_FI_MOVIE_TMP_VIEW;

ALTER TABLE SCI_FI_MOVIE RENAME TO SCI_FI_MOVIE_OLD;

CREATE VIEW SCI_FI_MOVIE AS
  SELECT
    SCI_FI_MOVIE_ID,
    NAME,
    DIRECTOR,
    CAST(CASE WHEN DIRECTOR = 'KUBRICK'
      THEN 5
    ELSE 2 END AS NUMBER(1,0)) AS REVIEW_SCORE
  FROM NO_SCORES_SCI_FI_MOVIE;

DROP PUBLIC SYNONYM SCI_FI_MOVIE;

DROP VIEW SCI_FI_MOVIE_TMP_VIEW;

DROP TABLE SCI_FI_MOVIE_TMP_TAB;

DROP TABLE SCI_FI_MOVIE_OLD;

This relies on how Oracle resolves schema object references. When you rename the original table it can no longer find an object with that name in the current schema (namespace), and looks for a public synonym, and happily uses that. When the view is created that takes precedence over the public synonym again.