Home » RDBMS Server » Server Utilities » Deploying parts of the DB including users
Deploying parts of the DB including users [message #638622] Tue, 16 June 2015 03:28 Go to next message
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
Re: Deploying parts of the DB including users [message #638623 is a reply to message #638622] Tue, 16 June 2015 03:31 Go to previous messageGo to next message
Littlefoot
Messages: 21501
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.
Re: Deploying parts of the DB including users [message #638624 is a reply to message #638623] Tue, 16 June 2015 03:44 Go to previous messageGo to next message
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.
Re: Deploying parts of the DB including users [message #638626 is a reply to message #638624] Tue, 16 June 2015 03:53 Go to previous messageGo to next message
Littlefoot
Messages: 21501
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).
Re: Deploying parts of the DB including users [message #638627 is a reply to message #638623] Tue, 16 June 2015 04:25 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, 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 Go to previous messageGo to next message
Littlefoot
Messages: 21501
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).
Re: Deploying parts of the DB including users [message #638629 is a reply to message #638627] Tue, 16 June 2015 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66571
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Deploying parts of the DB including users [message #638630 is a reply to message #638629] Tue, 16 June 2015 04:53 Go to previous messageGo to next message
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
Re: Deploying parts of the DB including users [message #638631 is a reply to message #638630] Tue, 16 June 2015 04:57 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
You'll find a useful list of Oracle default users here, Pascal.

HTH
-g
Re: Deploying parts of the DB including users [message #638634 is a reply to message #638631] Tue, 16 June 2015 05:52 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3149
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.
Re: Deploying parts of the DB including users [message #638635 is a reply to message #638634] Tue, 16 June 2015 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66571
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Deploying parts of the DB including users [message #638644 is a reply to message #638624] Tue, 16 June 2015 12:38 Go to previous messageGo to next message
John Watson
Messages: 8012
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.
Re: Deploying parts of the DB including users [message #638649 is a reply to message #638644] Wed, 17 June 2015 00:14 Go to previous message
Littlefoot
Messages: 21501
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.
Previous Topic: Strange issue in SQL Loader
Next Topic: SQL Loader WHEN clause with NULL Values
Goto Forum:
  


Current Time: Mon Sep 23 12:55:07 CDT 2019