Home » RDBMS Server » Backup & Recovery » Taking Export without Materialized Views
Taking Export without Materialized Views [message #228754] Wed, 04 April 2007 01:40 Go to next message
novice_dba
Messages: 1
Registered: April 2007
Junior Member
Hi
Can I take export without materialized views ?
regards
Re: Taking Export without Materialized Views [message #228850 is a reply to message #228754] Wed, 04 April 2007 06:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You have'nt mentioned the version.

Assuming you are using 9i and exp tool, NOPE. You cannot.
Standard workaround is to extract the DDL using DBMS_METADATA
or
take an export with rows=n.
Import with show=y logfile=somelogfile.sql
This will do a dummy import and all ddl into somelogfile.sql.
Re: Taking Export without Materialized Views [message #229187 is a reply to message #228850] Thu, 05 April 2007 15:29 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood the question correctly, OP wants to export ALL BUT materialized views.

Not that I'd know how to do that, but - Mahesh, you are suggesting how to get MV creation script out of export file, aren't you?
Re: Taking Export without Materialized Views [message #229189 is a reply to message #229187] Thu, 05 April 2007 15:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Mahesh, you are suggesting how to get MV creation script out of export file, aren't you?
Nope. Only i was not clear Smile
If one cannot exclude a certain object, options are
extract only the required DDL with DBMS_METADATA
or
Extract all ddl (using show=y) and keep what you want and delete the rest (MV in this case).
or import all and delete MV Smile

[Updated on: Thu, 05 April 2007 15:40]

Report message to a moderator

Re: Taking Export without Materialized Views [message #229190 is a reply to message #229189] Thu, 05 April 2007 15:59 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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
Mahesh Rajendran
Messages: 10672
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).


Previous Topic: Export
Next Topic: Off site backup for Oracle 10g XE
Goto Forum:
  


Current Time: Fri Dec 09 23:23:44 CST 2016

Total time taken to generate the page: 0.08840 seconds