This forum is currently locked. You can't register or post questions at this time. (read more)

datapump full import EXCLUDING SYS and SYSTEM

All posts relating to Oracle database administration.

Moderator: Tim...

baseballGuy
Member
Posts: 30
Joined: Thu Mar 22, 2012 7:08 pm

datapump full import EXCLUDING SYS and SYSTEM

Postby baseballGuy » Mon Apr 16, 2012 2:27 pm

Hi Tim,

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

thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: datapump full import EXCLUDING SYS and SYSTEM

Postby Tim... » Mon Apr 16, 2012 4:37 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 2 guests