Skip to Main Content
  • Questions

  • How to do a EXP full excluding some schemas?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Priscila.

Asked: July 31, 2017 - 12:44 pm UTC

Last updated: July 31, 2017 - 12:52 pm UTC

Version: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hello Oracle Masters,

Thanks a lot for taking time to answer questions.

I know that it is possible to do an export full datapump and use the exclude clause. Since I have space limitations, I´m using original export with exp_pipe.

It´s possible to use the exclude clause in the original export?

If not possible, how to do a full export (EXP) without some schemas?

Should I use a parfile with the 'owner =' option and include all the schemas except the ones that I do not want?

Thanks a lot.


and Connor said...

Yes, you can do that, eg

C:\temp>cat multi_owner.par
owner=scott
owner=hr

C:\temp>exp parfile=multi_owner.par.txt

Export: Release 12.2.0.1.0 - Production on Mon Jul 31 20:50:02 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting foreign function library names for user HR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
. exporting object type definitions for user HR
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                     APP_BRANCH
. . exporting composite partition        BRN_PART_2015
. . exporting subpartition           BRN_SUBPART_JAN15          8 rows exported
. . exporting subpartition           BRN_SUBPART_FEB15          9 rows exported
. . exporting subpartition           BRN_SUBPART_MAR15          9 rows exported
. . exporting subpartition           BRN_SUBPART_APR15          9 rows exported
. . exporting subpartition           BRN_SUBPART_MAY15          8 rows exported
. . exporting subpartition           BRN_SUBPART_JUN15          8 rows exported
. . exporting subpartition           BRN_SUBPART_JUL15          8 rows exported
. . exporting subpartition           BRN_SUBPART_AUG15          8 rows exported
. . exporting subpartition           BRN_SUBPART_SEP15          8 rows exported
. . exporting subpartition           BRN_SUBPART_OCT15          8 rows exported
. . exporting subpartition           BRN_SUBPART_NOV15          8 rows exported
. . exporting subpartition           BRN_SUBPART_DEC15          8 rows exported
. . exporting composite partition        BRN_PART_2016
. . exporting subpartition           BRN_SUBPART_JAN16          8 rows exported
. . exporting subpartition           BRN_SUBPART_FEB16          8 rows exported
. . exporting subpartition           BRN_SUBPART_MAR16          8 rows exported
. . exporting subpartition           BRN_SUBPART_APR16          8 rows exported
. . exporting subpartition           BRN_SUBPART_MAY16          9 rows exported
. . exporting subpartition           BRN_SUBPART_JUN16          9 rows exported
. . exporting subpartition           BRN_SUBPART_JUL16          9 rows exported
. . exporting subpartition           BRN_SUBPART_AUG16          9 rows exported
. . exporting subpartition           BRN_SUBPART_SEP16          8 rows exported
. . exporting subpartition           BRN_SUBPART_OCT16          8 rows exported
. . exporting subpartition           BRN_SUBPART_NOV16          8 rows exported
. . exporting subpartition           BRN_SUBPART_DEC16          8 rows exported

...
...

. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

C:\temp>


Just remember that its not *totally* the same as a "full" export, because that includes the definitions of the "database" as well (tablespaces, users, roles etc etc).

But you could get all that with a datapump export (or old style export) with rows=n or content=metadata

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.