Forum Stats

  • 3,768,648 Users
  • 2,252,827 Discussions
  • 7,874,668 Comments

Discussions

Droping Materialized View but preserve its local table - Oracle 9i

Exor
Exor Member Posts: 143
edited Nov 11, 2015 3:42PM in General Database Discussions

Hello gurus,

How would one drop a materialized view in 9i but preserve the table?

Thank you,

Tagged:

Best Answer

  • Unknown
    edited Nov 11, 2015 3:20PM Accepted Answer
    How would one drop a materialized view in 9i but preserve the table?

    Dropping the MV will drop the table unless the MV was created on a prebuilt table.

    If you have licensed the PARALLEL option you can use EXCHANGE PARTITION to swap the MV segment to a work table with ONE empty partition.

    Then you can drop the MV and the (now empty) table.

    Then create a standard table and use EXCHANGE PARTITION again to swap in the data segment from the work table.

Answers

  • Unknown
    edited Nov 11, 2015 3:20PM Accepted Answer
    How would one drop a materialized view in 9i but preserve the table?

    Dropping the MV will drop the table unless the MV was created on a prebuilt table.

    If you have licensed the PARALLEL option you can use EXCHANGE PARTITION to swap the MV segment to a work table with ONE empty partition.

    Then you can drop the MV and the (now empty) table.

    Then create a standard table and use EXCHANGE PARTITION again to swap in the data segment from the work table.

  • Exor
    Exor Member Posts: 143
    edited Nov 11, 2015 3:42PM

    Thank you very much,

This discussion has been closed.