|
|
|
|
Re: Taking Export without Materialized Views [message #229190 is a reply to message #229189] |
Thu, 05 April 2007 15:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
This is how I saw it (I'll draw a little bit as I'm not too good with words): Scott has one huge (relatively speaking, of course, when compared to its tables) materialized view:SQL> connect scott/tiger
Connected.
SQL> create materialized view mv_ocit refresh on demand as
2 select * From kriz.tob_ocit;
Materialized view created.
SQL> exit As we export Scott's schema, materialized view also gets exported and - as MV takes space (as opposed to an ordinary view) - export file is also large:>exp scott/tiger@ora10 file=scott_mv.dmp
<snip>
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table MV_OCIT 55723 rows exported
. . exporting table SALGRADE 5 rows exported
</snip> Now let's drop that MV and do another export:SQL> drop materialized view mv_ocit;
Materialized view dropped.
SQL> exit
>exp scott/tiger@ora10 file=scott_no_mv.dmp
<snip>
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
</snip> So, if we compare two export files, difference is obvious:C:\Documents and Settings\sdominkovic>dir scott*.dmp
05.04.2007 22:42 3.786.752 scott_mv.dmp
05.04.2007 22:43 20.480 scott_no_mv.dmp
Now, I *guess* OP would like to get rid of MV's in order to preserve space (for example, he/she burns exports onto a CD which is only ~700 MB in size). On the other hand, you think that he'd like to grasp DDL (either all but MV or vice versa).
I don't know his/her real reason for such a requirement; perhaps we'll find it if OP decides to reply.
|
|
|
Re: Taking Export without Materialized Views [message #229191 is a reply to message #229190] |
Thu, 05 April 2007 16:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/42800.jpg) |
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
Well, if that is the intention, there is a limited workaround.
Dynamically build a list of tables (excluding the materialized view), use USER MODE export and parfile option.
An second export with rows=n will take care of all other objects (if anything is left behind).
|
|
|