datapump full import EXCLUDING SYS and SYSTEM

[baseballGuy]

Hi Tim,

when we do full datapump import EXCLUDING SYS,SYSTEM, will public synonyms, roles, DBlinks will be created?

thanks

[Tim...]

Hi.

By excluding SYS and SYSTEM you will exclude all objects they own.

public synonyms : Owned by PUBLIC, so should not be affected.
private synonyms: Those owned by SYS or SYSTEM will be lost.
roles : Roles don't have an owner, so I would expect them to be included.
dblinks : Any owned by SYS and SYSTEM will be lost.

There is a simple way to check this:

- Do a full export, but only include the object type you want to check.

Code: Select all

expdp system/password full=Y include=synonym directory=DATA_PUMP_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

- Do a full import, and exclude SYS and SYSTEM the schemas. Use the sqlfile parameter to push the DDL to a file.

Code: Select all

impdp system/password full=y exclude=schema:\"IN \(\'SYS\', \'SYSTEM\'\)\" directory=DATA_PUMP_DIR dumpfile=DB11G.dmp logfile=impdpDB11G.log sqlfile=sqlfileDB11G.sql

- Check the contents of the file to see if the objects you expect to imported have been.

If you used the command above to test synonyms, you would see that all public synonyms were written to the sqlfile, along with any private synonyms owned by users other than SYS and SYSTEM.

To test the roles, you would use these:

Code: Select all

expdp system/password full=Y include=role directory=DATA_PUMP_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log
impdp system/password full=y exclude=schema:\"IN \(\'SYS\', \'SYSTEM\'\)\" directory=DATA_PUMP_DIR dumpfile=DB11G.dmp logfile=impdpDB11G.log sqlfile=sqlfileDB11G.sql

If you used them you would see the roles are present.

Cheers

Tim...