OraFAQ Forum: Performance Tuning » Multiple schemas against single schema

Home » RDBMS Server » Performance Tuning » Multiple schemas against single schema - ERP (Oracle 10g)

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

Multiple schemas against single schema - ERP [message #382751]

Sat, 24 January 2009 04:40

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

Report message to a moderator

Re: Multiple schemas against single schema - ERP [message #382851 is a reply to message #382751]

Sun, 25 January 2009 19:49

rleishman
Messages: 3728
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

Report message to a moderator

Re: Multiple schemas against single schema - ERP [message #382940 is a reply to message #382751]

Mon, 26 January 2009 08:34

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

Report message to a moderator

Re: Multiple schemas against single schema - ERP [message #382948 is a reply to message #382940]

Mon, 26 January 2009 10:40

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Multiple schemas against single schema - ERP [message #383003 is a reply to message #382751]

Mon, 26 January 2009 23:41

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

Report message to a moderator

Re: Multiple schemas against single schema - ERP [message #383107 is a reply to message #383003]

Tue, 27 January 2009 04:53

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 12:21:37 CST 2024