Home » RDBMS Server » Performance Tuning » Multiple schemas against single schema - ERP (Oracle 10g)
Multiple schemas against single schema - ERP [message #382751] Sat, 24 January 2009 04:40 Go to next message
rushimails
Messages: 24
Registered: November 2008
Location: Mumbai
Junior Member
Hi Experts

What are the advantages of maintaining multiple schemas in ERP.
My query, in case of ERP mutiple schemas are maintained for individual modules.
e.g Sales, Finance, Inventory etc have individual schemas.

What is the advantage of such architecture.
Why should we avoid a single Schema with all module tables within it.

Also what is the ideal way to maintain mutiple schemas and then how should the Rollback segments, Datafiles etc be managed.

Hope my question is clear.

Rgds
Rushi
Re: Multiple schemas against single schema - ERP [message #382851 is a reply to message #382751] Sun, 25 January 2009 19:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Schemas help with two things:
1. Security: people connecting to one schema cannot access anything outside the schema unless they granted privs.

2. Namespaces. Two tables in different schemas can have the same name. Every table in a single schema must be named differently.

Ross Leishman
Re: Multiple schemas against single schema - ERP [message #382940 is a reply to message #382751] Mon, 26 January 2009 08:34 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
I would also say that entities in each schema shouldn't be in relation with the entities in the others.

If you would have any kind of relation between two entities you shouldn't use different schemas for them.

So don't exaggerate!!

Bye Alessandro
Re: Multiple schemas against single schema - ERP [message #382948 is a reply to message #382940] Mon, 26 January 2009 10:40 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I wouldn't agree with that; there certainly are cases where data in one schema depend on data in another schema, are closely related, or share the same information.

One example may be financial applications which share account numbers; the other ones are difficult to explain as I'm uncertain in English translation of Croatian terms; dictionary helps, but ... these phrases sound too awkward so I'd rather not do that, although I could name a few cross-schema relationships which exist in our company.

I could name a few drawbacks of such a design as well, but I still don't think that it is something that should be avoided.

Quote:
So don't exaggerate!
Yes, I agree with this one.
Re: Multiple schemas against single schema - ERP [message #383003 is a reply to message #382751] Mon, 26 January 2009 23:41 Go to previous messageGo to next message
rushimails
Messages: 24
Registered: November 2008
Location: Mumbai
Junior Member
After reading through Oracle APPS Architecture following was noticed:
1. Individual schemas for all Modules (FA, SD, PROD etc)
2. Parent schema APPS which has synonyms for all the Objects in the respective module wise schemas.
3. All Program Units in the APPS Schema.

SO I guess this is a better way of doing it. But I am still looking out for all the major advantages with this.

Rgds
Rushi
Re: Multiple schemas against single schema - ERP [message #383107 is a reply to message #383003] Tue, 27 January 2009 04:53 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
That looks like a good design generally.

Significant objects are all in the same schema.

Each module has a link to the resources it needs through synonyms in this case.

Each module has its own privilege settings.

With the right usage this design would make your schema to be more flexible than usual with applications.

Bye Alessandro
Previous Topic: Not able to reduce the optimizer cost after creating index
Next Topic: Stale Stats for Partitoined table
Goto Forum:
  


Current Time: Tue Dec 06 10:04:06 CST 2016

Total time taken to generate the page: 0.16797 seconds