OraFAQ Forum: Backup & Recovery » Taking Export without Materialized Views

Home » RDBMS Server » Backup & Recovery » Taking Export without Materialized Views

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Taking Export without Materialized Views [message #228754]

Wed, 04 April 2007 01:40

novice_dba
Messages: 1
Registered: April 2007

Junior Member

Hi
Can I take export without materialized views ?
regards

Report message to a moderator

Re: Taking Export without Materialized Views [message #228850 is a reply to message #228754]

Wed, 04 April 2007 06:59

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: Taking Export without Materialized Views [message #229187 is a reply to message #228850]

Thu, 05 April 2007 15:29

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: Taking Export without Materialized Views [message #229189 is a reply to message #229187]

Thu, 05 April 2007 15:39

Mahesh Rajendran
Messages: 10708
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

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Taking Export without Materialized Views [message #229191 is a reply to message #229190]

Thu, 05 April 2007 16:07

Mahesh Rajendran
Messages: 10708
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).

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 20:10:51 CST 2024