Export pump all schemas except Sytem related schemas

Question

I have around 111 schemas in one database instance and have to export all the schemas except the system related schemas to a new instance. Is there any way as to exclude system related schemas while using expdp as it would not be easy to write all the schema name in schema paramter of expdp.

Answer 1

As @a_horse_with_no_name mentioned, you can use the exclude parameter to limit the objects to be exported.

It's very simple to use and it supports in/ like... operators.

Usage example:

expdp username/password directory=datapump dumpfile=x.dmp logfile=y.log full=y exclude=schema:"in ('SYS', 'SYSTEM', ..., ...)"

Answer 2

A schema export of 111 schemas is not the same as a full export that excludes all but this 111 schemas. The full export contains roles, public synonyms, datafiles and a lot of other things.

So make a schema export of these 111 schemas. Use a parameterfile to specify your schema names.

expdp user/password@connect parfile=parameterfile

Within a parameter file, a comma is implicit at every newline character so you do not have to enter commas at the end of each line. If you have a long line that wraps, such as a long table name, enter the backslash continuation character (\) at the end of the current line to continue onto the next line.

The query

select distinct owner
from dba_objects
order by owner

will retrieve all users tha have at least one object, the query

select username
from dba_users
order by username

will retrieve all users from the database.

Edit one of this lists and paste it into the parameter file

The parameter file contains

...
SCHEMAS=(
schema1,
schema2,
...
schemaN
)
...

For the import you can use the

FULL=YES

parameter. This will import everything from the dump of the schema export

Answer 3

Instead of excluding the system-related schemas on the export, and assuming your new instance only contains system-related schemas before the import, you could just exclude them on import:

impdp username/password exclude=schema:\"IN \(SELECT username FROM all_users\)\"