datapump full import EXCLUDING SYS and SYSTEM

All posts relating to Oracle database administration.

Moderator: Tim...

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
baseballGuy
Member
 
Posts: 30
Joined: Thu Mar 22, 2012 7:08 pm

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
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 2 guests

cron