Re: Can an MV be converted into a "normal" table?

Just to prove it works:
 
SQL> drop materialized view yarpno_mv preserve table;
 
Materialized view dropped.
 
SQL> desc yarpno_mv

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ----------------------------------------------
----------
 SMING                                                                               NOT NULL NUMBER
 ALROO                                                                                        VARCHAR2(12)
 REPNART                                                                                      VARCHAR2(8)
 
SQL>
SQL> select * from yarpno_mv;
 
     SMING ALROO        REPNART
---------- ------------ --------
       601 Smeezo 1     Twe1
       602 Smeezo 2     Twe2
       603 Smeezo 3     Twe3
       604 Smeezo 4     Twe4
       605 Smeezo 5     Twe5
       606 Smeezo 6     Twe6
       607 Smeezo 7     Twe7
       608 Smeezo 8     Twe8
       609 Smeezo 9     Twe9
       610 Smeezo 10    Twe10
       611 Smeezo 11    Twe11
...
     SMING ALROO        REPNART
---------- ------------ --------
      1074 Smeezo 474   Twe474
      1075 Smeezo 475   Twe475
      1076 Smeezo 476   Twe476
      1077 Smeezo 477   Twe477
      1078 Smeezo 478   Twe478
      1079 Smeezo 479   Twe479
      1080 Smeezo 480   Twe480
      1081 Smeezo 481   Twe481
      1082 Smeezo 482   Twe482
      1083 Smeezo 483   Twe483
      1084 Smeezo 484   Twe484
 
     SMING ALROO        REPNART
---------- ------------ --------
      1085 Smeezo 485   Twe485
      1086 Smeezo 486   Twe486
      1087 Smeezo 487   Twe487
      1088 Smeezo 488   Twe488
      1089 Smeezo 489   Twe489
      1090 Smeezo 490   Twe490
      1091 Smeezo 491   Twe491
      1092 Smeezo 492   Twe492
      1093 Smeezo 493   Twe493
      1094 Smeezo 494   Twe494
      1095 Smeezo 495   Twe495
 
     SMING ALROO        REPNART
---------- ------------ --------
      1096 Smeezo 496   Twe496
      1097 Smeezo 497   Twe497
      1098 Smeezo 498   Twe498
      1099 Smeezo 499   Twe499
      1100 Smeezo 500   Twe500
 
500 rows selected.
 
SQL>
SQL> drop table yarpno_mv purge;
 
Table dropped.
 
SQL>
 

David Fitzjarrell




On Friday, November 15, 2013 1:01 PM, Andreas Piesk <a.piesk_at_gmx.net> wrote:
  
On 15.11.2013 20:34, Rich wrote:

>
> Subject nearly says it all:
> Can an MV be directly (not CTAS) converted into a "normal" table?
>

yes, it can:

drop materialized view my_mv preserve table;

-ap
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l