OraFAQ Forum: Server Utilities » Deploying parts of the DB including users

Home » RDBMS Server » Server Utilities » Deploying parts of the DB including users

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Deploying parts of the DB including users [message #638622]

Tue, 16 June 2015 03:28

PASAG
Messages: 3
Registered: June 2015
Location: Germany

Junior Member

Hi all,

am new to the forum and somewhat desperate to find a solution to my problem:

My customer requires me to deploy my 11g Oracle instance without any system related objects. So instead of providing a full database dump I should produce one just containing all relevant objects in the export. I have succeeded exporting (datapump) the relevant schemas, but when importing those in a fresh instance, I get plenty of errors that the users do not exist.

How can I export schemas, grants, public synonyms, procedures, functions, packages and related users? Or perhaps I can do a full export and then partially import that again (I tried it, but again I got errors reg. non exisiting users)?

Thanks in advance for any of your kind help with this matter.

Regards,
Pascal

Report message to a moderator

Re: Deploying parts of the DB including users [message #638623 is a reply to message #638622]

Tue, 16 June 2015 03:31

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Unless I'm wrong, full database export won't export SYS nor SYSTEM so - I'd go with that option.

Report message to a moderator

Re: Deploying parts of the DB including users [message #638624 is a reply to message #638623]

Tue, 16 June 2015 03:44

PASAG
Messages: 3
Registered: June 2015
Location: Germany

Junior Member

Thanks for the input, Littlefoot.

I think it is more a problem with different database versions and the implications they bring along. As for example the creation of the APEX-user account in my instance within the db of the customer caused the local DBA to get hysterical. Also accepting a couple of thousand errors of attempted creations of already existing system objects did not contribute to his general happiness.

Report message to a moderator

Re: Deploying parts of the DB including users [message #638626 is a reply to message #638624]

Tue, 16 June 2015 03:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Data pump (or even traditional export and import utilities) work well between different database versions. There's the matrix which shows which versions are compatible, but - generally speaking - you should perform export with the source database EXP(DP) utility and import with the target database's IMP(DP).

As of installing Apex, I'm not sure why would that upset any DBA. I've installed it quite a few times, no problems whatsoever (and I'm far from being a DBA, not even close to it, so I guess that he/she should do it easily).

Report message to a moderator

Re: Deploying parts of the DB including users [message #638627 is a reply to message #638623]

Tue, 16 June 2015 04:25

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

SYSTEM objects are included. SYS are not. I'll see if I can find the relevant documentation.

FULL export.

I use the TABLE_EXISTS_ACTION=SKIP when doing a full import.

[Edit: link and addendum]

[Updated on: Tue, 16 June 2015 04:31]

Report message to a moderator

Re: Deploying parts of the DB including users [message #638628 is a reply to message #638627]

Tue, 16 June 2015 04:37

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I see; thank you, Gareth. I apologize regarding my SYS and SYSTEM exporting statement; it appears that I was 50% wrong. Or was I 50% right? Can't decide (thought, I'm most probably more wrong than right).

Report message to a moderator

Re: Deploying parts of the DB including users [message #638629 is a reply to message #638627]

Tue, 16 June 2015 04:50

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And also use EXCLUDE parameter to exclude the schemes you don't want to export or import.

Report message to a moderator

Re: Deploying parts of the DB including users [message #638630 is a reply to message #638629]

Tue, 16 June 2015 04:53

PASAG
Messages: 3
Registered: June 2015
Location: Germany

Junior Member

Michel Cadot wrote on Tue, 16 June 2015 11:50

And also use EXCLUDE parameter to exclude the schemes you don't want to export or import.

So I should just do the full expdp and then exclude the schemes of the system users? Sounds reasonable... I will try that.
Thanks Smile

Report message to a moderator

Re: Deploying parts of the DB including users [message #638631 is a reply to message #638630]

Tue, 16 June 2015 04:57

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

You'll find a useful list of Oracle default users here, Pascal.

HTH
-g

Report message to a moderator

Re: Deploying parts of the DB including users [message #638634 is a reply to message #638631]

Tue, 16 June 2015 05:52

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I like Michel's suggestion about EXCLUDE. The only issue I find with the INCLUDE/EXCLUDE commands is that when the data types are same for the objects in the list, Oracle implicitly applies "AND" condition. You could see this topic to know what I mean.

Report message to a moderator

Re: Deploying parts of the DB including users [message #638635 is a reply to message #638634]

Tue, 16 June 2015 05:57

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Here there is only one type: SCHEMA and one operation: EXCLUDE, so AND is what we need.

Report message to a moderator

Re: Deploying parts of the DB including users [message #638644 is a reply to message #638624]

Tue, 16 June 2015 12:38

John Watson
Messages: 8966
Registered: January 2010
Location: Global Village

Senior Member

PASAG wrote on Tue, 16 June 2015 09:44

Thanks for the input, Littlefoot.

I think it is more a problem with different database versions and the implications they bring along. As for example the creation of the APEX-user account in my instance within the db of the customer caused the local DBA to get hysterical. Also accepting a couple of thousand errors of attempted creations of already existing system objects did not contribute to his general happiness.

Your DBA is correct to get hysterical regarding APEX. You should not attempt to import the APEX schemas, you need to install them, and then import (using APEX import, not impdp) the workspace(s) and application(s). You import only the parsing schemas tat owen te data. I think that is how I've usually done it.

Report message to a moderator

Re: Deploying parts of the DB including users [message #638649 is a reply to message #638644]

Wed, 17 June 2015 00:14

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

(Not that it matters, but) I got it wrong; it never crossed my mind that DBA's hysteria is caused by importing Apex. I thought that it comes from installing it.

I'd rather keep my mouth shut & go away from the keyboard.

Report message to a moderator

Previous Topic:	Strange issue in SQL Loader
Next Topic:	SQL Loader WHEN clause with NULL Values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:22:25 CST 2025