0

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.

  • exclude seems to be what you are looking for: docs.oracle.com/cd/E11882_01/server.112/e22490/… – a_horse_with_no_name Sep 8 '15 at 10:44
  • First check the documentation (or google) or use expdp help=y. – Marco Sep 8 '15 at 10:49
  • If you are moving a large database, and for some reason you can't use RMAN to clone the database, you can try transportable tablespaces. You would just specify all of the table spaces that you need. so you should be able to skip the system, sysaux, etc. Depending on the version of Oracle that you are using, you should be able to change the endian of the export. you just need to look up the instructions for the OS that you are exporting from and to. – Gandolf989 Sep 8 '15 at 12:46
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', ..., ...)"
  • 1
    You must not exclude the SYS schema because it is already excluded, Only the SYSTEM schema hast to be excluded. – miracle173 Sep 8 '15 at 16:07
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.